Sheetcraft

Google Sheets MCP server + CLI for AI agents — 34 tools across 6 opt-in toolsets. One shared engine, two surfaces:

• MCP server — for chat agents (Claude Desktop, Claude Code, OpenCode, any MCP client). Tools appear in the agent's tool list and are permission-gateable.
• CLI (sheets) — for scripts, pipes, and bulk work. Same 34 operations, same auth, same errors, one process per command.

Designed for agent reliability: teaching errors (a bad range returns an example + the list of available sheets, not a stack trace), token-efficient reads (CSV defaults, hard cell caps, explicit truncation notices), and write echoes (updatedRange/updatedCells on every write so the agent closes its own loop without a verification read).

---

Requirements

• Node ≥ 20
• A Google account, and either an OAuth Client ID (recommended — see Option A) or a service account key (see Option B)
• Works on Linux, macOS, and Windows (all paths resolve under your home directory)

Install

# Run directly with npx — no install step
npx sheetcraft-mcp@latest --help

# or install globally — then use the `sheets` and `sheets-mcp` commands
npm install -g sheetcraft-mcp

Three commands are installed:

Command	What it is
sheets	The CLI
sheets-mcp	The MCP server (stdio)
sheetcraft-mcp	Both — with arguments it runs the CLI, with none it starts the MCP server. This is what MCP configs use (npx sheetcraft-mcp@latest).

Quick start

1. Add the MCP config (OpenCode example; Claude Desktop and others in MCP configuration):

// opencode.json
{
  "mcp": {
    "sheets": {
      "type": "local",
      "command": ["npx", "-y", "sheetcraft-mcp@latest"],
      "enabled": true
    }
  }
}

2. Authenticate once (in a terminal — see Authentication for details):

npx sheetcraft-mcp@latest auth login

A browser opens → you consent → done. Tokens persist and auto-refresh; the MCP config itself contains no secrets.

3. Use it. In agents with terminal access (OpenCode, Claude Code), the agent can do this whole setup itself: the first Sheets tool call without credentials returns a teaching message that says exactly what to run. Tools appear as sheets_get_values, sheets_update_values, etc.

---

Authentication

Two modes. Pick one.

	Option A: OAuth	Option B: Service account
Acts as	You (your Google account)	A robot account
Can create/copy spreadsheets	✅ Yes	❌ No (Google gives SAs a 0-byte storage quota)
Setup	Browser consent, once	Env vars + manual sharing of each file
Recommended for	Personal use, agents that create files	CI, servers, headless setups

The two-file mental model

OAuth confuses everyone exactly once — when they see two JSON files. Here is the whole thing:

client_secret_xxx.json   your APP's identity with Google  → used ONCE by `auth login`
oauth-tokens.json        YOUR logged-in session           → created automatically, auto-refreshes

The client_secret_*.json you download from Google is not a credential for the MCP config or env — it's the key fob that lets the tool open a login flow. It's consumed once by auth login; after that the saved token does all the work and you can even delete the JSON.

Option A: OAuth — act as yourself (recommended)

1. In Google Cloud Console:

• Enable Google Sheets API and Google Drive API
• Create Credentials → OAuth client ID → Desktop app → download the JSON

2. Hand the file to auth login — any one of these three ways:

# (a) zero file moves — point at the download directly:
npx sheetcraft-mcp@latest auth login --client ~/Downloads/client_secret_xxx.json

# (b) or park it in the config dir once, then plain login forever after:
mkdir -p ~/.config/sheetcraft-mcp
cp ~/Downloads/client_secret_xxx.json ~/.config/sheetcraft-mcp/oauth-client.json
npx sheetcraft-mcp@latest auth login

# (c) or skip the file — set the values it contains as env vars:
#     GOOGLE_OAUTH_CLIENT_ID=…xxx.apps.googleusercontent.com
#     GOOGLE_OAUTH_CLIENT_SECRET=…
npx sheetcraft-mcp@latest auth login

3. Browser opens → consent → done. Verify:

npx sheetcraft-mcp@latest auth status

Tokens persist at ~/.config/sheetcraft-mcp/oauth-tokens.json (permissions 0600) and auto-refresh on every use.

Useful auth commands:

npx sheetcraft-mcp@latest auth status   # which mode is active, which email
npx sheetcraft-mcp@latest auth logout   # delete stored tokens

Switching accounts: run auth logout first — auth login refuses to overwrite an existing session otherwise (pass --force to override).

Option B: Service account — edit-only on shared files

1. In Cloud Console: create a service account, create a JSON key for it, enable the Sheets API.
2. Point the server at the key:

export GOOGLE_SERVICE_ACCOUNT_CREDENTIALS="$(cat service-account.json)"
# or: export GOOGLE_SERVICE_ACCOUNT_FILE=/path/to/key.json

3. Share spreadsheets with the SA's email like any collaborator. It can read and edit but cannot create files — use OAuth for that.

Which mode wins

If both are configured: SHEETS_AUTH_MODE=oauth|service-account forces a mode; otherwise service-account env credentials win, then stored OAuth tokens.

Consent-screen gotchas

Your OAuth app stays in "Testing" mode until Google verifies it. Expect these two screens:

What you see	What it means	Fix
"Access blocked … Error 403: access_denied"	The Google account you're logging in with is not a test user of your own app	Cloud Console → APIs & Services → OAuth consent screen → Audience / Test users → Add users → add that account. Takes effect immediately.
"Google hasn't verified this app" warning	Normal for your own unverified app	Advanced → Go to <app name> (unsafe) → Allow

If you'd rather not touch the consent screen at all, use a service account.

---

MCP configuration

OpenCode

// opencode.json
{
  "mcp": {
    "sheets": {
      "type": "local",
      "command": ["npx", "-y", "sheetcraft-mcp@latest"],
      "environment": {
        "SHEETS_TOOLSETS": "core,drive"
      },
      "enabled": true
    }
  }
}

Gate destructive tools in agent config so the agent asks before acting:

{ "tools": { "sheets_delete_sheet": "ask", "sheets_batch_update": "ask", "sheets_trash_spreadsheet": "ask" } }

Claude Desktop / any standard MCP client

{
  "mcpServers": {
    "sheets": {
      "command": "npx",
      "args": ["-y", "sheetcraft-mcp@latest"],
      "env": { "SHEETS_TOOLSETS": "core,drive" }
    }
  }
}

Notes for all clients:

• OAuth login done via the CLI is picked up automatically (same token store) — configure the server first, log in after, no restart dance needed.
• The tool prefix comes from your config key: "sheets" → sheets_get_values, sheets_update_values, …
• Credentials are never put in this config. The server reads them from ~/.config/sheetcraft-mcp/ (OAuth) or env vars (service account).

---

Toolsets

34 tools ship in 6 opt-in groups. Select with the SHEETS_TOOLSETS environment variable — default is core,drive.

Toolset	Tools	Register when the agent needs to…
core (default)	15	Read/write values, manage sheets/tabs, find & replace
drive (default)	7	Create/copy/search/share/trash/export spreadsheets
formatting	5	Style cells, merge, freeze, conditional formatting
charts	3	Create/edit/delete embedded charts
pivot	2	Build pivot tables (see limitations)
power	2	Raw batchUpdate escape hatch + range sorting
all	34	Everything

SHEETS_TOOLSETS=core                     # 15 tools  (~4K tokens of schema)
SHEETS_TOOLSETS=core,drive               # 22 tools  (default)
SHEETS_TOOLSETS=core,drive,formatting,charts
SHEETS_TOOLSETS=all                      # 34 tools  (~10K tokens of schema)

Unknown names hard-fail at startup. Keep the list small — every tool's schema costs standing tokens in every conversation.

---

Tool catalog

Every data operation takes a spreadsheetId (bare ID, or paste a full URL — it's parsed), and sheet parameters accept a quoted name ('My Sheet') or gid:N. Every write echoes updatedRange/updatedCells. Every read is capped (default 5,000 cells) with an explicit truncation notice.

core — values and sheets (15)

Tool	What it does	Key parameters / defaults
get_values	Read a range. Returns CSV by default (most token-efficient)	format: csv · tsv · grid (2D JSON array) · records (header-joined objects)
batch_get_values	Read multiple ranges in one call	ranges — each result labeled with its A1
update_values	Write a 2D array to a range	input: USER_ENTERED (default — strings starting with = become formulas, dates parse) or RAW
batch_update_values	Write multiple ranges in one API call (one quota unit)	prefer over several update_values calls
append_rows	Append rows below the existing table (auto-detected)	default INSERT_ROWS — never overwrites rows below the table
clear_values	Clear values, keep formatting	destructive
get_spreadsheet_info	List tabs with titles, gids, dimensions, frozen state	call this first when all you have is a URL
add_sheet / delete_sheet / duplicate_sheet / rename_sheet	Tab management	delete is destructive; add_sheet accepts freezeRows/freezeCols
move_rows_columns	Move rows/columns to a new position in place — data, formatting, formulas move intact	the right way to reorder columns; never delete-and-recreate
insert_delete_dimensions	Insert/delete rows or columns	indices are 0-based (0 = first row/column)
find_replace	Find & replace across a sheet, range, or whole spreadsheet	replacement is required (omitting it would erase matches)
set_data_validation	Dropdowns (ONE_OF_LIST/ONE_OF_RANGE), checkboxes, number/text constraints; clearValidation=true removes rules	dropdown options via literal list or a range on an existing sheet

drive — file lifecycle (7)

Tool	What it does	Notes
create_spreadsheet	New empty spreadsheet	owned by whoever is logged in; in SA mode pass shareWith or it's invisible to humans
copy_spreadsheet	Copy data, formulas, formatting (not sharing)
find_spreadsheets	Search by name → candidate IDs, never auto-selected	sees files the authenticated identity can access
share_spreadsheet	Grant an account access	in SA mode, required after create_spreadsheet
trash_spreadsheet	Move to Drive trash (restorable)	permanent=true bypasses trash — cannot be undone
export_spreadsheet	Download whole file as xlsx / pdf / ods	per-sheet CSV: use get_values instead
resolve_target	Parse a URL → spreadsheetId + gid	also accepts bare IDs

formatting (5)

Tool	What it does	Notes
format_cells	Bold/italic/strikethrough, font size/color, background, number format, alignment, wrapping	only provided properties change; colors are #RRGGBB
merge_cells	Merge a range (ALL / COLUMNS / ROWS)	unmerge=true to undo
freeze_rows_columns	Sticky headers	pass 0 to unfreeze
conditional_format	Add/delete highlight rules	use get_formatting to find rule indices
get_formatting	Read formatting as run-length-encoded ranges + merges + rules	far cheaper than reading the full grid

charts (3)

Tool	What it does	Notes
create_chart	Embedded chart: COLUMN, BAR, LINE, AREA, SCATTER, COMBO, STEPPED_AREA	from a domain range + series ranges; returns chartId
update_chart	Change type, ranges, title, legend, stacking	patch semantics — fetch, merge, send
delete_chart	Delete by id

pivot (2) ⚠️

Tool	What it does	Notes
create_pivot	Grouped summaries (SUM/COUNT/AVERAGE/…) by row/column fields	Google's API silently drops pivot writes (verified; see limitations). The tool verifies persistence and warns honestly when the write didn't take
delete_pivot	Clear the cell holding the definition

power (2)

Tool	What it does	Notes
batch_update	Raw spreadsheets.batchUpdate — pass the API's requests array verbatim	covers banding, named ranges, protection, data validation, tables, slicers, and more; atomic (one bad request aborts the batch)
sort_range	Sort rows by one or more columns	the entire range sorts in place — exclude headers from the range

---

The CLI

The CLI runs the same 34 operations as subcommands — for scripting, piping, and bulk work. Same auth, same errors; any failure prints the same teaching message the MCP tools return and exits 1.

# discovery
npx sheetcraft-mcp@latest list                  # all 33 ops by toolset
npx sheetcraft-mcp@latest help get_values       # one op's full JSON schema

# operations — args as key=value pairs
npx sheetcraft-mcp@latest resolve_target url="https://docs.google.com/spreadsheets/d/…/edit"
npx sheetcraft-mcp@latest get_values spreadsheetId=<id> range="'My Sheet'!A1:D10"
npx sheetcraft-mcp@latest get_values spreadsheetId=<id> range="'My Sheet'!A1:D10" format=records

# structured output and stdin JSON (for 2D arrays with quotes/formulas)
npx sheetcraft-mcp@latest get_values spreadsheetId=<id> range=A:D --json | jq .
npx sheetcraft-mcp@latest append_rows spreadsheetId=<id> sheet=Data --stdin-json < rows.json

Flag	Purpose
--json	Force JSON output (reads print compact text by default)
--stdin-json	Read the whole args object as JSON from stdin — the safe way to pass rows containing quotes, apostrophes, newlines, or formulas

Auth: sheets auth login [--client <path>], sheets auth status, sheets auth logout. Every op is also available as sheets <op> … when installed globally.

Reads compose with standard tooling; writes are better done through MCP tools (the agent UI handles quoting and permission gating for you).

Agent skill

The npm package bundles a skill that teaches agents the CLI conventions (quoting, stdin JSON, exit codes, pitfalls) without trial and error:

# Claude Code:
ln -s "$(npm root -g)/sheetcraft-mcp/skills/managing-google-sheets" \
      ~/.claude/skills/managing-google-sheets

# OpenCode (`skill`, singular):
cp -r "$(npm root -g)/sheetcraft-mcp/skills/managing-google-sheets" \
      ~/.config/opencode/skill/managing-google-sheets

---

Environment variables

Variable	Purpose	Default
SHEETS_TOOLSETS	Which toolsets register: core, drive, formatting, charts, pivot, power, all, or a comma list	core,drive
GOOGLE_SERVICE_ACCOUNT_CREDENTIALS	Service-account key JSON (raw)	—
GOOGLE_SERVICE_ACCOUNT_FILE	Service-account key JSON (path)	—
SHEETS_AUTH_MODE	Force oauth or service-account	auto-resolved
GOOGLE_OAUTH_CLIENT_ID / GOOGLE_OAUTH_CLIENT_SECRET	OAuth client values (alternative to the client JSON file)	—
SHEETS_OAUTH_CLIENT_FILE	Custom path to the OAuth client JSON	~/.config/sheetcraft-mcp/oauth-client.json
GOOGLE_OAUTH_TOKEN_FILE	Custom token-store path	~/.config/sheetcraft-mcp/oauth-tokens.json
SHEETS_MAX_CELLS	Read cap per call	5000
SHEETS_RETRY_ATTEMPTS / SHEETS_RETRY_BASE_MS / SHEETS_RETRY_MAX_MS	Retry tuning for 429/5xx/network errors (exponential backoff + jitter)	3 / 300 / 8000

Troubleshooting

Symptom	Cause / fix
Tool call returns "No credentials configured"	Nothing is set up yet. Run npx sheetcraft-mcp@latest auth login, or set service-account env vars. The message lists both options verbatim
auth login → browser → "Access blocked / 403 access_denied"	Your account isn't a test user of your own OAuth app → add it under OAuth consent screen → Test users (see consent-screen gotchas)
"Already logged in as …" when switching accounts	Run auth logout first, or pass --force
Sheet "Data 2" not found. Sheets: "Sheet1"(gid:0), …	Teaching error — quote names with spaces: 'My Sheet'!A1:B2
Write landed in the wrong place	append_rows inserts below the table (never overwrites); update_values writes exactly the range you name — check updatedRange in the echo
find_replace erased text	replacement was empty/omitted. It's required for this reason
Sorting scrambled headers	sort_range sorts the whole range — exclude the header row from the range
Agent says a parameter "doesn't exist" or recalls a tool failing	Its tool list/schema may be stale (server updated mid-session). Re-check with help <op> / a fresh tools list — and never work around tools by reading the token store; that's out of bounds

Known limitations

• Pivot tables via the API are unreliable — Google's API accepts the write but silently drops the definition (verified 2026-08-28, re-verified 2026-08-29 against Google's own documented request shape). create_pivot verifies persistence and warns when the write didn't take. For summaries, prefer get_values + update_values.
• Cells in Google Tables (typed columns) reject data validation — if a tab is a Google Table (column chips with types), set_data_validation on its columns fails with "This operation is not allowed on cells in typed columns" (Google blocks it in the UI too). Use the table's column-chip menu in the UI instead, or convert the table to a normal range first.
• Service accounts cannot create files — Google policy (0-byte storage quota). Use OAuth for create_spreadsheet / copy_spreadsheet.
• Drive CSV export covers only the first sheet — use get_values for per-sheet CSV.
• update_values writes in ROWS orientation — arrays are row-major.

Design notes for agent reliability

• ID-first — every op takes spreadsheetId (full URLs parsed); sheet params accept 'Name' or gid:N.
• Errors teach — bad ranges quote an example and list available sheets; rate-limit errors say that retries already happened.
• Reads are capped — SHEETS_MAX_CELLS with an explicit notice telling the model how to get the rest.
• Batching is encouraged — batch_update_values / batch_update = one API call per quota unit.
• Cell contents are data, not instructions — sheet content is never echoed into error messages or prompts.
• Retry + backoff built in — 429/5xx/network errors retry with exponential backoff + jitter before surfacing.

License

MIT — see LICENSE.