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.
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 of how to export job results to Google Sheets, view Treasure Boxes.
- Basic knowledge of Treasure Data, including the TD 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. Select Google Sheets from the Catalog and create your 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 (non-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. Used only when using Spreadsheet name. If empty, the destination folder is determined by matching the spreadsheet name. If there is no spreadsheet match, the My Drive root folder is used. Important: In order to use this Folder Key option, you must update authentications what were created before this parameter was added to this data connector. Otherwise, your export jobs fail. See Appendix 4.3.
- 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 (leftmost) 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:
Required: Migrate your Existing Authentications
Due to Google Sheet V3 deprecation scheduled for early March 2020, all your existing authentications must be updated. You must perform the update on your existing authentications before mid of Feb 2020.
Until March 2020, your existing authentications, without the updates, continue to work properly when the Folder Key option is left blank.
The upgrade requires that your existing Google Sheet Authentications apply a new Folder Key option. To use the new 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 new Folder Key option, your data connector no longer works as expected and your export jobs fail.
Complete the update as follows:
- 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 Authentication in Google.
- In Google, select the appropriate account and with “View metadata for files in your Google Drive” scope included, click Allow.
- Go back to the TD Console, open your Google Sheet Authentication again, and select the latest authentication account at the bottom of the dropdown list.
- Click Continue and Done to save the Authentication.
- 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 new one with the authentication.
- Repeat steps 4~6 with the other authentications that use the same Google account.
To check whether your authentication includes the permission, log into Google account and on the browser, access the URL: https://myaccount.google.com/permissions . Check for the Arm Treasure Data permissions on Google Drive as follows: