# Google Sheets Export Integration You can export job results from Treasure Data directly to Google Sheets. You can further analyze your data in Google Sheets and uncover critical business insights. You can transfer up to 2 million cells for spreadsheets you created or converted to Google Sheets. Read more from [Google Help](https://support.google.com/drive/answer/37603?hl=en). View Treasure Boxes for sample workflows to export job results to Google Sheets. ## Prerequisites - Basic knowledge of Treasure Data, including the [TD Toolbelt](https://toolbelt.treasuredata.com/). - A Google account (for Google Drive). - Authorized Treasure Data account access. ## Obtain the Spreadsheet Key You can obtain the spreadsheet key from the access URL as follows: ![](/assets/image-20191009-211849.8281ddd6d0505b3314ac2952a3cacda53368e56e6f59b21131295124b97a1349.26c56f91.png) ## Obtain the Folder Key You can obtain the Google Drive folder key from the access URL as follows: ![](/assets/image-20191009-211909.43939ecdd993a1cf7b9c8f3b011f5b9253f7a5aa9fdac8c4732ef3841f45b532.26c56f91.png) ## Requirements ### Migrate Your Existing Authentications Because of the Google Sheet V3 March 2020 deprecation, all your existing authentications must be updated. You must perform the update on your existing authentications before mid-Feb 2020. The upgrade requires that your existing Google Sheet Authentications apply a Folder Key. To use the Folder Key option, select the permission "View metadata for files in your Google Drive" in your Google account. If you do not update your existing Authentications with the Folder Key option, you experience unpredictable behavior. 1. In TD Console, go to **Integrations Hub** > **Authentications.** 2. Locate your Google Sheet Authentication object. 3. Select **Click here to connect a new account** to redirect to the Authentication in Google. 4. Select the appropriate account in Google, and with the "View metadata for files in your Google Drive" scope included, select **Allow**. 5. In TD Console, open your Google Sheet Authentication again, and select the latest authentication account at the bottom of the dropdown list. 6. Select **Continue** and **Done** to save Authentication. 7. Suppose you have saved a query using authentication to export the result. In that case, you must delete the existing result export setting and create a new query with the authentication. 8. Repeat steps 4 - 6 with the other authentications that use the same Google account. To check whether your authentication includes permission, log in to your Google account, and on the browser, access the URL: [https://myaccount.google.com/permissions](https://myaccount.google.com/permissions). Check for the Treasure Data permissions on Google Drive as follows: ![](/assets/google-sheets-export-integration-2024-01-24.8e472dfb5acdaafe5a7931f0a4acc63451ee3e6c300929fef34daa1b7c9f295b.26c56f91.png) ## Static IP Address of Treasure Data Integration If your security policy requires IP whitelisting, you must add Treasure Data's IP addresses to your allowlist to ensure a successful connection. Please find the complete list of static IP addresses, organized by region, at the following link: [https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/](https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/) ## Use the TD Console to Create a Connection ### Create a New Connection You must create and configure the export data connection before running your query. As part of the data connection, you provide authentication to access the integration. 1. Navigate to **Integrations Hub** > **Catalog**. 2. Search and select Google Sheets. ![](/assets/image-20191016-211606.08a0941c9380afef437097f8793d94e6eaac4dce47067e827895ab7f0f9e95b6.26c56f91.png) 3. The Authentication dialog opens. 4. Authenticate the connection by selecting an existing OAuth connection or creating a new one. ![](/assets/image2022-6-29_0-46-30.31d3d6d8e156ca02345d5741fc49f66a759f7453ee3fd0924db2146a7b2ee16f.26c56f91.png) 5. Select a name for your new Google Sheets connection. ### Authenticate an Account Access to Treasure Data Google Sheets integration requires OAuth2 authentication. The authentication requires connecting your Treasure Data account to your respective Google account. If you don't have an existing connection, create a new authentication. 1. After opening the authentication dialog, select the link under "OAuth connection" to create a new connection. 2. Log into your Google Sheets account in the popup window and grant access to the Treasure Data app. ![](https://confluence-20211204-258573741.us-west-2.elb.amazonaws.com/download/attachments/328295/image-20191016-211626.png?version=1&modificationDate=1571260587564&api=v2) 3. You are redirected back to the integration dialog pane. Select Google Sheets and then select the new connection from the dropdown menu. ![](/assets/image-20191016-211634.d9cf8b57211e91b61fc2b4ae9bc866be8b85254782d4aade26a4e3d0219a0498.26c56f91.png) ### Define your Query 1. Navigate to Data Workbench > Queries. 2. Select **New Query**. 3. Run the query to validate the result set. ![](/assets/image2021-9-7_15-1-38.a0ef34a3cfb6035eb6a53a758a755062decbb29fac046d1b24440a76d0662c7a.26c56f91.png) ### Configure Export Results in Your Data Connection 1. Navigate to **Data Workbench** > **Queries.** 2. Select an existing query or create a new one**.** 3. In the query editor, select the **Export Results** checkbox. ![](/assets/image-20191016-211654.c01ebf114bc73d11d17c870994e5e9a16952645c80d4f307b2f2ab2eefded596.26c56f91.png) 4. Select your Google Sheet connection. 5. The Export Results dialog pane opens. Edit the following parameters. | **Parameters** | **Description** | | --- | --- | | You must specify either the ***spreadsheet_id*** OR **spreadsheet_title**. You cannot use both. | | | **Use Spreadsheet Key** | If unchecked, the spreadsheet_title is used as the key. Otherwise, the spreadsheet_id is used. | | **Spreadsheet name** | The title of the new spreadsheet. If multiple spreadsheets with the same title (case-sensitive) exist in the destination folder (given by Folder Key) or any folder. (if FolderKey is empty), the job fails. In this case, specify by spreadsheet key instead. | | **Folder key** | A folder id of the data to be exported. If empty, the destination folder is determined by matching the spreadsheet name, or the *My Drive* root folder is used when unmatched. This parameter is available when using a spreadsheet name only. | | **Worksheet** | The name of a worksheet in the spreadsheet. The name is compared (including spaces) with the Google worksheet name. If empty, data is exported to the first worksheet. | | **Upload mode** | The mode to modify data in the spreadsheet. | | **Range** | The initial cell position to which the data is written. | | **Batch rows to upload** | The number of rows that upload to a spreadsheet per call. The more rows in your batch upload, the more payload volume is uploaded, which results in fewer calls to complete the upload of all data. | | **Value Input Option** | Use RAW to upload data to the spreadsheet directly or USER_ENTERED. USER_ENTERED leaves the data parsed as if the user typed them into the user interface. Numbers remain as numbers, but strings might be converted to numbers, data, etc., following the same rules applied when entering text into a cell via the Google Sheet UI. | | **Set Cell to Null** | Control the display value of the cell when an invalid value (such as dividing by 0) is encountered. | ### Create an Activation Using an Integration You can create an activation to export segment data or stages. Review the steps to [Creating an Activation](https://docs.treasuredata.com/smart/project-product-documentation/create-an-activation) in the Product Documentation portal. ### Example Query ``` SELECT * FROM www_access ``` ### Modify Data You can manipulate spreadsheet data in the following ways: Replace, Append, Truncate, and Update. #### Replace If a table already exists, the rows of the existing table are replaced with the query results. If the table does not exist, a new table is created. This is the default mode. #### Append The query results are appended to the end of the spreadsheet. If the table does not exist yet, a new table is created. For example, if you have the default sheet with 1000 empty rows, APPEND mode starts adding the rows in the query result at the row after the last one visible in the spreadsheet (in this example, the 1001st row). #### Truncate If the table already exists, the existing rows are cleared, and query results are updated in the table. If the table does not exist yet, a new table is created. The following is an example of table values before executing truncate mode: ![](/assets/image-20191016-211740.0906f21c4359278384d8cda93e0e9747d644e82b7fd25825dd04f9584562f87b.26c56f91.png) The following is an example of table values after executing truncate mode, with query results in the id column: ![](/assets/image-20191016-211746.e6348078d15d6339a80744b2460e70cc8cbbf2e5480e8780788c22b58d07514f.26c56f91.png) #### Update If the table already exists, the existing rows are updated in the table by query results. If the table does not exist yet, a new table is created. The following is an example of table values before executing update mode. ![](/assets/image-20191016-211755.a2acbd4579cca5af43883e8425c737e97e1bb444fd1ae8ec61ece83c0e53a30e.26c56f91.png) The following is an example of table values after executing update mode; from the range A1, the column is updated. ![](/assets/image-20191016-211804.4f50d580800a8345f767b22e30a009f76c57de8b786858fa114bb381385889e9.26c56f91.png) ### (Optional) Schedule Query Export Jobs You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify. Treasure Data's scheduler feature supports periodic query execution to achieve high availability. When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored. For example, if the cron schedule is `'0 0 1 * 1'`, then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed. #### Scheduling your Job Using TD Console 1. Navigate to **Data Workbench > Queries** 2. Create a new query or select an existing query. 3. Next to **Schedule**, select None. ![](/assets/image2021-1-15_17-28-51.f1b242f6ecc7666a0097fdf37edd1682786ec11ef80eff68c66f091bc405c371.0f87d8d4.png) 4. In the drop-down, select one of the following schedule options: ![](/assets/image2021-1-15_17-29-47.45289a1c99256f125f4d887e501e204ed61f02223fde0927af5f425a89ace0c0.0f87d8d4.png) | Drop-down Value | Description | | --- | --- | | Custom cron... | Review [Custom cron... details](#custom-cron-details). | | @daily (midnight) | Run once a day at midnight (00:00 am) in the specified time zone. | | @hourly (:00) | Run every hour at 00 minutes. | | None | No schedule. | #### Custom cron... Details ![](/assets/image2021-1-15_17-30-23.0f94a8aa5f75ea03e3fec0c25b0640cd59ee48d1804a83701e5f2372deae466c.0f87d8d4.png) | **Cron Value** | **Description** | | --- | --- | | `0 * * * *` | Run once an hour. | | `0 0 * * *` | Run once a day at midnight. | | `0 0 1 * *` | Run once a month at midnight on the morning of the first day of the month. | | "" | Create a job that has no scheduled run time. | ``` * * * * * - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +---------- month (1 - 12) | | +--------------- day of month (1 - 31) | +-------------------- hour (0 - 23) +------------------------- min (0 - 59) ``` The following named entries can be used: - Day of Week: sun, mon, tue, wed, thu, fri, sat. - Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec. A single space is required between each field. The values for each field can be composed of: | Field Value | Example | Example Description | | --- | --- | --- | | A single value, within the limits displayed above for each field. | | | | A wildcard `'*'` to indicate no restriction based on the field. | `'0 0 1 * *'` | Configures the schedule to run at midnight (00:00) on the first day of each month. | | A range `'2-5'`, indicating the range of accepted values for the field. | `'0 0 1-10 * *'` | Configures the schedule to run at midnight (00:00) on the first 10 days of each month. | | A list of comma-separated values `'2,3,4,5'`, indicating the list of accepted values for the field. | `0 0 1,11,21 * *'` | Configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month. | | A periodicity indicator `'*/5'` to express how often based on the field's valid range of values a schedule is allowed to run. | `'30 */2 1 * *'` | Configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. `'0 0 */5 * *'` configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month. | | A comma-separated list of any of the above except the `'*'` wildcard is also supported `'2,*/5,8-10'`. | `'0 0 5,*/10,25 * *'` | Configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month. | 1. (Optional) You can delay the start time of a query by enabling the Delay execution. ### Execute the Query Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically exported to the specified destination. Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications. (Optional) You can delay the start time of a query by enabling the Delay execution. ## (Optional) Configure Export Results in Workflow Within Treasure Workflow, you can specify the use of this data connector to output data. The available parameters are as follows: - **spreadsheet_id**: String. Spreadsheet key. Required. - **spreadsheet_title**: String. Spreadsheet name. Required. - **spreadsheet_folder**: String. The default is null. Folder id. - **sheet_title**: String. The default is null. Worksheet name. - **mode**: String(append|replace|truncate|update). The default is append. - **range**: String. The default is A1, the top left corner of a sheet. The range has no effect for APPEND mode because new rows are appended after the last row. - **rows_threshold**: Integer. The default is 50000; the maximum is 2000000. Google API has a threshold of 10MB for each request payload. This data connector automatically detects which threshold is reached first. - **value_input_option**: String`(RAW, USER_ENTERED)`. The default is RAW. - **set_nil_for_double_nan**: The default is true; for example, turn NaN into an empty string. Choose **spreadsheet_id** or **spreadsheet_title**. ```yaml timezone: UTC _export: td: database: sample_datasets +td-result-into-target: td>: queries/sample.sql result_connection: my_googlesheet_connector result_settings: spreadsheet_title: value1 mode: replace .... ``` # (Optional) Export Integration Using the CLI You can specify using this data connector to export data within Treasure Workflow. Learn more at [Exporting Data with Parameters](https://docs.treasuredata.com/smart/project-product-documentation/exporting-data-with-parameters).