Prajapdh/sheets-mcp-server

Google Sheets MCP Server

A Model Context Protocol (MCP) server that provides comprehensive Google Sheets integration. This server enables you to create, read, update, and manage Google Sheets spreadsheets programmatically.

🎯 Purpose

This server enables you to:

• Create new Google Sheets spreadsheets with custom sheet names
• Read data from any range in a spreadsheet
• Write data to specific ranges
• Append new rows to existing data
• Clear ranges of data
• Get spreadsheet information and metadata
• Batch update multiple ranges efficiently

🛠️ Available Tools

Core Operations

• create-spreadsheet

  • Creates a new Google Sheets spreadsheet
  • Input: title (required), sheet_names (optional array)
  • Returns: Spreadsheet ID, URL, and created sheet names

• read-range

  • Reads data from a specific range
  • Input: spreadsheet_id, range_name (e.g., 'Sheet1!A1:C10')
  • Returns: 2D array of cell values

• write-range

  • Writes data to a specific range (overwrites existing data)
  • Input: spreadsheet_id, range_name, values (2D array)
  • Returns: Update statistics

• append-rows

  • Appends rows to the end of a range
  • Input: spreadsheet_id, range_name, values (2D array)
  • Returns: Update statistics

• clear-range

  • Clears all data from a specified range
  • Input: spreadsheet_id, range_name
  • Returns: Confirmation of cleared range

• get-spreadsheet-info

  • Gets metadata about a spreadsheet
  • Input: spreadsheet_id
  • Returns: Title, URL, sheet information, dimensions

• batch-update

  • Performs multiple range updates in a single request
  • Input: spreadsheet_id, updates (array of range/values pairs)
  • Returns: Total update statistics

Prompts

• manage-sheets: General Google Sheets management prompt for AI assistants

🚀 Setup

1. Google Sheets API Setup

1. Create a Google Cloud project or use an existing one
2. Enable the Google Sheets API
3. Configure an OAuth consent screen
   • Select "External" for testing purposes
   • Add your email as a test user
4. Add OAuth scope: https://www.googleapis.com/auth/spreadsheets
5. Create OAuth 2.0 Client ID credentials
   • Choose "Desktop Application"
6. Download the credentials JSON file
7. Save it securely and note the file path

2. Installation

Using uv (recommended):

cd sheets-mcp-server
uv sync

3. Authentication

On first run, the server will launch a browser for OAuth authentication. Access tokens will be saved to the specified --token-path for future use.

💼 Usage

Standalone Usage

uv run sheets \
  --creds-file-path /path/to/your/credentials.json \
  --token-path /path/to/your/tokens.json

Integration with Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "google-sheets": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/sheets-mcp-server",
        "run",
        "sheets",
        "--creds-file-path",
        "/path/to/your/credentials.json",
        "--token-path",
        "/path/to/your/tokens.json"
      ]
    }
  }
}

Integration with Other MCP Clients

This server follows the standard MCP protocol and can be integrated with any MCP-compatible client.

📋 Usage Examples

Creating a New Spreadsheet

{
  "tool": "create-spreadsheet",
  "arguments": {
    "title": "My Data Analysis",
    "sheet_names": ["Data", "Analysis", "Charts"]
  }
}

Reading Data

{
  "tool": "read-range",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range_name": "Sheet1!A1:E10"
  }
}

Writing Data

{
  "tool": "write-range",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range_name": "Sheet1!A1:C3",
    "values": [
      ["Name", "Age", "City"],
      ["Alice", "30", "New York"],
      ["Bob", "25", "San Francisco"]
    ]
  }
}

Appending New Data

{
  "tool": "append-rows",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range_name": "Sheet1!A:C",
    "values": [
      ["Charlie", "35", "Chicago"],
      ["Diana", "28", "Boston"]
    ]
  }
}

Batch Updates

{
  "tool": "batch-update",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "updates": [
      {
        "range": "Sheet1!A1:B2",
        "values": [["Header1", "Header2"], ["Data1", "Data2"]]
      },
      {
        "range": "Sheet1!D1:E2",
        "values": [["Header3", "Header4"], ["Data3", "Data4"]]
      }
    ]
  }
}

🧪 Testing

With MCP Inspector

Test the server using MCP Inspector:

npx @modelcontextprotocol/inspector uv run sheets \
  --creds-file-path /path/to/credentials.json \
  --token-path /path/to/tokens.json

Manual Testing

1. Create a test spreadsheet
2. Read some data to verify connectivity
3. Write test data to ensure write permissions work
4. Try different range formats (A1 notation, named ranges, etc.)

📊 Common Use Cases

Data Analysis Workflows

1. Create spreadsheet for analysis
2. Import raw data via append-rows
3. Read data for processing
4. Write calculated results back
5. Generate reports and summaries

Content Management

1. Create content tracking spreadsheet
2. Append new content entries
3. Update status and metadata
4. Generate content reports

Project Management

1. Create project tracking sheet
2. Add tasks and milestones
3. Update progress and status
4. Generate project dashboards

Data Synchronization

1. Read data from external systems
2. Transform and validate data
3. Write to Google Sheets for sharing
4. Keep data synchronized across platforms

🔧 Advanced Features

Range Formats Supported

• A1 notation: Sheet1!A1:C10
• Named ranges: MyNamedRange
• Entire columns: Sheet1!A:C
• Entire rows: Sheet1!1:5
• Open-ended ranges: Sheet1!A1:C

Error Handling

The server includes comprehensive error handling for:

• Authentication failures and token refresh
• Network timeouts and connectivity issues
• Invalid spreadsheet IDs or range names
• Permission errors
• API quota limits
• Malformed data inputs

Performance Considerations

• Uses asyncio.to_thread for non-blocking API calls
• Supports batch operations for efficiency
• Handles Google Sheets API rate limits gracefully
• Optimized for both small and large data operations

🔒 Security & Permissions

Required OAuth Scopes

• https://www.googleapis.com/auth/spreadsheets - Full access to Google Sheets

Security Best Practices

• Store credentials securely
• Use environment variables for sensitive paths
• Implement proper access controls
• Regularly rotate access tokens
• Monitor API usage and quotas

🤝 Contributing & Extending

This server is designed to be easily extensible. Common enhancements:

Additional Features

• Formatting operations (bold, colors, borders)
• Formula support for calculated cells
• Chart creation and management
• Conditional formatting rules
• Data validation constraints
• Pivot tables and summaries

Integration Enhancements

• Database connectors for data import/export
• CSV/Excel file import/export
• Real-time collaboration features
• Webhook notifications for changes
• Advanced search and filtering

Performance Optimizations

• Caching strategies for frequently accessed data
• Streaming support for large datasets
• Parallel processing for bulk operations
• Connection pooling for high-throughput scenarios

📚 API Reference

Google Sheets API Limits

• 100 requests per 100 seconds per user
• 1000 requests per 100 seconds (total quota)
• Maximum 10 million cells per spreadsheet
• Maximum 200 sheets per spreadsheet

Response Formats

All tools return structured responses with:

• Status indicators (success/error)
• Detailed error messages when applicable
• Update statistics for write operations
• Structured data for read operations

🆘 Troubleshooting

Common Issues

1. Authentication Errors

   • Verify credentials file path
   • Check OAuth consent screen configuration
   • Ensure correct scopes are configured

2. Permission Errors

   • Verify spreadsheet sharing permissions
   • Check if spreadsheet exists
   • Ensure account has edit access

3. Range Errors

   • Validate A1 notation format
   • Check sheet names for typos
   • Verify range bounds

4. Quota Exceeded

   • Implement request throttling
   • Use batch operations when possible
   • Monitor usage in Google Cloud Console

Ready to supercharge your Google Sheets workflows with automated operations! 🚀