Google Sheets Resource

The Google Sheets Resource provides access to the Google Sheets API (opens in a new tab). 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 (opens in a new tab).
2. Enable the Google Sheets API (opens in a new tab) for your selected project.
   • Enabling this API lets you read the data inside a given Google Sheets document.
3. Enable the Google Drive API (opens in a new tab) 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 (opens in a new tab) 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

Environment Profiles

The profiles configured for this resource in an environment

Environment Profile

A profile of this resource for an environment

JSON Key

The JSON key for a Google Cloud service account.

Built-in service account

The workspace service account

Google Sheet URL

The Google Sheet URL for connecting

Environment

The name of the environment with which this profile is associated

Auth Mode

The mode of the authentication.

Actions

List Spreadsheets

Lists all spreadsheets accessible by the provided API key.

Read Spreadsheet Rows

Reads rows from the specified spreadsheet.

Append Rows

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

Update Rows

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

Delete Row

Delete a row in a spreadsheet.