Supercharge your workflow with the Google Sheets integration, simplifying interactions with your spreadsheet data. Easily update, append, and retrieve values to keep your spreadsheets up-to-date. From tracking inventory and managing project tasks to organizing event attendees, Google Sheets integration streamlines your data management tasks. Give your bot new abilities like add new entries, update existing records, and retrieve essential information. Stay agile and organized by dynamically adding new sheets to accommodate evolving data needs, ensuring your spreadsheets remain flexible and scalable.
Configuration
Automatic configuration with OAuth
To set up the Google Sheets integration using OAuth, click the authorization button and follow the on-screen instructions to connect your Botpress chatbot to Google Sheets.
When using this configuration mode, a Botpress-managed Google Sheets application will be used to connect to your Google account. However, actions taken by the bot will be attributed to the user who authorized the connection, rather than the application. For this reason, we do not recommend using personal Google accounts for this integration. You should set up a service account and use this account to authorize the connection.
Once the connection is established, you must specify the identifier of the Google Spreadsheet you want to interact with. This identifier is the long string of characters in the URL between /spreadsheets/d/ and /edit when you are editing a spreadsheet.
For example, if the URL is
https://docs.google.com/spreadsheets/d/1a2b3c4d5e6f7g8h9i0j/edit, the identifier of the spreadsheet is1a2b3c4d5e6f7g8h9i0j.
- Find your Google Spreadsheet ID for the spreadsheet you want to interact with.
- Authorize the Google Sheets integration by clicking the authorization button.
- Fill in the Spreadsheet ID field and save the configuration.
Manual configuration using a service account
- Login to Google Cloud Console and create a new project.
- Enable Google Sheets API for the project.
- Create a service account for the project. This integration won't work with any other type of credentials.
- Download the JSON credentials file and save it somewhere safe.
- The downloaded JSON file contains a
client_emailfield. Share your spreadsheet with this email address to give it access. - Install this integration in your bot with the following configuration:
- Spreadsheet ID: The ID of the Google Spreadsheet to interact with. When editing a spreadsheet, the ID is the long string of characters in the URL between
/spreadsheets/d/and/edit.- For example, if the URL is
https://docs.google.com/spreadsheets/d/1a2b3c4d5e6f7g8h9i0j/edit, the ID is1a2b3c4d5e6f7g8h9i0j.
- For example, if the URL is
- Service account private key: The private key from the Google service account. You can get it from the downloaded JSON file.
- Service account email: The client email from the Google service account. You can get it from the downloaded JSON file.
Managing several sheets
While this integration allows you to interact with a single Google Spreadsheet, you can manage multiple sheets within that spreadsheet. To interact with a specific sheet, you must specify the sheet name as part of the range when performing operations.
The range field uses the same notation as Google Sheets. For example, to interact with a sheet named Sheet1, you would use the range Sheet1!A1:B2.
Key concepts
Spreadsheet
A spreadsheet is the primary object in Google Sheets. It can contain multiple sheets, each with structured information contained in cells. Each spreadsheet has a unique identifier called the Spreadsheet ID.
Spreadsheet ID
The Spreadsheet ID is a unique identifier for a Google spreadsheet. It is a long string of characters that can be found in the URL when editing a spreadsheet. The ID is located between /spreadsheets/d/ and /edit.
Sheet
A sheet is a page or tab within a spreadsheet that contains a grid of cells. Each sheet has a unique name and can contain data, formulas, and formatting.
Range, A1 notation
The range specifies the sheet and cell range to interact with in the Google Spreadsheet. The range must be given in A1 notation, which uses the following format: SheetName!A1:B2. The range includes the sheet name followed by an exclamation mark and the cell range.
While the sheet name is optional, it is recommended to include it to avoid ambiguity when interacting with multiple sheets within the same spreadsheet. If it is omitted, the first visible sheet is used.
A1 notation examples
Sheet1!A1:B2refers to all the cells in the first two rows and columns of Sheet1.Sheet1!A:Arefers to all the cells in the first column of Sheet1.Sheet1!1:2refers to all the cells in the first two rows of Sheet1.Sheet1!A5:Arefers to all the cells of the first column of Sheet 1, from row 5 onward.A1:B2refers to all the cells in the first two rows and columns of the first visible sheet.Sheet1refers to all the cells in Sheet1.'Mike's_Data'!A1:D5refers to all the cells in the first five rows and four columns of a sheet named "Mike's_Data."'My Custom Sheet'!A:Arefers to all the cells in the first column of a sheet named "My Custom Sheet."'My Custom Sheet'refers to all the cells in "My Custom Sheet".MyNamedRangerefers to all the cells in the named range "MyNamedRange".
Please note: single quotes are required for sheet names with spaces, special characters, or an alphanumeric combination.
Major dimension
The major dimension specifies whether the data is arranged in rows or columns. The major dimension can be either ROWS or COLUMNS. When performing operations like updating or retrieving values, you can optionally specify the major dimension. If not specified, it defaults to ROWS.
For example, assuming the range Sheet1!A1:F3 contains the following data:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 | 6 |
| 2 | 7 | 8 | 9 | 10 | 11 | 12 |
| 3 | 13 | 14 | 15 | 16 | 17 | 18 |
If the major dimension is set to ROWS, the data will be returned as follows:
{
"values": [
["1", "2", "3", "4", "5", "6"],
["7", "8", "9", "10", "11", "12"],
["13", "14", "15", "16", "17", "18"]
]
}
If the major dimension is set to COLUMNS, the data will be returned as follows:
{
"values": [
["1", "7", "13"],
["2", "8", "14"],
["3", "9", "15"],
["4", "10", "16"],
["5", "11", "17"],
["6", "12", "18"]
]
}
Values
The values array contains the data retrieved from the Google Spreadsheet. The data is returned as an array of arrays, with each inner array representing a major dimension (a row or column) of data.
Important: the values are always returned as strings, regardless of the original data type in the spreadsheet. Likewise, when updating values, you must provide the data as strings. Google Sheets will then automatically convert the data to the appropriate type.
The values array accepts all data types supported by Google Sheets, including text, numbers, dates, and formulas.
For example, if you want to update the range Sheet1!A3:A6 with the values 1, 2, 3, and the formula =SUM(A3:A5), you would provide the following data:
{
"range": "Sheet1!A3:A6",
"majorDimension": "ROWS",
"values": [["1", "2", "3", "=SUM(Sheet1!A3:A5)"]]
}
Usage
Inserting, modifying, and retrieving values from cells
Inserting rows at the end of a table
To insert a new row of data at the end of a table, you can use the Append Values action. This action appends a new row of data after all other rows of data.
To use this action, you must specify the range of one of the rows in the table. This could be the header row or any other row of the table. The action will then find the last row of the table and append the new data after it.
For example, if you have a table with the following data in a sheet called Sheet1:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Age | City |
| 2 | John | 30 | NY |
| 3 | Alice | 25 | LA |
To append a new row with the data Mike, 35, SF, you could use the following configuration:
{
"range": "Sheet1!A1:C1",
"majorDimension": "ROWS",
"values": [["Mike", "35", "SF"]]
}
You can insert multiple rows at once by providing multiple rows of data in the values field. For example, to append two new rows with the data Mike, 35, SF and Jane, 28, Chicago, you could use the following configuration:
{
"range": "Sheet1!A1:C1",
"majorDimension": "ROWS",
"values": [
["Mike", "35", "SF"],
["Jane", "28", "Chicago"]
]
}
Inserting columns at the end of a table
If you have a table in which data is arranged in columns instead of rows, you can still use the Append Values action to append new columns of data at the end of the table.
For example, if you have a table with the following data in a sheet called Sheet1:
| A | B | C | |
|---|---|---|---|
| 1 | Name | John | Alice |
| 2 | Age | 30 | 25 |
| 3 | City | NY | LA |
To append a new column with the data Mike, 35, SF, you could use the following configuration:
{
"range": "Sheet1!A1:A3",
"majorDimension": "COLUMNS",
"values": [["Mike", "35", "SF"]]
}
The Major Dimension field is the field that determines whether the data is arranged in rows or columns. If you are appending columns, set the Major Dimension field to COLUMNS.
Obtaining values from a cell range
To retrieve values from a cell range, you can use the Get Values action. This action retrieves the values from the specified range in the Google Spreadsheet.
For example, if you have the following data in a sheet called Sheet1:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 |
| 2 | 5 | 6 | 7 | 8 |
| 3 | 9 | 10 | 11 | 12 |
To retrieve the values from the range Sheet1!A1:C3, you could use the following configuration:
{
"range": "Sheet1!A1:C3",
"majorDimension": "ROWS"
}
The values will be returned in the following format:
{
"values": [
["1", "2", "3"],
["5", "6", "7"],
["9", "10", "11"]
]
}
If your data is arranged as columns instead of rows, you can set the Major Dimension field to
COLUMNS.
Updating values in a cell range
To change values instead of appending new ones, you can use the Update Values action. This action updates the values in the specified range in the Google Spreadsheet.
For example, if you have the following data in a sheet called Sheet1:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | City | Job |
| 2 | John | 30 | NY | Engineer |
| 3 | Alice | 25 | LA | Designer |
To change Alice's age to 26 and job to Developer, you could use the following configuration:
{
"range": "Sheet1!B3:C3",
"majorDimension": "ROWS",
"values": [["26", "LA", "Developer"]]
}
If instead you want to change the city of both John and Alice to SF, you could use the following configuration:
{
"range": "Sheet1!C2:C3",
"majorDimension": "ROWS",
"values": [["SF"], ["SF"]]
}
This is equivalent to:
{
"range": "Sheet1!C2:C3",
"majorDimension": "COLUMNS",
"values": [["SF", "SF"]]
}
Creating and manipulating sheets
Creating a new sheet
To create a new sheet in the Google Spreadsheet, you can use the Add Sheet action. This action creates a new sheet with the specified name and places it at the end of the list of sheets.
Obtaining the list of all sheets
To retrieve the list of all sheets in the Google Spreadsheet, you can use the Get All Sheets in Spreadsheet action. This action returns the names and id of all sheets in the spreadsheet.
It will return a JSON object with the following structure:
{
"sheets": [
{
"sheetId": 904893745,
"title": "Time sheet",
"index": 0,
"isHidden": false,
"hasProtectedRanges": false,
"isFullyProtected": false
},
{
"sheetId": 937004904,
"title": "Stats",
"index": 1,
"isHidden": false,
"hasProtectedRanges": true,
"isFullyProtected": false
}
]
}
Moving a sheet horizontally
If you want a sheet to appear before or after another sheet, you can use the Move Sheet Horizontally action. This action moves the specified sheet to the specified position in the list of sheets.
To use this action, you must first retrieve the id of the sheet you want to move using the Get All Sheets in Spreadsheet action.
For example, if you want to move the sheet named Stats to the first position in the list of sheets, you could use the following configuration:
{
"sheetId": 937004904,
"newIndex": 0
}
When changing the order of sheets, the new position is based on their current order. For example, if you have three sheets (S1, S2, S3) and you want to move S1 to be after S2, you would set the index to 2. A request to change a sheet's position will be ignored if the new index is the same as the current index or if it is one more than the current index.
Working with named ranges
When working with large or complex spreadsheets, it can be helpful to define named ranges for specific cell ranges. Named ranges provide a convenient way to reference a specific cell range by a meaningful name.
Creating a named range
To create a new named range in the Google Spreadsheet, you can use the Create Named Range in Sheet action. This action creates a new named range with the specified name and range.
To use this action, you must first retrieve the id of the sheet you want to move using the Get All Sheets in Spreadsheet action.
For example, if you want to create a named range called MyNamedRange that refers to the range Sheet1!A1:B2, you could use the following configuration:
{
"sheetId": 937004904, // The id the sheet; not its name
"name": "MyNamedRange",
"range": "Sheet1!A1:B2"
}
Once the named range is created, you can reference it by name in other actions that require a range.
Obtaining the list of all named ranges
To retrieve the list of all named ranges in the Google Spreadsheet, you can use the Get Named Ranges action. This action returns the names, ids, and ranges of all named ranges in the spreadsheet.
For example, if you have two named ranges in the spreadsheet, MyRange and NamedRange1, the action would return data similar to the following:
{
"namedRanges": [
{
"namedRangeId": "1001473037",
"name": "MyRange",
"range": "A1",
"sheetId": 206659759
},
{
"namedRangeId": "lkk0nrl90uiy",
"name": "NamedRange1",
"range": "F28:F32",
"sheetId": 937004904
}
]
}
Working with protected ranges
Google Sheets allows you to protect specific ranges of cells to prevent them from being edited. Protected ranges can be useful when you want to ensure that certain data remains unchanged.
Protecting a named range
To created a protected range from a previously-defined named range, you can use the Protect Named Range action. This action creates a protected range from the specified named range.
To use this action, you must first retrieve the id of the named range you want to protect using the Get Named Ranges action.
For example, if you have a named range called MyNamedRange, you could use the following configuration to protect this range:
{
"namedRangeId": "1001473037",
"warningOnly": false,
"requestingUserCanEdit": true
}
In the above example, the warningOnly field specifies whether a warning should be displayed when users try to edit the protected range. If this mode is activated, users are still able to edit the range if they dismiss the warning.
The requestingUserCanEdit field specifies whether the user who requested the protection can edit the protected range, regardless of the warningOnly option.
Obtaining the list of all protected ranges
To retrieve the list of all protected ranges in the Google Spreadsheet, you can use the Get Protected Ranges action. This action returns the ids, ranges, and permissions of all protected ranges in the spreadsheet.
{
"protectedRanges": [
{
"protectedRangeId": 1815142986,
"namedRangeId": "",
"range": ":",
"sheetId": 937004904,
"description": "",
"warningOnly": true,
"requestingUserCanEdit": true
},
{
"protectedRangeId": 640323292,
"namedRangeId": "lkk0nrl90uiy",
"range": "F28:F32",
"sheetId": 937004904,
"description": "",
"warningOnly": false,
"requestingUserCanEdit": true
}
]
}
In the above example, the first protect range is a warning-only range that covers the entire sheet with id 937004904, while the second protected range is a range that covers the entirety of the named range with id lkk0nrl90uiy.
Unprotecting a range
To remove protection from a previously protected range, you can use the Unprotect Range action. This action removes the protection from the specified range.
To use this action, you must first retrieve the id of the protected range you want to unprotect using the Get Protected Ranges action.
For example, if you have a protected range with the id 1815142986, you could use the following configuration to unprotect this range:
{
"protectedRangeId": 1815142986
}
Working with formulas
When inserting or updating values in a cell range, you can include formulas in the data. Google Sheets will automatically interpret the data as a formula and store it in the cell.
Please make sure to include the = sign at the beginning of the formula to indicate that it is a formula.
Querying metadata
To obtain metadata about the spreadsheet or its sheets, you can use the Get Info of a SpreadSheet action. In the Field name field, you can specify the metadata you want to retrieve.
For example, to retrieve the title, locale, and time zone of the spreadsheet, you could use the following configuration:
{
"fields": ["properties.title", "properties.locale", "properties.timeZone"]
}
The action will return an object with the requested metadata:
{
"properties": {
"title": "My Spreadsheet",
"locale": "en_US",
"timeZone": "America/New_York"
}
}
Note: Using wildcards to retrieve all metadata fields is supported, but it is not recommended as it can quickly exhaust your API limits. For instance, you could use
*to fetch all metadata fields orsheets.properties.*to fetch all property fields of all sheets.
Here are some examples of metadata fields you can query:
properties.title: The title of the spreadsheet.properties.locale: The locale of the spreadsheet.properties.timeZone: The time zone of the spreadsheet.properties.autoRecalc: The auto-recalculation setting of the spreadsheet.properties.defaultFormat: The default format of the spreadsheet.sheets.properties.title: The title of all sheets.sheets.properties.sheetId: The ID of all sheets.sheets.properties.gridProperties.rowCount: The number of rows in all sheets.sheets.properties.gridProperties.columnCount: The number of columns in all sheets.namedRanges.namedRangeId: The ID of all named ranges.
Limitations
Standard Google Sheets API limitations apply to the Google Sheets integration in Botpress. These limitations include rate limits, payload size restrictions, and other constraints imposed by the Google Cloud platform. Ensure that your chatbot adheres to these limitations to maintain optimal performance and reliability.
More details are available in the Google Sheets API documentation.
