You can write 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.
- Basic knowledge of Treasure Data, including the toolbelt.
- A Google account (for Google Drive).
- Authorized Treasure Data account access
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:
Go to Treasure Data Connections. Search and select Google Sheets.
The following dialog opens:
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 will be redirected back to Treasure Data Connections. Repeat the first step (
Create a new connection) and choose your new OAuth connection.
Name your new Google Sheets Connection.
Create or reuse a query
- Go to Treasure Data console
- Go to Query Editor. You can pick the query language and write your query.
- Select your database and table.
- Access the query that you plan to use to export data.
Configure the connection by specifying the parameters
Select existing connection:
After you select your Google Sheets connection, the Configuration dialog pane appears:
The meaning of parameters:
Note that you must use either the spreadsheet_id OR spreadsheet_title. You cannot use both.
- Use Spreadsheet Key instead: If it is un-checked, the spreadsheet title is used as the key, otherwise spreadsheet id is used.
- Spreadsheet ID: The id of spreadsheet, an error is raised if the ID does not exist.
- Spreadsheet name: The title of spreadsheet. A new spreadsheet with the title you specify is created if the spreadsheet does not exist.
- Worksheet: The title of sheet in spreadsheet. A new spreadsheet with the title you specify is created if the sheet does not exist.
- Upload mode: The mode to modify data in spreadsheet. See the 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 to use Excel formulas in the exported results.
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.
Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets. Read more here.