gsheets-mcp

ajaysmb/gsheets-mcp

3.2

If you are the rightful owner of gsheets-mcp and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

The Google Sheets MCP Server provides local access to Google Sheets for reading, writing, and managing spreadsheet data with enhanced features.

Tools
5
Resources
0
Prompts
0

Google Sheets MCP Server

A Model Context Protocol (MCP) server that gives MCP Clients safe, local access to your Google Sheets for reading, writing, formatting, and managing spreadsheet data — with intelligent auto-formatting, chart creation, and token-efficient outputs.

Privacy note: The MCP server runs locally on your machine. Google Sheets data is fetched directly from Google's APIs to your machine and is only shared with Claude when you explicitly allow a tool call.

Table of Contents

What You Get

  • Read & Write data to any Google Sheets range using A1 notation (A1:C10, Sales!A:Z, etc.).
  • Auto-formatting that intelligently detects and applies formatting for dates, currency, percentages, and formulas.
  • Append data to sheets with smart placement and header detection.
  • Create charts (line, bar, column, pie, scatter) directly in spreadsheets.
  • Apply filters and perform batch operations efficiently.
  • Compact JSON responses to minimize LLM tokens and improve performance.
  • Intelligent caching to reduce API calls and speed up repeated operations.
  • URL flexibility - works with full Google Sheets URLs, short URLs, or direct spreadsheet IDs.

Prerequisites

  • A Google account with Google Sheets access.
  • Python 3.8+ installed.
  • An MCP host of your choice. This guide is configured for Claude Desktop (macOS or Windows).

1) Create a Google Cloud Project

  1. Open Google Cloud Console and sign in: https://console.cloud.google.com/
  2. Create a project: https://console.cloud.google.com/projectcreate Give it any name (e.g., Google Sheets MCP). Note the selected project in the top bar.

2) Enable the Google Sheets and Drive APIs

  1. While your project is selected, open the Google Sheets API page: https://console.cloud.google.com/apis/library/sheets.googleapis.com

  2. Click Enable.

  3. Also enable the Google Drive API (needed for spreadsheet metadata): https://console.cloud.google.com/apis/library/drive.googleapis.com

  4. Click Enable.

3) Configure the OAuth Consent Screen

  1. Go to the OAuth consent screen: https://console.cloud.google.com/apis/credentials/consent
  2. User type: choose External.
  3. Set Publishing status to Testing.
  4. Under Test users, click Add users and add the Google account(s) that will use this app (typically just your own).
  5. Fill in App name, User support email, and Developer contact information. Save.

Using Testing mode with Test users avoids a lengthy app verification process for personal use. The Google Sheets scopes requested are considered "sensitive," which is expected.

4) Create OAuth Client Credentials (Desktop App)

  1. Go to Credentials: https://console.cloud.google.com/apis/credentials
  2. Click Create credentials → OAuth client ID.
  3. Application type: choose Desktop app (recommended for local tools).
  4. After it's created, click Download JSON and save it next to gsheets_mcp.py as client_secret.json.

Important: Use Desktop app. "Web application" often causes redirect errors for local tools.

5) Get the Code

# clone this repo (or download ZIP and extract)
git clone https://github.com/yourname/gsheets-mcp.git
cd gsheets-mcp

Place your downloaded client_secret.json in this folder (the same folder as gsheets_mcp.py). The app will create token.json here after your first login.

Do not commit secrets. Both files are already in .gitignore:

client_secret.json
token.json

6) Install Python Dependencies

Install Requirements

macOS / Linux

python3 -V
python3 -m venv .venv
source .venv/bin/activate

pip install --upgrade pip
pip install -r requirements.txt

Windows (PowerShell)

py -3 -V
py -3 -m venv .venv
.\.venv\Scripts\Activate.ps1

python -m pip install --upgrade pip
pip install -r requirements.txt

7) First Run (Authorize and Create token.json)

Run the server once to complete Google sign-in and write token.json:

macOS / Linux

source .venv/bin/activate
python3 gsheets_mcp.py

Windows (PowerShell)

.\.venv\Scripts\Activate.ps1
python .\gsheets_mcp.py
  • Your browser opens a Google login/consent page.
  • Approve the requested scopes to allow the server to access your Google Sheets on your behalf.
  • After success, token.json is created next to the script. Keep this file safe and private.

If you see a redirect_uri_mismatch error, you likely created a Web application OAuth client instead of a Desktop app. Create a new Desktop app client and download it as client_secret.json.

You can close the terminal after you see a "Google Sheets MCP Server starting..." log message.

8) Wire Into Claude Desktop (MCP Config)

Claude Desktop reads a JSON config file named claude_desktop_config.json.

Where is the config file?

  • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
  • Windows: %APPDATA%\Claude\claude_desktop_config.json

In Claude Desktop you can also open this from Settings → Developer → Edit Config.

Minimal configuration examples

macOS

{
  "mcpServers": {
    "gsheets-mcp": {
      "command": "python3",
      "args": ["/ABSOLUTE/PATH/TO/gsheets_mcp.py"],
      "env": {
        "PYTHONIOENCODING": "utf-8",
        "GSHEETS_CACHE_TTL": "300"
      }
    }
  }
}

Windows

{
  "mcpServers": {
    "gsheets-mcp": {
      "command": "python",
      "args": ["C:\\\\ABSOLUTE\\\\PATH\\\\TO\\\\gsheets_mcp.py"],
      "env": {
        "PYTHONIOENCODING": "utf-8",
        "GSHEETS_CACHE_TTL": "300"
      }
    }
  }
}

Optional Environment Variables

  • GSHEETS_CACHE_TTL: Cache timeout in seconds (default: 300)
  • GSHEETS_TOKEN_FILE: Custom path for token.json file
  • GSHEETS_CREDENTIALS_FILE: Custom path for client_secret.json file
  • GSHEETS_DEBUG: Set to "true" for debug logging

After saving the config, fully quit and restart Claude Desktop. You should see the MCP hammer icon indicating the server is available.

9) Use It in Claude

Try prompts like:

  • "Read data from cells A1:E10 in my sales spreadsheet and analyze the trends."
  • "Create a bar chart from the data in range A1:C5 of the 'Q4 Results' sheet."
  • "Add these new sales records to the bottom of my tracking spreadsheet."
  • "Apply a filter to show only rows where column C contains 'Completed'."
  • "Format the currency column with proper dollar formatting."
  • "Write a formula to calculate the sum of column D."

Claude will ask for your approval before each tool call.

Tool Reference

All outputs use compact JSON with efficient data structures to reduce LLM tokens and improve performance.

read_range

Read cell values from a Google Sheets range with auto-discovery of spreadsheet structure.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • sheet_name (optional): Sheet name (uses first sheet if omitted)
  • range (required): A1 notation range (e.g., "A1:C10", "A:A")
  • include_formatting (optional): Include cell formatting info (default: false)

Example Response:

{
  "values": [
    ["Name", "Price", "Date"],
    ["Product A", 29.99, "2024-01-15"],
    ["Product B", 39.99, "2024-01-16"]
  ],
  "range": "Sheet1!A1:C3",
  "majorDimension": "ROWS"
}

write_range

Write/update values to a specified range with intelligent auto-formatting.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • sheet_name (optional): Sheet name
  • range (required): A1 notation range
  • values (required): 2D array of values
  • auto_format (optional): Auto-detect and apply formatting (default: true)
  • value_input_option (optional): "RAW" or "USER_ENTERED" (default: "USER_ENTERED")

Example Response:

{
  "spreadsheetId": "1ABC123...",
  "updatedRange": "Sheet1!A1:C3",
  "updatedRows": 3,
  "updatedColumns": 3,
  "updatedCells": 9
}

append_data

Add new rows to the end of a sheet with intelligent placement and header detection.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • sheet_name (optional): Sheet name
  • data_rows (required): Array of rows to append
  • auto_format (optional): Auto-detect formatting (default: true)

Example Response:

{
  "spreadsheetId": "1ABC123...",
  "tableRange": "Sheet1!A1:D5",
  "updates": {
    "updatedRange": "Sheet1!A4:D5",
    "updatedRows": 2,
    "updatedColumns": 4,
    "updatedCells": 8
  }
}

create_chart

Create an embedded chart directly in the spreadsheet.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • sheet_name (optional): Sheet to place chart
  • chart_type (required): "LINE", "BAR", "COLUMN", "PIE", or "SCATTER"
  • data_range (required): A1 notation for chart data
  • title (optional): Chart title
  • position (optional): Chart position {"row": 0, "col": 5}

Example Response:

{
  "chartId": 1234567890,
  "position": {"sheetId": 0, "overlayPosition": {"anchorCell": {"rowIndex": 0, "columnIndex": 5}}},
  "chartType": "COLUMN"
}

apply_filter

Apply basic filters to a data range with column-specific conditions.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • sheet_name (optional): Sheet name
  • range (required): A1 notation for filter range
  • column_filters (required): Column-based filter conditions

Filter Conditions:

  • TEXT_CONTAINS: Text contains value
  • TEXT_EQ: Text equals value exactly
  • NUMBER_GREATER: Number greater than value
  • NUMBER_LESS: Number less than value

Example Request:

{
  "column_filters": {
    "Status": {"condition": "TEXT_CONTAINS", "value": "Completed"},
    "Amount": {"condition": "NUMBER_GREATER", "value": "100"}
  }
}

batch_update

Execute multiple operations efficiently in a single request.

Parameters:

  • spreadsheet_url (required): Google Sheets URL or spreadsheet ID
  • operations (required): Array of operations to execute

Supported Operations:

  • clear_range: Clear cell contents
  • sort_range: Sort data ranges
  • format_cells: Apply cell formatting
  • merge_cells: Merge cell ranges
  • create_sheet: Add new sheets
  • delete_sheet: Remove sheets

Example Response:

{
  "spreadsheetId": "1ABC123...",
  "replies": [
    {"addSheet": {"properties": {"sheetId": 123, "title": "New Sheet"}}},
    {"updateCells": {"updatedRows": 5, "updatedColumns": 3}}
  ]
}

Smart Features

Auto-Format Detection

The MCP automatically detects and applies appropriate formatting for different data types:

  • Dates: Recognizes formats like "2024-01-15", "1/15/2024", "Jan 15, 2024"
  • Currency: Handles "$1,234.56" format with proper number formatting
  • Percentages: Converts "15.5%" to decimal values with percentage formatting
  • Numbers: Processes numeric values with thousands separators
  • Formulas: Recognizes Excel/Sheets formulas starting with "=" and preserves them
  • Text: Preserves text formatting and handles special characters

Intelligent Caching System

  • Spreadsheet metadata cached for 5 minutes by default (configurable)
  • Structure discovery cached to avoid repeated API calls for sheet names and ranges
  • Automatic cache invalidation when data is modified
  • Memory-efficient cleanup of expired cache entries
  • Reduces API quota usage significantly for repeated operations

URL Processing Flexibility

Accepts various Google Sheets URL formats automatically:

Full URL: https://docs.google.com/spreadsheets/d/1ABC123.../edit#gid=0
Short URL: https://docs.google.com/spreadsheets/d/1ABC123...
Direct ID: 1ABC123...

Smart Range Expansion

  • Auto-detects data boundaries when using partial ranges like "A:A"
  • Handles merged cells intelligently
  • Preserves formatting across range operations
  • Optimizes API calls by batching range requests

Security Tips

  • Keep client_secret.json and token.json private. Do not commit them to Git.

  • Both files are automatically excluded by the included .gitignore.

  • To revoke access:

    • Delete token.json and run the server again to re-consent later, or
    • Visit Google account permissions and remove the app: https://myaccount.google.com/permissions

  • Scopes requested (minimal necessary permissions):

    • https://www.googleapis.com/auth/spreadsheets (read/write Google Sheets)
    • https://www.googleapis.com/auth/drive.metadata.readonly (read spreadsheet metadata only)

Troubleshooting

Redirect URI mismatch You probably created a Web application OAuth client. Make a Desktop app OAuth client and download it as client_secret.json.

"This app is blocked" or missing scopes Keep the OAuth consent screen in Testing and add your account under Test users.

403 / insufficient permissions

  • Delete token.json and run the server again to re-consent with the latest scopes.
  • Ensure the Google account has edit access to the spreadsheet you're trying to modify.
  • Check that both Google Sheets API and Drive API are enabled in your project.

"Spreadsheet not found" errors

  • Verify the spreadsheet URL or ID is correct.
  • Ensure the spreadsheet is shared with your Google account.
  • Check that the spreadsheet hasn't been deleted or moved to trash.

Claude can't see the server

  • Restart Claude Desktop after editing the config.
  • Double-check the absolute path and JSON syntax in claude_desktop_config.json.
  • On Windows, ensure PYTHONIOENCODING=utf-8 is set as in the example.
  • Check that Python and all dependencies are correctly installed.

Auto-formatting not working

  • Ensure auto_format=true is set (default).
  • Check that value_input_option="USER_ENTERED" is used (default).
  • Some formatting may require specific locale settings in your Google Sheets.

Rate limiting / quota exceeded

  • The server implements intelligent caching to minimize API calls.
  • If you hit quotas, wait a few minutes or increase cache TTL.
  • Consider using batch operations for multiple changes.

Chart creation fails

  • Ensure the data range contains valid numeric data for chart types.
  • Check that the target sheet exists and you have edit permissions.
  • Pie charts require exactly 2 columns (labels and values).

Firewall/browser issues during auth Allow the local browser window to open and http://localhost:<random-port> callbacks. Temporarily allow pop-ups if blocked.

Uninstall / Remove

  1. Remove the "gsheets-mcp" block from claude_desktop_config.json and restart Claude Desktop.
  2. Delete token.json and optionally client_secret.json.
  3. Remove the virtual environment: rm -rf .venv (macOS/Linux) or rmdir /s .venv (Windows).
  4. Revoke the app in your Google Account permissions if desired: https://myaccount.google.com/permissions

License (MIT)

Copyright (c) 2025

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the authors or copyright holders be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the Software or the use or other dealings in the Software.