Google Sheets MCP for Claude.ai

Connect Claude.ai to your Google Sheets via a custom MCP (Model Context Protocol) server hosted on Vercel. Once set up, Claude can read, append, and update your spreadsheet directly from the chat.

What you get

• read_sheet — read any range from your sheet

• append_row — add a new row

• update_cell — update a specific cell

Related MCP server: Google Sheets MCP Server

Prerequisites

• A Vercel account

• A Google Cloud account

• A Google Sheet you want Claude to access

• Node.js 18+ installed locally

• Git

Step 1 — Clone and install

git clone <your-repo-url>
cd custom_mcp
npm install

Step 2 — Create a Google Cloud OAuth app

1. Go to Google Cloud Console

2. Create a new project (or use existing)

3. Go to APIs & Services → Enable APIs → enable Google Sheets API

4. Go to APIs & Services → Credentials → Create Credentials → OAuth 2.0 Client ID

5. Application type: Web application

6. Name it (e.g. Claude MCP)

7. Under Authorized redirect URIs add:

   https://YOUR-VERCEL-APP.vercel.app/oauth/google/callback

   Replace YOUR-VERCEL-APP with your actual Vercel app name.

8. Click Save — copy the Client ID and Client Secret

⚠️ Make sure the redirect URI matches your Vercel deployment URL exactly — no trailing slash, must be https.

Step 3 — Configure OAuth consent screen

1. Go to APIs & Services → OAuth consent screen

2. User type: External

3. Fill in app name, support email

4. Add scope: https://www.googleapis.com/auth/spreadsheets

5. Under Audience → Test users add your Gmail address

6. Click Publish app → Confirm

⚠️ You must publish the app (even unverified) or Google will block the login. When you see the "unverified app" warning during login, click Advanced → Go to app.

Step 4 — Deploy to Vercel

Option A — Via Vercel CLI

npm install -g vercel
vercel login
vercel --prod

Option B — Via GitHub

Push to GitHub → import project in Vercel dashboard → it auto-deploys.

Step 5 — Set environment variables in Vercel

Go to Vercel Dashboard → Your Project → Settings → Environment Variables and add:

JWT secret is a private random string only your server knows — it's used to sign and verify tokens so Claude can't be impersonated. It must be at least 32 characters, completely random, and never shared or committed to Git. Generate one with:

bashnode -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

This outputs 64 random hex characters like a3f8c2... — copy that output directly as your JWT_SECRET value in Vercel. Never use a human-readable phrase like "mysecret123" — it's trivially guessable.|

Generate a secure JWT secret:

node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

Get your Spreadsheet ID from the sheet URL:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID_IS_HERE/edit

⚠️ After adding env vars, you must redeploy for them to take effect:

vercel --prod

Step 6 — Verify deployment

Hit your health endpoint:

https://YOUR-VERCEL-APP.vercel.app/health

Should return:

{ "status": "ok", "message": "Google Sheets MCP OAuth Server" }

Step 7 — Connect to Claude.ai

1. Go to claude.ai → Settings → Connectors

2. Click Add connector

3. Enter:

   • Name: Google Sheets MCP

   • URL: https://YOUR-VERCEL-APP.vercel.app

4. Click Connect

5. Google login screen appears → sign in with the account you whitelisted

6. Authorize the Sheets scope

7. Done — Claude now has access to your sheet ✅

Usage examples

Once connected, just ask Claude naturally:

Read the data in Sheet1!A1:D10

Append a row with ["John", "Doe", "john@example.com"] to Sheet1!A:Z

Update cell Sheet1!B3 to "Completed"

Project structure

├── api/
│   └── oauth.cjs        ← Main server (Vercel entry point)
├── public/
│   └── index.html       ← Dashboard UI
├── vercel.json          ← Vercel routing config
└── package.json

How it works

Claude.ai → POST / (tools/list)     → Your Vercel server → returns tool definitions
Claude.ai → GET /oauth/authorize    → Redirects to Google login
Google    → GET /oauth/google/callback → Issues JWT token back to Claude
Claude.ai → POST / (tools/call) + JWT → Your server → Google Sheets API → data

Troubleshooting

500 FUNCTION_INVOCATION_FAILED Your package.json likely has "type": "module" — remove it. The server uses CommonJS (require).

"This connector has no tools available" The tools/list method is behind auth middleware. Make sure it's handled in the unauthenticated first handler.

Error 400: redirect_uri_mismatch

• Check GOOGLE_REDIRECT_URI in Vercel matches exactly what's registered in Google Console

• Make sure you're using the OAuth Client ID, not a service account ID

• Publish your OAuth app in Google Console (Testing mode blocks logins)

"Authorization with the MCP server failed" Disconnect and reconnect the Claude connector to force a fresh token. Cached tokens from failed attempts won't work.

POST returning 404 All JSON-RPC errors must return HTTP 200, not 404. Check that unknown methods return res.status(200).json(...).

Security notes

• Change MCP_API_KEY from any placeholder value before sharing the deployment

• JWT_SECRET should be at least 32 random characters

• The Spreadsheet ID and OAuth credentials are tied to your Vercel deployment — don't commit .env files

• For production use, replace in-memory pkceStore/tokenStore Maps with a database (Redis, Upstash, etc.) — they reset on cold starts

License

MIT