mcp-gsheets

<!-- mcp-name: io.github.freema/mcp-gsheets -->

# MCP Google Sheets Server

<a href="https://glama.ai/mcp/servers/@freema/mcp-gsheets">
  <img width="380" height="200" src="https://glama.ai/mcp/servers/@freema/mcp-gsheets/badge" />
</a>

[![npm version](https://badge.fury.io/js/mcp-gsheets.svg)](https://www.npmjs.com/package/mcp-gsheets)
![CI](https://github.com/freema/mcp-gsheets/workflows/CI/badge.svg)
![Coverage](https://codecov.io/gh/freema/mcp-gsheets/branch/main/graph/badge.svg)
![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)
![TypeScript](https://img.shields.io/badge/TypeScript-5.0%2B-007ACC?logo=typescript&logoColor=white)
![Node](https://img.shields.io/badge/Node.js-20%2B-339933?logo=node.js&logoColor=white)
![code style: prettier](https://img.shields.io/badge/code_style-prettier-ff69b4.svg?logo=prettier&logoColor=white)

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](https://nodejs.org/) v20 or higher
- [Google Cloud Project](https://console.cloud.google.com) with Sheets API enabled
- Service Account with JSON key file
- [npm](https://www.npmjs.com/)

## Getting Started

### Quick Install (Recommended)

Add the following config to your MCP client:

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

> [!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

<details>
  <summary>Claude Code</summary>
  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>):

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

After adding, edit your Claude Code config to add the required environment variables:

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

</details>

<details>
  <summary>Claude Desktop</summary>

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`

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

</details>

<details>
  <summary>Cursor</summary>

Go to `Cursor Settings` → `MCP` → `New MCP Server`. Use the config provided above.

</details>

<details>
  <summary>Cline</summary>

Follow https://docs.cline.bot/mcp/configuring-mcp-servers and use the config provided above.

</details>

<details>
  <summary>Other MCP Clients</summary>

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.

</details>

### Google Cloud Setup

1. Go to [Google Cloud Console](https://console.cloud.google.com)
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.

```json
{
  "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\":\"...@....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\":\"...\"}"
      }
    }
  }
}
```

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

```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": "spreadsheet@your-project.iam.gserviceaccount.com"
      }
    }
  }
}
```

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

```bash
# Clone the repository
git clone https://github.com/freema/mcp-gsheets.git
cd mcp-gsheets

# Install dependencies
npm install

# Build the project
npm run build
```

### Interactive Setup Script

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

```bash
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:

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

## 📦 Build & Development

### Development Commands

```bash
# 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](https://taskfile.dev) installed:

```bash
# 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 `.env` file for testing:
```bash
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:
```bash
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:

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

| Toolset | Tools | What it covers |
|---|---|---|
| `core` | 11 | Read/write values, metadata, sheet structure, create spreadsheet |
| `sheets` | 9 | Sheet lifecycle, rows and columns |
| `formatting` | 15 | Colours, borders, merges, conditional rules, links, dates |
| `charts` | 3 | Create, update, delete charts |
| `tables` | 4 | Nat