# gsheets-mcp

[![Website](https://img.shields.io/badge/Website-gsheets--mcp.trombella.org-0F9D58.svg)](https://gsheets-mcp.trombella.org/)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](./LICENSE)

A **local MCP (Model Context Protocol) server** that lets Claude read and write your
Google Sheets through the Google Sheets API v4.

It runs entirely on your own machine. You authenticate with your own Google account
using OAuth2 (the "installed app" / Desktop flow), and your data never passes through
any third-party server.

**Free and open source under the MIT License.** No telemetry, no third-party servers.

🌐 **Website:** <https://gsheets-mcp.trombella.org/>

## What you get

| Tool | What it does |
|---|---|
| `list_spreadsheets` | List your Google Sheets on Drive (optionally filtered by name). |
| `get_sheet_info` | Metadata for a spreadsheet: title, locale, and its tabs (names, IDs, size). |
| `read_range` | Read values from a range (e.g. `Foglio1!A1:D10`). |
| `update_range` | Write/overwrite values into a range. |
| `append_rows` | Append rows to the end of a table. |

Most tools take a **spreadsheet ID** — the long string in a sheet's URL:
`https://docs.google.com/spreadsheets/d/`**`<THIS_IS_THE_ID>`**`/edit`.
You can also discover IDs with `list_spreadsheets` instead of copying them by hand.

---

## Prerequisites

- **Node.js 18+** (`node --version`).
- A Google account.

---

## Part 1 — Set up Google Cloud (one time)

You need an OAuth "Desktop app" client so the server can ask for your permission to
access your sheets.

### 1. Create a Google Cloud project

1. Go to <https://console.cloud.google.com/>.
2. Top bar → project dropdown → **New Project**. Give it a name (e.g. `gsheets-mcp`)
   and create it. Make sure it's selected.

### 2. Enable the APIs

1. Go to **APIs & Services → Library** (<https://console.cloud.google.com/apis/library>).
2. Search for **Google Sheets API**, open it, click **Enable**.
3. Search for **Google Drive API**, open it, click **Enable**.

> The Drive API is used **only** by `list_spreadsheets` to enumerate your sheets, via
> the read-only `drive.readonly` scope. It is not used to modify, move or delete files.

### 3. Configure the OAuth consent screen

1. Go to **APIs & Services → OAuth consent screen**.
2. **User type: External** → **Create**. (Internal is only available on Google Workspace
   organizations.)
3. Fill in the required fields: App name (e.g. `gsheets-mcp`), your email as
   **User support email** and **Developer contact**. You can leave the rest blank. **Save
   and continue**.
4. **Scopes**: you can skip adding scopes here (the app requests them at login). **Save
   and continue**.
5. **Test users**: click **Add users** and add **your own Google email**. This is
   required — in "Testing" mode only listed test users can authorize the app. **Save and
   continue**.
6. Leave the app in **Testing** mode. That's fine for personal use and never expires for
   your own test-user account. (Publishing to "Production" would trigger Google's app
   verification, which you don't need here.)

### 4. Create the OAuth client credentials

1. Go to **APIs & Services → Credentials**.
2. **Create Credentials → OAuth client ID**.
3. **Application type: Desktop app**. Name it (e.g. `gsheets-mcp desktop`). **Create**.
4. In the confirmation dialog, click **Download JSON**. This file contains your
   `client_id` and `client_secret`.

### 5. Place the credentials file

Save the downloaded file as **`credentials.json`** in the config directory:

```bash
mkdir -p ~/.config/gsheets-mcp
mv ~/Downloads/client_secret_*.json ~/.config/gsheets-mcp/credentials.json
```

> Keep this file private — it's ignored by git. You can override its location with the
> `GSHEETS_MCP_CREDENTIALS` environment variable (see `.env.example`).

---

## Part 2 — Install and build

From the project folder:

```bash
npm install
npm run build
```

---

## Part 3 — Log in (one time)

Run the interactive login. It opens your browser on Google's consent screen; approve
access, and the token is saved to `~/.config/gsheets-mcp/token.json` (refreshed
automatically from then on).

```bash
npm run login
# equivalently: node dist/index.js login
```

Because the app is in **Testing** mode, Google shows a
**"Google hasn't verified this app"** warning. This is expected for your own app —
click **Advanced → Go to gsheets-mcp (unsafe)** and continue. Then grant the two
requested permissions (see below).

When you see `✅ Authorization complete` in the terminal, you're done.

> **Scopes requested:**
> - `https://www.googleapis.com/auth/spreadsheets` — read/write your spreadsheets.
> - `https://www.googleapis.com/auth/drive.readonly` — read-only, used **only** by
>   `list_spreadsheets` to enumerate your sheets. It cannot modify or delete files.
>
> To revoke access at any time, visit <https://myaccount.google.com/permissions>.
>
> **Note:** if you upgrade the server and the requested scopes change, you must run
> `npm run login` again — a previously granted consent doesn't cover new scopes. The
> same applies per machine (each computer stores its own token).

---

## Part 4 — Add the server to Claude Desktop

Open Claude Desktop's config file:

- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`

Add a `gsheets` entry under `mcpServers`, pointing at the compiled entrypoint. Use the
**absolute path** to `dist/index.js` in this project:

```json
{
  "mcpServers": {
    "gsheets": {
      "command": "node",
      "args": ["/absolute/path/to/google-sheets-mcp/dist/index.js"]
    }
  }
}
```

Save the file and **fully quit and reopen Claude Desktop**. You should now see the
`gsheets` tools available. Try asking Claude something like:

> "List my Google Sheets, then read `A1:C5` from the one named 'Budget'."

### Using it with Claude Code

```bash
claude mcp add gsheets -- node /absolute/path/to/google-sheets-mcp/dist/index.js
```

---

## Usage examples (what to ask Claude)

- **List:** "List my Google Sheets" / "Find my spreadsheets whose name contains 'budget'."
- **Info:** "What tabs does spreadsheet `<ID>` have?" (returns the exact tab names to use).
- **Read:** "Read range `Foglio1!A1:D10` from spreadsheet `<ID>`."
- **Update:** "Put the values `[[\"Name\",\"Score\"],[\"Ada\",42]]` starting at `Foglio1!A1`
  in spreadsheet `<ID>`."
- **Append:** "Append the row `[\"Grace\", 99]` to `Foglio1` in spreadsheet `<ID>`."

### ⚠️ Note: tab names are localized

Ranges use the **tab (sheet) name**, e.g. `Sheet1!A1:D10`. But the default tab name
depends on your Google account's language: it's `Sheet1` in English, **`Foglio1`** in
Italian, `Hoja1` in Spanish, `Feuille1` in French, and so on. Using the wrong name
returns `Unable to parse range: …`.

If you're unsure of the real tab name, open the sheet and read the tab label at the
bottom, or just ask Claude to read the whole sheet by passing **only the tab name** as
the range (e.g. `Foglio1`). A dedicated `get_sheet_info` tool that lists the exact tab
names is on the roadmap.

## Configuration reference

All optional; defaults work out of the box. See `.env.example`.

| Variable | Default | Purpose |
|---|---|---|
| `GSHEETS_MCP_CONFIG_DIR` | `~/.config/gsheets-mcp` | Where `credentials.json` / `token.json` live. |
| `GSHEETS_MCP_CREDENTIALS` | `<config dir>/credentials.json` | Path to the OAuth client file. |
| `GSHEETS_MCP_TOKEN` | `<config dir>/token.json` | Path to the saved token. |

For **headless / HTTP mode** (see below) you can instead provide credentials via env:

| Variable | Purpose |
|---|---|
| `GOOGLE_CLIENT_ID` / `GOOGLE_CLIENT_SECRET` | OAuth client, instead of `credentials.json`. |
| `GOOGLE_REFRESH_TOKEN` | Refresh token, instead of `token.json` (no browser login needed). |
| `MCP_AUTH_TOKEN` | **Required** in HTTP mode. Bearer token clients must send. |
| `PORT` | HTTP port (default `8000`). |

---

## Remote / mobile use (advanced)

The default transport is **stdio** (local). The server can also run as a **remote MCP
connector** over HTTP so you can reach it from clients that can't launch a local process
— e.g. the Claude mobile app:

```bash
MCP_AUTH_TOKEN=$(openssl rand -hex 32) npm run serve:http   # listens on :8000/mcp
```

Every request must send `Authorization: Bearer <MCP_AUTH_TOKEN>`. Because this endpoint
can write your spreadsheets, always put it behind a network gate (Cloudflare Access, VPN)
in addition to the bearer token — never expose it raw on the internet.

A ready-made **Home Assistant OS add-on** for a personal always-on setup (behind a
Cloudflare Tunnel) lives in [`ha-addon/gsheets-mcp/`](./ha-addon/gsheets-mcp/) — see its
[DOCS.md](./ha-addon/gsheets-mcp/DOCS.md) for the full step-by-step.

---

## Troubleshooting

- **"Not authenticated. Run the one-time login first"** — you haven't logged in yet, or
  the token file is missing. Run `npm run login`.
- **"OAuth client credentials not found"** — `credentials.json` isn't where the server
  expects it. Check Part 1, step 5.
- **`403 access_denied` in the browser** — your Google account isn't listed as a test
  user. Add it under OAuth consent screen → Test users (Part 1, step 3.5).
- **"Request had insufficient authentication scopes"** — your saved token predates a
  scope change (e.g. `list_spreadsheets` needs `drive.readonly`). Run `npm run login`
  again to re-consent.
- **`Unable to parse range: …`** — the tab name is wrong. Tab names are localized
  (`Foglio1` in Italian, `Sheet1` in English). Use `get_sheet_info` to see the exact names.
- **No `refresh_token` warning** — revoke the app at
  <https://myaccount.google.com/permissions> and run `npm run login` again.
- **Tools don't appear in Claude Desktop** — confirm the path in
  `claude_desktop_config.json` is absolute and points to `dist/index.js`, that you ran
  `npm run build`, and that you fully restarted Claude Desktop.

---

## Development

```bash
npm run build       # compile to dist/
npm run watch       # recompile on change
npm run typecheck   # type-check without emitting
```

Source layout: `src/index.ts` (entrypoint), `src/auth.ts` (OAuth), `src/sheetsClient.ts`
and `src/driveClient.ts` (API wrappers), `src/tools/*` (one file per MCP tool).

---

## License

Released under the [MIT License](./LICENSE). You're free to use, modify and distribute
it. If it saves you time, you can support development with a coffee — see the
[website](https://gsheets-mcp.trombella.org/) for the link. ☕

> Not affiliated with or endorsed by Google. "Google Sheets" is a trademark of Google LLC.