MCP Gsheets

🚀 MCP Google Sheets Server

A Model Context Protocol (MCP) server for Google Sheets API integration. This server enables seamless reading, writing, and management of Google Sheets documents directly from your MCP client, such as Claude Desktop.

🚀 Quick Start

1. Prerequisites

• Node.js v18 or higher
• A Google Cloud Project with the Sheets API enabled
• A Service Account with a 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

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing one.
3. Enable the Google Sheets API:
   • Go to "APIs & Services" → "Library".
   • Search for "Google Sheets API" and click "Enable".
4. Create a 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 the JSON file).
   • Grant "Editor" permissions.

4. Configure MCP Client

Easy Setup (Recommended)

Run the interactive setup script:

npm run setup

This script will:

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

Manual Setup

If you prefer manual configuration, add the following 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 a .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

💻 Usage Examples

Basic Usage

The following are some basic commands to interact with Google Sheets:

# Read from a range
sheets_get_values

# Write to a range
sheets_update_values

Advanced Usage

For more complex operations, you can use batch operations:

# Read from multiple ranges
sheets_batch_get_values

# Write to multiple ranges
sheets_batch_update_values

📋 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

🔧 Technical Details

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 that the JSON key path is absolute and correct.
• Check that the GOOGLE_PROJECT_ID matches your project.
• Ensure that the Sheets API is enabled.

"Permission denied"

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

"Spreadsheet not found"

• Verify the spreadsheet ID from the 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 the Node.js path is correct in the Claude Desktop config.
• Look for errors in the Claude Desktop logs.
• Use npm run inspector to debug.

🔍 Finding IDs

Spreadsheet ID

You can find the 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.

💡 Usage Tip

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.