Google Sheets Resource Node

The Google Sheets Resource provides access to the Google Sheets API. Using this resource, you can read and update existing spreadsheets.

Configuration Steps

This resource requires a Service Account in a project that has the Sheets API enabled. Once created, only sheets that have been shared with this service account can be seen.

  1. Open or create a project to use to create a service account in the Google Cloud Console.
  2. Enable the Google Sheets API for your selected project.
    • Enabling this API lets you read the data inside a given Google Sheets document.
  3. Enable the Google Drive API for your selected project.
    • Enabling this API lets you list every Google Sheets document your service account will be able to access.
  4. Open the Service Accounts page and (if necessary) re-select the project you would like to use.
  5. Click the "Create Service Account" button at the top of the page.
  6. Enter a service account ID, name, and description. Click "Create and Continue".
  7. Roles are not required for a service account using the Google Sheets and Google Drive APIs.
  8. Add your email to the Service accounts admins role and click "Done" to finish creating the account.
  9. Copy the newly created service account's email for later. We'll need it to share in Google Sheets.
    • The emails have the format: service-account-name@project-name.iam.gserviceaccount.com
  10. Open your newly created service account and click on the "Keys" tab.
    • Alternatively, click the more button (three dots) under Actions for the newly created service account and click the "Manage Keys" option.
  11. Click "Add Key" → "Create new key". Make sure JSON is selected and then click "Create"
    • A JSON key will be downloaded to your computer.
  12. Open the downloaded JSON key with a text editor, and copy the contents of the file into Dynaboard.
    • Secret fields (e.g. passwords or certificates) require clicking the ✓ to the right of the field to save the input.
  13. Click "Test Connection" at the bottom of the panel to verify everything works!
  14. Once you've verified the connection works, you can share any sheets you'd like to access in Dynaboard with the service account email you saved in step #9.
    • Share documents by clicking on the "Share" button in the top right of a Google Sheet and adding the email of the service account.
    • Note: Google Sheets URLs have the format: https://docs.google.com/spreadsheets/d/spreadSheetID/edit

Common Configuration Errors

  • A secret may be unsaved (e.g. passwords or certificates). Click the ✓ to the right of the field to save it. The text will disappear after a successful save.
  • The service account may not have been created in the correct project.
  • The service account may not have had the Sheets or Drive APIs enabled.
  • An incorrect JSON key may have been copied.

Properties

JSON Key

PropgoogleSheetsNode.serviceAccount
Type({ ref: string } | undefined)
Defaultundefined

The JSON key for a Google Cloud service account.

Actions

List Spreadsheets

Lists all spreadsheets accessible by the provided API key.

ParameterTypeDefault

Read Spreadsheet Rows

Reads rows from the specified spreadsheet.

ParameterTypeDefault
idstringundefined
rangestringundefined
firstRowAsHeader(string | undefined)true
includeExtraRaw(string | undefined)false
includeExtraHeaders(string | undefined)false

Append Rows

Appends rows to a spreadsheet. Values is a nested array of format: [[row1col1, row1col2], [row2col1, ...]]

ParameterTypeDefault
idstringundefined
rangestringundefined
valuesstringundefined

Update Rows

Updates rows in a spreadsheet. Values is a nested array of format: [[row1col1, row1col2], [row2col1, ...]]

ParameterTypeDefault
idstringundefined
rangestringundefined
valuesstringundefined