This article explains how to import data from Google Sheets to Treasure Data using embulk-input-google_spreadsheets input plugin.

Prerequisites

• Basic knowledge of Treasure Data.
• Basic knowledge of Embulk
• Embulk and embulk-output-td plugin installed on your machine.

Step 1: Install embulk-input-google_spreadsheets plugin

To install embulk-input-mysql plugin, run the following command:

$ embulk gem install embulk-input-google_spreadsheets

Step 2: Obtain required Google Api credentials.

In order for embulk to connect to the Google Sheets Api there a different authentication options available. This example will use the authorized_user method which requires a json key file. You can get the key file from the Google Api console. The key file contains the credentials to allow connection to Google Sheets api, it has three fields required for the embulk plugin to run.

* client_id
* client_secret
* refresh_token 

You will need to download the key file from an existing Google api project. If you have not created a project you can do so using this wizard provided by Google which will allow you to create a project and turn on the Google sheets API. Google Sheets Api Wizard.

Next, Go to Credentials –> Create Credentials –> OAuth ClientID –> Web Application. Enter a name then ‘Create’. The next screen will show you the client ID and client secret. Copy them as you will need them for the json key file. Completing these steps will provide you with the client_id and client_secret.

The remaining credential you need to get is the refresh token. One way to get the refresh token is to use the Google OAuth 2.0 Playground, available here: Google OAuth 2.0 Playground

First, in the Top right select the Gear Icon and make sure to check the Use your own OAuth credentials. In theOAuth Client IDand theOAuth Client secret`, insert the credentials you got from api console.

From the Api list on the left select the Google Sheets API v4. Then proceed to the Step 2. - Exchange authorization code for tokens. Press the Exchange authorization code for tokens button which will fill the Refresh Token and Access Token fields. The refresh token field is the value you should note down for the json key file embulk will require. With all components of the json key file collected you are ready to begin the embulk configuration. The format of the json key file is:

{
    "client_id": "xxxxxxxxxx",
    "client_secret": "xxxxxxxxxx",
    "refresh_token": "xxxxxxxxxx"
}

Step 3: Create Embulk configuration file

Using your favorite text editor, create the embulk config file defining input(Google Sheets) and ouput(TD) parameters. Below is an example (Note that for simplicity’s sake the required json key file has been used in-line). For further details about additional parameters and other examples are available, refer to Embulk Input Google Sheets. Take note of the fields required in order for the plugin to run e.g. spreadsheets_url and worksheet_title. Fill in the relevant details for the sheet you are trying to import. The same applies for the output section which outputs to Treasure Data. It assumes that you have already created a relevant table in Treasure Data that matches the details used in the config file, i.e. database, would be the database created for the data, table would be the within that database to receive the data.

in:
    type: google_spreadsheets
    auth_method: authorized_user
    json_keyfile:
        content: |
            {
                "client_id": "xxxxxxxxxx",
                "client_secret": "xxxxxxxxxx",
                "refresh_token": "xxxxxxxxxx"
            }
    spreadsheets_url: https://docs.google.com/spreadsheets/d/1xP_ZBA-Or4sJ63Zj99-JreuAFGn7Y2gcUKd1vtwSqwM/edit#gid=0
    worksheet_title: title
    start_row: starting row
    default_timezone: timezone to be used
    null_string: '\N'
    default_typecast: strict
    columns:
        - {name: color, type: string}
        - {name: size, type: string}
        - {name: type, type: string}
out:
    type: td
    apikey: xxxxxxxxxxxx
    endpoint: api.treasuredata.com
    database: dbname
    table: tblname
    time_column: datecolumn
    mode: replace 
    #by default mode: append is used, if not defined. Imported records are appended to the target table with this mode.
    #mode: replace, replaces existing target table
    default_timestamp_format: '%d/%m/%Y'

Step 4: Execute Load Job

Finally, issue the import job by running the following command:

$embulk run load.yml

The run time of the import job will vary depending on the size of the data you are importing. After the job is done, the data should reflect in the TD database and table used in the embulk configuration file.