# sheets-mcp

MCP server exposing the Google Sheets API v4 (read/write cell values, batch
value operations, create spreadsheets, copy sheets, raw batch formatting
updates) over stdio, authenticated as a single Google user via OAuth 2.0.

## One-time setup

### 1. Create a Google Cloud project

Go to https://console.cloud.google.com/projectcreate and create a project (or pick an existing one).

### 2. Enable the Google Sheets API

In your project, go to **APIs & Services > Library**, search for "Google Sheets API", and click **Enable**.

### 3. Configure the OAuth consent screen

Go to **APIs & Services > OAuth consent screen**.

- User type: **Internal** if your Google Workspace admin allows it (simplest), otherwise **External**.
- Fill in the required app name/support email fields.
- Add your Google account as a test user if the app is in "Testing" publish status (External apps stay in Testing indefinitely for personal/internal use without Google's review — just re-consent every 7 days, or use Internal to avoid that entirely).

### 4. Create an OAuth 2.0 Client ID (Desktop app)

Go to **APIs & Services > Credentials > Create Credentials > OAuth client ID**.

- Application type: **Desktop app**.
- Give it any name (e.g. "sheets-mcp").
- Click **Create**, then **Download JSON**.

### 5. Install the credentials

Save the downloaded file as `credentials.json` in this project's root directory:

```bash
mv ~/Downloads/client_secret_*.json /path/to/sheets-mcp/credentials.json
```

### 6. Install dependencies and build

```bash
npm install
npm run build
```

### 7. First run — one-time browser consent

```bash
node build/index.js
```

The first run opens your default browser to Google's consent screen. Approve
access. After approval, a `token.json` file is created in the project root
(mode `0600`) — subsequent runs use it silently without opening a browser
again, refreshing automatically as needed.

## Usage

Point your MCP client (e.g. Claude Code) at `node /path/to/sheets-mcp/build/index.js` as a stdio MCP server:

```bash
claude mcp add sheets --scope user -- node "/path/to/sheets-mcp/build/index.js"
```

## Tools

| Tool                        | Description                                                                                                                                                               |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `get_sheet_values`          | Read cell values from an A1-notation range, with control over `value_render_option` (formatted/unformatted/formula), `date_time_render_option`, and `major_dimension`     |
| `batch_get_sheet_values`    | Read multiple A1-notation ranges in one call                                                                                                                              |
| `update_sheet_values`       | Overwrite cell values in a range                                                                                                                                          |
| `batch_update_sheet_values` | Overwrite cell values across multiple ranges in one call                                                                                                                  |
| `append_row`                | Append row(s) after the last row with data                                                                                                                                |
| `clear_range`               | Clear cell contents without deleting the sheet                                                                                                                            |
| `batch_clear_sheet_values`  | Clear cell contents across multiple ranges in one call                                                                                                                    |
| `create_spreadsheet`        | Create a new spreadsheet, optionally with named tabs                                                                                                                      |
| `get_spreadsheet_metadata`  | Get title, sheet names/IDs/dimensions; optionally full grid data (formatting, validation, merges) via `include_grid_data`                                                 |
| `copy_sheet_to`             | Copy a sheet/tab into another spreadsheet                                                                                                                                 |
| `batch_update`              | Apply raw Sheets API `batchUpdate` requests (formatting, sort, find/replace, charts, named ranges, add/delete sheets, etc.) — full-power escape hatch, marked destructive |

Anything not covered by a dedicated tool (sort, find/replace, charts,
conditional formatting, data validation, named ranges, protected ranges,
pivot tables, merges) is reachable through `batch_update`'s raw request
passthrough — see the [Sheets API `Request` reference](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request).

## Security notes

- OAuth scope is the minimal `https://www.googleapis.com/auth/spreadsheets` — no broader Drive access is requested.
- `credentials.json` and `token.json` are gitignored and never committed; `token.json` permissions are (re-)set to `0600` on every write, including overwrites of a pre-existing file.
- All dynamic path segments (spreadsheet IDs, sheet IDs, A1 ranges) are `encodeURIComponent`-escaped before being placed in request URLs.
- `batch_update` is annotated `destructiveHint: true` since its raw request objects can delete sheets, named ranges, or overwrite data — MCP clients should gate it accordingly.
- The one-time OAuth consent callback server (`src/auth.ts`) binds explicitly to `127.0.0.1`, not all interfaces — it's implemented in-repo rather than via `@google-cloud/local-auth`, whose equivalent server bound to `0.0.0.0`, briefly exposing the callback endpoint to other hosts on the same network during first-run consent.
- Requests to the Sheets API have a 30s timeout and don't retry non-idempotent POSTs (`append_row`, `create_spreadsheet`, `copy_sheet_to`, `batch_update`) on network error, to avoid double-executing a request whose response was simply lost in transit.

## Testing

```bash
npm test
```

All tests run offline against mocked HTTP responses — no real Google credentials or network access required.