Google Sheets MCP Server

A Model Context Protocol (MCP) server that provides a secure bridge between MCP-compatible clients (like Claude Desktop) and the Google Sheets API.

Overview

This MCP server provides a secure interface for AI assistants to interact with Google Spreadsheets, enabling powerful automation and data manipulation workflows. It supports both Service Account and OAuth 2.0 authentication methods and runs as a containerized service for enhanced security.

Key Features

31 Tools for comprehensive spreadsheet manipulation
Service Account & OAuth 2.0 authentication support
Docker-based deployment with non-root user execution
Table-level operations for structured data management
Batch operations for efficient API usage
Conditional formatting with custom rules and formulas
CSV import/export capabilities

Architecture

Claude Desktop → MCP Gateway → Google Sheets Server → Google Sheets API
                                       ↓
                            Docker Desktop Secrets

Tools Available

Spreadsheet Management (3 tools)

list_spreadsheets - List spreadsheets from Drive folder or user access
create_spreadsheet - Create new spreadsheet
share_spreadsheet - Share with users/emails (reader, commenter, writer roles)

Sheet Operations (6 tools)

list_sheets - List all sheet names in a spreadsheet
create_sheet - Add new sheet (tab)
rename_sheet - Rename existing sheet
copy_sheet - Duplicate sheet within or across spreadsheets
add_columns - Add columns to sheet
add_conditional_formatting - Add conditional formatting rules
update_conditional_formatting - Update or move existing rules

Data Access (4 tools)

get_sheet_data - Read data from range (with optional grid metadata)
get_sheet_formulas - Read formulas from range
get_multiple_sheet_data - Fetch multiple ranges in one call
get_multiple_spreadsheet_summary - Get titles, headers, and preview rows

Data Modification (3 tools)

update_cells - Write data to specific range (overwrites)
batch_update_cells - Update multiple ranges in one call
add_rows - Append rows to end of sheet

Table Operations (11 tools)

list_tables - List defined tables (named ranges)
create_table - Create table with headers and optional data
get_table_data - Read table data with filters, limit, offset
insert_table_rows - Insert rows into table
update_table_rows - Update rows matching criteria
delete_table_rows - Delete rows matching criteria
add_table_columns - Add columns to table
rename_table_column - Rename table header
export_table_as_csv - Export table to CSV format
import_csv_to_table - Import CSV content into table

Prerequisites

Docker Desktop with MCP Toolkit enabled
Docker MCP CLI plugin (docker mcp command)
Google Cloud Project with:
- Google Sheets API enabled
- Google Drive API enabled
Credentials:
- Service Account JSON file (recommended), OR
- OAuth 2.0 credentials for user authentication

Quick Start

1. Build Docker Image

git clone <repository-url>
cd MCP_GoogleSheets
docker build -t googlesheets-mcp-server .

2. Configure Credentials

# Service Account (recommended)
docker mcp secret set GOOGLE_APPLICATION_CREDENTIALS="/path/to/service-account.json"
docker mcp secret set SERVICE_ACCOUNT_EMAIL="your-sa@project.iam.gserviceaccount.com"

# Optional: Specify default Drive folder
docker mcp secret set DRIVE_FOLDER_ID="your-folder-id"

3. Create Custom Catalog

Create or edit ~/.docker/mcp/catalogs/custom.yaml:

version: 2
name: custom
displayName: Custom MCP Servers
registry:
  googlesheets:
    description: "Bridge between MCP clients and Google Sheets API"
    title: "Google Sheets"
    type: server
    dateAdded: "2025-10-11T00:00:00Z"
    image: googlesheets-mcp-server:latest
    ref: ""
    tools:
      - name: list_spreadsheets
      - name: create_spreadsheet
      - name: get_sheet_data
      # ... (see readme.txt for complete list)
    secrets:
      - name: GOOGLE_APPLICATION_CREDENTIALS
        env: GOOGLE_APPLICATION_CREDENTIALS
      - name: SERVICE_ACCOUNT_EMAIL
        env: SERVICE_ACCOUNT_EMAIL
      - name: DRIVE_FOLDER_ID
        env: DRIVE_FOLDER_ID
    metadata:
      category: productivity
      tags: [google, sheets, spreadsheet, data]

4. Update Registry

Edit ~/.docker/mcp/registry.yaml and add:

registry:
  googlesheets:
    ref: ""

5. Configure Claude Desktop

Edit your Claude Desktop config file:

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

Add the custom catalog to the args array:

{
  "mcpServers": {
    "mcp-toolkit-gateway": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "-v", "/var/run/docker.sock:/var/run/docker.sock",
        "-v", "/Users/your_username/.docker/mcp:/mcp",
        "docker/mcp-gateway",
        "--catalog=/mcp/catalogs/docker-mcp.yaml",
        "--catalog=/mcp/catalogs/custom.yaml",
        "--config=/mcp/config.yaml",
        "--registry=/mcp/registry.yaml",
        "--tools-config=/mcp/tools.yaml",
        "--transport=stdio"
      ]
    }
  }
}

6. Restart Claude Desktop

Quit and restart Claude Desktop completely. Your Google Sheets tools should now be available!

Usage Examples

Basic Operations

"List all my spreadsheets"
"Create a new spreadsheet called 'Q1 Sales Data 2025'"
"Get data from Sheet1 range A1:D10 in spreadsheet [ID]"
"Add a new sheet called 'Revenue' to my spreadsheet"

Table Operations

"Create a table with headers ['Name', 'Email', 'Status'] in Sheet1"
"Get all data from the 'Customers' table where Status is 'Active'"
"Insert rows [['John', 'john@example.com', 'Active']] into the Users table"
"Export the 'Sales' table as CSV"

Advanced Features

"Share my spreadsheet with user@example.com as a writer"
"Add conditional formatting to highlight values > 100 in A1:D10"
"Update cells A1:B2 with data [[1,2],[3,4]]"
"Batch update multiple ranges: A1:B2 and D5:E6"

Data Format Examples

2D Array (for update_cells, add_rows)

[["Header1", "Header2"], ["Value1", "Value2"]]

Multiple Ranges (for batch_update_cells)

{"A1:B2": [[1, 2], [3, 4]], "D5": [["Hello"]]}

Filters (for get_table_data)

{"Status": "Active", "Country": "USA"}

Conditional Formatting Rule

{
  "type": "boolean",
  "condition": {
    "type": "NUMBER_GREATER",
    "values": [{"userEnteredValue": "100"}]
  },
  "format": {
    "backgroundColor": {"red": 1.0, "green": 0.8, "blue": 0.8},
    "textFormat": {"bold": true}
  }
}

Development

Local Testing

# Set environment variables
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/service-account.json"

# Run server
python googlesheets_server.py

# Test MCP protocol
echo '{"jsonrpc":"2.0","method":"tools/list","id":1}' | python googlesheets_server.py

Adding New Tools

Add function to googlesheets_server.py
Decorate with @mcp.tool()
Use single-line docstring only
Use empty string defaults (param: str = "")
Always return strings
Update catalog with new tool name
Rebuild Docker image

Implementation Rules

See CLAUDE.md for detailed guidelines:

NO multi-line docstrings (causes gateway panic)
NO type hints from typing module
NO None defaults (use "" instead)
Single-line docstrings ONLY
Always return strings from tools

Troubleshooting

Tools Not Appearing

# Check Docker image
docker images | grep googlesheets

# Verify server in list
docker mcp server list

# Check logs
docker logs [container_name]

Authentication Errors

# Verify secrets
docker mcp secret list

# Check APIs enabled in Google Cloud Console:
# - Google Sheets API
# - Google Drive API

Common Issues

Gateway panic: Check for multi-line docstrings in tools
JSON parse errors: Ensure valid JSON with double quotes
Empty results: Verify spreadsheet ID, sheet name, and range notation

Security

Credentials stored in Docker Desktop secrets (never hardcoded)
Server runs as non-root user (mcpuser)
Sensitive data never logged
Only operates on authorized spreadsheets

API Limits

Google Sheets API quotas:

500 requests per 100 seconds per project
100 requests per 100 seconds per user

Consider implementing request batching and caching for production use.

Files

googlesheets_server.py - Main MCP server implementation
Dockerfile - Container definition
requirements.txt - Python dependencies
readme.txt - Detailed installation guide
CLAUDE.md - Development guidelines for Claude Code
mcp-builder-prompt.md - Template prompt used to build this server

References

License

MIT License

Support

For issues and questions:

Check the Troubleshooting section
Review readme.txt for detailed setup instructions
See CLAUDE.md for implementation guidelines
Check Docker logs: docker logs [container_name]