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

---

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.|

Variable	Value
GOOGLE_CLIENT_ID	From Google Cloud Console OAuth client
GOOGLE_CLIENT_SECRET	From Google Cloud Console OAuth client
GOOGLE_REDIRECT_URI	https://YOUR-VERCEL-APP.vercel.app/oauth/google/callback
JWT_SECRET	A long random string (generate below)
SPREADSHEET_ID	Your Google Sheet ID (from the URL)

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