# gsheets-mcp

A small, token-lean MCP server that gives Claude Code (or any MCP client) CRUD access to
your own Google Sheets, Docs and Drive files — a lightweight, free alternative to routing
those calls through Zapier.

One Python file, stdio transport, your own Google Cloud OAuth client. Google's Sheets,
Drive and Docs APIs are free at personal scale, and there is nothing to host.

## Why not just use a generic connector

Generic Sheets connectors are expensive in tokens: huge tool schemas, an "inspect the
columns" round-trip before every write, JSON objects for every row, and whole-grid reads.
This server is built the other way round:

| Design choice | Effect |
|---|---|
| Output is **TSV with the row number first** | ~3× fewer tokens than keyed JSON, and the model can address a row straight back |
| Trailing blank cells trimmed, cells truncated to 80 chars, default limit 60 rows | no dumping a 200-row tab to read 5 |
| Sheet layout lives in **`sheets.yaml`** (tabs, header rows, protected ranges, notes) | no discovery calls; `describe()` returns the whole map in a few lines |
| Writes are **header-keyed** (`{"row": 12, "Status": "Done"}`) | the server caches the header row, so no inspect round-trip |
| `find()` filters **server-side** | only matching rows cross the wire |
| Guards: refuses writes into protected ranges and formula cells | the model cannot clobber a spill formula or a live tally by accident |

## Tools

**Sheets**
- `describe(sheet)` — tabs, sheetIds, header rows, notes, protected ranges
- `read(tab, range, limit, offset, render)` — TSV, row number first
- `find(tab, where)` — server-side filter; `where` is `Col op value` joined by `&`, ops `= != ~ !~ empty nonempty`
- `update_rows(tab, rows)` — `[{"row": 12, "Status": "Done"}]`, keyed by header name or column letter
- `append_rows(tab, rows)` — same shape, appended below the last used row
- `write_range(tab, range, values)` — 2-D block write
- `batch(requests)` — raw `spreadsheets.batchUpdate` (sortRange, repeatCell, setBasicFilter…)

**Drive / Docs**
- `drive_search(query, folder, kind, limit)` — `id  kind  name  modified`
- `drive_read(file_id, max_chars, offset)` — Docs exported as Markdown, text files as-is
- `drive_create(name, content, kind)` — `doc` | `txt` | `md` | `folder`
- `drive_update(file_id, name, content, folder)` — rename, move, or replace content
- `drive_trash(file_id)`
- `doc_append(doc_id, text)` / `doc_replace(doc_id, find, replace)` — Docs API, keeps formatting

Writes use `USER_ENTERED`, so `=FORMULA`, dates and numbers behave as if typed by a human.

## Quickstart

```bash
git clone https://github.com/<you>/gsheets-mcp.git ~/mcp/gsheets
cd ~/mcp/gsheets
python3 -m venv .venv && .venv/bin/pip install -r requirements.txt

cp sheets.example.yaml sheets.yaml   # then edit: your spreadsheet id, tabs, header rows
# put your OAuth desktop client JSON here as credentials.json  (see SETUP.md)

.venv/bin/python server.py auth      # opens a browser once, caches token.json

claude mcp add -s user gsheets -- ~/mcp/gsheets/.venv/bin/python ~/mcp/gsheets/server.py
```

Restart the client; the tools appear as `mcp__gsheets__*`.

Every tool's `sheet` argument defaults to the alias `dnd` (the sheet this was built for) —
either name your main alias `dnd` in `sheets.yaml`, or pass `sheet="myproject"` explicitly.

Getting `credentials.json` (the Google Cloud console part) is in **[SETUP.md](SETUP.md)**,
including the two places it is easy to get stuck.

## Files

| File | |
|---|---|
| `server.py` | the whole server |
| `sheets.yaml` | your spreadsheet map — **gitignored**, start from `sheets.example.yaml` |
| `credentials.json` | OAuth desktop client — **gitignored, never commit** |
| `token.json` | cached login — **gitignored, never commit** |

## Re-auth

While the OAuth consent screen is in *Testing*, Google expires the refresh token every
7 days. When a tool starts failing with an auth error:

```bash
cd ~/mcp/gsheets && .venv/bin/python server.py auth
```

Publishing the app (Audience → Publish app) removes the 7-day expiry. As a single-user
internal tool it is fine either way.

## Extending

The same shape works for any REST API: add a lazy service builder, a `@mcp.tool()`
function per verb, and keep the return value flat text. The value is in the config file
and the guards, not the API calls.

MIT.