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
Model Context Protocol (MCP) server for Google Sheets API integration
2026-11-13
ดาว
—
เครื่องมือ
—
อัปไทม์
—
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
For other MCP clients, use the standard configuration format shown above. Ensure the command is set to npx and include the environment variables for Google Cloud authentication.
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
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.
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:
# 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
# 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
🎚️ 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:
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
Tool
Description
Key Parameters
sheets_get_values
Read cell values from a single range
spreadsheetId, range (A1 notation), valueRenderOption
sheets_batch_get_values
Read cell values from multiple ranges in one request
spreadsheetId, ranges (array of A1 ranges)
sheets_get_metadata
Get spreadsheet metadata: title, locale, sheets list with IDs, row/column counts
spreadsheetId
sheets_check_access
Verify that the service account can access a spreadsheet
spreadsheetId
Writing Data
Tool
Description
Key Parameters
sheets_update_values
Write values to a single range (overwrites existing content)
Read native tables for a spreadsheet or a specific sheet
spreadsheetId, sheetName
Charts
Tool
Description
Key Parameters
sheets_create_chart
Create a bar, line, pie, column or other chart on a sheet
spreadsheetId, sheetId, chartSpec, position
sheets_update_chart
Modify an existing chart's spec or position
spreadsheetId, chartId, chartSpec, position
sheets_delete_chart
Remove a chart from a spreadsheet
spreadsheetId, chartId
Read / Snapshot Tools
Tool
Description
Key Parameters
sheets_get_merged_cells
Return all merged cell ranges for a sheet, with A1 notation and raw GridRange coordinates
spreadsheetId, sheetName
sheets_get_sheet_dimensions
Return column widths, row heights, frozen column/row counts, and hidden flags for every column and row
spreadsheetId, sheetName
sheets_get_sheet_formatting
Read raw cell formatting (background colour, font, borders, alignment, number format) for a range without returning cell values
spreadsheetId, range
sheets_get_conditional_formatting
Read all conditional formatting rules and banded (alternating-colour) ranges defined on a sheet
spreadsheetId, sheetName
sheets_get_sheet_structure
Lightweight structural metadata only — no per-cell data. Returns dimensions, frozen rows/cols, tab colour, column widths, row heights, hidden columns/rows, and all merges in A1 notation. Single fast API call
spreadsheetId, sheetName
sheets_get_formatting_compact
Read cell formatting for a range and return it as compact A1Range→format pairs (run-length encoded). Identical adjacent cells are collapsed into rectangular ranges — reduces output by 90 %+ compared to per-cell data
Master one-shot tool — returns all structural and formatting metadata (merges, dimensions, conditional formatting, and optionally cell formatting) in a single API call. Supports fields filter and compactMode to limit response size
Read the Basic Filter (AutoFilter) configuration for a sheet, including filtered range, sort specs, and per-column filter criteria (hidden values, conditions, colour filters)
spreadsheetId, sheetName
sheets_get_data_validation
Read data validation rules (checkboxes, dropdowns, custom formulas) from a sheet or range. Returns compact run-length-encoded list of unique rules grouped by cell ranges
spreadsheetId, sheetName, range
🔧 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"
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 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
📘 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
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"
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')
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
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)
