# google-sheets-mcp

MCP server to read and edit Google Sheets. Built for editorial calendars,
task trackers and any agency-style spreadsheet workflow.

## Tools (50)

### Reading

| Tool | What it does |
|---|---|
| `describe_sheet` | Structural X-ray of a tab: header row, first data row, first free row, allowed dropdown values per column, merged cells, protected ranges and conditional-format rules. **Call this before writing to any tab you don't already know** |
| `get_spreadsheet_info` | Title, tabs and dimensions of the spreadsheet |
| `get_sheet_data` | All data of a tab as an array of rows |
| `read_range` | Read a specific A1 range |
| `read_multiple_ranges` | Read several ranges in a single API call — more efficient than calling `read_range` repeatedly |
| `get_hyperlinks` | Extract hyperlink URLs from cells in a range — useful when a cell displays text that links elsewhere |

### Writing

| Tool | What it does |
|---|---|
| `write_range` | Write data into a range (overwrites) |
| `update_cell` | Update a single cell |
| `batch_update_cells` | Update multiple individual cells in a single API call |
| `append_rows` | Append rows at the end of a tab — the safe way to add data, never overwrites |
| `find_replace` | Find and replace text across the whole spreadsheet |
| `clear_range` | Clear a range |

### Rows and columns

| Tool | What it does |
|---|---|
| `insert_rows` | Insert N blank rows starting at a given row, pushing existing content down |
| `delete_rows` | Delete a range of rows |
| `move_rows` | Move rows to another position keeping their values, format, dropdowns and notes — a real move, not a copy+delete |
| `insert_columns` | Insert blank columns, pushing the rest to the right |
| `delete_columns` | Delete columns |
| `sort_range` | Sort a range by a column (ascending or descending) |
| `auto_resize_columns` | Auto-fit column widths to content |

### Tabs and spreadsheets

| Tool | What it does |
|---|---|
| `add_sheet` | Create a new tab |
| `rename_sheet` | Rename a tab |
| `copy_sheet` | Duplicate a tab within the same spreadsheet |
| `delete_sheet` | Delete an entire tab |
| `create_spreadsheet` | Create a new empty spreadsheet |
| `duplicate_spreadsheet` | Clone an entire spreadsheet (all tabs and data) |

### Task-sheet blocks (months, sections)

| Tool | What it does |
|---|---|
| `insert_block` | Create a new block (e.g. a month section) copying the look of existing ones — separator row, repeated header and blank rows ready to fill |

### Data validation and conditional formatting

| Tool | What it does |
|---|---|
| `set_data_validation` | Create a dropdown on a range: the cell only accepts the given values |
| `add_conditional_format` | Auto-color cells that match a condition (e.g. green for DONE) |
| `delete_conditional_format` | Remove a conditional-format rule by index |

### Borders, merging, banding

| Tool | What it does |
|---|---|
| `set_borders` | Add borders (outer and/or inner) to a range |
| `merge_cells` | Merge cells, e.g. for a block title spanning several columns |
| `unmerge_cells` | Undo merged cells in a range |
| `add_banding` | Apply alternating row colors ("zebra stripes") |

### Data cleanup

| Tool | What it does |
|---|---|
| `delete_duplicates` | Remove duplicate rows from a range, keeping the first occurrence |
| `trim_whitespace` | Strip leading/trailing/extra whitespace from cells — a stray space breaks exact-match dropdowns and formulas |
| `text_to_columns` | Split a text column into several by a separator |
| `auto_fill` | Continue a series automatically (dates, numbers, formulas), like dragging the corner handle |

### Organization

| Tool | What it does |
|---|---|
| `group_rows` | Group rows so they can be collapsed/expanded with the +/- in the margin |
| `ungroup_rows` | Undo a row grouping |
| `set_rows_visibility` | Hide or show rows |
| `set_column_width` | Fix the width of one or more columns, in pixels |
| `freeze_rows` | Freeze the top N rows |
| `set_filter` | Turn on the header filter (the funnel icons for sort/filter) |
| `clear_filter` | Remove the filter from a tab |

### Protection, ranges, cell operations

| Tool | What it does |
|---|---|
| `protect_range` | Protect a range from accidental edits |
| `unprotect_range` | Remove a protection |
| `add_named_range` | Name a range so it can be referenced in formulas without breaking when rows move |
| `copy_range` | Copy a range to another location, choosing what to copy (values, format, or both) |
| `cut_paste_range` | Move a range of cells (cut from source, paste into destination, with formatting) |

## Semantic colors for `format_range`

| Name | Use |
|---|---|
| `hecho` | Light green — task done |
| `in_progress` | Light yellow — in progress |
| `por_hacer` | Light grey — pending |
| `alta` | Light red — high priority |
| `media` | Light yellow — medium priority |
| `baja` | Light blue — low priority |
| `header` | Steel blue — table header |
| `white`, `red`, `green`, `blue`, `yellow`, `orange`, `purple`, `grey` | Basic colors |
| `#RRGGBB` | Direct hex |

## Safety

- Every tool carries MCP Tool Annotations from the spec (`readOnlyHint`, `destructiveHint`,
  `idempotentHint`, `openWorldHint`) so a client can tell read-only calls from destructive ones
  without guessing from the name.
- Execution errors propagate as real MCP protocol errors (`isError=true`) — never as a JSON
  payload that looks like a success.
- Risky writes carry a `confirmed: bool = False` parameter. Without it, the tool returns a preview
  of what it would do (and why it's flagged) instead of executing: `write_range`, `update_cell`,
  `append_rows`, `clear_range`, `delete_rows`, `insert_block`, `move_rows`, `delete_duplicates`,
  `cut_paste_range`, `delete_sheet`, `delete_columns`. What's considered risky: overwriting cells
  that already have content, touching frozen header rows, merged or protected cells, or writing a
  value outside a column's dropdown (which silently breaks its conditional-format color). Filling
  empty cells and appending to the end run without friction — they can't destroy anything.

These sheets are treated as **visual documents, not databases**: headers aren't necessarily in row
1, cells can be merged, and color often comes from conditional formatting that triggers on exact
values. Call `describe_sheet` before writing to a tab you don't already know its structure — it
tells you where the header and first free row are, and which values each dropdown column accepts.

## Setup

1. Create a Google Cloud project (or reuse one) and enable the
   **Google Sheets API**, **Google Docs API** and **Google Drive API**
   (the token scopes cover all three, so you can reuse it with the sibling
   `google-docs-mcp` server if you want).
2. Create an OAuth 2.0 Client ID of type "Desktop app" and download it as
   `client_secret.json`.
3. If the app is in "Testing" mode, add your Google account as a test user.
4. Install dependencies:
   ```bash
   python3 -m venv .venv
   source .venv/bin/activate
   pip install -r requirements.txt
   ```
5. Run the OAuth flow once:
   ```bash
   CLIENT_SECRET_PATH=~/.config/google-sheets-mcp/client_secret.json \
     python3 setup_auth.py
   ```
   This opens a browser — log in and grant access.

## MCP client configuration

```json
{
  "mcpServers": {
    "google-sheets": {
      "command": "/path/to/.venv/bin/python3",
      "args": ["/path/to/google-sheets-mcp/server.py"],
      "env": {
        "GOOGLE_WORKSPACE_TOKEN_PATH": "~/.config/google-workspace-mcp/token.json"
      }
    }
  }
}
```

| Env var | Default | Purpose |
|---|---|---|
| `GOOGLE_WORKSPACE_TOKEN_PATH` | `~/.config/google-workspace-mcp/token.json` | Path to the OAuth token |

## Suggested task-sheet structure

Standard columns (row 1 = header):
`OWNER | START | END | TASK | TOPIC | STATUS | PRIORITY | DETAILS`

Suggested status values: `TODO`, `IN PROGRESS`, `DONE`
Suggested priorities: `HIGH`, `MEDIUM`, `LOW`

## License

MIT — see [LICENSE](LICENSE).