Writing Job Results into Google SpreadSheet

This article explains how to write job results directly to your Google SpreadSheet.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • A Google account (for Google Drive).
  • Authorized Treasure Data account access to own Google Spreadsheet account

Authorization

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:

Step 1

Google Spreadsheet Authentication - Step 1

Step 2

Visit the ‘My Profile’ page at /users/current.
Click on the ‘Add Account’ button: Google Spreadsheet Authentication - Step 2

Step 3

You are taken to the Google Spreadsheet Authentication page.
Enter your Google Account credentials: Google Spreadsheet Authentication - Step 3

Step 4

You will get a list of permission you need to grant access to.
Click on the ‘Accept Button’: Google Spreadsheet Authentication - Step 4

Step 5

You are taken back to the Treasure Data Console ‘My Profile’ page.
Your Google Account email address should now be listed under ‘Google Account’. Google Spreadsheet Authentication - Step 5

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.

Untitled-3
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.
Untitled-3
At the moment writing the result of a job to a Google Spreadsheet owned by another Google account and shared is not possible.

Basic Usage



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 sample_spreadsheet.

$ td query
  --result 'gspreadsheet://user:password@domain.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:password@gmail.com/TDTest/Sheet1

Google Apps
gspreadsheet://kazuki.ohta:password@treasure-data.com/TDTest/Sheet1
Untitled-3
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 sample_worksheet.

$ td sched:create hourly_count_example "0 * * * *" \
  -d testdb \
  --result "gspreadsheet://user:password@domain.com/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:password@domain.com/sample_spreadsheet/sample_worksheet              # replace
gspreadsheet://user:password@domain.com/sample_spreadsheet/sample_worksheet?mode=replace # replace
gspreadsheet://user:password@domain.com/sample_spreadsheet/sample_worksheet?mode=append  # append

REPLACE

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.

APPEND

The query results are appended to the table. If the table does not exist yet, a new table will be created.

Untitled-3
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

File sizes

Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets. Referrence is here.


Last modified: Aug 04 2016 01:15:37 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.