MCP Google Sheets Server

A Model Context Protocol (MCP) server for Google Sheets API integration. Enables reading, writing, and managing Google Sheets documents directly from your MCP client (e.g., Claude Desktop).

🚀 Quick Start

1. Prerequisites

• Node.js v18 or higher
• Google Cloud Project with Sheets API enabled
• Service Account with JSON key file

2. Installation

# Clone the repository
git clone https://github.com/freema/mcp-gsheets.git
# Or using SSH
# git clone [email protected]:freema/mcp-gsheets.git
cd mcp-gsheets

# Install dependencies
npm install

# Build the project
npm run build

3. Google Cloud Setup

1. Go to Google Cloud Console
2. Create a new project or select existing
3. Enable Google Sheets API:
   • Navigate to "APIs & Services" → "Library"
   • Search for "Google Sheets API" and click "Enable"
4. Create Service Account:
   • Go to "APIs & Services" → "Credentials"
   • Click "Create Credentials" → "Service Account"
   • Download the JSON key file
5. Share your spreadsheets:
   • Open your Google Sheet
   • Click Share and add the service account email (from JSON file)
   • Grant "Editor" permissions

4. Configure MCP Client

Easy Setup (Recommended)

Run the interactive setup script:

npm run setup

This will:

• Guide you through the configuration
• Automatically detect your Node.js installation (including nvm)
• Find your Claude Desktop config
• Create the proper JSON configuration
• Optionally create a .env file for development

Manual Setup

If you prefer manual configuration, add to your Claude Desktop config:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json
• Linux: ~/.config/claude/claude_desktop_config.json

{
  "mcpServers": {
    "mcp-gsheets": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-gsheets/dist/index.js"],
      "env": {
        "GOOGLE_PROJECT_ID": "your-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
      }
    }
  }
}

Restart Claude Desktop after adding the configuration.

📦 Build & Development

Development Commands

# Development mode with hot reload
npm run dev

# Build for production
npm run build

# Type checking
npm run typecheck

# Clean build artifacts
npm run clean

# Run MCP inspector for debugging
npm run inspector

# Run MCP inspector in development mode
npm run inspector:dev

Task Runner (Alternative)

If you have Task installed:

# Install dependencies
task install

# Build the project
task build

# Run in development mode
task dev

# Run linter
task lint

# Format code
task fmt

# Run all checks
task check

Development Setup

1. Create .env file for testing:

cp .env.example .env
# Edit .env with your credentials:
# GOOGLE_PROJECT_ID=your-project-id
# GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json
# TEST_SPREADSHEET_ID=your-test-spreadsheet-id

2. Run in development mode:

npm run dev  # Watch mode with auto-reload

📋 Available Tools

Reading Data

• sheets_get_values - Read from a range
• sheets_batch_get_values - Read from multiple ranges
• sheets_get_metadata - Get spreadsheet info
• sheets_check_access - Check access permissions

Writing Data

• sheets_update_values - Write to a range
• sheets_batch_update_values - Write to multiple ranges
• sheets_append_values - Append rows to a table
• sheets_clear_values - Clear cell contents

Sheet Management

• sheets_insert_sheet - Add new sheet
• sheets_delete_sheet - Remove sheet
• sheets_duplicate_sheet - Copy sheet
• sheets_copy_to - Copy to another spreadsheet
• sheets_update_sheet_properties - Update sheet settings

Cell Formatting

• sheets_format_cells - Format cells (colors, fonts, alignment, number formats)
• sheets_update_borders - Add or modify cell borders
• sheets_merge_cells - Merge cells together
• sheets_unmerge_cells - Unmerge previously merged cells
• sheets_add_conditional_formatting - Add conditional formatting rules

🔧 Code Quality

Linting

# Run ESLint
npm run lint

# Fix auto-fixable issues
npm run lint:fix

Formatting

# Check formatting with Prettier
npm run format:check

# Format code
npm run format

Type Checking

# Run TypeScript type checking
npm run typecheck

❗ Troubleshooting

Common Issues

"Authentication failed"

• Verify JSON key path is absolute and correct
• Check GOOGLE_PROJECT_ID matches your project
• Ensure Sheets API is enabled

"Permission denied"

• Share spreadsheet with service account email
• Service account needs "Editor" role
• Check email in JSON file (client_email field)

"Spreadsheet not found"

• Verify spreadsheet ID from URL
• Format: https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit

MCP Connection Issues

• Ensure you're using the built version (dist/index.js)
• Check that Node.js path is correct in Claude Desktop config
• Look for errors in Claude Desktop logs
• Use npm run inspector to debug

🔍 Finding IDs

Spreadsheet ID

From the URL:

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
                                        ↑ This is the spreadsheet ID

Sheet ID

Use sheets_get_metadata to list all sheets with their IDs.

📝 Tips

1. Always test with a copy of your data
2. Use batch operations for better performance
3. Set appropriate permissions (read-only vs edit)
4. Check rate limits for large operations
5. Use sheets_check_access to verify permissions before operations

🤝 Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Run tests and linting (npm run check)
4. Commit your changes (git commit -m 'Add some amazing feature')
5. Push to the branch (git push origin feature/amazing-feature)
6. Open a Pull Request

📄 License

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