# Google Sheets > Interact with Google Sheets to read and write data in spreadsheets. Add to your Google Sheet to allow the bot to read and write data. Source: https://cotera.co/docs/reference/tools/individual-tools/google-sheets --- **Authentication Type:** No Authentication\ **Description:** Interact with Google Sheets to read and write data in spreadsheets. Add 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:** ```json { "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:** ```json { "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:** ```json { "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:** ```json { "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