integrations/gsheets/hub.md
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.
Unfortunately, automatic configuration is temporarily unavailable.
We are currently in the process of getting our Google Sheets integration verified by Google. Once this verification is complete, you will be able to use the automatic configuration method to set up the Google Sheets integration with just a few clicks. Until then, you will need to create your own Google Cloud Platform (GCP) Service Account by following the steps outlined in the Manual configuration using a service account section below.
Version 2.0.0 of the Google Sheets integration introduces changes to the Append Values action. If you are migrating from a previous version to 2.0.0, please note the following changes:
The range parameter has been replaced with two separate parameters: sheetName (optional) and startColumn (required).
range parameter in A1 notation (e.g., "Sheet1!A1:B2").sheetName (e.g., "Sheet1") and startColumn (e.g., "A") as separate parameters.The range is now automatically constructed from the start column to row 100,000. The action will search for existing data in this range to find the table and append values after the last row.
Migration guide: If you were using the old format with a range like "Sheet1!A1:B2", you should now use:
{
"sheetName": "Sheet1",
"startColumn": "A",
"values": [["value1", "value2"]]
}
If you were using a range without a sheet name like "A1:B2", you can now use:
{
"startColumn": "A",
"values": [["value1", "value2"]]
}
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.
Select a resource dropdown in the top navigation bar and selecting New Project.APIs & Services section.Library in the left sidebar.Google Sheets API and click on the result.Enable button to enable the Google Sheets API for your project.IAM & Admin section.Service Accounts in the left sidebar.Create service account button.Service account ID field.Done to proceed. There is no need to grant any roles or permissions at this stage.IAM & Admin section.Service Accounts in the left sidebar.Keys tab.Add Key button and select JSON.client_email field. This is the email address of the service account you created. Copy the email address, excluding the quotation marks. You will need this email address to share your spreadsheet with the service account and to configure the integration in Botpress.private_key field. This is the private key associated with the service account. Copy the private key, excluding the quotation marks. You will need this private key to configure the integration in Botpress.
This public key begins with
-----BEGIN PRIVATE KEY-----\nand ends with\n-----END PRIVATE KEY-----\n. You must copy the entire key: everything that is between the quotation marks.
Share button in the top right corner of the screen.Add people field.Editor permission to the service account by selecting it from the dropdown menu.Send button to share the spreadsheet with the service account.Please note: your organization may have restrictions on sharing spreadsheets with external users. If you are unable to share the spreadsheet with the service account email address, you may need to use a different account or ask your organization's administrator for help.
/spreadsheets/d/ and /edit in the URL.
https://docs.google.com/spreadsheets/d/1a2b3c4d5e6f7g8h9i0j/edit, the ID is 1a2b3c4d5e6f7g8h9i0j./spreadsheets/d/ and /edit parts./spreadsheets/d/ and /edit.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.
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.
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.
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.
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.
Sheet1!A1:B2 refers to all the cells in the first two rows and columns of Sheet1.Sheet1!A:A refers to all the cells in the first column of Sheet1.Sheet1!1:2 refers to all the cells in the first two rows of Sheet1.Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.A1:B2 refers to all the cells in the first two rows and columns of the first visible sheet.Sheet1 refers to all the cells in Sheet1.'Mike's_Data'!A1:D5 refers to all the cells in the first five rows and four columns of a sheet named "Mike's_Data."'My Custom Sheet'!A:A refers 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".MyNamedRange refers 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.
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"]
]
}
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)"]]
}
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 start column of the table. The action will search for existing data in that column and find the last row of the table, then 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:
{
"sheetName": "Sheet1",
"startColumn": "A",
"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:
{
"sheetName": "Sheet1",
"startColumn": "A",
"majorDimension": "ROWS",
"values": [
["Mike", "35", "SF"],
["Jane", "28", "Chicago"]
]
}
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:
{
"sheetName": "Sheet1",
"startColumn": "A",
"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.
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.
To change values instead of appending new ones, you can use the Set Values action. This action sets 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"]]
}
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.
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
}
]
}
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.
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.
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.
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
}
]
}
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.
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.
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.
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
}
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.
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.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.