Complete the following steps to migrate your Google Sheets Legacy connection in the (old) V2 console to a Google Sheets connection in the (current) V3 console. You migrate so that you can use the most current Google Sheets API to output data through an OAuth-based connection.
- Create a new Google Sheet export connector in the V3 (current) console
- Save the existing Legacy connection settings.
- Modify the existing Query (to replace the Legacy connection) in V3 console
Create a Google Sheets export connector in the V3 (current) console
Go to Treasure Data Connections and search and select Google Sheets. (In the old V2 console click Try New Version then go to Connections.
Click on Click here to connect to a new account. You are redirected to Google to login if you haven't logged in yet, or the consent page to grant access to Treasure Data.
Click Allow to enable Treasure Data to access your Google Account to view and manage your spreadsheets in Google Drive.
After you grant access to Treasure Data you are redirected back to Treasure Data Console.
Choose the Google Sheets connector again, then choose OAuth Connection as in the preceding step. You will see an OAuth connection with your account name in the dropdown list. Choose the account you want to use and then proceed to create the connection.
Complete the fields. Click Continue.
Specify connection name and click Done.
Save the settings of the Legacy export connector
Click the google sheet connector. (The default name for the data connector is
google_spreadsheet if the connection was created in the v2 console.)
Copy and save the details of the existing connection to use later in step 3:
Row Batch size.
Note: In V2, you can use either Spreadsheet or Key or could use both. After you migrate to V3, you must choose to use Spreadsheet or Key. You cannot use both.
Click Delete to remove the Legacy connector.
Modify the existing Query (to replace the Legacy connection) in V3 console
In the query, click Output results. Next, you are going to set up the Google Sheets connector.
Find and select the Google Sheet export connector that you created in step 1. Click Create New Connection.
In “Configuration” pane, specify the fields saved in step 2:
Spreadsheet Name or
Spreadsheet Key, and
Batch rows to upload.
Check Output results to... to verify that you are using the created output connection. Click Save.
|It is strongly recommended to create a test spreadsheet and use it for the first data export to verify that exported data looks as expected and the new export does not corrupt existing data. In your test case, choose an alternate “Spreadsheet Name” or “Spreadsheet Key” for your test spreadsheet.|
Limitation Concerning array(bigint) type
The new connector cannot export arrays of type BIGINT. If your query result contains arrays of type BIGINT, you will receive the following error when exporting to Google sheets using the new connector:
“Writing query result with array(bigint) type is not supported.
Please cast it to varchar type explicitly using CAST(... AS..”
This is a limitation that was not present in the legacy connector and only applies to jobs that use the new connector. One possible workaround is to CAST the array to VARCHAR, which allows the export to complete successfully.
Updating Existing Workflows
If you have a workflow that used the Google Sheets connector, you must update the result settings to account for the new connector. The new Google sheets connector uses new field names for the result settings. For example:
- key is changed to spreadsheet_id
- worksheet is changed to spreadsheet_title
Note that after you migrate to V3, you must choose to use either the spreadsheet_id OR spreadsheet_title. You cannot use both.
An example of new workflow result output settings is as follows:
# Use either spreadsheet_id OR spreadsheet_title.
An example of old workflow result output settings is as follows.
Contact us at email@example.com with any questions.