# 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.

[![Docker](https://img.shields.io/badge/Docker-Required-blue.svg)](https://www.docker.com/)
[![Python](https://img.shields.io/badge/Python-3.11-blue.svg)](https://www.python.org/)
[![MCP](https://img.shields.io/badge/MCP-1.2.0+-green.svg)](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]`