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 3.x. Runs locally over stdio.

Contents

• What it does

• Setup

  • 1. Create a service account

  • 2. Share the spreadsheet

  • 3. Install

  • 4. Register with Claude

  • 5. Run with Docker

• Configuration

• Tools

• Design notes

• Development

• Troubleshooting

Related MCP server: Google Sheet MCP Server

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 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.

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:

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

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

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

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:

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:

claude mcp list
claude mcp get google-sheets

Equivalent, using the module entry point:

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:

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

{
  "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.

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.

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:

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

Day-to-day:

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

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

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

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

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

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

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