Skip to main content
Glama
prafulstha98

google-sheets-mcp

by prafulstha98
OverviewSchemaSourceRelated ServersDiscussionsAdmin
JavaScript
Remote

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

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

Available Tools

3 tools
append_rowB

Append a row to a Google Sheet

ParametersJSON Schema
NameRequiredDescriptionDefault
rangeYesSheet range (e.g., "Sheet1!A:Z")
valuesYesArray of values for the row

TDQS

B3.2/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations provided, so description must cover behavioral traits. It does not disclose error conditions, permissions required, idempotency, or what happens if the sheet is empty. Only states the operation without depth.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Extremely concise single sentence with no wasted words. Appropriate for a simple tool with minimal parameters.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given no output schema and no annotations, the description is insufficient. It lacks information about return values, error handling, and operational behavior, making it incomplete for an agent to invoke confidently.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 100%, so baseline 3. The description adds no extra meaning beyond the schema's parameter descriptions. 'range' and 'values' are already defined in the input schema.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description 'Append a row to a Google Sheet' uses a specific verb (append) and resource (row to Google Sheet), clearly distinguishing from siblings 'read_sheet' and 'update_cell'.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

No guidance on when to use this tool versus alternatives like 'update_cell' for modifying existing rows or 'read_sheet' for reading. The description lacks any context for decision-making.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

read_sheetC

Read data from a Google Sheet

ParametersJSON Schema
NameRequiredDescriptionDefault
rangeYesSheet range (e.g., "Sheet1!A1:B10")

TDQS

C2.9/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations present, the description bears the full burden of behavioral disclosure. It only states 'Read data from a Google Sheet', which is a basic functional description. It does not mention safety, idempotency, rate limits, error behavior, or any other behavioral traits. For a read operation, minimal disclosure is acceptable, but it still falls short of providing useful behavioral context.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, concise sentence that front-loads the purpose. It is efficient with no wasted words. However, it is somewhat minimal and could benefit from including sibling differentiation or a hint about the output format without losing conciseness.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the simplicity of the tool (one parameter, no output schema), the description adequately explains what the tool does but lacks completeness in describing the output. It does not mention what the returned data looks like (e.g., an array of rows/values), which is important since there is no output schema. The description also omits any usage context like typical use cases or prerequisites.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 100% with a single 'range' parameter that has a clear description and example ('Sheet1!A1:B10'). The tool description does not add any additional parameter information beyond what the schema provides. Baseline 3 is appropriate since the schema already does its job.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the verb 'Read' and the resource 'Google Sheet', effectively conveying the tool's purpose. It distinguishes from sibling tools 'append_row' and 'update_cell' which are write operations. However, it could be more precise by explicitly mentioning that it reads from a specified range, as indicated by the required 'range' parameter.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

No explicit guidance is provided on when to use this tool versus alternatives. While the name and description imply it's for reading, and siblings handle writing, the description lacks direct statements of when/when-not to use it, nor does it mention exclusions or prerequisites. The agent must infer usage from context.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

update_cellB

Update a specific cell in a Google Sheet

ParametersJSON Schema
NameRequiredDescriptionDefault
rangeYesCell range (e.g., "Sheet1!A1")
valueYesValue to set

TDQS

B3.2/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations, the description carries the full burden of behavioral disclosure. It merely states 'Update' without explaining consequences like overwriting, idempotency, error handling, or required permissions. This is insufficient for a mutation tool.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, efficient sentence that conveys the core functionality without wasted words. It is appropriately concise for a simple tool.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool's simplicity, the description adequately explains the basic operation. However, the absence of behavioral transparency (e.g., confirmation of overwrite) and no output schema leaves some gaps for an agent to confidently use the tool.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema already provides 100% coverage with descriptions for both parameters ('range' and 'value'). The description adds no additional semantic value beyond the schema, so baseline 3 is appropriate.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the action ('Update') and the resource ('a specific cell in a Google Sheet'), making the tool's purpose instantly understandable. It differentiates from sibling tools like 'append_row' and 'read_sheet' by focusing on updating a single cell.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

No guidance is provided on when to use this tool versus alternatives. The sibling tools are listed but not compared, leaving the agent to infer usage context without explicit direction.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 3 tool updatesv1.0.0
    • First observedappend_row
    • First observedread_sheet
    • First observedupdate_cell
Deploy Server
One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

TDQS

B3.4/5.0

Scored across 3 tools

Disambiguation5/5

Each tool targets a distinct operation: appending a row, reading a sheet, and updating a cell. There is no overlap in functionality, making it clear to an agent which tool to select.

Naming Consistency5/5

All tool names follow the consistent verb_noun pattern: append_row, read_sheet, update_cell. This makes the set predictable and easy to understand.

Tool Count4/5

With 3 tools, the set is slightly lean but still reasonable for basic Google Sheets operations. It covers create (append), read, and update, but lacks a delete operation, which is a minor shortfall.

Completeness3/5

The set covers core CRUD operations except delete, and lacks ability to create or delete sheets themselves. Agents may need additional functionality for full lifecycle management, indicating notable gaps.

Maintenance

ActivityStale
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers