Google Sheets MCP Server Setup: OAuth and Config

1. Architectural Overview & Official SDK Reference

Connecting an LLM client (such as Claude Desktop, Cursor, or an internal agent orchestrator) to Google Sheets via the Model Context Protocol (MCP) frequently fails in production for two reasons: developers either grant overly broad full-Drive OAuth permissions (https://www.googleapis.com/auth/drive) that expose every corporate document to prompt-injection exfiltration, or they hardcode raw JSON private keys inside client config files where newline escaping breaks JWT signing.

Rather than relying on unverified third-party npm wrappers, this guide demonstrates a self-contained, auditable setup using:

• Official Protocol & SDKs: Official MCP Python SDK (mcp >= 1.2.0) using FastMCP over stdio, paired with Google’s official google-api-python-client (>= 2.150.0) and google-auth (>= 2.35.0).
• Authentication Mechanism: A dedicated Google Cloud Service Account with zero project-wide IAM roles, granted explicit per-sheet viewer (or editor) access only on an allowlisted set of spreadsheetId values.

2. Prerequisites & Least-Privilege Google Cloud Setup

Before configuring your MCP client, verify the following runtime and Google Cloud Console prerequisites:

1. Local Runtime: Python 3.11+ with uv (uv >= 0.5.0) installed, plus Node.js 20.18+ LTS if you plan to run the interactive MCP Inspector CLI (npx @modelcontextprotocol/inspector).
2. Enable Only the Google Sheets API: In the Google Cloud Console, enable sheets.googleapis.com (Google Sheets API v4). Do not enable drive.googleapis.com unless you specifically require filename search across Drive folders; keeping Drive disabled ensures the credential can only access spreadsheets addressed by their explicit 44-character spreadsheetId.
3. Create a Zero-Role Service Account: Under IAM & Admin → Service Accounts, create a service account (for example, mcp-sheets-reader@your-project.iam.gserviceaccount.com). Skip granting any GCP project IAM roles (it does not need Project Viewer or Editor). Download the JSON key file to a restricted local path outside any Git repository:

   mkdir -p ~/.config/mcp-secrets
   mv ~/Downloads/your-project-sa-key.json ~/.config/mcp-secrets/gcp-sheets-sa.json
   chmod 600 ~/.config/mcp-secrets/gcp-sheets-sa.json

4. Share the Target Spreadsheet Explicitly: Open your target Google Sheet in the browser, click Share, and add mcp-sheets-reader@your-project.iam.gserviceaccount.com as a Viewer (for read-only telemetry) or Editor (only if write tools are required).

3. Least-Privilege Google Sheets API v4 Scope Matrix

4. Reproducible Read-Only MCP Server (sheets_mcp_server.py)

Save the following 55-line script as /opt/mcp-sheets/sheets_mcp_server.py. It enforces both the read-only scope (spreadsheets.readonly) and an optional comma-separated ALLOWED_SPREADSHEET_IDS allowlist so the agent cannot query unauthorized sheet IDs:

# /// script
# requires-python = ">=3.11"
# dependencies = [
#   "mcp>=1.2.0",
#   "google-api-python-client>=2.150.0",
#   "google-auth>=2.35.0",
# ]
# ///
import os
from google.oauth2 import service_account
from googleapiclient.discovery import build
from mcp.server.fastmcp import FastMCP

SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
KEY_PATH = os.environ["GOOGLE_APPLICATION_CREDENTIALS"]
ALLOWED_IDS = {
    s.strip() for s in os.environ.get("ALLOWED_SPREADSHEET_IDS", "").split(",") if s.strip()
}

mcp = FastMCP("foxydata-google-sheets-readonly")

def _sheets_client():
    creds = service_account.Credentials.from_service_account_file(KEY_PATH, scopes=SCOPES)
    return build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets()

def _check_allowed(spreadsheet_id: str) -> None:
    if ALLOWED_IDS and spreadsheet_id not in ALLOWED_IDS:
        raise ValueError(f"Spreadsheet ID {spreadsheet_id} is not in ALLOWED_SPREADSHEET_IDS.")

@mcp.tool()
def list_sheet_tabs(spreadsheet_id: str) -> list[dict]:
    """Return tab titles and grid dimensions for an allowlisted Google Sheet."""
    _check_allowed(spreadsheet_id)
    meta = _sheets_client().get(
        spreadsheetId=spreadsheet_id,
        fields="sheets(properties(sheetId,title,gridProperties(rowCount,columnCount)))",
    ).execute()
    return [s["properties"] for s in meta.get("sheets", [])]

@mcp.tool()
def read_sheet_range(spreadsheet_id: str, a1_range: str = "Sheet1!A1:Z100") -> list[list[str]]:
    """Read a 2D array of formatted cell values from an A1 notation range."""
    _check_allowed(spreadsheet_id)
    res = _sheets_client().values().get(
        spreadsheetId=spreadsheet_id,
        range=a1_range,
        valueRenderOption="FORMATTED_VALUE",
    ).execute()
    return res.get("values", [])

if __name__ == "__main__":
    mcp.run(transport="stdio")

5. Sanitized Client Configuration (claude_desktop_config.json)

Add the server entry to your MCP client configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS or ~/.config/Claude/claude_desktop_config.json on Linux). Notice that no private keys or tokens appear in the JSON config—only the file path in GOOGLE_APPLICATION_CREDENTIALS:

{
  "mcpServers": {
    "google-sheets-readonly": {
      "command": "uv",
      "args": [
        "run",
        "/opt/mcp-sheets/sheets_mcp_server.py"
      ],
      "env": {
        "GOOGLE_APPLICATION_CREDENTIALS": "/Users/your_username/.config/mcp-secrets/gcp-sheets-sa.json",
        "ALLOWED_SPREADSHEET_IDS": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
      }
    }
  }
}

6. Step-by-Step Verification & Troubleshooting

Step 1: Verify Handshake & Tools in MCP Inspector

Before restarting your desktop client, test the server directly from your terminal using the official MCP Inspector:

GOOGLE_APPLICATION_CREDENTIALS="$HOME/.config/mcp-secrets/gcp-sheets-sa.json" \
ALLOWED_SPREADSHEET_IDS="1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms" \
npx @modelcontextprotocol/inspector uv run /opt/mcp-sheets/sheets_mcp_server.py

In the Inspector web UI (default http://127.0.0.1:6274), click Connect, open the Tools tab, and invoke list_sheet_tabs with your spreadsheet_id. A valid response returns an array of sheet tab objects (sheetId, title, gridProperties) in under 400 ms.

Step 2: Common Error Codes & Fixes