Google Sheets MCP Server

A Model Context Protocol (MCP) server that provides tools for reading and interacting with Google Sheets data. This server enables AI assistants like Claude Desktop to seamlessly access, search, and analyze Google Spreadsheets.

Built with uv for fast and reliable dependency management.

🚀 Features

• 📋 List Spreadsheets: Discover all accessible Google Spreadsheets
• 🔍 Search by Name: Find spreadsheets by name (exact or partial match)
• 📊 Read Sheet Data: Extract data from specific ranges or entire sheets
• 📝 Sheet Metadata: Get detailed information about spreadsheet structure
• 📑 List Sheets/Tabs: View all sheets within a spreadsheet
• 🔎 Search Content: Find specific data within sheets
• 🎯 Range Data: Get formatted data from specific cell ranges
• 🔐 Dual Authentication: Supports both OAuth 2.0 (private sheets) and API key (public sheets)

📦 Installation

Prerequisites

• Python 3.8+
• uv package manager
• Google Cloud Project with Sheets API enabled
• Claude Desktop or other MCP-compatible client

Dependencies

This project uses uv for fast and reliable dependency management:

# Install dependencies using uv
uv sync

# Or if you don't have uv installed yet:
pip install uv
uv sync

Note: All dependencies are managed through pyproject.toml and uv.lock files.

⚙️ Setup

Option 1: OAuth 2.0 Authentication (Recommended for Private Sheets)

1. Create Google Cloud Project:

   • Go to Google Cloud Console
   • Create a new project or select existing one
   • Enable Google Sheets API and Google Drive API

2. Create OAuth 2.0 Credentials:

   • Go to "Credentials" → "Create Credentials" → "OAuth 2.0 Client ID"
   • Choose "Desktop application"
   • Download the credentials.json file
   • Place it in the project root directory

3. Generate Authentication Token:

   # Run the token generation script using uv
   uv run token_gen.py
   

   • A browser window will open for Google authentication
   • Grant necessary permissions
   • This will create token.json file for future use

4. Environment Setup (Optional):

   # Create .env file if you want to use API key fallback
   echo "SHEET_API_KEY=your_api_key_here" > .env
   

Option 2: API Key Authentication (Public Sheets Only)

1. Create API Key:

   • Go to Google Cloud Console
   • Enable Google Sheets API
   • Go to "Credentials" → "Create Credentials" → "API Key"
   • Copy the API key

2. Environment Configuration:

   # Create .env file
   echo "SHEET_API_KEY=your_api_key_here" > .env
   

Note: API key authentication only works with publicly shared spreadsheets.

🏃‍♂️ Running the Server

Prerequisites

Important: You must generate the authentication token before adding the MCP server to Claude Desktop.

1. Generate Token First:

   uv run token_gen.py
   

   Complete the OAuth flow in your browser.

2. Then Run Server:

Standalone Mode

uv run main.py

With Claude Desktop

Only after completing token generation, add to your Claude Desktop configuration:

{
  "mcpServers": {
    "sheet-mcp-server": {
        "command": "uv",
        "args": [
            "--directory",
            "C:\\path\\to\\your\\SheetMCP",
            "run",
            "main.py"
        ]
    }
  }
}

Replace C:\\path\\to\\your\\SheetMCP with your actual project directory path.

🛠️ Available Tools

1. List Spreadsheets

Lists all Google Spreadsheets accessible to the authenticated user.

{
  "name": "list_spreadsheets",
  "arguments": {
    "limit": 20,
    "order_by": "modifiedTime desc"
  }
}

2. Search Spreadsheets by Name

Find spreadsheets by name with exact or partial matching.

{
  "name": "search_spreadsheets_by_name",
  "arguments": {
    "name": "Sales Report",
    "exact_match": false
  }
}

3. Read Sheet Data

Extract data from a specific range or entire sheet.

{
  "name": "read_sheet_data",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:C10"
  }
}

4. Get Sheet Metadata

Retrieve detailed information about a spreadsheet.

{
  "name": "get_sheet_metadata",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  }
}

5. List Sheets

Get all sheet/tab names within a spreadsheet.

{
  "name": "list_sheets",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  }
}

6. Search Sheet Data

Find specific content within a sheet.

{
  "name": "search_sheet_data",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "search_term": "Revenue",
    "sheet_name": "Q4 Data"
  }
}

7. Get Range Data

Extract data with specific formatting options.

{
  "name": "get_range_data",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:E20",
    "value_render_option": "FORMATTED_VALUE"
  }
}

🔐 Authentication Scopes

The server uses these Google API scopes:

• https://www.googleapis.com/auth/spreadsheets.readonly - Read spreadsheet content
• https://www.googleapis.com/auth/drive.metadata.readonly - List and discover spreadsheets

📁 Project Structure

SheetMCP/
├── main.py                 # Main MCP server implementation
├── token_gen.py           # OAuth token generation script
├── pyproject.toml         # Project configuration and dependencies
├── uv.lock                # Locked dependency versions
├── requirements.txt        # Legacy pip requirements (optional)
├── .env                   # Your environment variables (optional)
├── credentials.json       # OAuth credentials (download from Google)
├── token.json            # Generated OAuth tokens (created by token_gen.py)
└── README.md             # This file

🔧 Configuration

Environment Variables

Create a .env file with:

# For API key authentication (public sheets only) - Optional fallback
SHEET_API_KEY=your_api_key_here

# Server Configuration
SERVER_NAME=google-sheets-mcp-server
SERVER_VERSION=1.0.0
LOG_LEVEL=INFO

Note: The .env file is optional. OAuth authentication via token_gen.py is the primary method.

🐛 Troubleshooting

Common Issues

1. OAuth Browser Doesn't Open:

   • Check firewall settings
   • Ensure Python has internet access
   • Try running authentication manually first

2. Permission Denied Errors:

   • Verify spreadsheet is accessible with your Google account
   • Check if spreadsheet is shared appropriately
   • Ensure correct API scopes are enabled

3. API Key Limitations:

   • API keys only work with public spreadsheets
   • Use OAuth 2.0 for private spreadsheets
   • Verify spreadsheet sharing settings

4. Token Expiration:

   • Delete token.json to force re-authentication
   • Run uv run token_gen.py again to regenerate
   • Check if refresh token is still valid

5. uv Installation Issues:

   • Install uv: curl -LsSf https://astral.sh/uv/install.sh | sh (Unix) or powershell -c "irm https://astral.sh/uv/install.ps1 | iex" (Windows)
   • Or use pip: pip install uv
   • Verify installation: uv --version

Pre-Authentication

Recommended workflow to avoid OAuth interruption during use:

# Step 1: Install dependencies
uv sync

# Step 2: Generate authentication token first
uv run token_gen.py

# Step 3: Then start the MCP server or add to Claude Desktop
uv run main.py

This ensures authentication is completed before Claude Desktop tries to use the server.

📋 Example Usage with Claude

User: "List my Google Spreadsheets"
Claude: Uses list_spreadsheets tool to show available spreadsheets

User: "Find spreadsheets with 'Budget' in the name"
Claude: Uses search_spreadsheets_by_name tool with name="Budget"

User: "Read data from the Sales Q4 spreadsheet, range A1:E20"
Claude: Uses search + read_sheet_data tools to find and extract data

User: "What sheets are in this spreadsheet?"
Claude: Uses list_sheets tool to show all tabs/sheets

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🔗 Related Links

• Model Context Protocol (MCP)
• Google Sheets API Documentation
• Google Drive API Documentation
• Claude Desktop

⚠️ Security Notes

• Keep your credentials.json and token.json files secure
• Never commit authentication files to version control
• Use environment variables for API keys
• Regularly review and rotate credentials
• Follow the principle of least privilege for API scopes