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

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
• 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