Skip to main content

Google Sheets

ToolJet can connect to Google Sheet using OAuth 2.0, which helps us to limit an application's access to a user's account.

How to integrate Google Sheets

Self-Hosted Configuration

If you are self-hosting the application, you will need to perform some additional steps.

  1. Follow the Google OAuth 2.0 setup steps outlined here
  2. Set the following environment variables with the values from the previous step:
    • GOOGLE_CLIENT_ID
    • GOOGLE_CLIENT_SECRET
    • TOOLJET_HOST
  3. Enable the Google Sheets API in the GCP console

Authorization Scopes

You can create a Google Sheets data source with one of either of the two permission scopes :

  1. Read Only
  2. Read and Write
google sheets

Operations

Using Google sheets data source you can perform several operations from your applications like:

  1. Read data from a sheet
  2. Append data to a sheet
  3. Update single row of a sheet
  4. Delete row from a sheet
  5. Get spreadsheet info

Read data from a sheet

This operation returns the table data from the spreadsheet in the form of json object.

Fieldsdescription
Spreadsheet IDIt is mandatory to enter the spreadsheet-id. The spreadsheet-id can be found in the URL of the spreadsheet. Example URL: https://docs.google.com/spreadsheets/d/1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM/edit#gid=0 - in this URL, the 1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM is the spreadsheet-id.
RangeThis is optional. You can specify the range of cells in this field. If left empty, it will select the range A1:Z500.
SheetThis is optional. You can specify sheet name if it has more than 1 sheets, else it will automatically choose the first sheet.
google sheets read

Append data to a sheet

You can add more rows to the table using the append operation.

Fieldsdescription
Spreadsheet IDIt is mandatory to enter the spreadsheet-id. The spreadsheet-id can be found in the URL of the spreadsheet. Example URL: https://docs.google.com/spreadsheets/d/1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM/edit#gid=0 - in this URL, the 1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM is the spreadsheet-id.
SheetThis is optional. You can specify sheet name if it has more than 1 sheets, else it will automatically choose the first sheet.
RowsEnter the row data in the json array form. Each object in an array will represent a single row. Example: [ {"name":"John", "email":"John@tooljet.com"},{...},{...} ] In each object, the key represents the column name and the value represents the cell data.
google sheets append

Update single row of a sheet

You can update the existing data in sheet using this operation.

Fieldsdescription
Spreadsheet IDIt is mandatory to enter the spreadsheet-id. The spreadsheet-id can be found in the URL of the spreadsheet. Example URL: https://docs.google.com/spreadsheets/d/1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM/edit#gid=0 - in this URL, the 1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM is the spreadsheet-id.
WhereEnter the column name such as id for choosing a row.
OperatorChoose the === operator to check the equality.
ValueEnter the any id number/name that you want to update.
RowsEnter the row data. Example: {{({id: components.textinput4.value, company: components.textinput1.value, position: components.textinput2.value, url: components.textinput3.value, 'date-applied': components.datepicker1.value, status: components.dropdown1.value})}}
google sheets update

Delete row from a sheet

Use this operation delete a specific row from the sheet.

Fieldsdescription
Spreadsheet IDIt is mandatory to enter the spreadsheet-id. The spreadsheet-id can be found in the URL of the spreadsheet. Example URL: https://docs.google.com/spreadsheets/d/1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM/edit#gid=0 - in this URL, the 1W2S4re7zNaPk9vqv6_CqOpPdm_mDEqmLmzjVe7Nb9WM is the spreadsheet-id.
GIDYou'll find the GID in the end of the URL of spreadsheet. In the example mentioned above, the GID is 0
Delete row numberJust enter the row number that you want to delete.
google sheets delete

Get spreadsheet info

This operation can be used to get some basic information of the spreadsheet such as the number of sheets, theme, time-zone, format, and url etc.

Here is the Preview of the query that used the get spreadsheet info operation.

google sheets get info