Google Sheets

The Google Sheets integration allows your bot to access and update Google Sheets data in real-time, automating tasks like CRM updates, inventory management, and survey tracking directly within chat.

Setup

You will need:

A published bot
A Google account

In Botpress Studio, select Explore Hub in the upper-right corner.

Search for the Google Sheets integration, then select Install Integration.

In the Configuration menu, select Authorize Google Sheets.

Follow the instructions to connect Botpress to your Google account.

For more advanced use cases, you can manually configure the integration using a service account.

You will need:

Step 1: Create a Google Cloud project

Go to the Google Cloud Console and sign in with your Google account.

Open the project picker dropdown in the top navigation. Then, select New Project.

Enter your project details:

Project name: Choose a descriptive name for your project.
Organization: Select your organization (if applicable).
Location: Choose the appropriate location.

Select Create and wait for the project to be created.

Verify you’re in the correct project by checking the project name next to the Google logo.

Step 2: Enable the Google Sheets API

In the Google Cloud Console, open the left sidebar and go to API & Services > Credentials.

Select + Create Credentials > API Key to create an API key.

Go to Library, search for “Google Sheets API”, and select Enable.

Step 3: Create a service account

Open the left sidebar and go to API & Services > Credentials.

Select + Create Credentials > Service Account.

Enter a Service account name. The Service Account ID will be automatically generated.

The remaining fields are optional. Select Done to create the service account.

Select your newly created service account, then go to the Keys tab.

Select Add Key > Create new key, choose JSON as the key type, then select Create.

A JSON file will be downloaded to your machine. Keep this file secure—you’ll need it to configure the integration.

The downloaded JSON file contains sensitive credentials. Store it securely and never share it publicly. The file structure looks like this:

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "...",
  "private_key": "...",
  "client_email": "your-service-account@your-project.iam.gserviceaccount.com",
  "client_id": "...",
  "auth_uri": "...",
  "token_uri": "...",
  "auth_provider_x509_cert_url": "...",
  "client_x509_cert_url": "...",
  "universe_domain": "googleapis.com"
}

Step 4: Grant spreadsheet access to the service account

Open your Google Sheets spreadsheet in your browser.

Select the Share button in the upper-right corner.

Open the JSON file you downloaded and copy the client_email value.

Paste the client_email into the Add people and groups field, as if you were inviting someone to access your spreadsheet.

Set the permission to Editor so the bot can read and write to the spreadsheet. Then select Send.

Step 5: Configure the integration in Botpress

In Botpress Studio, select Explore Hub in the upper-right corner.

Search for the Google Sheets integration, then select Install Integration.

In the Configuration menu, select the drop-down and choose Configure manually with a Service Account Key.

Fill in the integration’s configuration fields:

Spreadsheet ID

Open your Google Sheets spreadsheet in your browser.
Copy the ID from the URL. The ID is the string between /d/ and /edit in the URL.

For example, if your URL is https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz/edit, the Spreadsheet ID is 1AbCdEfGhIjKlMnOpQrStUvWxYz.

Client Email

Open the JSON file you downloaded in Step 3 and copy the client_email value.

Private Key

Open the JSON file you downloaded in Step 3 and copy the entire private_key value, including the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- markers.

When you’ve filled in all the fields, select Save Configuration.

You’ve successfully configured the Google Sheets integration!

Cards

Add Sheet

Adds a new sheet to the spreadsheet.

inputobject

outputobject

Append Values

Appends values to the spreadsheet. The input startColumn is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table.

inputobject

outputobject

Clear Values

Clears values from a spreadsheet. Only values are cleared; all other properties of the cell (such as formatting, data validation, etc..) are kept.

inputobject

outputobject

Create Named Range in Sheet

Creates a named range in a sheet.

inputobject

outputobject

Delete Rows

Delete one or more rows by their 1-based indexes. Rows are deleted in reverse order to preserve indexes during deletion.

inputobject

outputobject

Delete Sheet

Deletes a sheet from the spreadsheet.

inputobject

Find Row (First Match)

Search for the first row where a specific column matches a value. Returns the row data and its index, or null if no match is found.

inputobject

outputobject

Find Rows

Search for rows where a specific column matches a value. Returns all matching rows with their indexes. Handles empty sheets and no matches gracefully by returning an empty array.

inputobject

outputobject

Get All Sheets in Spreadsheet

Returns all sheets in the spreadsheet.

outputobject

Get Info of a SpreadSheet

Returns the properties and metadata of the specified spreadsheet.

inputobject

outputobject

Get Named Ranges

Returns all named ranges in the spreadsheet.

outputobject

Get Protected Ranges

Returns all protected ranges in the spreadsheet.

outputobject

Get Row

Fetch a specific row by its 1-based index. Provides direct row access without A1 notation math.

inputobject

outputobject

Get Values

Returns the values of a range in the spreadsheet.

inputobject

outputobject

Insert Row at Index

Insert a new row at a specific 1-based index. Existing rows at and below the index are shifted down.

inputobject

outputobject

Move Sheet Horizontally

Moves a sheet to a new index in the spreadsheet.

inputobject

Protect Named Range

Creates a protected range from a named range, preventing modification.

inputobject

outputobject

Rename Sheet

Renames a sheet in the spreadsheet.

inputobject

Set Sheet Visibility

Sets the visibility of a sheet in the spreadsheet.

inputobject

Set Values

Sets values in a range in the spreadsheet.

inputobject

outputobject

Unprotect Range

Removes protection from a protected range in the spreadsheet.

inputobject

Update Row

Update a specific row by its 1-based index with a partial or complete set of values. Only the provided values are updated.

inputobject

outputobject

Upsert Row

Update a row if it exists (based on a key column match), or append a new row if no match is found. Useful for maintaining unique records.

inputobject

outputobject

Triggers

For Cards that take named ranges as input, Google’s standard restrictions for range names apply. Range names:

Can contain only letters, numbers, and underscores
Can’t start with a number, or the words “true” or “false”
Can’t contain any spaces or punctuation
Must be 1–250 characters
Can’t be in either A1 or R1C1 syntax. For example, you might get an error if you give your range a name like A1:B2 or R1C1:R2C2