Google Sheets

Overview

Connect Superblocks to Google Sheets so you can build apps without needing to manage a database:

• Read data from a spreadsheet and utilize it in API steps or UI components

• Save information retrieved by other API steps or UI components by writing data to a spreadsheet

Setting up Google Sheets

Select Google Sheets from the integrations page and fill out the form according to your preferred authentication method, either a standard user based Google Account or a non-human Service Account that you can share specific spreadsheets with:

Connect using Google Account

• Display Name - Name that will be displayed to users when selecting this integration in Superblocks resources
• Authentication - Connect using Google Account
• Access Level - 'Read only' or 'Read and write'

caution

To restrict access to certain sheets in your Google Account, use a Service Account to connect to Superblocks.

After configuring the above, click 'Connect to Google account'.

Then follow the OAuth popup instructions to grant Superblocks access to your Google Account.

caution

Note: You need to grant both "See information about your Google Drive files" and "See all your Google Sheets spreadsheets" (for read access) or "See, edit, create, and delete all your Google Sheets spreadsheets" (for read and write access) when connecting. If you'd like to limit access on a per-spreadsheet basis, use a service account.

Once you are connected, Click "Create Integration" to finish the integration setup.

Connect using Service Account

• Display Name - Name that will be displayed to users when selecting this integration in Superblocks resources
• Authentication - Connect using Service Account
• Access Level - 'Read only' or 'Read and write'
• Service Account Key - See guide from Google on how to create a Service Account and associated key

caution

You must also enable the Google Drive and Google Sheets API in your GCP project. Only sheets that have been shared with the service account email will be available through the integration.

After configuring the above, click "Create Integration" to finish the integration setup.

info

Currently, Staging and Prod environments must use the same account

Creating Google Sheets steps

Connect to your Google Sheets integration from Superblocks by creating steps in Application APIs, Workflows, and Scheduled Jobs. You can configure a Superblocks Google Sheets step to read a spreadsheet (whole or range), as well as append new rows to a spreadsheet. See example configuration for each type of action below.

Read the whole spreadsheet

Specify the settings:

• Action - Read the whole spreadsheet
• Spreadsheet - Name of the spreadsheet
• Sheet name - Name of the sheet / tab
• Table Header - Choose to use Row 1 of the sheet as the table header

Read from a range

Specify the settings:

• Action - Read from a range (e.g. A1:D100)
• Spreadsheet - Name of the spreadsheet
• Sheet name - Name of the sheet / tab
• Table Header - Choose to use Row 1 of the sheet as the table header
• Data range - Range of cells to read from

Create spreadsheet row(s)

info

Requires 'Read and write' access level in the Google Sheets integration setup

Specify the settings:

• Action - Create new spreadsheet rows at a specific position.
• Spreadsheet - Name of the spreadsheet
• Sheet name - Name of the sheet / tab
• Include Table Header - Define if a Table Header row will be created
• Header row number - If this is set to true a table header row will be created from the keys passed via the JSON (See example below).
• Write location - Define where in the document the data should be added:
  • Append data starting at the first empty row (relative to table header row if present)
  • Replace data starting at a specific row (must be inserted after table header row if present)
• Rows to append - Rows to write to the selected location. Data format is an array of objects that can use variables to reference output of previous steps (e.g {{Step1.output}}) output and UI components (e.g {{Input1.value}}).

info

Note that bindings must be in quotes, see below example

Example of Rows to append:

[
    {
        "first_name": "Peter",
        "last_name": "Parker",
        "category": "{{categoryDropdown.selectedOptionValue}}"
    }
]

Result in Gsheet (Header row is added if Include Table Header option is set):

first_name	last_name	category
Peter	Parker	Loan

Update specific row based on selection in a table

To make updates to a specific row based on data selected in a Superblocks table, create an API that writes the new row to the same location in your Google Sheet. To do so, set Write location to Replace data starting at a specific row and Starting row number to the following.

{{<TABLE_NAME>.filteredOrderMap[<TABLE_NAME>.selectedRowIndex] + 2}}

Example

In the example below, a table displays customer data read from a Google Sheet. Clicking a table row launches a modal where the user can edit a value in a dropdown. Pressing the confirm button triggers the updateRow API, which writes an object back to that same Google Sheet. Here we're using the JavaScript spread syntax with bindings ({{}}) to create a new object copied from the selected table row and replace the desired values with the user input from the dropdown. The previous Starting row number setting ensures this data is written to the original cell in Google Sheets.

Clear the spreadsheet

info

Requires 'Read and write' access level in the Google Sheets integration setup

Specify the settings:

• Action - Clear data from a given spreadsheet
• Spreadsheet - Name of the spreadsheet
• Sheet name - Name of the sheet / tab
• Preserve table header - Whether to preserve or delete the header section of this spreadsheet. If selected, all rows up to the header row number will be preserved. E.g. if the table header is at row 4, when this is selected, the data in rows 1-4 will be preserved, data in rows 5+ will be cleared
• Header row number - Row number that will be preserved if the above option is set to true

Append rows to the spreadsheet (deprecated)

info

Requires 'Read and write' access level in the Google Sheets integration setup

caution

This action is deprecated! Please use Create spreadsheet row(s) instead.

Specify the settings:

• Action - Append rows to the spreadsheet
• Spreadsheet - Name of the spreadsheet
• Sheet name - Name of the sheet / tab
• Rows to append - Rows to write to the end of the spreadsheet. Data format is an array of objects that can use variables to reference output of previous steps (e.g {{Step1.output}}) output and UI components (e.g {{Input1.value}}).

Example of Rows to append:

[
    {
        "first_name": "Peter",
        "last_name": "Parker",
        "category": "Loan"
    }
]

Result in Gsheet (a header row is not added):

Peter	Parker	Loan

Use cases

Applications

Read the whole spreadsheet

Read an entire Google Sheet and attach the API response to a Table component to visualize the Google Sheet in Superblocks.

Read from a range

Read a range of cells within a Google Sheet and attach the API response to a Table component to visualize the Google Sheet in Superblocks.

Create spreadsheet row(s)

Append a row to your spreadsheet when a user fills out an input form in your application.

Clear the spreadsheet

Reset a spreadsheet when a button is clicked in the UI.

Append rows to the spreadsheet (deprecated)

Write to a Google Sheet by triggering an API on click of a Form button. The API's Rows to append field is an array of objects that dynamically references values from the Input and Checkbox components that the user submits in the form.

Workflows

Automate sending a marketing campaign email to all your sales leads contained in a Google Sheet.

Scheduled Jobs

Copy data from Postgres to a Google Sheet on a daily basis for teams who need to see info on new bookings, but don't have access to the database.

Troubleshooting

When using or configuring the Google Sheets integration, you may come across errors. Below we list the most common errors and basic troubleshooting steps. If you are encountering an error that you don't see below, or the provided steps are insufficient to resolve the error, please contact us at help@superblocks.com.

Insufficient permission

Error: Google Sheets client configuration failed. Insufficient Permission: Request had insufficient authentication scope

You can troubleshoot this error by making sure that all required permissions are allowed as requested by the Google Sheets integration. You need to grant both "See information about your Google Drive files" and "See all your Google Sheets spreadsheets" (for read access) or "See, edit, create, and delete all your Google Sheets spreadsheets" (for read and write access) when connecting. If you'd like to limit access on a per-spreadsheet basis, use a service account.

Token access revoked

Error: Failed to refresh user token on server, most likely the access has been revoked

If you receive this error, it means that your user token has had its access revoked on the Google side. Please recreate the Google Sheets integration in order to resolve this issue.

Unexpected Token in JSON

Error: Google Sheets request failed. Failed to parse rows to append as JSON: Unexpected token * in JSON at position 15

Proper syntax for writing into a spreadsheet encloses the bindings in quotes in the Rows to append field. Example:

[
    {
        "Name": "{{nameInputUpdate.value}}",
        "Favorite Food": "{{foodInputUpdate.value}}",
        "Email": "{{emailInputUpdate.value}}"
    }
]

No spreadsheets are loading

If you see that your spreadsheets are not loading in and you're using a service account, double check that you have enabled the Google Drive and Google Sheets API in your GCP project. Additionally, only sheets that have been shared with the service account email will be available.