mcp-google-sheets

# 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** ```bash git clone https://github.com/yourusername/mcp-google-sheets.git cd mcp-google-sheets 2. **Install dependencies** ```bash bun install ``` 3. **Configure environment** Create a .env (or export) with: ```bash # 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 ```bash export CREDENTIALS_CONFIG=$(base64 service_account.json | tr -d '\n') ``` ## ▶️ Running the Server ```bash 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: ```json { "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: ```bash bun index.ts Invoke a tool via Claude ``` ## Demo <img width="1274" alt="mcp-google-sheets" src="https://github.com/user-attachments/assets/979d4fec-f5f1-42ff-bdc6-765b992f98a9" /> <img width="1274" alt="mcp-google-sheets" src="https://github.com/user-attachments/assets/7285ad5b-96c9-4960-9290-7be1ba9728f0" /> ## ❤️ Contributing Feel free to open issues or PRs for new tools, bug fixes, and enhancements.