ayush1184/google-sheet-mcp

Google Sheets MCP Server

Overview

A Google Sheets MCP (Model Context Protocol) Server that enables AI assistants like Claude Desktop to interact with Google Sheets. Built with TypeScript and designed for reliability and ease of use.

Key Features

• MCP Integration: Works with Claude Desktop through Model Context Protocol
• Secure Authentication: OAuth 2.0 authentication with Google APIs
• Multiple Operations: Supports reading, writing, formatting, and managing spreadsheets
• Batch Processing: Efficient batch operations to minimize API calls
• TypeScript: Full TypeScript implementation with type safety

Table of Contents

• Installation
• Setup Guide
• Available Tools
• Authentication
• Configuration
• Usage Examples
• Troubleshooting

Installation

Prerequisites

• Node.js 18.0.0 or higher
• Google Cloud Project with Sheets API enabled
• OAuth 2.0 Credentials configured

Quick Setup

# Clone the repository
git clone https://github.com/ayush1184/google-sheet-mcp.git
cd google-sheet-mcp

# Install dependencies
npm install

# Configure environment
cp .env.example .env
# Edit .env with your Google OAuth credentials

# Validate setup
npm run validate-setup

# Build and start
npm run build
npm run start

Setup Guide

Step 1: Google Cloud Console Configuration

1.1 Enable Google Sheets API

1. Navigate to Google Cloud Console
2. Create a new project or select existing one
3. Go to APIs & Services > Library
4. Search for "Google Sheets API"
5. Click Enable

1.2 Create OAuth 2.0 Credentials

1. Go to APIs & Services > Credentials
2. Click + CREATE CREDENTIALS > OAuth 2.0 Client IDs
3. Choose Desktop application
4. Set name: Google Sheets MCP Server
5. Add authorized redirect URI: http://localhost:3000/auth/callback
6. Download the credentials JSON file

Step 2: Environment Configuration

Create and configure .env file:

# Copy template
cp .env.example .env

Edit .env with your credentials:

# Google OAuth Configuration (Required)
GOOGLE_CLIENT_ID=your_google_client_id_here.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=your_google_client_secret_here
GOOGLE_REDIRECT_URI=http://localhost:3000/auth/callback

# Server Settings
NODE_ENV=production
PORT=3000
LOG_LEVEL=info

# Optional Configuration
ENABLE_METRICS=true
PERFORMANCE_THRESHOLD_MS=10000
MAX_CONCURRENT_OPERATIONS=10
RATE_LIMIT_PER_MINUTE=100

Step 3: Authentication Setup

3.1 Validate Configuration

# Validate Google OAuth setup
npm run validate-setup

3.2 Authenticate with Google

# Start authentication flow
npm run auth

Step 4: Build and Test

4.1 Build Application

npm run build

4.2 Run Tests

# Run tests
npm test

# Run specific test categories
npm run test:unit              # Unit tests only
npm run test:integration       # Integration tests
npm run test:coverage          # With coverage report

Step 5: Claude Desktop Integration

Add to your Claude Desktop MCP configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "google-sheets": {
      "command": "node",
      "args": ["/absolute/path/to/google-sheet-mcp/dist/index.js"],
      "env": {
        "NODE_ENV": "production"
      }
    }
  }
}

Available Tools

Spreadsheet Management

• list-spreadsheets - List all accessible spreadsheets
• create-spreadsheet - Create a new spreadsheet
• get-spreadsheet - Get spreadsheet details
• delete-spreadsheet - Delete a spreadsheet

Worksheet Operations

• list-sheets - List worksheets in a spreadsheet
• add-sheet - Add a new worksheet
• delete-sheet - Delete a worksheet

Data Operations

• get-values - Read cell values from a range
• update-values - Update cell values in a range
• append-values - Append new rows of data
• clear-values - Clear cell values in a range

Batch Operations

• batch-get-values - Read from multiple ranges
• batch-operations - Perform multiple operations

Advanced Features

• format-cells - Apply formatting to cells
• add-conditional-formatting - Add conditional formatting
• create-chart - Create charts from data
• insert-rows-columns - Insert rows or columns
• delete-rows-columns - Delete rows or columns
• set-data-validation - Set data validation rules
• find-replace - Find and replace text

Authentication

The server uses OAuth 2.0 for secure authentication with Google APIs:

• Secure Token Storage: Tokens stored securely on the local system
• Automatic Token Refresh: Handles expired tokens automatically
• Minimal Scopes: Uses only required permissions for spreadsheet access

Required OAuth scopes:

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

Configuration

Environment Variables

# Required Google OAuth Configuration
GOOGLE_CLIENT_ID=your_client_id
GOOGLE_CLIENT_SECRET=your_client_secret  
GOOGLE_REDIRECT_URI=http://localhost:3000/auth/callback

# Server Configuration
NODE_ENV=production
PORT=3000
LOG_LEVEL=info

# Performance Settings
ENABLE_METRICS=true
PERFORMANCE_THRESHOLD_MS=10000
MAX_CONCURRENT_OPERATIONS=10
RATE_LIMIT_PER_MINUTE=100

# Security Settings
ENABLE_CORS=true
TOKEN_VALIDATION=true
MAX_BATCH_SIZE=100
MAX_CELLS_PER_OPERATION=1000

Usage Examples

Reading Data

{
  "tool": "get-values",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:D10"
  }
}

Writing Data

{
  "tool": "update-values",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:B3",
    "values": [
      ["Product", "Price"],
      ["Laptop", "999.99"],
      ["Mouse", "25.50"]
    ]
  }
}

Batch Operations

{
  "tool": "batch-operations",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "operations": [
      {
        "type": "read",
        "range": "A1:B5"
      },
      {
        "type": "write", 
        "range": "C1:D2",
        "data": [["New", "Data"]]
      }
    ]
  }
}

Troubleshooting

Common Issues

Authentication Issues

Problem: OAuth authentication failed Solution:

1. Verify GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET in .env
2. Check redirect URI matches Google Cloud Console exactly
3. Ensure Google Sheets API is enabled
4. Run: npm run validate-setup

Token Expired

Problem: Token expired Solution:

1. Re-authenticate: npm run auth
2. Check token refresh is working in logs

Connection Issues

Problem: ECONNREFUSED Solution:

1. Check firewall settings
2. Verify port 3000 is available
3. Check if process is running: ps aux | grep node

Debug Mode

Enable comprehensive logging:

NODE_ENV=development LOG_LEVEL=debug npm run start

Health Check

curl -X GET http://localhost:3000/health

Development

Scripts

npm run build          # Build TypeScript to JavaScript
npm run dev            # Development mode with hot reload
npm run start          # Start production server
npm run auth           # Authenticate with Google
npm run validate-setup # Validate configuration
npm test               # Run tests
npm run lint           # Run ESLint
npm run typecheck      # TypeScript type checking

Project Structure

src/
├── index.ts                    # Main entry point
├── server.ts                   # MCP server implementation
├── auth/                       # OAuth2 authentication
├── handlers/                   # Tool handler implementations  
├── tools/                      # Tool definitions and schemas
├── schemas/                    # TypeScript interfaces
├── utils/                      # Utilities and helpers
└── config/                     # Configuration files

Contributing

1. Fork the repository
2. Create a feature branch
3. Add tests for new features
4. Ensure all tests pass
5. Submit a pull request

License

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

Support

• GitHub Issues: Report Issues
• Documentation: See project files and source code comments

Ready to integrate Google Sheets with Claude Desktop? Get started today!