mcp-google-sheets CLAUDE.md

CLAUDE.md — mcp-google-sheets

MCP server for the Google Sheets API v4 (TypeScript, stdio). Mixed read/write: tools cover spreadsheet search/metadata, range reads and (batch) writes, sheet management, formatting/dimensions/freeze/borders, data validation, protected ranges, conditional formats, tables, charts and Drive-based sharing; raw_request is the escape hatch. The server talks to https://sheets.googleapis.com with a Bearer token; the token is minted from an OAuth2 refresh token via https://oauth2.googleapis.com/token (or a static GOOGLE_SHEETS_ACCESS_TOKEN, mostly for testing). The Drive API v3 (https://www.googleapis.com) is an internal dependency only — spreadsheet search and permissions have no Sheets equivalent — and is never reachable from raw_request.

Commands

npm run dev        # run from source (tsx watch)
npm test           # unit tests + dist smoke, no network
npm run typecheck  # types for src + tests
npm run build      # emit dist/
npm run smoke      # live check: read-only by default; GOOGLE_SHEETS_SMOKE_WRITE=1
                   # runs the disposable create→write→verify→trash cycle

Architecture

• src/config.ts — env → config. Credentials: either the refresh triple GOOGLE_SHEETS_CLIENT_ID + GOOGLE_SHEETS_CLIENT_SECRET + GOOGLE_SHEETS_REFRESH_TOKEN (all three or ConfigError incomplete_oauth_config) or GOOGLE_SHEETS_ACCESS_TOKEN; optional GOOGLE_SHEETS_API_BASE, GOOGLE_SHEETS_TIMEOUT_MS, GOOGLE_SHEETS_MAX_RETRIES. No credentials at all is NOT an error: the fields stay undefined and the server starts degraded. Also home to CredentialsError / MISSING_CREDENTIALS_MESSAGE (opens with the historical startup error verbatim, then names the variables and the restart) and hasCredentials().
• src/client.ts — all HTTP and all wire mapping. Token lifecycle (cache until ~60s before expiry, dedupe concurrent refreshes, one forced re-mint + replay on 401); httpRequest() resolves the path against its base and rejects foreign origins (SSRF guard — request() is pinned to the Sheets origin, the private driveRequest() to the Drive origin), enforces an AbortController timeout that also covers reading the body, retries 429 always but 5xx/network errors only for GET — replaying a write after an ambiguous failure would duplicate it — and throws GoogleSheetsError(status, body). Typed per-endpoint methods build the batchUpdate requests and computed fields masks; hexToColor(), buildCellFormat(), buildChartSpec(), buildConditionalRule() map the normalized vocabulary (hex colors, snake_case enums, simplified chart params) to the wire shapes.
• src/tools/spreadsheets.ts — create_spreadsheet, get_spreadsheet, search_spreadsheets (Drive-backed). src/tools/values.ts — read_values, write_values, batch_write_values, append_values, clear_values. src/tools/sheets.ts — manage_sheets (add/duplicate/rename/delete/copy_to). src/tools/format.ts — format_cells, set_frozen, set_borders, manage_dimensions (resize/auto_resize/insert/delete/hide/show/group/ungroup). src/tools/rules.ts — set_data_validation, manage_protected_ranges, manage_conditional_formats. src/tools/objects.ts — manage_tables, manage_charts. src/tools/sharing.ts — manage_permissions (Drive-backed). src/tools/raw.ts — raw_request (GET/POST/PUT, Sheets origin only). src/tools/util.ts — ok/fail, the four annotation presets (READ_ONLY/WRITE/UPDATE/DESTRUCTIVE) and shared zod schema factories (spreadsheetIdSchema, a1RangeSchema, sheetIdSchema, gridRangeSchema + toGridRange, hexColorSchema, valuesSchema).
• src/index.ts — wires every register* into the McpServer. loadConfigOrDegraded() catches ConfigError, pings startup_failed (fire-and-forget) and degrades the config to "no credentials"; an unconfigured start prepends UNCONFIGURED_PREFIX — plus Configuration problem: <message> when a ConfigError was caught — to the initialize instructions, and oninitialized sends server_start for a configured install or unconfigured_start (with the reason) otherwise.
• src/telemetry.ts — anonymous usage pings (ids/names/versions only, never data or arguments; fire-and-forget, must never block or throw; opt-out ASKADS_TELEMETRY=0). server_start means "a usable install started"; unconfigured_start is a degraded start and startup_failed a malformed config caught at load — both carry a reason from a closed vocabulary (missing_credentials, incomplete_oauth_config) — never a variable's name or value.
• src/smoke.ts — live smoke. Read-only by default (metadata fetch or token mint); GOOGLE_SHEETS_SMOKE_WRITE=1 opts into create → write → read-back-verify → trash on a disposable spreadsheet, with cleanup in finally (success and failure alike).