Google Sheets Analytics MCP

TNTM Google Sheets Analytics MCP Server

A clean, practical MCP (Model Context Protocol) server for analyzing Google Sheets data with multi-tab support. Built for Claude Code and other MCP-compatible AI assistants by TNTM.

🚀 Features

• Smart Sync - Sync Google Sheets with configurable row limits to prevent timeouts
• Multi-tab Support - Query across multiple sheets with SQL JOINs
• SQL Queries - Direct SQL access to synced data
• Sheet Analysis - Get suggestions for cross-sheet queries
• Quick Preview - Preview sheets without full sync
• Performance Optimized - Row limits and result pagination for large datasets

📋 Prerequisites

• Python 3.8+
• Claude Code or another MCP-compatible client
• Google Cloud Project with Sheets API enabled
• OAuth2 credentials from Google Cloud Console

🛠️ Setup

⚡ One-Click Setup with Claude Code (Recommended)

1. Drag this project folder into Claude Code
2. Ask Claude Code: "Follow the README instructions to install this MCP server into Claude Code"
3. Get Google OAuth credentials (Claude Code will guide you through this):
   • Go to Google Cloud Console
   • Create a new project or select existing one
   • Enable the Google Sheets API
   • Create OAuth2 credentials (Desktop Application)
   • Download and save as credentials.json in the project root

That's it! Claude Code will handle virtual environments, dependencies, and OAuth setup automatically.

🚀 Automated Installation (Alternative)

For non-Claude Code users or manual setup:

Option 1: Shell Script (macOS/Linux)

Option 2: Python Script (All platforms)

Option 3: Manual Step-by-step

🔐 Getting Google Credentials

Before first use, you need OAuth2 credentials:

1. Go to Google Cloud Console
2. Create a new project or select existing one
3. Enable the Google Sheets API
4. Go to APIs & Services > Credentials
5. Click Create Credentials > OAuth 2.0 Client IDs
6. Choose Desktop Application
7. Download the JSON file
8. Save it as credentials.json in the project root

🚀 First Run - OAuth Setup

After adding your credentials.json file, run the OAuth setup:

This will:

1. Open your browser for Google authentication
2. Create a token.json file with your access credentials
3. Verify the connection works

You only need to do this once! After setup, all MCP tools will work automatically.

🔧 Tools

smart_sync

Sync Google Sheet data with intelligent chunking for large datasets.

• url (required): Google Sheets URL
• max_rows (optional): Max rows per sheet (default: 100000, supports up to 1M+)
• sheets (optional): Array of specific sheet names to sync

Auto-scaling behavior:

• Sheets <10K rows: Single fetch
• Sheets 10K-100K rows: 10K row chunks
• Sheets >100K rows: 50K row chunks with sampling

query_sheets

Run SQL queries on synced data, including JOINs across tabs.

• query (required): SQL query to execute

list_synced_sheets

View all synced sheets and their table names.

analyze_sheets

Get suggestions for queries across multiple sheets.

• question (required): What you want to analyze

get_sheet_preview

Quick preview without syncing.

• url (required): Google Sheets URL
• sheet_name (optional): Specific sheet to preview
• rows (optional): Number of rows to preview (default: 10)

📊 How It Works

1. Authentication - Uses OAuth2 to securely access Google Sheets API
2. Sync - Downloads sheet data to local SQLite database with configurable limits
3. Query - Enables SQL queries across all synced sheets
4. Multi-tab - Each sheet becomes a separate table, joinable via SQL

🏗️ Project Structure

⚡ Performance

Scale & Capacity

• 1 Million Row Support: Handles sheets with up to 1M rows efficiently
• Chunked Processing: Automatically chunks large sheets (>10K rows) for optimal performance
• Bulk Operations: 50-100x faster inserts using batch processing
• Configurable Limits: Default 1000 rows, expandable to 1M+ rows per sheet

Optimizations

• Smart Caching: Skip unchanged sheets, 5-minute cache TTL
• Streaming Queries: Results streamed in batches to prevent memory overflow
• Progressive Hashing: Samples large datasets for efficient change detection
• Dynamic Indexing: Auto-creates indexes on large tables for faster queries
• Memory Management: Automatic cleanup after processing large datasets

Performance Metrics

• Sync Speed: 50,000-100,000 rows/second (vs 1,000 rows/second previously)
• Query Response: <1 second for most queries on 1M rows
• Memory Usage: Constant ~200-500MB regardless of dataset size
• 1M Row Sync Time: ~10-20 seconds

🔍 Example Use Cases

Multi-tab Analysis

Cross-sheet Aggregation

🔒 Security

• OAuth2 authentication with Google
• Credentials stored locally (never committed to repo)
• Read-only access to Google Sheets
• Local SQLite database (no external data transmission)

🐛 Troubleshooting

Installation Issues

Common Runtime Issues

OAuth Troubleshooting

• Check status: venv/bin/python src/auth/oauth_setup.py --status
• Test auth: venv/bin/python src/auth/oauth_setup.py --test
• Reset OAuth: venv/bin/python src/auth/oauth_setup.py --reset
• Manual setup: venv/bin/python src/auth/oauth_setup.py --manual

MCP Server Not Appearing

1. Verify config: cat ~/.config/claude-code/config.json
2. Check the config includes the google-sheets-analytics server
3. Ensure the virtual environment and dependencies are properly installed
4. Check that the Python path in the config is correct

Database Issues

• Database location: data/sheets_data.sqlite
• Reset database: Delete the file and re-sync
• Check synced sheets: Use the list_synced_sheets tool

🤝 Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📄 License

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

🙏 Acknowledgments

• Built for the Model Context Protocol
• Designed for Claude Code
• Uses Google Sheets API

Need help? Open an issue on GitHub or check the troubleshooting section above.