Google Sheets Authentication

Authenticating to Google Sheets

To authenticate with Google Sheets, start by creating a Service Account by going to https://console.cloud.google.com/iam-admin/serviceaccounts.

Keep track of the Service account ID. You will use this when you need to grant access to Google Sheets.

Next, go to the Service account details and click Create private key for your service account.
Set the Key type to be JSON and download the key.

When creating a new Google Sheets Resource in DataSiv, paste the JSON contents of the secret key file into the Service Account field. Make sure to set the Service Account Scopes to ["https://www.googleapis.com/auth/drive"].

Your resource might look something like this:

When writing queries that read or write to existing Google Sheets (such as the query below), make sure to grant access to the Service Account ID to the sheet.

For example, if you had [email protected] as your Service Account ID, and you wanted to grant access to Spreadsheet ID 1r6ytlatXh78rji9uYq4Y8KVggrdpn6kXFol_0cfeCWk, you'd go to https://docs.google.com/spreadsheets/d/1r6ytlatXh78rji9uYq4Y8KVggrdpn6kXFol_0cfeCWk/edit#gid=1618853414
click Share at the top right, and add the email as you would with a regular user.

To enable write access, grant the service account write permissions.

Congratulations! You are now able to read and write from Google Sheets using DataSiv.

Updating existing rows in Google Sheets

When writing back to Google Sheets, there's two main options, adding new rows and updating an existing row.

As this comes up quite often, this guide demonstrates how to update an existing row and column, from a table.

Suppose you're reading from Sheet1!A1:E1000, and you're using the first row as a header, so you're displaying the following query in a table.

To update the Google Sheet, you'll need to reference the current row in a table. However, tables are zero indexed (first row is at index 0), while your grid in Google Sheets starts at row 2 because row 1 is the header. Therefore, you'll use something similar to the following transform to display the data in the table instead:

Finally, in the Google Sheets query that updates the row that a user selected, you'll reference the googleSheetsIndex column of the Selected Row of the table, as follows:

For example, this query updates the first two column (A, B) to be the values [1,2], on the row that the user selected in the table.