Mcp Google Sheets

What is Mcp Google Sheets about?

Enables AI agents to securely interact with Google Sheets and Google Drive using well‑typed tool calls. The server abstracts the Google Sheets and Drive APIs behind a set of high‑level operations that agents can invoke via standard MCP JSON messages.

How to use Mcp Google Sheets?

1. Prerequisites – Install Bun (v1.0+), create a Google Cloud project, and enable the Sheets and Drive APIs. Obtain either an OAuth2 client configuration (credentials.json + token.json) or a Service‑Account key (service_account.json).
2. Clone & install

   git clone https://github.com/rohans2/mcp-google-sheets.git
   cd mcp-google-sheets
   bun install
   

3. Configure environment – Set CREDENTIALS_CONFIG with a base64‑encoded service‑account JSON or place the OAuth2 files next to index.ts. Optionally define EMAIL_ID and DRIVE_FOLDER_ID.
4. Run the server

   bun index.ts
   

   The server starts a Stdio transport allowing an AI model (e.g., Claude) to call tools via stdin/stdout.

Key features of Mcp Google Sheets

• Dual authentication: Service‑Account (base64 env var) and OAuth2 flow.
• Comprehensive toolset:
  • Create, list, rename, copy, and delete spreadsheets/sheets.
  • Read and write cell ranges, batch updates, add rows/columns.
  • Share spreadsheets with specific permissions and notifications.
  • Retrieve spreadsheet metadata and list spreadsheets in a Drive folder.
• Typed TypeScript definitions for all tool inputs and outputs.
• Built on Bun for fast startup and minimal overhead.
• Stdio transport enables seamless integration with any MCP‑compatible AI model.

Use cases of Mcp Google Sheets

• Automating report generation or data aggregation by having an agent populate Google Sheets directly.
• Building AI assistants that can query, update, and share spreadsheets on behalf of users.
• Integrating spreadsheet‑driven workflows into larger automation pipelines without exposing raw Google APIs.
• Enabling collaborative AI agents that need persistent, shared state stored in Google Sheets.

FAQ from the Mcp Google Sheets

Q: Do I need a Google Cloud project? A: Yes, a project with the Sheets and Drive APIs enabled is required.

Q: Which authentication method should I choose? A: Use Service‑Account for server‑to‑server automation; use OAuth2 when actions must run under a specific user’s account.

Q: Can I run the server with Node instead of Bun? A: The codebase targets Bun, but it can be adapted to Node with minor changes to the runtime scripts.

Q: How does the agent send requests? A: By sending a JSON object containing the tool name and args via the server’s stdin; the server returns a JSON‑wrapped response.

Q: Where are the credentials stored? A: Service‑Account credentials are provided via the CREDENTIALS_CONFIG env var; OAuth2 credentials are read from credentials.json and the generated token.json.

MCP-Google-Sheets

A TypeScript Model Context Protocol (MCP) server that lets AI agents securely interact with Google Sheets via well-typed “tools.” Built on Bun and the @modelcontextprotocol/sdk, it supports both OAuth2 and Service-Account flows.

---

🚀 Features

• Authentication
  • Service-Account via base64-encoded CREDENTIALS_CONFIG
  • OAuth2 using credentials.json + token.json for user-scoped access
• Tools
  • create
    Creates a new spreadsheet (and moves it into your Drive folder if configured).
  • listSheets
    Lists all sheet tabs in a given spreadsheet.
  • renameSheet
    Renames an existing sheet tab.
  • createSheet
    Adds a new sheet tab to a spreadsheet.
  • spreadsheetInfo
    Fetches metadata (title, sheet IDs, grid properties) for a spreadsheet.
  • listSpreadsheets
    Lists all spreadsheets in your configured Drive folder (or My Drive).
  • shareSpreadsheet
    Shares a spreadsheet with users (reader/commenter/writer) and sends notifications.
  • sheetData
    Reads cell values from a sheet and range (or whole sheet).
  • updateCells
    Writes a 2D array of values into an A1-style range.
  • batchUpdate
    Applies multiple range updates in a single request.
  • addRows / addColumns
    Inserts rows or columns at a specified index.
  • copySheet
    Copies a sheet tab between spreadsheets (optionally renaming it).

---

📋 Prerequisites

• Bun (v1.0+) installed and on your PATH
• A Google Cloud project with:
  • Sheets API & Drive API enabled
  • An OAuth2 Client ID (download credentials.json) or a Service Account key (download service_account.json)
• (Optional) A Drive folder ID if you want new sheets moved out of My Drive

---

⚙️ Setup

1. Clone the repo

   git clone https://github.com/yourusername/mcp-google-sheets.git
   cd mcp-google-sheets
   

2. Install dependencies

bun install

3. Configure environment

Create a .env (or export) with:

# Base64-encoded service-account key JSON (optional)
CREDENTIALS_CONFIG=BASE-64 ENCODED SERVICE_ACCOUNT.JSON


# Or put your OAuth2 JSON files next to index.ts:
#   credentials.json  (OAuth client secret)
#   token.json        (generated after first OAuth run)

# The google email address that you'll use to access the spreadsheet
EMAIL_ID="Enter the email address you’ll use to access the spreadsheet"
# (Optional) ID of the Drive folder to store new sheets
DRIVE_FOLDER_ID=1a2B3c4D5e6F...

Tip: On Linux/macOS you can do

export CREDENTIALS_CONFIG=$(base64 service_account.json | tr -d '\n')

▶️ Running the Server

bun index.ts

On first OAuth2 run (if using credentials.json), you’ll see a URL. Visit it, grant access, then paste the code back into your terminal. A token.json will be generated automatically.

🔧 How It Works

Initialization

• initContext() picks your auth method (Service-Account → OAuth2 → error).

• Builds google.sheets & google.drive clients and stores them in a shared context.

MCP Tool Registration

• Each “tool” (e.g. create, listSheets, sheetData) is registered via server.tool(...).

Transport

• Uses StdioServerTransport so Claude can invoke tools over stdin/stdout.

Invocation

The agent sends a JSON request:

{ "tool": "create", "args": { "title": "Budget Q2" } }

The server runs your handler, calls Google APIs, and returns JSON-wrapped results.

🛠️ Try It Out

Clone & configure as above.

Start the server:

bun index.ts
Invoke a tool via Claude

Demo

❤️ Contributing

Feel free to open issues or PRs for new tools, bug fixes, and enhancements.