google-sheets-mcp

# gsheets-mcp [](https://gsheets-mcp.trombella.org/) [](./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. | --- ## 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.