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.
View Treasure Boxes for sample workflows to export job results to Google Sheets.
Prerequisites
Basic knowledge of Treasure Data, including the TD Toolbelt.
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:
Obtain the Folder Key
You can obtain the Google Drive folder key from the access URL as follows:
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.
In TD Console, go to Integrations Hub > Authentications.
Locate your Google Sheet Authentication object.
Select Click here to connect a new account to redirect to the Authentication in Google.
Select the appropriate account in Google, and with the "View metadata for files in your Google Drive" scope included, select Allow.
In TD Console, open your Google Sheet Authentication again, and select the latest authentication account at the bottom of the dropdown list.
Select Continue and Done to save Authentication.
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.
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. Check for the Treasure Data permissions on Google Drive as follows:
Static IP Address of Treasure Data
The static IP address of Treasure Data is the access point and source of the linkage for this integration. Contact your Customer Success representative or Technical support to determine the static IP address.
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.
Navigate to Integrations Hub > Catalog.
Search and select Google Sheets.
The Authentication dialog opens.
Authenticate the connection by selecting an existing OAuth connection or creating a new one.
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.
After opening the authentication dialog, select the link under "OAuth connection" to create a new connection.
Log into your Google Sheets account in the popup window and grant access to the Treasure Data app.
You are redirected back to the integration dialog pane. Select Google Sheets and then select the new connection from the dropdown menu.
Define your Query
1. Navigate to Data Workbench > Queries.
2. Select New Query.
3. Run the query to validate the result set.
Configure Export Results in Your Data Connection
Navigate to Data Workbench > Queries.
Select an existing query or create a new one.
In the query editor, select the Export Results checkbox.
Select your Google Sheet connection.
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
Create an Activation
Navigate to Audience Studio.
Select a segment.
Select Create Activation.
Next Step: Enter Activation Details
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:
The following is an example of table values after executing truncate mode, with query results in the id column:
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.
The following is an example of table values after executing update mode; from the range A1, the column is updated.
(Optional) Schedule Query Export Jobs
You can use Scheduled Jobs with Result Output to periodically write the output result to a specific target destination.
1. Navigate to Data Workbench > Queries.
2. Create a new query or select an existing query.
3. Next to Schedule, select None.
4. In the drop-down, select one of the following schedule options:
| Drop-down Value | Description |
|---|---|
| Custom cron... | Review 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
Cron Value | Description |
|---|---|
| Run once an hour. |
| Run once a day at midnight. |
| 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 | ‘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. |
| 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. |
| 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. |
5. (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 imported to the specified container destination.
Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications.
(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.
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.