# 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.
[](https://www.docker.com/)
[](https://www.python.org/)
[](https://modelcontextprotocol.io/)
## 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
```bash
git clone <repository-url>
cd MCP_GoogleSheets
docker build -t googlesheets-mcp-server .
```
### 2. Configure Credentials
```bash
# 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`:
```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:
```yaml
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:
```json
{
"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)
```json
[["Header1", "Header2"], ["Value1", "Value2"]]
```
### Multiple Ranges (for batch_update_cells)
```json
{"A1:B2": [[1, 2], [3, 4]], "D5": [["Hello"]]}
```
### Filters (for get_table_data)
```json
{"Status": "Active", "Country": "USA"}
```
### Conditional Formatting Rule
```json
{
"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
```bash
# 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](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
```bash
# Check Docker image
docker images | grep googlesheets
# Verify server in list
docker mcp server list
# Check logs
docker logs [container_name]
```
### Authentication Errors
```bash
# 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](https://developers.google.com/sheets/api)
- [Google Drive API](https://developers.google.com/drive/api)
- [Model Context Protocol](https://modelcontextprotocol.io/)
- [FastMCP Framework](https://github.com/jlowin/fastmcp)
## License
MIT License
## Support
For issues and questions:
1. Check the [Troubleshooting](#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]`
