You can write job results from 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:
Upon logging into the Treasure Data platform, select to “View Old Console”. The old console is v2.
Under your user name, select the “My Profile” option from the drop down menu.
Under Google Accounts, select the “Add Account” option.
Allow Treasure Data to access your Google Account to view and manage your spreadsheets in Google Drive.
You now can use the newer (v3) Google Sheets integration authentication and output the result of a job or query to a Google Sheet by completing the following steps.
Output Results Flow
Go back to Treasure Data’s v3 platform version.
Search and select the ‘Google Sheets’ integration.
Enter the Google account email that you authenticated in v2 Console (see the preceding OAuth flow steps).
Name your new Google Sheets Connection.
Execute your query and ‘Output Results’.
Search and choose your named Google Sheets connection from Step 4 and enter all required configuration parameters.
You are now set to use Treasure Data and Google Sheets direct integration!
|The new authentication method requires every user of a Google Sheets Result Export definition to connect their Treasure Data user account to the Google Sheets Account's email address indicated in the Result Export definition.
This means Google Sheets result export definition associated to jobs and queries can no longer be shared between the various users of a Treasure Data account. If a user attempts to run another user's job or query with a Google Sheets result export, the execution will fail.
|At the moment writing the result of a job to a Google Sheets owned by another Google account and shared is not possible.|
For On-demand Jobs
For on-demand jobs, just add the
--result option to the
td query command. After the job is finished, the results are written into the worksheet
sample_worksheet within the spreadsheet
$ td query --result 'gspreadsheet://user:email@example.com/sample_spreadsheet/sample_worksheet' \ -w -d testdb \ "SELECT code, COUNT(1) AS cnt FROM www_access GROUP BY code"
Here are some example URLs:
Gmail gspreadsheet://kazuki.ohta:firstname.lastname@example.org/TDTest/Sheet1 Google Apps gspreadsheet://kazuki.ohta:email@example.com/TDTest/Sheet1
|If your password includes special characters such as ':', '@', etc., convert them using URL encoding. See Wikipedia's URL Encoding article for details.|
Shown below is a screenshot of the result.
For Scheduled Jobs
For scheduled jobs, just add the
--result option when scheduling a job. After every job run, the results are written into
$ td sched:create hourly_count_example "0 * * * *" \ -d testdb \ --result "gspreadsheet://user:firstname.lastname@example.org/sample_spreadsheet/sample_worksheet" \ "SELECT code, COUNT(1) AS cnt FROM www_access GROUP BY code"
Two Ways to Modify Data (Append/Replace)
You can add or delete data in two ways.
gspreadsheet://user:email@example.com/sample_spreadsheet/sample_worksheet # replace gspreadsheet://user:firstname.lastname@example.org/sample_spreadsheet/sample_worksheet?mode=replace # replace gspreadsheet://user:email@example.com/sample_spreadsheet/sample_worksheet?mode=append # append
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 empty rows at the end of the document, APPEND mode starts adding the rows in the query result at the row after the last one visible in the spreadsheet. Refer to the following example:
Tips and TroubleShooting
Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets. Read more here.
Job failed due to An invalid XML character (Unicode: 0x8)
If you encountered the following type of error, remove the invalid character by Query.
An invalid XML character (Unicode: 0x8) was found in the value of attribute "inputValue" and element is "gs:cell".
In this case, Unicode: 0x8 means the Backspace character in ASCII.
-- Presto SELECT regexp_replace(xxxxx, '[\b]') FROM table