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

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:

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

Alternative: JSON String Authentication

Instead of using a file path, you can provide the service account credentials directly as a JSON string. This is useful for containerized environments, CI/CD pipelines, or when you want to avoid managing credential files.

Note: When using GOOGLE_SERVICE_ACCOUNT_KEY:

• The entire JSON must be on a single line

• All quotes must be escaped with backslashes

• Newlines in the private key must be represented as \\n

• If the JSON includes a project_id, you can omit GOOGLE_PROJECT_ID

Restart Claude Desktop after adding the configuration.

📦 Build & Development

Development Commands

Task Runner (Alternative)

If you have Task installed:

Development Setup

1. Create .env file for testing:

2. Run in development mode:

📋 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 (Note: Default insertDataOption is OVERWRITE. To insert new rows, set insertDataOption: 'INSERT_ROWS')

• sheets_clear_values - Clear cell contents

• sheets_insert_rows - Insert new rows at specific position with optional data

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

Batch Operations

• sheets_batch_delete_sheets - Delete multiple sheets at once

• sheets_batch_format_cells - Format multiple cell ranges at once

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

Charts

• sheets_create_chart - Create various types of charts

• sheets_update_chart - Modify existing charts

• sheets_delete_chart - Remove charts

🔧 Code Quality

Linting

Formatting

Type Checking

❗ Troubleshooting

Common Issues

"Authentication failed"

• If using file-based auth: Verify JSON key path is absolute and correct

• If using JSON string auth: Ensure JSON is properly escaped and valid

• Check GOOGLE_PROJECT_ID matches your project (or is included in JSON)

• 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:

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

📘 Tool Details

sheets_insert_rows

Insert new rows at a specific position in a spreadsheet with optional data.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet

• range (required): A1 notation anchor point where rows will be inserted (e.g., "Sheet1!A5")

• rows (optional): Number of rows to insert (default: 1)

• position (optional): 'BEFORE' or 'AFTER' the anchor row (default: 'BEFORE')

• inheritFromBefore (optional): Whether to inherit formatting from the row before (default: false)

• values (optional): 2D array of values to fill the newly inserted rows

• valueInputOption (optional): 'RAW' or 'USER_ENTERED' (default: 'USER_ENTERED')

Examples:

📋 Changelog

See CHANGELOG.md for a list of changes in each version.

🤝 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.