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

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

2. Configure Credentials

3. Create Custom Catalog

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

4. Update Registry

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

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:

6. Restart Claude Desktop

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

Usage Examples

Basic Operations

Table Operations

Advanced Features

Data Format Examples

2D Array (for update_cells, add_rows)

Multiple Ranges (for batch_update_cells)

Filters (for get_table_data)

Conditional Formatting Rule

Development

Local Testing

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

Authentication Errors

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]