# google-sheets-mcp-server (Python / FastMCP)

An MCP server giving an agent full CRUD over Google Sheets through a service
account. Read tabs and rows, find rows by column value, append, update rows
partially or by key, delete rows, and manage tabs.

Built on [FastMCP](https://gofastmcp.com) 3.x. Runs locally over stdio.

---

## Contents

- [What it does](#what-it-does)
- [Setup](#setup)
  - [1. Create a service account](#1-create-a-service-account)
  - [2. Share the spreadsheet](#2-share-the-spreadsheet)
  - [3. Install](#3-install)
  - [4. Register with Claude](#4-register-with-claude)
  - [5. Run with Docker](#5-run-with-docker)
- [Configuration](#configuration)
- [Tools](#tools)
- [Design notes](#design-notes)
- [Development](#development)
- [Troubleshooting](#troubleshooting)

---

## What it does

Eleven tools, all prefixed `sheets_`. Every one declares an `outputSchema`
(derived from its Pydantic return model) and returns `structuredContent`, and
every one is annotated with `readOnlyHint`, `destructiveHint` and
`idempotentHint` so a client can decide what needs confirmation.

The design goal throughout is that an agent should never have to guess. Rows
come back tagged with their real 1-based sheet row numbers. Rows go in keyed by
column name rather than by position. When something is wrong, the error says
what to do about it:

```
COLUMN_NOT_FOUND: Column 'Statsu' not found. Available columns: Date, Task,
Status, Owner. Did you mean 'Status'? Column names come from row 1 of the tab;
call sheets_list_tabs to see the header row for every tab.
```

---

## Setup

### 1. Create a service account

A service account is a Google identity that belongs to a program rather than a
person. It gets its own email address, and you grant it access to a sheet the
same way you would a colleague.

1. Open the [Google Cloud console](https://console.cloud.google.com/) and select
   a project, or create one.
2. Enable the Sheets API: **APIs & Services → Library → Google Sheets API →
   Enable**. This is per-project and easy to forget; a 403 that mentions the API
   being disabled means you skipped it.
3. Go to **IAM & Admin → Service Accounts → Create service account**. Give it a
   name like `sheets-mcp`. You can skip the optional role and user-access steps
   — for sheet access, roles do nothing. Access comes from sharing the file.
4. Open the new account, go to the **Keys** tab, then **Add key → Create new key
   → JSON**. A `.json` file downloads. This is the only copy; Google will not
   show it again.
5. Note the `client_email` inside that file. It looks like
   `sheets-mcp@your-project.iam.gserviceaccount.com`. You need it for the next
   step.

Use the file exactly as downloaded. It contains `token_uri`, `client_id` and
several other fields that `google-auth` requires; a hand-assembled key with only
`client_email` and `private_key` will be rejected.

Treat the key file like a password. Keep it out of version control.

### 2. Share the spreadsheet

This is the step people miss, and it produces the most common error by a wide
margin.

Open your spreadsheet in Google Sheets, click **Share**, paste the service
account's `client_email`, set the role to **Editor**, untick **Notify people**
(it is not a real mailbox), and click **Share**.

Viewer is enough if you only ever read. Every write tool needs Editor.

The service account cannot see any file that has not been shared with it — not
even files owned by you in the same organisation.

### 3. Install

Requires Python 3.10 or newer.

```bash
git clone <this-repo> google-sheets-mcp-server
cd google-sheets-mcp-server

python3 -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install -e ".[dev]"
```

Or with [uv](https://docs.astral.sh/uv/):

```bash
uv venv && source .venv/bin/activate
uv pip install -e ".[dev]"
```

Point the server at your key and confirm it works end to end:

```bash
export GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/key.json
export VERIFY_SPREADSHEET_ID=<id of a scratch sheet>
python scripts/verify_live.py
```

`verify_live.py` runs a real append → read → update → delete cycle against a
temporary tab in that spreadsheet, then deletes the tab. Use a throwaway sheet.
See [Development](#development) for what it checks.

The spreadsheet ID is the part of the URL between `/d/` and `/edit`:

```
https://docs.google.com/spreadsheets/d/1AbC_dEfGhIjKlMnOpQrStUvWxYz/edit#gid=0
                                       └───────── this part ────────┘
```

### 4. Register with Claude

Use the `google-sheets-mcp-server` console script that `pip install` puts in
your virtualenv's `bin/`. Point at it by absolute path so the right interpreter
and dependencies are used regardless of what shell Claude launches:

```bash
claude mcp add google-sheets \
  --env GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/key.json \
  -- /absolute/path/to/google-sheets-mcp-server/.venv/bin/google-sheets-mcp-server
```

Everything after `--` is the command that gets run.

To pin the server to one spreadsheet so `spreadsheet_id` becomes optional on
every tool:

```bash
claude mcp add google-sheets \
  --env GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/key.json \
  --env DEFAULT_SPREADSHEET_ID=1AbC_dEfGhIjKlMnOpQrStUvWxYz \
  -- /absolute/path/to/google-sheets-mcp-server/.venv/bin/google-sheets-mcp-server
```

Add `--scope project` to share the config with a repo via `.mcp.json`, or
`--scope user` to make it available across all your projects. The default scope
is local to the current project and private to you.

Check it registered:

```bash
claude mcp list
claude mcp get google-sheets
```

<details>
<summary>Alternatives: <code>python -m</code>, uv, and Claude Desktop</summary>

Equivalent, using the module entry point:

```bash
claude mcp add google-sheets \
  --env GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/key.json \
  -- /absolute/path/to/.venv/bin/python -m google_sheets_mcp
```

With uv, no activation needed:

```bash
claude mcp add google-sheets \
  --env GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/key.json \
  -- uv run --directory /absolute/path/to/google-sheets-mcp-server \
     google-sheets-mcp-server
```

Claude Desktop — edit `claude_desktop_config.json`
(**Settings → Developer → Edit Config**):

```json
{
  "mcpServers": {
    "google-sheets": {
      "command": "/absolute/path/to/.venv/bin/google-sheets-mcp-server",
      "env": {
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/key.json",
        "DEFAULT_SPREADSHEET_ID": "1AbC_dEfGhIjKlMnOpQrStUvWxYz"
      }
    }
  }
}
```

Restart Claude Desktop afterwards.
</details>

---

### 5. Run with Docker

The image serves streamable HTTP. Inside the container the server listens on
8000 as an unprivileged user; publish it on 80 from the host.

Credentials are mounted as a file rather than passed as an environment
variable — `docker run --env-file` does not understand quoting, and a
service-account key is a long single line of JSON with spaces in it.
`service-account.json` is already covered by `.gitignore`.

```bash
docker build -t google-sheets-mcp-server .

docker run -d --name google-sheets-mcp \
  -p 80:8000 \
  -v "$PWD/service-account.json:/run/secrets/service-account.json:ro" \
  -e GOOGLE_APPLICATION_CREDENTIALS=/run/secrets/service-account.json \
  --restart unless-stopped \
  google-sheets-mcp-server
```

Add `-e DEFAULT_SPREADSHEET_ID=<sheet id>` to pin the server to one spreadsheet.
Swap `-d` for `--rm` to run it in the foreground and have it clean up on Ctrl-C.

The endpoint is then `http://localhost/mcp`. Register it with:

```bash
claude mcp add --transport http google-sheets http://localhost/mcp
```

Day-to-day:

```bash
docker logs -f google-sheets-mcp
docker stop google-sheets-mcp
docker start google-sheets-mcp
docker rm -f google-sheets-mcp        # before rebuilding under the same name
```

Port 80 is privileged on Linux hosts; Docker's port forwarding handles the
bind, so no `sudo` is needed for the container itself. If something already
owns port 80, change the left-hand side: `-p 8080:8000`.

---

## Configuration

| Variable | Required | Purpose |
|---|---|---|
| `GOOGLE_APPLICATION_CREDENTIALS` | one of these two | Absolute path to the service-account JSON key file. |
| `GOOGLE_SERVICE_ACCOUNT_JSON` | one of these two | The key JSON inline, raw or base64-encoded. For containers and CI, where mounting a file is awkward. Takes precedence if both are set. |
| `DEFAULT_SPREADSHEET_ID` | no | When set, `spreadsheet_id` becomes optional on every tool and defaults to this. A full sheet URL is accepted and the ID extracted. |

These can be exported into the environment, or written to a `.env` file in the
project root — the server loads it at startup. Real environment variables take
precedence, so an explicit `export` still overrides the file. Keep `.env` out of
version control; it is already gitignored.

Scope requested: `https://www.googleapis.com/auth/spreadsheets`. That covers
reading and writing sheet contents. It does not include Drive, so this server
cannot list your files, move them, or change sharing.

---

## Tools

### Reading

| Tool | Notes |
|---|---|
| `sheets_list_tabs` | Titles, numeric `sheet_id` (the gid), row/column counts, and row 1 of each tab as inferred headers. Start here. |
| `sheets_read_rows` | Read a tab or an A1 range. Paged via `offset`/`limit` (default 100, max 1000). `as_objects` (default true) maps each row to `{header: value}`. |
| `sheets_find_rows` | Rows where a named column matches a value, `exact` or `contains`, case-insensitive by default. |

`sheets_read_rows` has two modes worth understanding:

- **Without `range`** — row 1 is the header row, rows 2 onward are data,
  `offset`/`limit` page over the data rows, and `as_objects` maps by header.
- **With `range`** (e.g. `"B5:D40"`) — the range is read verbatim as positional
  values with no header mapping. Mapping headers onto an arbitrary range that
  might not start at column A is a good way to write data into the wrong
  column, so the two modes are kept separate.

Either way, every row carries its true 1-based sheet row number in `row`. That
is the number to pass to update and delete. Do not recompute it from the list
index — `offset` shifts it.

### Writing

| Tool | Notes |
|---|---|
| `sheets_append_rows` | Append to the bottom. Rows may be lists of values or dicts keyed by header name, mixed freely. Uses `values.append` with `USER_ENTERED` and `INSERT_ROWS`. |
| `sheets_update_rows` | Update by row number or by matching a key column. Dict payloads are partial. |
| `sheets_update_cells` | Raw A1 range write, for when the row abstraction does not fit. |

Two behaviours that matter:

**Partial updates.** When `values` is a dict, only the named columns are
written; everything else on the row is untouched. Adjacent columns are merged
into one range, so a payload touching `Task` and `Status` becomes a single
`B7:C7` write, while `Date` and `Owner` become two separate writes rather than
one range that would clobber the columns in between.

**Ambiguous keys are refused.** A `key_column`/`key_value` update that matches
more than one row fails and tells you which rows matched, unless you pass
`allow_multiple=true`. An ambiguous key never quietly rewrites several records.

### Deleting

| Tool | Notes |
|---|---|
| `sheets_delete_rows` | Delete by 1-based row number. Rows below shift up. **Destructive.** |
| `sheets_clear_range` | Blank the values, keep the rows and their positions. **Destructive.** |

Prefer `sheets_clear_range` when other row numbers are in flight — clearing
does not shift anything, so row numbers you are holding stay valid. After a
delete they are stale and you should re-read.

### Structure

| Tool | Notes |
|---|---|
| `sheets_add_tab` | Add a tab, optionally writing its header row in the same call. |
| `sheets_delete_tab` | Delete a tab and everything on it. **Destructive.** |
| `sheets_create_spreadsheet` | Create a new spreadsheet. See the caveat below. |

`sheets_create_spreadsheet` produces a file **owned by the service account**,
which means it will not appear in anyone's Drive until it is shared — and this
server lacks the Drive scope needed to share it. The tool's response says so.
For anything long-lived, create the sheet in your own account and share it with
the service account instead.

---

## Design notes

**Row numbers are 1-based everywhere in the tool API**, matching the row
headers you see in the Sheets UI. Internally, `deleteDimension` uses 0-based
half-open intervals, so row 1 becomes `start_index=0, end_index=1` and rows 3–5
become `start_index=2, end_index=5`. That conversion lives in
`plan_row_deletions` in `rows.py` and has its own tests.

**Deletions are applied bottom-up.** `batchUpdate` runs requests sequentially
against the mutating sheet, so deleting a low row first would shift every later
row up by one and subsequent deletions would hit the wrong rows. Target rows
are sorted descending, and contiguous runs are collapsed into single requests.

**Tab titles are quoted when they need it.** Titles that are not plain
identifiers, that look like a cell reference (`A1`), or that are reserved words
get wrapped in single quotes, with internal quotes doubled: `Bob's Tab` becomes
`'Bob''s Tab'`.

**429 and 5xx are retried** three times with exponential backoff plus jitter.
On final failure the quota message comes through plainly, along with the actual
rate limits and what to do about them.

**The gid and header row are cached** per `(spreadsheet_id, tab)` for the
process lifetime. The cache is invalidated automatically after anything
structural — adding or deleting a tab, deleting rows, or writing over row 1 —
and `sheets_list_tabs` takes a `refresh` flag to bust it by hand when the sheet
has been changed by someone else.

**Large reads are truncated, not dropped.** Responses are held to a ~40k
character budget; if a page overruns it, rows are trimmed from the tail and the
response says how many were omitted and what `offset` to use next.

**Tools are synchronous.** `google-api-python-client` is a blocking library, and
FastMCP runs sync tool functions in a worker thread by default, so a slow Sheets
call never blocks the event loop. Wrapping blocking I/O in `async def` would be
worse, not better.

**Errors are `SheetsToolError`, a subclass of FastMCP's `ToolError`.** Raising
one produces an `isError` result carrying the message. FastMCP skips
output-schema validation on error results, so an error never has to be squeezed
into a tool's success shape.

---

## Development

```bash
pytest                          # unit + integration tests
python scripts/verify_live.py   # real CRUD cycle against a scratch spreadsheet

# Inspect the tool surface
npx @modelcontextprotocol/inspector .venv/bin/google-sheets-mcp-server
```

### Tests

`pytest` runs 99 tests with no network access and no credentials.

The integration tests drive the **real MCP protocol** — an in-process FastMCP
`Client` — against an in-memory fake of the Sheets API (`tests/fake_sheets.py`).
The fake mirrors `googleapiclient`'s builder style
(`.spreadsheets().values().get(...).execute()`) and implements `deleteDimension`
with the genuine 0-based half-open semantics, so the index conversion is
actually exercised rather than merely asserted in isolation. Coverage includes
the full append → read → update → delete cycle, mixed dict and list row payloads
with scrambled key order, paging, retry on 429 and 5xx, the 403 message carrying
the real `client_email`, ambiguous-key refusal, quoted tab titles, cache
invalidation after a header rewrite, and every credential-loading failure path.

The delete-index logic has been mutation-tested: reversing the bottom-up sort
and shifting `end_index` by one each fail the suite, including the end-to-end
test that checks which rows actually survived.

### Live verification

A fake cannot catch a wrong `valueInputOption`, a range Google parses
differently, or a permission you have not actually granted.
`python scripts/verify_live.py` covers that gap against a real sheet. Beyond
repeating the CRUD cycle it checks things only a live API can answer — most
usefully, that `USER_ENTERED` turns `=E2+E3+E4` into a formula Sheets evaluates
to `425` rather than storing it as literal text.

It works in a temporary `_verify_<timestamp>` tab and deletes it at the end. It
never touches your existing tabs. If it dies midway the tab is left behind for
inspection; delete it by hand.

### Schema portability

`npx @modelcontextprotocol/inspector --cli .venv/bin/google-sheets-mcp-server --method tools/list --strict`
reports 0 errors and 0 warnings across all 11 tools.

Pydantic emits unions as `anyOf` with a single `type` per branch, which is the
portable form every MCP client handles. (The TypeScript port of this server
needed explicit work here, because Zod collapses unions of bare primitives into
`type: ["string","number",...]`, which some clients mis-read.)

---

## Troubleshooting

**`PERMISSION_DENIED` / 403.** The spreadsheet is not shared with the service
account, or the Sheets API is not enabled for its project. The error message
includes the exact `client_email` to share with. Check for a typo in the email
and confirm you set the role to Editor, not Viewer, for writes.

**`NOT_FOUND` / 404.** The `spreadsheet_id` is wrong. It is the segment between
`/d/` and `/edit` — not the tab name, and not the `gid` at the end of the URL.
A trashed spreadsheet also returns 404.

**`UNAUTHENTICATED` / 401.** The key was rejected. Check the path in
`GOOGLE_APPLICATION_CREDENTIALS` is absolute and readable, and that the key has
not been revoked in the Cloud console.

**`BAD_CREDENTIALS` mentioning `client_email`.** You probably downloaded an
OAuth client secret rather than a service-account key. Go to **IAM & Admin →
Service Accounts → your account → Keys → Add key → JSON**.

**`BAD_CREDENTIALS` mentioning `token_uri` or "rejected by google-auth".** The
key JSON is incomplete. Use the file exactly as downloaded rather than
reconstructing it from a couple of fields.

**`TAB_NOT_FOUND`.** Tab titles are case-sensitive. The error lists the titles
that do exist.

**`RATE_LIMITED` / 429.** Sheets allows roughly 60 reads and 60 writes per
minute per user per project. Batch rows into single calls —
`sheets_append_rows` takes many rows at once — or raise the quota in the Cloud
console.

**The server starts but Claude sees no tools.** Check the command path is
absolute and points into the virtualenv (`.venv/bin/google-sheets-mcp-server`),
not a bare `python`. `claude mcp get google-sheets` shows the resolved config.

**Garbled protocol errors on startup.** Anything a dependency prints to stdout
corrupts the JSON-RPC stream. This server sends its own diagnostics to stderr,
and FastMCP's startup banner goes to stderr too, which the test suite checks. If
you add code here, never `print()` to stdout.

---

## License

MIT