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

1. Add function to googlesheets_server.py
2. Decorate with @mcp.tool()
3. Use single-line docstring only
4. Use empty string defaults (param: str = "")
5. Always return strings
6. Update catalog with new tool name
7. 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

• Google Sheets API
• Google Drive API
• Model Context Protocol
• FastMCP Framework

License

MIT License

Support

For issues and questions:

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