This article explains how to write job results directly to your Google SpreadSheet.
- Basic knowledge of Treasure Data, including the toolbelt.
- A Google account (for Google Drive).
- Authorized Treasure Data account access to own Google Spreadsheet account
The integration with Google Spreadsheet based on API authentication via email and password was deprecated on April 20, 2015. We upgraded the integration to use the required Oauth2 authentication. In order for the new integration to work, users are required to connect their Treasure Data user account to their Google (Spreadsheet) account as soon as possible.
This can be done following the procedure below:
Visit the ‘My Profile’ page at /users/current.
Click on the ‘Add Account’ button:
You are taken to the Google Spreadsheet Authentication page.
Enter your Google Account credentials:
You will get a list of permission you need to grant access to.
Click on the ‘Accept Button’:
You are taken back to the Treasure Data Console ‘My Profile’ page.
Your Google Account email address should now be listed under ‘Google Account’.
If all goes well you will now be able to use the new Google Spreadsheet integration authentication and you will be able to output the result of a job or query to a Google Spreadsheet.
|The new authentication method requires every user of a Google Spreadsheet Result Export definition to connect their Treasure Data user account to the Google Spreadsheet Account's email address indicated in the Result Export definition.
This means Google Spreadsheet 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 Spreadsheet result export, the execution will fail.
|At the moment writing the result of a job to a Google Spreadsheet 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., please convert them using URL encoding. Please 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 will be created.
The query results are appended to the table. If the table does not exist yet, a new table will be created.
|The result of the query will be appended at the end of the spreadsheet. That mean if for example you have empty rows at the end of the document, APPEND mode will start adding the rows in the query result at the row after the last one visible in the spreadsheet. Please refer to the example below:
Tips & TroubleShootings
Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets. Referrence is here.
Job failed due to An invalid XML character (Unicode: 0x8)
If you encountered the following type of error, please 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 Character.
-- Presto SELECT regexp_replace(xxxxx, '[\b]') FROM table