# CLAUDE.md
This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.
## Project Overview
This repository contains a complete, working Google Sheets MCP server implementation that bridges MCP-compatible clients (like Claude Desktop) with the Google Sheets API. It also includes the comprehensive prompt template used to build it ([mcp-builder-prompt.md](mcp-builder-prompt.md)).
## Key Architecture Concepts
### MCP Server Structure
All MCP servers built using this template follow a consistent architecture:
```
Claude Desktop → MCP Gateway → Custom MCP Server → External API/Service
↓
Docker Desktop Secrets
```
- **FastMCP Framework**: All servers use the `mcp.server.fastmcp` framework
- **Docker-based**: Servers run as non-root services in Docker containers
- **Stdio Transport**: Communication via `stdio` transport protocol
- **Secret Management**: Credentials managed through Docker Desktop secrets
### Google Sheets Server Implementation
The implemented server ([googlesheets_server.py](googlesheets_server.py)) provides:
**Authentication Methods**:
- Service Account (via `GOOGLE_APPLICATION_CREDENTIALS` environment variable)
- OAuth 2.0 user flow (via stored tokens at `~/.google_sheets_token.json`)
**Implemented Tools** (31 total):
*Spreadsheet Management*:
- `list_spreadsheets` - Lists spreadsheets from Drive folder or user access
- `create_spreadsheet` - Creates new spreadsheet
- `share_spreadsheet` - Shares with users/emails (reader, commenter, writer roles)
*Sheet Operations*:
- `list_sheets` - Lists all sheet names in a spreadsheet
- `create_sheet` - Adds new sheet (tab)
- `rename_sheet` - Renames existing sheet
- `copy_sheet` - Duplicates sheet within or across spreadsheets
- `add_columns` - Adds columns to sheet
- `add_conditional_formatting` - Adds conditional formatting rules to ranges with custom conditions
- `update_conditional_formatting` - Updates or moves existing conditional formatting rules
*Data Access*:
- `get_sheet_data` - Reads data from range (with optional grid metadata)
- `get_sheet_formulas` - Reads formulas from range
- `get_multiple_sheet_data` - Fetches multiple ranges in one call
- `get_multiple_spreadsheet_summary` - Gets titles, headers, and preview rows
*Data Modification*:
- `update_cells` - Writes data to specific range (overwrites)
- `batch_update_cells` - Updates multiple ranges in one call
- `add_rows` - Appends rows to end of sheet
*Table Operations*:
- `list_tables` - Lists defined tables (named ranges)
- `create_table` - Creates table with headers and optional data
- `get_table_data` - Reads table data with filters, limit, offset
- `insert_table_rows` - Inserts rows into table
- `update_table_rows` - Updates rows matching criteria
- `delete_table_rows` - Deletes rows matching criteria
- `add_table_columns` - Adds columns to table
- `rename_table_column` - Renames table header
*Import/Export*:
- `export_table_as_csv` - Exports table to CSV format
- `import_csv_to_table` - Imports CSV content into table
## Critical Implementation Rules
When implementing MCP servers based on this template, **ALWAYS** follow these rules:
### Prohibited Patterns
1. **NO** `@mcp.prompt()` decorators - breaks Claude Desktop
2. **NO** `prompt` parameter to `FastMCP()` - breaks Claude Desktop
3. **NO** type hints from typing module (`Optional`, `Union`, `List[str]`, etc.)
4. **NO** complex parameter types
5. **NO** multi-line docstrings - causes gateway panic errors
### Required Patterns
1. **Single-line docstrings ONLY** for all tools
2. **Default to empty strings**: Use `param: str = ""` never `param: str = None`
3. **Always return strings** from tools (formatted results)
4. **Always use Docker** for deployment
5. **Always log to stderr** using the logging configuration in template
6. **Check empty strings with `.strip()`** not just truthiness
### Tool Implementation Template
```python
@mcp.tool()
async def tool_name(param: str = "") -> str:
"""Single-line description - MUST BE ONE LINE."""
if not param.strip():
return "❌ Error: Parameter is required"
try:
# Implementation
return f"✅ Success: {result}"
except Exception as e:
return f"❌ Error: {str(e)}"
```
## File Structure for MCP Servers
All MCP server implementations require exactly 5 files:
1. **Dockerfile** - Container definition with non-root user
2. **requirements.txt** - Python dependencies (must include `mcp[cli]>=1.2.0`)
3. **[SERVER_NAME]_server.py** - Main server implementation
4. **readme.txt** - Complete documentation
5. **CLAUDE.md** - Implementation guidelines (this file)
## Development Workflow
### Building and Testing
```bash
# Build Docker image
docker build -t googlesheets-mcp-server .
# Test MCP protocol locally (requires credentials)
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/service-account.json"
echo '{"jsonrpc":"2.0","method":"tools/list","id":1}' | python googlesheets_server.py
# View logs
docker logs [container-name]
```
### Deployment Steps
1. Build Docker image: `docker build -t googlesheets-mcp-server .`
2. Set up secrets:
```bash
docker mcp secret set GOOGLE_APPLICATION_CREDENTIALS="/path/to/sa.json"
docker mcp secret set SERVICE_ACCOUNT_EMAIL="your-sa@project.iam.gserviceaccount.com"
docker mcp secret set DRIVE_FOLDER_ID="folder-id" # optional
```
3. Create custom catalog in `~/.docker/mcp/catalogs/custom.yaml` (see [readme.txt](readme.txt))
4. Update registry in `~/.docker/mcp/registry.yaml` (add `googlesheets: ref: ""` under `registry:`)
5. Configure Claude Desktop config file to include custom catalog
6. Restart Claude Desktop
### Adding New Tools
1. Add decorated function to [googlesheets_server.py](googlesheets_server.py)
2. Follow the tool implementation template (single-line docstring, empty string defaults, return string)
3. Update catalog entry in `~/.docker/mcp/catalogs/custom.yaml` with new tool name
4. Rebuild Docker image: `docker build -t googlesheets-mcp-server .`
5. Restart Claude Desktop
## Google Sheets API Integration
Key implementation details in [googlesheets_server.py](googlesheets_server.py):
- **Credential Loading**: `get_credentials()` tries service account first, then OAuth token
- **Service Singletons**: `get_sheets_service()` and `get_drive_service()` maintain global service objects
- **Error Formatting**: `format_error()` handles `HttpError` and generic exceptions
- **Range Notation**: All range operations use A1 notation (e.g., 'A1:C10', 'Sheet1!B2:D')
- **Grid Data Option**: `include_grid_data` parameter controls metadata inclusion in `get_sheet_data`
- **Batch Operations**: `batch_update_cells` updates multiple ranges in one API call
- **Table Abstraction**: Table operations read full sheet data and filter/modify in memory (not using named ranges API)
- **JSON Parameters**: Complex data (2D arrays, filters, recipients) passed as JSON strings and parsed in tools
- **CSV Handling**: Uses Python's csv module for import/export operations
## Security Requirements
- Run as non-root user (`mcpuser`)
- Load credentials via environment variables only
- Never hardcode credentials
- Never log sensitive data
- Use Docker Desktop secrets for all credentials
- Only operate on authorized spreadsheets/resources
## Reference Documentation
- Google Sheets API: https://developers.google.com/workspace/sheets/api/guides/concepts
- Google Drive API: https://developers.google.com/drive/api/guides/about-sdk
- MCP protocol specification: Model Context Protocol documentation
- FastMCP framework: `mcp.server.fastmcp` module
- Template prompt: [mcp-builder-prompt.md](mcp-builder-prompt.md)
## JSON Parameter Formats
Tools accept JSON-formatted strings for complex data:
```python
# 2D arrays (update_cells, add_rows, batch data)
'[["Header1", "Header2"], ["Value1", "Value2"]]'
# Range mapping (batch_update_cells)
'{"A1:B2": [[1, 2], [3, 4]], "D5": [["Hello"]]}'
# Filters (get_table_data, update_table_rows, delete_table_rows)
'{"Status": "Active", "Country": "USA"}'
# Recipients (share_spreadsheet)
'[{"email_address": "user@example.com", "role": "writer"}]'
# Query list (get_multiple_sheet_data)
'[{"spreadsheet_id": "abc", "sheet": "Sheet1", "range": "A1:B2"}]'
# Spreadsheet IDs (get_multiple_spreadsheet_summary)
'["spreadsheet_id_1", "spreadsheet_id_2"]'
# Column names (create_table, add_table_columns)
'["Name", "Email", "Status"]'
# Conditional formatting rule (add_conditional_formatting)
# Boolean rule with number condition
'{
"type": "boolean",
"condition": {
"type": "NUMBER_GREATER",
"values": [{"userEnteredValue": "100"}]
},
"format": {
"backgroundColor": {"red": 1.0, "green": 0.8, "blue": 0.8},
"textFormat": {
"foregroundColor": {"red": 0.8, "green": 0.0, "blue": 0.0},
"bold": true
}
}
}'
# Custom formula conditional formatting
'{
"type": "boolean",
"condition": {
"type": "CUSTOM_FORMULA",
"values": [{"userEnteredValue": "=GT($D2,median($D$2:$D$11))"}]
},
"format": {
"textFormat": {"foregroundColor": {"red": 0.0, "green": 0.5, "blue": 0.0}}
}
}'
# Update conditional formatting (update_conditional_formatting)
# Must include ranges when updating rule
'{
"type": "boolean",
"ranges": [{"sheetId": 0, "startRowIndex": 0, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 4}],
"condition": {
"type": "NUMBER_LESS",
"values": [{"userEnteredValue": "50"}]
},
"format": {
"backgroundColor": {"red": 0.8, "green": 0.8, "blue": 1.0}
}
}'
```
## Common Issues
**Tools not appearing**:
- Check catalog/registry YAML syntax
- Ensure custom catalog path in Claude Desktop config
- Verify Docker image built: `docker images | grep googlesheets`
- Restart Claude Desktop completely
**Gateway panic errors**:
- Check for multi-line docstrings in tools
- Remove type hints from typing module
- Change `None` defaults to `""`
**Authentication errors**:
- Verify secrets: `docker mcp secret list`
- Check APIs enabled in Google Cloud Console (Sheets API, Drive API)
- Ensure service account has spreadsheet access
- Verify secret names match between code and catalog
**JSON parse errors**:
- Ensure JSON parameters are valid JSON strings
- Use double quotes in JSON (not single quotes)
- Escape special characters properly
**Empty results**:
- Verify spreadsheet ID and sheet name are correct
- Check range notation (A1 format)
- Ensure data exists in the specified range
