You can export job results from Arm Treasure Data directly to Google Sheets. In Google Sheets, you can then further analyze your data and uncover key business insights.
Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets. Read more here.
- Basic knowledge of Treasure Data, including the toolbelt.
- A Google account (for Google Drive).
- Authorized Treasure Data account access
Use TD Console to create your connection
You can use the Treasure Data console to configure your connection.
Create a new connection
You must create and configure the data connection to be used during export, prior to running your query. As part of the data connection, you provide authentication to access the integration.
Go to Catalog and search and select Google Sheets.
The following dialog opens:
Access to Treasure Data Google Sheets integration requires OAuth2 authentication. The authentication requires that users manually connect their Treasure Data account to their respective Google account.
To authenticate, complete the following procedure:
Select an existing OAuth connection for Google, or click 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 integration dialog pane. Repeat the first step (
Create a new connection) and choose your new OAuth connection.
Name your new Google Sheets connection.
Configure Export Results in Your Data Connection
- Go to Treasure Data console
- Go to Data Workbench > Queries. You can pick the query language and write your query.
- Select your database and table.
- Create or pick the query that you plan to use to export data.
Next, select Export Results checkbox
and select your Google Sheet connection:
After you select your Google Sheets connection, the dialog pane appears:
You must specify either the spreadsheet_id OR spreadsheet_title. You cannot use both.
- Use Spreadsheet Key: If it is un-checked, the spreadsheet title is used as the key, otherwise, spreadsheet id is used.
- Spreadsheet name: The title of spreadsheet. A new spreadsheet with the title you specify is created if the spreadsheet does not exist. 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: 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 spreadsheet name only.
- Worksheet: The title of sheet in spreadsheet. A new spreadsheet with the title you specify is created if the sheet does not exist. The Name entered is compared "as is" (including spaces) with the worksheet name in Google. If empty, data is exported to the first (left most) worksheet.
- Upload mode: The mode to modify data in spreadsheet. See Section 4 for details.
- Range: The initial cell position that the data is written to.
- Batch rows to upload: The number of rows that are uploaded to a spreadsheet per call. The larger value of batch, the larger the volume of the payload that is uploaded, and the fewer number of calls are needed to complete upload of all data.
- Value Input Option: Specify either RAW to upload data to the spreadsheet directly or USER_ENTERED which leaves the data parsed as if the user typed them into the UI. Numbers remain as numbers, but strings may 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
Ways to modify data
You can manipulate spreadsheet data in the following ways: Replace, Append, Truncate, Update.
This is the default mode. If the table already exists, the rows of the existing table are replaced with the query results. If the table does not exist yet, a new table is created.
The query results are appended to the table. If the table does not exist yet, a new table is created.
The result of the query is appended at the end of the spreadsheet. If, for example, 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 (i.e. 1001st row). Refer to the following example:
If the table already exists, the existing rows are cleared, query results are updated into the table. If the table does not exist yet, a new table is created.
Before executing truncate mode:
After executing truncate mode with query results contain id column:
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.
Before executing update mode:
After executing update mode, from the range A1, the perspective column is updated.
Optional: Use of 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.
Available parameters are as follows:
- spreadsheet_id: String. Spreadsheet key. Required*.
- spreadsheet_title: String. Spreadsheet name. Required*.
- spreadsheet_folder: String. Default is null. Folder id.
- sheet_title: String. Default is null. Worksheet name.
- mode: String(append|replace|truncate|update). Default is append.
- range: String. Default is A1, which is the top left corner of a sheet. For APPEND mode, range has no effect because new rows are appended after the last row.
- rows_threshold: Integer. Default is 50000, maximum is 2000000) Google API has a threshold of 10MB for request payload. This data connector automatically detects which threshold is reached first.
- value_input_option: String(raw|user_entered), RAW). Default is raw.
- set_nil_for_double_nan: Default is true, for example, turn NaN to empty string)
|*Choose either spreadsheet_id OR spreadsheet_title. The two parameters are mutually exclusive.|
Click here for more information on using data connectors in workflow to export data.
Find the Spreadsheet Key
You can obtain the Spreadsheet key from the access URL, as follows:
Find the Folder Key
You can obtain the Google Drive Folder key from the access URL, as follows: