Google Sheets
Authentication Type: No Authentication
Description: Interact with Google Sheets to read and write data in spreadsheets. Add coterabot@cotera-manual.iam.gserviceaccount.com to your Google Sheet to allow the bot to read and write data.
Sheets
Manage data in Google Sheets spreadsheets including adding rows and reading data.
Append Row
Add a new row of data to the bottom of a Google Sheets spreadsheet. The data will be appended to the end of the existing content.
Operation Type: Mutation (Write)
Parameters:
- spreadsheetId string(required): The ID of the Google Sheets spreadsheet. This can be found in the URL of the sheet
- sheetName string(required): The name of the specific sheet/tab within the spreadsheet. If not provided, defaults to the first sheet
- values array of strings(required): An array of values to append as a new row. Each value corresponds to a column in the sheet
Returns:
- spreadsheetId string: The ID of the spreadsheet that was updated
- tableRange string: The range where the data was appended
- updatedRows number: Number of rows that were updated
- updatedColumns number: Number of columns that were updated
- updatedCells number: Number of cells that were updated
Example Usage:
{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Sheet1",
  "values": ["John Doe", "john.doe@example.com", "Sales Manager", "2024-12-01"]
}
Read Range
Read data from a specific range in a Google Sheets spreadsheet. Supports A1 notation and various formatting options.
Operation Type: Query (Read)
Parameters:
- spreadsheetId string(required): The ID of the Google Sheets spreadsheet. This can be found in the URL of the sheet
- range string(required): The range to read from in A1 notation (e.g., "Sheet1!A1:D5", "Sheet1!B:B", or "A1:D5")
- majorDimension string(nullable): Whether values should be organized by rows or columns. Options: "ROWS", "COLUMNS". Defaults to "ROWS"
- valueRenderOption string(nullable): How values should be rendered. Options: "FORMATTED_VALUE", "UNFORMATTED_VALUE", "FORMULA". Defaults to "FORMATTED_VALUE"
- dateTimeRenderOption string(nullable): How dates should be rendered. Options: "SERIAL_NUMBER", "FORMATTED_STRING". Defaults to "FORMATTED_STRING"
Returns:
- range string: The range that was read
- majorDimension string: The dimension organization used ("ROWS" or "COLUMNS")
- values array of arrays of strings(nullable): The data that was read, as an array of arrays. Each sub-array represents a row (or column if majorDimension is COLUMNS). Will be null if no data is found
Example Usage:
{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "range": "Sheet1!A1:D10",
  "majorDimension": "ROWS",
  "valueRenderOption": "FORMATTED_VALUE",
  "dateTimeRenderOption": "FORMATTED_STRING"
}
Read Multiple Ranges
Read data from multiple ranges in a Google Sheets spreadsheet in a single request. Efficiently fetch data from different parts of the sheet or multiple sheets.
Operation Type: Query (Read)
Parameters:
- spreadsheetId string(required): The ID of the Google Sheets spreadsheet. This can be found in the URL of the sheet
- ranges array of strings(required): Array of ranges to read from in A1 notation (e.g., ["Sheet1!A1:D5", "Sheet2!B:B"])
- majorDimension string(nullable): Whether values should be organized by rows or columns. Options: "ROWS", "COLUMNS". Defaults to "ROWS"
- valueRenderOption string(nullable): How values should be rendered. Options: "FORMATTED_VALUE", "UNFORMATTED_VALUE", "FORMULA". Defaults to "FORMATTED_VALUE"
- dateTimeRenderOption string(nullable): How dates should be rendered. Options: "SERIAL_NUMBER", "FORMATTED_STRING". Defaults to "FORMATTED_STRING"
Returns:
- spreadsheetId string: The ID of the spreadsheet that was read
- valueRanges array of objects: Array of range results- range string: The range that was read
- majorDimension string: The dimension organization used
- values array of arrays of strings(nullable): The data that was read for this range
 
- range 
Example Usage:
{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "ranges": ["Sheet1!A1:C5", "Sheet1!F1:H5", "Sheet2!A:A"],
  "majorDimension": "ROWS",
  "valueRenderOption": "FORMATTED_VALUE",
  "dateTimeRenderOption": "FORMATTED_STRING"
}
Search Values
Search for specific values within a Google Sheets spreadsheet. Supports text search, regex patterns, case-sensitive matching, and exact cell matching across entire spreadsheets or specific ranges.
Operation Type: Query (Read)
Parameters:
- spreadsheetId string(required): The ID of the Google Sheets spreadsheet. This can be found in the URL of the sheet
- searchValue string(required): The value to search for in the spreadsheet
- range string(nullable): The range to search within in A1 notation (e.g., "Sheet1!A1:D5"). If not provided, searches the entire spreadsheet
- matchCase boolean(nullable): Whether the search should be case-sensitive. Defaults to false
- matchEntireCell boolean(nullable): Whether to match the entire cell content or allow partial matches. Defaults to false (partial matches allowed)
- useRegex boolean(nullable): Whether to treat searchValue as a regular expression. Defaults to false
- searchByRegex string(nullable): Alternative regex pattern to search by. If provided, this takes precedence over searchValue and useRegex is automatically true
Returns:
- matches array of objects: Array of match results- range string: The A1 notation of the cell where the match was found
- value string: The actual value found in the cell
- row number: The row number (1-indexed)
- column number: The column number (1-indexed)
- sheetName string(nullable): The name of the sheet where the match was found
 
- range 
- totalMatches number: Total number of matches found
Example Usage:
{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "searchValue": "john.doe@example.com",
  "range": "Sheet1!A1:Z100",
  "matchCase": false,
  "matchEntireCell": true,
  "useRegex": false,
  "searchByRegex": null
}
Common Use Cases
Data Entry and Management:
- Append new leads or customer data to tracking spreadsheets
- Add daily sales records or inventory updates automatically
- Log form submissions or survey responses to organized sheets
Data Analysis and Reporting:
- Read specific ranges to extract KPIs and metrics for dashboards
- Fetch multiple data ranges simultaneously for comprehensive reports
- Search for specific values to identify trends or outliers
Data Integration:
- Sync external system data with Google Sheets for collaborative editing
- Read formatted data for use in other applications or workflows
- Search and validate data entries before processing in external systems
Automated Workflows:
- Append timestamped logs or audit trails to tracking sheets
- Read configuration data or lookup tables for automated processes
- Search for specific records to trigger follow-up actions or notifications