gsheets-mcp

gsheets-mcp

A strict, agent-friendly MCP server for Google Sheets. Version 1.0.0 exposes wire contract 1.0 through one explicit CLI and direct structured results.

Tool contract

| Tool | Inputs | Broker | Local | |---|---|---:|---:| | gsheets_list_tabs | spreadsheet | yes | yes | | gsheets_read_range | spreadsheet, range, optional render options | yes | yes | | gsheets_write_range | spreadsheet, range, values | yes | yes | | gsheets_append_rows | spreadsheet, range, values | yes | yes | | gsheets_create_spreadsheet | title | yes | yes | | gsheets_copy_spreadsheet | source spreadsheet, destination title, optional tabs | yes | yes | | gsheets_search_spreadsheets | query, optional limit | no | yes | | gsheets_clear_range | spreadsheet, range | yes | yes | | gsheets_recalculate_range | spreadsheet, range | yes | yes |

spreadsheet accepts only a spreadsheet ID or a strict https://docs.google.com/spreadsheets/... URL. Common tools never treat a title as an identifier. In local mode, search by title first and pass the selected results[n].spreadsheet ID to the next tool. range is A1 notation, preferably including the tab (for example, Comps!A1:H20).

Inputs are closed-world, strictly typed JSON. Unknown fields—including plausible fields such as dry_run and idempotency_key—are rejected. values must be a non-empty two-dimensional JSON array, not a JSON-encoded string.

Results and failures

Successful calls return a direct object rather than JSON inside a string:

{
  "status": "ok",
  "operation": "gsheets_read_range",
  "spreadsheet": "1AbCdEf...",
  "range": "Comps!A1:B2",
  "value_render_option": "FORMATTED_VALUE",
  "date_time_render_option": "FORMATTED_STRING",
  "values": [["Ticker", "PCTY"], ["Revenue", 100]]
}

Operational and validation failures set MCP isError=true. The structured content carries a stable code plus explicit outcome and retry guidance:

{
  "status": "error",
  "operation": "gsheets_write_range",
  "error": {
    "code": "operation_outcome_uncertain",
    "message": "The operation may have changed external state, but confirmation was lost.",
    "outcome": {
      "state": "uncertain",
      "phase": "write_range",
      "mutation_may_have_occurred": true
    },
    "retry": {
      "safe": false,
      "automatic": false,
      "action": "inspect_before_retry",
      "retry_after_seconds": null
    },
    "validation": null,
    "recovery": null,
    "incident_id": null
  }
}

Never blindly repeat append, create, copy, write, or recalculate after an uncertain result. Copy failures can include destination progress. Recalculation accepts only formulas plus blank cells, restores the exact formula matrix, and verifies formulas; it does not claim that custom-function values are fresh.

Install and inspect

Prerequisites are Python 3.10+ and a Google Cloud OAuth desktop client with the Sheets and Drive APIs enabled.

python3 -m venv venv
source venv/bin/activate
python -m pip install -e .

gsheets-mcp --help
gsheets-mcp --version
gsheets-mcp capabilities --json

The bare command prints help and exits. Only gsheets-mcp serve starts the stdio protocol server, and stdout is reserved for MCP frames while it runs. Discovery and capabilities inspection do not load credentials.

Local OAuth mode

Local mode is the default. Save the OAuth desktop client JSON as drive_credentials.json in the repository root, or set GOOGLE_CREDENTIALS_FILE. The token cache defaults to token.pickle; override it with GOOGLE_TOKEN_FILE. Both files are ignored by Git.

Run interactive consent once from a trusted local terminal:

source venv/bin/activate
python -c 'from gsheets_mcp.sheets_client import authenticate; authenticate()'

Set GSHEETS_HEADLESS=1 in non-interactive environments so missing consent fails closed. A local MCP configuration uses the installed console script explicitly:

{
  "mcpServers": {
    "gsheets-mcp": {
      "type": "stdio",
      "command": "/absolute/path/to/gsheets-mcp/venv/bin/gsheets-mcp",
      "args": ["serve"]
    }
  }
}

Broker mode

Set GSHEETS_TOKEN_MODE=broker when a trusted token broker supplies per-user, in-memory credentials. Calls then require GSHEETS_BROKER_URL and GSHEETS_BROKER_SESSION_TOKEN. Broker discovery exposes exactly eight tools; Drive title search is intentionally absent. Do not put broker tokens in tracked configuration or command arguments.

GSHEETS_TOKEN_MODE=broker gsheets-mcp capabilities --json
GSHEETS_TOKEN_MODE=broker gsheets-mcp serve

An invalid mode fails closed. Broker credentials refresh proactively near expiry, and Google 401 handling is bounded to one exact-request credential refresh.

Development

source venv/bin/activate
python -m pip install -e '.[dev]'
python -m pytest -q
python scripts/mcp_lint.py

The lint command imports the live registry without credentials and verifies the mode-specific tool sets, strict schemas, annotations, versions, dependency pins, and absence of retired runtime paths.

License

MIT