> ## Documentation Index
> Fetch the complete documentation index at: https://botpress.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Google Sheets

> Add a bot to Google Sheets using the official integration.

{/* vale on */}

[Open in Hub](https://studio.botpress.cloud/home?exploreHub=1\&hubItemId=intver_01KP5Z7BRNQNGVY406742M7BBE)
*v2.1.9*

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

> [tab] Basic configuration
>
> > [!NOTE]
> >
> > You will need:
> >
> > * A [published bot](https://botpress.com/docs/get-started/quick-start.md)
> > * A [Google account](https://accounts.google.com/)
>
> **Step 1**
>
> In Botpress Studio, select **Explore Hub** in the upper-right corner.
>
> **Step 2**
>
> Search for the **Google Sheets** integration, then select **Install Integration**.
>
> **Step 3**
>
> In the **Configuration** menu, select **Authorize Google Sheets**.
>
> **Step 4**
>
> Follow the instructions to connect Botpress to your Google account.

> [tab] Manual configuration
>
> For more advanced use cases, you can manually configure the integration using a service account.
>
> > [!NOTE]
> >
> > You will need:
> >
> > * A [published bot](https://botpress.com/docs/get-started/quick-start.md)
> > * A [Google account](https://accounts.google.com/)
> > * Access to [Google Cloud Console](https://console.developers.google.com/)
>
> ### Step 1: Create a Google Cloud project
>
> **Step 1**
>
> Go to the [Google Cloud Console](https://console.developers.google.com/) and sign in with your Google account.
>
> **Step 2**
>
> Open the project picker dropdown in the top navigation. Then, select **New Project**.
>
> **Step 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.
>
> **Step 4**
>
> Select **Create** and wait for the project to be created.
>
> **Step 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
>
> **Step 1**
>
> In the Google Cloud Console, open the left sidebar and go to **API & Services > Credentials**.
>
> **Step 2**
>
> Select **+ Create Credentials > API Key** to create an API key.
>
> **Step 3**
>
> Go to **Library**, search for "Google Sheets API", and select **Enable**.
>
> ### Step 3: Create a service account
>
> **Step 1**
>
> Open the left sidebar and go to **API & Services > Credentials**.
>
> **Step 2**
>
> Select **+ Create Credentials > Service Account**.
>
> **Step 3**
>
> Enter a **Service account name**. The Service Account ID will be automatically generated.
>
> **Step 4**
>
> The remaining fields are optional. Select **Done** to create the service account.
>
> **Step 5**
>
> Select your newly created service account, then go to the **Keys** tab.
>
> **Step 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.
>
> > [!NOTE]
> >
> > The downloaded JSON file contains sensitive credentials. Store it securely and never share it publicly. The file structure looks like this:
> >
> > ```json theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
> > {
> >   "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
>
> **Step 1**
>
> Open your Google Sheets spreadsheet in your browser.
>
> **Step 2**
>
> Select the **Share** button in the upper-right corner.
>
> **Step 3**
>
> Open the JSON file you downloaded and copy the `client_email` value.
>
> **Step 4**
>
> Paste the `client_email` into the **Add people and groups** field, as if you were inviting someone to access your spreadsheet.
>
> **Step 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
>
> **Step 1**
>
> In Botpress Studio, select **Explore Hub** in the upper-right corner.
>
> **Step 2**
>
> Search for the **Google Sheets** integration, then select **Install Integration**.
>
> **Step 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:
>
> > [accordion] 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`.
>
> > [accordion] Client Email
> >
> > Open the JSON file you downloaded in [Step 3](#step-3-create-a-service-account) and copy the `client_email` value.
>
> > [accordion] Private Key
> >
> > Open the JSON file you downloaded in [Step 3](#step-3-create-a-service-account) 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**.
>
> > [!TIP]
> >
> > You've successfully configured the Google Sheets integration!

***

## Cards

> [!NOTE]
>
> For Cards that take [named ranges](https://support.google.com/docs/answer/63175?hl=en\&co=GENIE.Platform%3DDesktop) 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`

{/* vale off */}

Here's a reference for all [Cards](https://botpress.com/docs/studio/concepts/cards/introduction.md) available with the integration:

### Add Sheet

Adds a new sheet to the spreadsheet.

**input** `object`

**title** `string` *required*

The title of the new sheet to add to the spreadsheet.

**output** `object`

**spreadsheetId** `string` *required*

The spreadsheet ID of the new sheet.

**newSheet** `object` *required*

The new sheet that was added to the spreadsheet.

**sheetId** `number` *required*

The ID of the new sheet.

**title** `string` *required*

The title of the new sheet.

**index** `number` *required*

The index of the new sheet within the spreadsheet.

**isHidden** `boolean` *required*

Whether the new sheet is hidden.

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**startColumn** `string` *required*

The start column letter(s) (e.g. "A", "B", "AA"). Used to identify the table in the sheet — data will be appended after the last row of the detected table.

**majorDimension** `enum\<string\>` (default: ROWS)

If it equals "ROWS", then the values are inserted as rows. If it equals "COLUMNS", then the values are inserted as columns.

Available options: `ROWS`, `COLUMNS`

**values** `array` *required*

The values to write to the range. This is an array of arrays, where each inner array represents a major dimension (a row or column) of data. (e.g. \[\["a", "b"], \["c", "d"]])

**output** `object`

**spreadsheetId** `string` *required*

The spreadsheet the updates were applied to.

**tableRange** `string` *required*

The range (in A1 notation) of the table that values are being appended to (before the values were appended). Empty if no table was found.

**updates** `object` *required*

The updates that were applied to the spreadsheet.

**spreadsheetId** `string` *required*

The spreadsheet the updates were applied to.

**updatedRange** `string` *required*

The range (in A1 notation) that updates were applied to.

**updatedRows** `number` *required*

The number of rows where at least one cell in the row was updated.

**updatedColumns** `number` *required*

The number of columns where at least one cell in the column was updated.

**updatedCells** `number` *required*

The number of cells updated.

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

**input** `object`

**range** `string` *required*

The A1 notation of the range to clear. (e.g. "Sheet1!A1:B2")

**output** `object`

**spreadsheetId** `string` *required*

The spreadsheet the updates were applied to.

**clearedRange** `string` *required*

The range (in A1 notation) that was cleared.

### Create Named Range in Sheet

Creates a named range in a sheet.

**input** `object`

**sheetId** `number` *required*

The ID of the sheet to create the named range in.

**rangeName** `string` *required*

The name of the named range. Must follow naming rules: cannot contain spaces, cannot start with a number, and cannot conflict with standard cell references (e.g., "A1", "R1C1"). Names violating these rules will be considered invalid.

**rangeA1** `string` *required*

The A1 notation of the range to associate with the named range. (e.g. "Sheet1!A1:B2")

**output** `object`

**namedRangeId** `string` *required*

The ID of the new named range.

### Delete Rows

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**rowIndexes** `array` *required*

The 1-based row indexes to delete.

**output** `object`

**deletedCount** `number` *required*

The number of rows deleted.

### Delete Sheet

Deletes a sheet from the spreadsheet.

**input** `object`

**sheetId** `number` *required*

The ID of the sheet to delete.

**output** `object`

This Card has no output.

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**searchColumn** `string` *required*

The column letter to search in (e.g. "A", "B", "AA").

**searchValue** `string` *required*

The value to search for in the specified column.

**dataRange** `string`

Optional A1 notation range to limit the search (e.g. "A1:F100"). If not provided, searches the entire sheet.

**output** `object`

**found** `boolean` *required*

Whether a matching row was found.

**row** `object | null` *required*

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**searchColumn** `string` *required*

The column letter to search in (e.g. "A", "B", "AA").

**searchValue** `string` *required*

The value to search for in the specified column.

**dataRange** `string`

Optional A1 notation range to limit the search (e.g. "A1:F100"). If not provided, searches the entire sheet.

**output** `object`

**rows** `array` *required*

The rows that match the search criteria.

**array item properties**

**rowIndex** `number` *required*

The 1-based index of the row in the sheet.

**values** `array` *required*

The cell values in the row.

**totalMatches** `number` *required*

The total number of matching rows found.

### Get All Sheets in Spreadsheet

Returns all sheets in the spreadsheet.

**input** `object`

This Card has no input fields.

**output** `object`

**sheets** `array` *required*

The sheets present in the spreadsheet.

**array item properties**

**sheetId** `number` *required*

The ID of the sheet.

**title** `string` *required*

The name of the sheet.

**index** `number` *required*

The index of the sheet within the spreadsheet.

**isHidden** `boolean` *required*

Whether the sheet is hidden.

**hasProtectedRanges** `boolean` *required*

Whether the sheet has protected ranges.

**isFullyProtected** `boolean` *required*

Whether the entire sheet is protected.

### Get Info of a SpreadSheet

Returns the properties and metadata of the specified spreadsheet.

**input** `object`

**fields** `array` *required*

The fields to include in the response when retrieving spreadsheet properties and metadata. This is a list of field names. (eg. spreadsheetId, properties.title, sheets.properties.sheetId, sheets.properties.title)

**output** `object`

**spreadsheetId** `string | null`

The unique identifier of the spreadsheet.

**spreadsheetUrl** `string | null`

The URL of the spreadsheet.

**dataSources** `array`

The data sources connected to the spreadsheet.

**dataSourceSchedules** `array`

The schedules of the data sources.

**developerMetadata** `array`

The developer metadata associated with the spreadsheet.

**namedRanges** `array`

The named ranges defined in the spreadsheet.

**properties** `string`

The properties of the spreadsheet.

**sheets** `array`

The sheets present in the spreadsheet.

### Get Named Ranges

Returns all named ranges in the spreadsheet.

**input** `object`

This Card has no input fields.

**output** `object`

**namedRanges** `array` *required*

The named ranges defined in the spreadsheet.

**array item properties**

**namedRangeId** `string` *required*

The ID of the named range.

**name** `string` *required*

The name of the named range.

**range** `string` *required*

The range of the named range in A1 notation. (e.g. "A1:B2")

**sheetId** `number` *required*

The ID of the sheet the named range applies to.

### Get Protected Ranges

Returns all protected ranges in the spreadsheet.

**input** `object`

This Card has no input fields.

**output** `object`

**protectedRanges** `array` *required*

The protected ranges defined in the spreadsheet.

**array item properties**

**protectedRangeId** `number` *required*

The ID of the protected range.

**namedRangeId** `string` *required*

The ID of the named range, if the protected range is backed by a named range.

**range** `string` *required*

The range of the protected range in A1 notation. (e.g. "A1:B2")

**sheetId** `number` *required*

The ID of the sheet the protected range applies to.

**description** `string` *required*

The description of the protected range.

**warningOnly** `boolean` *required*

Whether the protection displays a warning but still allows editing.

**requestingUserCanEdit** `boolean` *required*

Whether the user adding the protection can edit the protected range.

### Get Row

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**rowIndex** `number` *required*

The 1-based row index to retrieve (e.g. 1 for the first row, 2 for the second row).

**startColumn** `string` (default: A)

The starting column letter (e.g. "A"). Defaults to "A".

**endColumn** `string`

The ending column letter (e.g. "Z"). If not provided, returns all columns with data.

**output** `object`

**found** `boolean` *required*

Whether the row exists and has data.

**row** `object | null` *required*

### Get Values

Returns the values of a range in the spreadsheet.

**input** `object`

**range** `string` *required*

The A1 notation of the range to retrieve. (e.g. "Sheet1!A1:B2")

**majorDimension** `enum\<string\>` (default: ROWS)

If it equals "ROWS", then the values are returned as rows. If it equals "COLUMNS", then the values are returned as columns.

Available options: `ROWS`, `COLUMNS`

**output** `object`

**range** `string` *required*

The range the values cover, in A1 notation. This range indicates the entire requested range, even though the values will exclude trailing rows and columns. (e.g. "Sheet1!A1:B2")

**majorDimension** `enum\<string\>` (default: ROWS)

If it equals "ROWS", then the values are returned in rows. If it equals "COLUMNS", then the values are returned in columns.

Available options: `ROWS`, `COLUMNS`

**values** `array` *required*

The data that was read. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension (a row or column). Each item in the inner array corresponds with one cell. (e.g. \[\["a", "b"], \["c", "d"]])

### Insert Row at Index

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**rowIndex** `number` *required*

The 1-based index where the new row should be inserted.

**values** `array`

Optional values to populate the new row.

**startColumn** `string` (default: A)

The starting column letter for the values (e.g. "A"). Defaults to "A".

**output** `object`

**insertedRowIndex** `number` *required*

The 1-based index of the newly inserted row.

### Move Sheet Horizontally

Moves a sheet to a new index in the spreadsheet.

**input** `object`

**sheetId** `number` *required*

The ID of the sheet to move.

**newIndex** `number` *required*

The new index of the sheet within the spreadsheet.

**output** `object`

This Card has no output.

### Protect Named Range

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

**input** `object`

**namedRangeId** `string` *required*

The ID of the named range to protect.

**warningOnly** `boolean`

Whether the protection displays a warning but still allows editing.

**requestingUserCanEdit** `boolean`

Whether the user adding the protection can edit the protected range.

**output** `object`

**protectedRangeId** `number` *required*

The ID of the new protected range.

### Rename Sheet

Renames a sheet in the spreadsheet.

**input** `object`

**sheetId** `number` *required*

The ID of the sheet to rename.

**newTitle** `string` *required*

The new title of the sheet.

**output** `object`

This Card has no output.

### Set Sheet Visibility

Sets the visibility of a sheet in the spreadsheet.

**input** `object`

**sheetId** `number` *required*

The ID of the sheet to set visibility.

**isHidden** `boolean` *required*

Whether the sheet is hidden.

**output** `object`

This Card has no output.

### Set Values

Sets values in a range in the spreadsheet.

**input** `object`

**range** `string` *required*

The A1 notation of the range to update. (e.g. "Sheet1!A1:B2")

**majorDimension** `enum\<string\>` (default: ROWS)

If it equals "ROWS", then the values are inserted as rows. If it equals "COLUMNS", then the values are inserted as columns.

Available options: `ROWS`, `COLUMNS`

**values** `array` *required*

The values to write to the range. This is an array of arrays, where each inner array represents a major dimension (a row or column) of data. (e.g. \[\["a", "b"], \["c", "d"]])

**output** `object`

**spreadsheetId** `string` *required*

The spreadsheet the updates were applied to.

**updatedRange** `string` *required*

The range (in A1 notation) that updates were applied to.

**updatedRows** `number` *required*

The number of rows where at least one cell in the row was updated.

**updatedColumns** `number` *required*

The number of columns where at least one cell in the column was updated.

**updatedCells** `number` *required*

The number of cells updated.

### Unprotect Range

Removes protection from a protected range in the spreadsheet.

**input** `object`

**protectedRangeId** `number` *required*

The ID of the protected range to unprotect.

**output** `object`

This Card has no output.

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**rowIndex** `number` *required*

The 1-based row index to update.

**values** `array` *required*

The values to write to the row, starting from the start column.

**startColumn** `string` (default: A)

The starting column letter for the update (e.g. "A"). Defaults to "A".

**output** `object`

**updatedRange** `string` *required*

The range (in A1 notation) that was updated.

**updatedCells** `number` *required*

The number of cells updated.

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

**input** `object`

**sheetName** `string`

The name of the sheet (e.g. "Sheet1"). If not provided, the first visible sheet is used.

**keyColumn** `string` *required*

The column letter to use for matching (e.g. "A" for ID column).

**keyValue** `string` *required*

The value to match in the key column.

**values** `array` *required*

The values to write to the row.

**startColumn** `string` (default: A)

The starting column letter for the values (e.g. "A"). Defaults to "A".

**output** `object`

**action** `enum\<string\>` *required*

Whether the row was updated or inserted.

Available options: `updated`, `inserted`

**rowIndex** `number` *required*

The 1-based index of the affected row.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.