Skip to content

Google Sheets Tools Documentation

This document lists all available Google Sheets tools and their usage.

Tools

Tool Name Description Parameters
sheets_check_access Check access permissions for a spreadsheet Returns information about what operations are allowed
sheets_get_values Get values from a specified range in a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to retrieve (e.g., \"Sheet1!A1:C10\")
sheets_batch_get_values Get values from multiple ranges in a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- ranges (array): Array of A1 notation ranges to retrieve
sheets_get_metadata Get metadata about a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
sheets_update_values Update values in a specified range of a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to update
- values (array): 2D array of values to update
sheets_batch_update_values Update values in multiple ranges of a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- data (array): Array of range and values objects
sheets_append_values Append values to the end of a table in a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to append to
- values (array): 2D array of values to append
sheets_clear_values Clear values in a specified range of a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to clear
sheets_create_spreadsheet Create a new Google Sheets spreadsheet - title (string): The title of the new spreadsheet
- sheets (array, optional): Array of sheet properties to create
sheets_insert_sheet Add a new sheet to an existing Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- title (string): The title of the new sheet
sheets_delete_sheet Delete a sheet from a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- sheet_id (number): The ID of the sheet to delete
sheets_duplicate_sheet Duplicate a sheet within a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- source_sheet_id (number): The ID of the sheet to duplicate
- new_sheet_name (string, optional): Name for the duplicated sheet
sheets_copy_to Copy a sheet to another Google Sheets spreadsheet - source_spreadsheet_id (string): The ID of the source spreadsheet
- source_sheet_id (number): The ID of the sheet to copy
- destination_spreadsheet_id (string): The ID of the destination spreadsheet
sheets_update_sheet_properties Update properties of a sheet in a Google Sheets spreadsheet - spreadsheet_id (string): The ID of the spreadsheet
- sheet_id (number): The ID of the sheet
- properties (object): Sheet properties to update
sheets_format_cells Format cells in a Google Sheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to format
- format (object): Formatting properties (colors, fonts, alignment, number formats)
sheets_update_borders Update borders of cells in a Google Sheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range
- borders (object): Border properties
sheets_merge_cells Merge cells in a Google Sheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to merge
sheets_unmerge_cells Unmerge cells in a Google Sheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range to unmerge
sheets_add_conditional_formatting Add conditional formatting rules to a Google Sheet - spreadsheet_id (string): The ID of the spreadsheet
- range (string): The A1 notation range
- rule (object): Conditional formatting rule properties

Configuration

To use these tools, you need to configure the following:

  1. Google Cloud Project ID
  2. Service Account Key (JSON credentials file)

Step 1: Create a Google Cloud Project

If you don't already have a Google Cloud project, create one in the Google Cloud Console.

  1. Go to Google Cloud Console
  2. Click "Select a project" and then "NEW PROJECT"
  3. Enter a project name and click "Create"

Step 2: Enable Google Sheets API

  1. In your Google Cloud project, go to "APIs & Services" > "Library"
  2. Search for "Google Sheets API" and click "Enable"

Step 3: Create a Service Account

  1. In the Google Cloud console, go to Menu menu > IAM & Admin > Service Accounts. Click Create service account. Fill in the service account details, then click Create and continue.

  2. Navigate to "IAM & Admin" > "Service Accounts" in the left sidebar

  3. Click "Create Service Account"
  4. Enter a service account name (e.g., "sheets-datamate-integration"). Optionally add a description
  5. Click "Create and Continue"
  6. Grant the neccessary privileges
  7. Click "Done"

Step 4: Create and Download Service Account Key

  1. Select your service account. Click Keys > Add key > Create new key
  2. Select JSON, then click Create. Your new public/private key pair is generated and downloaded to your machine as a new file
  3. Save the downloaded JSON file

Step 5: Share Spreadsheets with Service Account

Now we are good to create our spreadsheet in Google Sheets and share it with our credentials. To do that, open your downloaded json file, copy the client_email information, and share your spreadsheet with this email. Please be certain to share it with the "can edit" option.

  1. Open your downloaded JSON service account key file
  2. Copy the client_email value (it will look like your-service-account@your-project-id.iam.gserviceaccount.com)
  3. Open any Google Sheets spreadsheet you want to access
  4. Click the "Share" button
  5. Add the service account email address
  6. Set permissions to "Editor" to allow full read/write access