Google Sheets

Add a bot to Google Sheets using the official integration.

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

Basic configuration

You will need:

• A published bot
• A Google account

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

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

3. In the Configuration menu, select Authorize Google Sheets.

4. Follow the instructions to connect Botpress to your Google account.

Manual configuration

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

You will need:

• A published bot
• A Google account
• Access to Google Cloud Console

Step 1: Create a Google Cloud project

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

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

3. Enter your project details:

   • Project name: Choose a descriptive name for your project.
   • Organization: Select your organization (if applicable).
   • Location: Choose the appropriate location.
4. Select Create and wait for the project to be created.

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

Step 2: Enable the Google Sheets API

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

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

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

Step 3: Create a service account

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

2. Select + Create Credentials > Service Account.

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

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

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

6. 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": "[e-mail]",
  "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

1. Open your Google Sheets spreadsheet in your browser.

2. Select the Share button in the upper-right corner.

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

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

5. 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

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

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

3. 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

1. Open your Google Sheets spreadsheet in your browser.
2. 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

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

Here’s a reference for all Cards available with the integration:

Add Sheet

Append Values

input · object

Show child attributes

majorDimension · enum<string> · default: "ROWS"

Available options: ROWS, COLUMNS

Clear Values

Create Named Range in Sheet

Delete Rows

Delete Sheet

output · object

This Card has no output.

Find Row (First Match)

output · object

Show child attributes

row · object | null · required

Find Rows

Get All Sheets in Spreadsheet

input · object

This Card has no input fields.

Get Info of a SpreadSheet

Get Named Ranges

input · object

This Card has no input fields.

Get Protected Ranges

input · object

This Card has no input fields.

Get Row

output · object

Show child attributes

row · object | null · required

Get Values

input · object

Show child attributes

majorDimension · enum<string> · default: "ROWS"

Available options: ROWS, COLUMNS

output · object

Show child attributes

majorDimension · enum<string> · default: "ROWS"

Available options: ROWS, COLUMNS

Insert Row at Index

Move Sheet Horizontally

output · object

This Card has no output.

Protect Named Range

Rename Sheet

output · object

This Card has no output.

Set Sheet Visibility

output · object

This Card has no output.

Set Values

input · object

Show child attributes

majorDimension · enum<string> · default: "ROWS"

Available options: ROWS, COLUMNS

Unprotect Range

output · object

This Card has no output.

Update Row

Upsert Row

output · object

Show child attributes

action · enum<string> · required

Available options: updated, inserted