mcp-gsheets

MCP Google Sheets Server

Coverage License: MIT TypeScript Node code style: prettier

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 git@github.com:freema/mcp-gsheets.git
cd mcp-gsheets

# Install dependencies
npm install

# Build the project
npm run build

3. Google Cloud Setup

Go to Google Cloud Console
Create a new project or select existing
Enable Google Sheets API:
- Navigate to "APIs & Services" → "Library"
- Search for "Google Sheets API" and click "Enable"
Create Service Account:
- Go to "APIs & Services" → "Credentials"
- Click "Create Credentials" → "Service Account"
- Download the JSON key file
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

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

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

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

🤝 Contributing

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

📄 License

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