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]