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.). - Complete Google Sheets Integration: Read, write, and manage spreadsheets - Advanced Operations: Batch operations, formatting, charts, and conditional formatting - Flexible Authentic
Add this skill
npx mdskills install freema/mcp-gsheetsComprehensive Google Sheets integration with excellent documentation and flexible authentication options
1# MCP Google Sheets Server23<a href="https://glama.ai/mcp/servers/@freema/mcp-gsheets">4 <img width="380" height="200" src="https://glama.ai/mcp/servers/@freema/mcp-gsheets/badge" />5</a>67[](https://www.npmjs.com/package/mcp-gsheets)89101112131415A 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.).1617## Key Features1819- **Complete Google Sheets Integration**: Read, write, and manage spreadsheets20- **Advanced Operations**: Batch operations, formatting, charts, and conditional formatting21- **Flexible Authentication**: Support for both file-based and JSON string credentials22- **Production Ready**: Built with TypeScript, comprehensive error handling, and full test coverage2324## Requirements2526- [Node.js](https://nodejs.org/) v18 or higher27- [Google Cloud Project](https://console.cloud.google.com) with Sheets API enabled28- Service Account with JSON key file29- [npm](https://www.npmjs.com/)3031## Getting Started3233### Quick Install (Recommended)3435Add the following config to your MCP client:3637```json38{39 "mcpServers": {40 "mcp-gsheets": {41 "command": "npx",42 "args": ["-y", "mcp-gsheets@latest"],43 "env": {44 "GOOGLE_PROJECT_ID": "your-project-id",45 "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"46 }47 }48 }49}50```5152> [!NOTE]53> Using `mcp-gsheets@latest` ensures that your MCP client will always use the latest version of the MCP Google Sheets server.5455### MCP Client Configuration5657<details>58 <summary>Claude Code</summary>59 Use the Claude Code CLI to add the MCP Google Sheets server (<a href="https://docs.anthropic.com/en/docs/claude-code/mcp">guide</a>):6061```bash62claude mcp add mcp-gsheets npx mcp-gsheets@latest63```6465After adding, edit your Claude Code config to add the required environment variables:6667```json68{69 "mcpServers": {70 "mcp-gsheets": {71 "command": "npx",72 "args": ["mcp-gsheets@latest"],73 "env": {74 "GOOGLE_PROJECT_ID": "your-project-id",75 "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"76 }77 }78 }79}80```8182</details>8384<details>85 <summary>Claude Desktop</summary>8687Add to your Claude Desktop config:88- macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`89- Windows: `%APPDATA%\Claude\claude_desktop_config.json`90- Linux: `~/.config/claude/claude_desktop_config.json`9192```json93{94 "mcpServers": {95 "mcp-gsheets": {96 "command": "npx",97 "args": ["-y", "mcp-gsheets@latest"],98 "env": {99 "GOOGLE_PROJECT_ID": "your-project-id",100 "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"101 }102 }103 }104}105```106107</details>108109<details>110 <summary>Cursor</summary>111112Go to `Cursor Settings` → `MCP` → `New MCP Server`. Use the config provided above.113114</details>115116<details>117 <summary>Cline</summary>118119Follow https://docs.cline.bot/mcp/configuring-mcp-servers and use the config provided above.120121</details>122123<details>124 <summary>Other MCP Clients</summary>125126For 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.127128</details>129130### Google Cloud Setup1311321. Go to [Google Cloud Console](https://console.cloud.google.com)1332. Create a new project or select existing1343. Enable Google Sheets API:135 - Navigate to "APIs & Services" → "Library"136 - Search for "Google Sheets API" and click "Enable"1374. Create Service Account:138 - Go to "APIs & Services" → "Credentials"139 - Click "Create Credentials" → "Service Account"140 - In the service accounts list, click the three dots in the `Actions` column → `Manage keys` → `Add key` → `Create new key` → select JSON format141 - Download the JSON key file1425. Share your spreadsheets:143 - Open your Google Sheet144 - Click Share and add the service account email (from JSON file)145 - Grant "Editor" permissions146147### Alternative Authentication Methods148149#### Option 1: JSON String Authentication150151Instead 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.152153```json154{155 "mcpServers": {156 "mcp-gsheets": {157 "command": "npx",158 "args": ["-y", "mcp-gsheets@latest"],159 "env": {160 "GOOGLE_PROJECT_ID": "your-project-id",161 "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\":\"...@....iam.gserviceaccount.com\",\"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\":\"...\"}"162 }163 }164 }165}166```167168**Note**: When using `GOOGLE_SERVICE_ACCOUNT_KEY`:169- The entire JSON must be on a single line170- All quotes must be escaped with backslashes171- Newlines in the private key must be represented as `\\n`172- If the JSON includes a `project_id`, you can omit `GOOGLE_PROJECT_ID`173174#### Option 2: Private Key Authentication (Simplified)175176For 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:177178```json179{180 "mcpServers": {181 "mcp-gsheets": {182 "command": "npx",183 "args": ["-y", "mcp-gsheets@latest"],184 "env": {185 "GOOGLE_PRIVATE_KEY": "-----BEGIN PRIVATE KEY-----\\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCgR6bvMNOUHZ29\\n+YgbVHAXsT/s+L/jnXTCB193zikCzspSBSfxLu8VRDjkNq9WUoDxizTATzMFNvNf\\n...\\n-----END PRIVATE KEY-----\\n",186 "GOOGLE_CLIENT_EMAIL": "spreadsheet@your-project.iam.gserviceaccount.com"187 }188 }189 }190}191```192193**Note**: When using `GOOGLE_PRIVATE_KEY`:194- Newlines in the private key should be represented as `\\n`195- The private key must include the `-----BEGIN PRIVATE KEY-----` and `-----END PRIVATE KEY-----` markers196- The client email should be the service account email from your JSON file197- `GOOGLE_PROJECT_ID` is optional when using this method198199## Local Development Setup200201If you want to develop or contribute to this project, you can clone and build it locally:202203```bash204# Clone the repository205git clone https://github.com/freema/mcp-gsheets.git206cd mcp-gsheets207208# Install dependencies209npm install210211# Build the project212npm run build213```214215### Interactive Setup Script216217Run the interactive setup script to configure your local MCP client:218219```bash220npm run setup221```222223This will:224- Guide you through the configuration225- Automatically detect your Node.js installation (including nvm)226- Find your Claude Desktop config227- Create the proper JSON configuration228- Optionally create a .env file for development229230### Manual Local Configuration231232If you prefer manual configuration with a local build, add to your MCP client config:233234```json235{236 "mcpServers": {237 "mcp-gsheets": {238 "command": "node",239 "args": ["/absolute/path/to/mcp-gsheets/dist/index.js"],240 "env": {241 "GOOGLE_PROJECT_ID": "your-project-id",242 "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"243 }244 }245 }246}247```248249## 📦 Build & Development250251### Development Commands252253```bash254# Development mode with hot reload255npm run dev256257# Build for production258npm run build259260# Type checking261npm run typecheck262263# Clean build artifacts264npm run clean265266# Run MCP inspector for debugging267npm run inspector268269# Run MCP inspector in development mode270npm run inspector:dev271```272273### Task Runner (Alternative)274275If you have [Task](https://taskfile.dev) installed:276277```bash278# Install dependencies279task install280281# Build the project282task build283284# Run in development mode285task dev286287# Run linter288task lint289290# Format code291task fmt292293# Run all checks294task check295```296297### Development Setup2982991. Create `.env` file for testing:300```bash301cp .env.example .env302# Edit .env with your credentials:303# GOOGLE_PROJECT_ID=your-project-id304# GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json305# TEST_SPREADSHEET_ID=your-test-spreadsheet-id306```3073082. Run in development mode:309```bash310npm run dev # Watch mode with auto-reload311```312313## 📋 Available Tools314315### Reading Data316- `sheets_get_values` - Read from a range317- `sheets_batch_get_values` - Read from multiple ranges318- `sheets_get_metadata` - Get spreadsheet info319- `sheets_check_access` - Check access permissions320321### Writing Data322- `sheets_update_values` - Write to a range323- `sheets_batch_update_values` - Write to multiple ranges324- `sheets_append_values` - Append rows to a table (**Note:** Default `insertDataOption` is `OVERWRITE`. To insert new rows, set `insertDataOption: 'INSERT_ROWS'`)325- `sheets_clear_values` - Clear cell contents326- `sheets_insert_rows` - Insert new rows at specific position with optional data327328### Sheet Management329- `sheets_insert_sheet` - Add new sheet330- `sheets_delete_sheet` - Remove sheet331- `sheets_duplicate_sheet` - Copy sheet332- `sheets_copy_to` - Copy to another spreadsheet333- `sheets_update_sheet_properties` - Update sheet settings334335### Batch Operations336- `sheets_batch_delete_sheets` - Delete multiple sheets at once337- `sheets_batch_format_cells` - Format multiple cell ranges at once338339### Cell Formatting340- `sheets_format_cells` - Format cells (colors, fonts, alignment, number formats)341- `sheets_update_borders` - Add or modify cell borders342- `sheets_merge_cells` - Merge cells together343- `sheets_unmerge_cells` - Unmerge previously merged cells344- `sheets_add_conditional_formatting` - Add conditional formatting rules345346### Charts347- `sheets_create_chart` - Create various types of charts348- `sheets_update_chart` - Modify existing charts349- `sheets_delete_chart` - Remove charts350351## 🔧 Code Quality352353### Linting354355```bash356# Run ESLint357npm run lint358359# Fix auto-fixable issues360npm run lint:fix361```362363### Formatting364365```bash366# Check formatting with Prettier367npm run format:check368369# Format code370npm run format371```372373### Type Checking374375```bash376# Run TypeScript type checking377npm run typecheck378```379380## ❗ Troubleshooting381382### Common Issues383384**"Authentication failed"**385- If using file-based auth: Verify JSON key path is absolute and correct386- If using JSON string auth: Ensure JSON is properly escaped and valid387- If using private key auth: Check that the private key includes BEGIN/END markers and newlines are escaped as `\\n`388- Verify GOOGLE_CLIENT_EMAIL is a valid service account email389- Check GOOGLE_PROJECT_ID matches your project (or is included in JSON for full JSON auth)390- Ensure Sheets API is enabled391392**"Permission denied"**393- Share spreadsheet with service account email394- Service account needs "Editor" role395- Check email in JSON file (client_email field)396397**"Spreadsheet not found"**398- Verify spreadsheet ID from URL399- Format: `https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit`400401**MCP Connection Issues**402- Ensure you're using the built version (`dist/index.js`)403- Check that Node.js path is correct in Claude Desktop config404- Look for errors in Claude Desktop logs405- Use `npm run inspector` to debug406407## 🔍 Finding IDs408409### Spreadsheet ID410From the URL:411```412https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit413 ↑ This is the spreadsheet ID414```415416### Sheet ID417Use `sheets_get_metadata` to list all sheets with their IDs.418419## 📝 Tips4204211. Always test with a copy of your data4222. Use batch operations for better performance4233. Set appropriate permissions (read-only vs edit)4244. Check rate limits for large operations4255. Use `sheets_check_access` to verify permissions before operations426427## 📘 Tool Details428429### sheets_insert_rows430431Insert new rows at a specific position in a spreadsheet with optional data.432433**Parameters:**434- `spreadsheetId` (required): The ID of the spreadsheet435- `range` (required): A1 notation anchor point where rows will be inserted (e.g., "Sheet1!A5")436- `rows` (optional): Number of rows to insert (default: 1)437- `position` (optional): 'BEFORE' or 'AFTER' the anchor row (default: 'BEFORE')438- `inheritFromBefore` (optional): Whether to inherit formatting from the row before (default: false)439- `values` (optional): 2D array of values to fill the newly inserted rows440- `valueInputOption` (optional): 'RAW' or 'USER_ENTERED' (default: 'USER_ENTERED')441442**Examples:**443444```javascript445// Insert 1 empty row before row 5446{447 "spreadsheetId": "your-spreadsheet-id",448 "range": "Sheet1!A5"449}450451// Insert 3 rows after row 10 with data452{453 "spreadsheetId": "your-spreadsheet-id",454 "range": "Sheet1!A10",455 "rows": 3,456 "position": "AFTER",457 "values": [458 ["John", "Doe", "john@example.com"],459 ["Jane", "Smith", "jane@example.com"],460 ["Bob", "Johnson", "bob@example.com"]461 ]462}463```464465## 📋 Changelog466467See [CHANGELOG.md](CHANGELOG.md) for a list of changes in each version.468469## 🤝 Contributing4704711. Fork the repository4722. Create your feature branch (`git checkout -b feature/amazing-feature`)4733. Run tests and linting (`npm run check`)4744. Commit your changes (`git commit -m 'Add some amazing feature'`)4755. Push to the branch (`git push origin feature/amazing-feature`)4766. Open a Pull Request477478## 👤 Author479480**Tomáš Grásl** - [tomasgrasl.cz](https://www.tomasgrasl.cz/)481482## 📄 License483484This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
Full transparency — inspect the skill content before installing.