Unify Integrations
Logo
Google Sheets

Google Sheets

Logo

7 mins READ

Google Sheets is Google's cloud-based spreadsheet tool for organizing, analyzing and collaborating on data. The UnifyApps Google Sheets connector uses the Google Sheets API, together with the Google Drive API, to create spreadsheets and sheets, add, read, search, update and clear rows, run queries, read spreadsheet metadata and react to new rows from workflows and agents.

This guide covers every authentication type available on the connection form, and the actions and triggers supported, along with the Google scope each one needs.

Authentication

Before you begin, you should have the following information:

  • Connection name: a descriptive name such as MyAppGoogleSheetsIntegration, so the connection is easy to identify later.

  • Authentication type: select the type of authentication for connecting to your Google Sheets account:

    1. OAuth

    2. OAuth with Client Credentials

    3. Service Account

The connection form has no Scopes field. Every authentication type requests https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/drive and https://www.googleapis.com/auth/drive.file, which together cover every action and trigger below.

1. OAuth

Use this when you want to connect with your own Google account and do not need to create a Google Cloud app.

  1. Enter a Connection name.

  2. Select OAuth as the Authentication type.

  3. Click Authorize. You are redirected to the Google sign-in page.

  4. Sign in with the Google account that has access to the spreadsheets you need.

  5. Review the permissions on the consent screen, for example See, edit, create, and delete all your Google Sheets spreadsheets, and click Allow.

  6. You are redirected back to UnifyApps with a confirmation that the account is connected.

google-sheets-screenshot-1-blurred.png
google-sheets-screenshot-1-blurred.png

2. OAuth with Client Credentials

Use this when your organization wants the connection to use its own Google Cloud OAuth client, while actions still run as the Google user who signs in.

Step 1: Create an OAuth client in Google Cloud

  1. In the Google Cloud Console, select or create a project, and enable the Google Sheets API and Google Drive API under APIs & Services → Library.

  2. Go to Google Auth platform → Branding. If you see Google Auth platform not configured yet, click Get Started, enter an App name, User support email, Audience and contact email, and click Create.

  3. Go to Data Access, click Add or Remove Scopes, add https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/drive and https://www.googleapis.com/auth/drive.file (the scopes the connection requests), and click Save. If the audience is External, add the users who will sign in under Audience → Test users.

  4. Go to Google Auth platform → Clients and click Create Client. Choose Web application as the Application type and enter a name.

  5. Under Authorized redirect URIs, click Add URI and paste the Callback URL shown on the UnifyApps connection form (it is copyable and differs per environment).

  6. Click Create, then copy the Client ID and Client secret. Copy the secret immediately: Google shows it only once.

Step 2: Connection setup in UnifyApps

  1. Open Connections → New Connection → Google Sheets and enter a Connection name.

  2. Select OAuth with Client Credentials as the Authentication type.

  3. Enter the Client ID and Client secret copied from Google Cloud.

  4. Click Authorize, sign in with Google and click Allow on the consent screen.

  5. Confirm the success message after the redirect back to UnifyApps.

3. Service Account

Use this for Google Workspace domains, where a service account acts on behalf of a user in the domain through domain-wide delegation. No user has to sign in.

Step 1: Create a service account in Google Cloud

  1. In the Google Cloud Console, select or create a project, and enable the Google Sheets API and Google Drive API under APIs & Services → Library.

  2. Go to IAM & Admin → Service Accounts and click Create service account. Enter a name and click Done.

  3. Open the service account and copy its Email. Click Show advanced settings and, under Domain-wide delegation, copy the Client ID used in the next step.

  4. Go to the Keys tab → Add key → Create new key, choose JSON and click Create. The downloaded file holds the client_email and private_key. Store it securely.

google-sheets-screenshot-2.png
google-sheets-screenshot-2.png

Step 2: Give the service account domain-wide access

  1. A Google Workspace super administrator opens the Admin console → Security → Access and data control → API controls.

  2. Click Manage Domain Wide Delegation → Add new.

  3. Enter the service account's client ID, add https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/drive and https://www.googleapis.com/auth/drive.file, separated by commas, and click Authorize. See Delegate domain-wide authority to your service account for details.

Note: the connection requests all three scopes, so all three must be granted here or the connection cannot get a token. It can only reach the spreadsheets of users in the Workspace domain where domain-wide delegation was granted. Google says changes can take up to 24 hours to apply.

Step 3: Create the connection in UnifyApps

  1. Select Service Account as the Authentication type.

  2. Enter the Service Account Email and paste the Private Key from the JSON key file.

  3. Enter the User Email of the Workspace user whose spreadsheets the connection acts on.

  4. Click Test Connection to validate the credentials, then click Create.

Actions

Scopes are shown in short form: spreadsheets.readonly means https://www.googleapis.com/auth/spreadsheets.readonly. The column shows the narrowest scope Google accepts for each call on spreadsheets the connection did not create; drive covers every action. drive.file only reaches files the connection created or was given access to by the user.

Actions

Description

Scopes Required

Add a row

Adds a row to the specified sheet in Google Sheets.

spreadsheets (or drive)

Add a row V2

Adds a row to the specified sheet in Google Sheets, and supports dynamic spreadsheets.

spreadsheets (or drive)

Add multiple rows

Adds multiple rows to a sheet in Google Sheets.

spreadsheets (or drive)

Clear rows

Clears rows in a sheet in Google Sheets.

spreadsheets (or drive)

Copy sheet

Copies a sheet from one spreadsheet to another in Google Sheets.

spreadsheets (or drive)

Create column

Creates a new column in Google Sheets.

spreadsheets (or drive)

Create sheet

Creates a new sheet in a spreadsheet in Google Sheets.

spreadsheets (or drive)

Create spreadsheet

Creates a new spreadsheet in Google Sheets.

drive.file (or spreadsheets)

Delete a row

Deletes a row in a sheet in Google Sheets.

spreadsheets (or drive)

Download sheet(Deprecated)

Downloads a sheet by its sheet ID. This action is deprecated.

drive.readonly (or drive)

Get file details

Gets the Google Drive file details of a spreadsheet.

drive.metadata.readonly (or drive.readonly)

Get metadata

Gets the metadata for Google Sheets files.

drive.metadata.readonly (or drive.readonly)

Get pivot table data

Gets the pivot table configuration and structure from a spreadsheet.

spreadsheets.readonly (or spreadsheets)

Get rows

Gets rows from a spreadsheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

Get spreadsheet grid data

Gets each cell's value in a spreadsheet, along with the hyperlinks it points to, such as cell links, rich-text links and smart chip links.

spreadsheets.readonly (or spreadsheets)

Get spreadsheet metadata

Gets the metadata of a spreadsheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

Get spreadsheet value

Gets a range of values from a spreadsheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

Get spreadsheets

Gets spreadsheets from Google Drive.

drive.metadata.readonly (or drive.readonly)

List file permissions

Lists the permissions for a spreadsheet in Google Sheets.

drive.metadata.readonly (or drive.readonly)

List Sheets

Lists the sheets in a spreadsheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

List Spreadsheets

Lists spreadsheets from Google Drive.

drive.metadata.readonly (or drive.readonly)

Run query

Runs a Google Visualization API Query Language query on a spreadsheet to filter, sort, aggregate and transform data.

spreadsheets.readonly (or drive.readonly)

Search rows

Searches rows in a sheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

Update a row

Updates a row in a sheet in Google Sheets.

spreadsheets (or drive)

Update rows

Updates rows in a sheet in Google Sheets.

spreadsheets (or drive)

Update spreadsheet

Updates a spreadsheet in Google Sheets.

spreadsheets (or drive)

Triggers

Triggers

Description

Scopes Required

New row created

Triggers when a new row is created in a sheet in Google Sheets.

spreadsheets.readonly (or spreadsheets)

New row created in team drive

Triggers when a new row is created in a sheet stored in a shared (team) drive.

spreadsheets.readonly (or spreadsheets)