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.

Authentication

To allow access to your Google Sheet:

1. Open your Google Sheet
2. Click "Share" in the top-right corner
3. Add coterabot@cotera-manual.iam.gserviceaccount.com with Editor permissions
4. Use your spreadsheet ID (found in the URL) when calling the operations

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