You can export job results from Treasure Data directly to Google Sheets. In Google Sheets, you can then further analyze your data and uncover key business insights.

You can transfer up to 2 million cells for spreadsheets that you created in or converted to Google Sheets. Read more from Google Help.

For sample workflows to export job results to Google Sheets, view Treasure Boxes.

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:

Required

Migrate Your Existing Authentications

Because the Google Sheet V3 March 2020 deprecation, all of 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, you must 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 thed Authentication in Google.

  4. In Google, select the appropriate account, and with “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. If you have saved a query that uses the authentication to export the result, you need to 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 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:


Create Your Connection with TD Console

Create a New Connection

You must create and configure the data connection for export prior to 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.


  3. The Authentication dialog opens.

  4. Authenticate the connection by selecting an existing OAuth connection or creating a new one.

  5. Name your new Google Sheets connection.

Authenticate an Account

Access to Treasure Data Google Sheets integration requires OAuth2 authentication. The authentication requires that you connect 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.


  3. You are redirected back to the integration dialog pane. Select Google Sheets and then select the new connection from the drop-down menu.


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.


  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 Folder Key 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 (leftmost) worksheet.

Upload mode

The mode to modify data in the spreadsheet. See Section 4 for details.

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, a larger payload volume is uploaded, which results in a fewer number of 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 via the user interface. Numbers remain as numbers, but strings might be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

Set Cell to Null

Control the display value of the cell when an invalid value (such as dividing by 0) is encountered.


Modify Data

You can manipulate spreadsheet data in the following ways: Replace, Append, Truncate, and Update.

Replace

This is the default mode. 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.

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 into the table. If the table does not exist yet, a new table is created.

The following is an example of table values prior to 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 into 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 prior to executing update mode.

The following is an example of table values after executing update mode, from the range A1, the column is updated.


Optional Use Scheduled Jobs for Output

You can use Scheduled Jobs with Result Output to periodically write the output result to a target destination that you specify.

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, which is the top left corner of a sheet. For APPEND mode, the range has no effect 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
    ....

Learn more about using data connectors to export data in workflows using CLI.

  • No labels