Mcp Gsheets

MCP Google Sheets Server

[npm version] [CI] [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 Code, Claude Desktop, Cursor, etc.).

Key Features

• Complete Google Sheets Integration: Read, write, and manage spreadsheets
• Advanced Operations: Batch operations, formatting, charts, and conditional formatting
• Flexible Authentication: Support for both file-based and JSON string credentials
• Production Ready: Built with TypeScript, comprehensive error handling, and full test coverage

Requirements

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

Getting Started

Quick Install (Recommended)

Add the following config to your MCP client:

{  "mcpServers": {    "mcp-gsheets": {      "command": "npx",      "args": ["-y", "mcp-gsheets@latest"],      "env": {        "GOOGLE_PROJECT_ID": "your-project-id",        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"      }    }  }}

[!NOTE] Using mcp-gsheets@latest ensures that your MCP client will always use the latest version of the MCP Google Sheets server.

MCP Client Configuration

claude mcp add mcp-gsheets npx mcp-gsheets@latest

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

{  "mcpServers": {    "mcp-gsheets": {      "command": "npx",      "args": ["-y", "mcp-gsheets@latest"],      "env": {        "GOOGLE_PROJECT_ID": "your-project-id",        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"      }    }  }}

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"
   • In the service accounts list, click the three dots in the Actions column → Manage keys → Add key → Create new key → select JSON format
   • 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

Alternative Authentication Methods

Option 1: JSON String Authentication

Instead of using a file path for credentials, 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.

{  "mcpServers": {    "mcp-gsheets": {      "command": "npx",      "args": ["-y", "mcp-gsheets@latest"],      "env": {        "GOOGLE_PROJECT_ID": "your-project-id",        "GOOGLE_SERVICE_ACCOUNT_KEY": "{\"type\":\"service_account\",\"project_id\":\"your-project\",\"private_key_id\":\"...\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\n...\\n-----END PRIVATE KEY-----\\n\",\"client_email\":\"[email protected]\",\"client_id\":\"...\",\"auth_uri\":\"https://accounts.google.com/o/oauth2/auth\",\"token_uri\":\"https://oauth2.googleapis.com/token\",\"auth_provider_x509_cert_url\":\"https://www.googleapis.com/oauth2/v1/certs\",\"client_x509_cert_url\":\"...\"}"      }    }  }}

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

Option 2: Private Key Authentication (Simplified)

For the most user-friendly approach, you can provide just the private key and email directly. This is the simplest method and requires only two fields from your service account JSON:

{  "mcpServers": {    "mcp-gsheets": {      "command": "npx",      "args": ["-y", "mcp-gsheets@latest"],      "env": {        "GOOGLE_PRIVATE_KEY": "-----BEGIN PRIVATE KEY-----\\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCgR6bvMNOUHZ29\\n+YgbVHAXsT/s+L/jnXTCB193zikCzspSBSfxLu8VRDjkNq9WUoDxizTATzMFNvNf\\n...\\n-----END PRIVATE KEY-----\\n",        "GOOGLE_CLIENT_EMAIL": "[email protected]"      }    }  }}

Note: When using GOOGLE_PRIVATE_KEY:

• Newlines in the private key should be represented as \\n
• The private key must include the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- markers
• The client email should be the service account email from your JSON file
• GOOGLE_PROJECT_ID is optional when using this method

Local Development Setup

If you want to develop or contribute to this project, you can clone and build it locally:

# Clone the repositorygit clone https://github.com/freema/mcp-gsheets.gitcd mcp-gsheets
# Install dependenciesnpm install
# Build the projectnpm run build

Interactive Setup Script

Run the interactive setup script to configure your local MCP client:

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 Local Configuration

If you prefer manual configuration with a local build, add to your MCP client config:

{  "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"      }    }  }}

📦 Build & Development

Development Commands

# Development mode with hot reloadnpm run dev
# Build for productionnpm run build
# Type checkingnpm run typecheck
# Clean build artifactsnpm run clean
# Run MCP inspector for debuggingnpm run inspector
# Run MCP inspector in development modenpm run inspector:dev

Task Runner (Alternative)

If you have Task installed:

# Install dependenciestask install
# Build the projecttask build
# Run in development modetask dev
# Run lintertask lint
# Format codetask fmt
# Run all checkstask 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

🎚️ Reducing context cost with toolsets

All 44 tools together cost about 9,900 tokens of context in every session, before the model does anything. Most workflows need a fraction of that. GSHEETS_TOOLSETS limits which tools the server exposes:

{  "mcpServers": {    "gsheets": {      "command": "npx",      "args": ["mcp-gsheets"],      "env": {        "GOOGLE_PROJECT_ID": "your-project-id",        "GOOGLE_APPLICATION_CREDENTIALS": "/path/to/key.json",        "GSHEETS_TOOLSETS": "core,charts"      }    }  }}

Measured tools/list cost:

Notes:

• The default is unchanged — leave GSHEETS_TOOLSETS unset and you get every tool, exactly as before.
• core is always included. GSHEETS_TOOLSETS=charts means "charts as well as core", not "charts only" — without core the server cannot read a cell.
• A typo is a startup error, not a silently smaller tool list.
• GSHEETS_READ_ONLY=true drops every writing tool and can be combined with GSHEETS_TOOLSETS. It is enforced when a tool is called, not just when the list is built, so a client cannot write by naming a hidden tool.

All tools also carry MCP annotations (readOnlyHint, destructiveHint, idempotentHint), so clients can skip confirmation prompts on reads and warn before destructive operations.

📋 Available Tools

Reading Data

Writing Data

Sheet Management

Cell Formatting

Native Tables

Charts

Read / Snapshot Tools

🔧 Code Quality

Linting

# Run ESLintnpm run lint
# Fix auto-fixable issuesnpm run lint:fix

Formatting

# Check formatting with Prettiernpm run format:check
# Format codenpm run format

Type Checking

# Run TypeScript type checkingnpm run typecheck

❗ 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
• If using private key auth: Check that the private key includes BEGIN/END markers and newlines are escaped as \\n
• Verify GOOGLE_CLIENT_EMAIL is a valid service account email
• Check GOOGLE_PROJECT_ID matches your project (or is included in JSON for full JSON auth)
• 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

📘 Tool Details

sheets_get_sheet_structure

Returns lightweight structural/dimensional metadata for a sheet without any per-cell data. Much faster and cheaper than sheets_get_full_sheet_snapshot when you only need layout information.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet
• sheetName (required): Name of the sheet (tab)

Returns: sheetName, sheetId, sheetIndex, tabColor, tabColorStyle, dimensions (rowCount, columnCount), frozen (rowCount, columnCount), columnWidths (array of pixel sizes), rowHeights (array of pixel sizes), hiddenColumns (0-based indices), hiddenRows (0-based indices), mergeCount, merges (A1 notation array)

sheets_get_formatting_compact

Read cell formatting for a range and return it as compact A1Range → format pairs. Adjacent cells with identical formatting are collapsed into rectangular ranges (run-length encoded), reducing output by 90 %+ compared to per-cell data.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet
• sheetName (required): Name of the sheet (tab)
• range (required): Range without sheet prefix, e.g. "A1:Z85"
• useEffectiveFormat (optional): false (default) = userEnteredFormat (only explicit overrides, smaller output); true = effectiveFormat (all inherited defaults)
• fields (optional): Array of format field names to include, e.g. ["backgroundColor", "textFormat", "borders"]

Returns: { range, formatType, rangeCount, data: { "A1:C3": { backgroundColor: {...} }, ... } }

Supported fields: backgroundColor, backgroundColorStyle, textFormat, horizontalAlignment, verticalAlignment, wrapStrategy, textRotation, numberFormat, padding, borders

sheets_get_full_sheet_snapshot

Master one-shot tool that returns all structural and formatting metadata in a single API call.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet
• sheetName (required): Name of the sheet (tab)
• includeFormattingRange (optional): If provided (e.g. "A1:Z100"), per-cell formatting is included in the response
• useEffectiveFormat (optional): Use effectiveFormat instead of userEnteredFormat when including cell formatting (default: false)
• fields (optional): Array of format field names to return, e.g. ["backgroundColor", "textFormat"] — reduces API transfer size and response size
• compactMode (optional): When true, identical adjacent cells are collapsed into rectangular ranges (RLE). Reduces a typical 85×28 sheet from ~60 000 lines to ~500 lines (default: false)

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:

// Insert 1 empty row before row 5{  "spreadsheetId": "your-spreadsheet-id",  "range": "Sheet1!A5"}
// Insert 3 rows after row 10 with data{  "spreadsheetId": "your-spreadsheet-id",  "range": "Sheet1!A10",  "rows": 3,  "position": "AFTER",  "values": [    ["John", "Doe", "[email protected]"],    ["Jane", "Smith", "[email protected]"],    ["Bob", "Johnson", "[email protected]"]  ]}

sheets_delete_columns

Delete one or more columns from a sheet using a full-column A1 range.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet
• range (required): Full-column A1 range to delete (e.g., "Sheet1!B:D" or "Sheet1!C:C")

Examples:

// Delete columns B through D from Sheet1{  "spreadsheetId": "your-spreadsheet-id",  "range": "Sheet1!B:D"}
// Delete a single column from the first sheet{  "spreadsheetId": "your-spreadsheet-id",  "range": "C:C"}

sheets_delete_rows

Delete one or more rows from a sheet using a full-row A1 range.

Parameters:

• spreadsheetId (required): The ID of the spreadsheet
• range (required): Full-row A1 range to delete (e.g., "Sheet1!2:4" or "Sheet1!3:3")

Examples:

// Delete rows 2 through 4 from Sheet1{  "spreadsheetId": "your-spreadsheet-id",  "range": "Sheet1!2:4"}
// Delete a single row from the first sheet{  "spreadsheetId": "your-spreadsheet-id",  "range": "3:3"}

📋 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

👤 Author

Tomáš Grásl - tomasgrasl.cz

📄 License

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