Skip to content
Back to search

mcp-gsheets

MCP server for Google Sheets - Read, write and manipulate spreadsheets through Claude Desktop

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today
Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/freema--mcp-gsheets/badge.svg)](/listings/freema--mcp-gsheets)

HTML

<a href="/listings/freema--mcp-gsheets"><img src="/api/listings/freema--mcp-gsheets/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

Quick Install (Recommended)

Add the following config to your MCP client:

{
  "mcpServers": {
    "mcp-gsheets": {
      "command": "npx",
      "args": ["-y", "mcp-gsheets@latest"],
      "env": {
        "GOOGLE_PROJECT_ID": "your-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
      }
    }
  }
}

> [!NOTE] > Using `mcp-gsheets@latest` ensures that your MCP client will always use the latest version of the MCP Google Sheets server.

MCP Client Configuration

<details> <summary>Claude Code</summary> Use the Claude Code CLI to add the MCP Google Sheets server (<a href="https://docs.anthropic.com/en/docs/claude-code/mcp">guide</a>):

claude mcp add mcp-gsheets npx mcp-gsheets@latest

After adding, edit your Claude Code config to add the required environment variables:

{
  "mcpServers": {
    "mcp-gsheets": {
      "command": "npx",
      "args": ["mcp-gsheets@latest"],
      "env": {
        "GOOGLE_PROJECT_ID": "your-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
      }
    }
  }
}

</details>

<details> <summary>Claude Desktop</summary>

Add to your Claude Desktop config: - macOS: `~/Library/Application Support/Claude/claude_desktop_config.json` - Windows: `%APPDATA%\Claude\claude_desktop_config.json` - Linux: `~/.config/claude/claude_desktop_config.json`

{
  "mcpServers": {
    "mcp-gsheets": {
      "command": "npx",
      "args": ["-y", "mcp-gsheets@latest"],
      "env": {
        "GOOGLE_PROJECT_ID": "your-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
      }
    }
  }
}

</details>

<details> <summary>Cursor</summary>

Go to `Cursor Settings` → `MCP` → `New MCP Server`. Use the config provided above.

</details>

<details> <summary>Cline</summary>

Follow https://docs.cline.bot/mcp/configuring-mcp-servers and use the config provided above.

</details>

<details> <summary>Other MCP Clients</summary>

For other MCP clients, use the standard configuration format shown above. Ensure the `command` is set to `npx` and include the environment variables for Google Cloud authentication.

</details>

Google Cloud Setup

1. Go to [Google Cloud Console](https://console.cloud.google.com) 2. Create a new project or select existing 3. Enable Google Sheets API: - Navigate to "APIs & Services" → "Library" - Search for "Google Sheets API" and click "Enable" 4. Create Service Account: - Go to "APIs & Services" → "Credentials" - Click "Create Credentials" → "Service Account" - In the service accounts list, click the three dots in the `Actions` column → `Manage keys` → `Add key` → `Create new key` → select JSON format - Download the JSON key file 5. Share your spreadsheets: - Open your Google Sheet - Click Share and add the service account email (from JSON file) - Grant "Editor" permissions

Alternative Authentication Methods

Option 1: JSON String Authentication

Instead of using a file path for credentials, you can provide the service account credentials directly as a JSON string. This is useful for containerized environments, CI/CD pipelines, or when you want to avoid managing credential files.

{
  "mcpServers": {
    "mcp-gsheets": {
      "command": "npx",
      "args": ["-y", "mcp-gsheets@latest"],
      "env": {
        "GOOGLE_PROJECT_ID": "your-project-id",

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 676 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. <!-- mcp-name: io.github.freema/mcp-gsheets -->
  2. # MCP Google Sheets Server
  3. <a href="https://glama.ai/mcp/servers/@freema/mcp-gsheets">
  4. <img width="380" height="200" src="https://glama.ai/mcp/servers/@freema/mcp-gsheets/badge" />
  5. </a>
  6. [![npm version](https://badge.fury.io/js/mcp-gsheets.svg)](https://www.npmjs.com/package/mcp-gsheets)
  7. ![CI](https://github.com/freema/mcp-gsheets/workflows/CI/badge.svg)
  8. ![Coverage](https://codecov.io/gh/freema/mcp-gsheets/branch/main/graph/badge.svg)
  9. ![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)
  10. ![TypeScript](https://img.shields.io/badge/TypeScript-5.0%2B-007ACC?logo=typescript&logoColor=white)
  11. ![Node](https://img.shields.io/badge/Node.js-22%2B-339933?logo=node.js&logoColor=white)
  12. ![code style: prettier](https://img.shields.io/badge/code_style-prettier-ff69b4.svg?logo=prettier&logoColor=white)
  13. A Model Context Protocol (MCP) server for Google Sheets API integration. Enables reading, writing, and managing Google Sheets documents directly from your MCP client (e.g., Claude Code, Claude Desktop, Cursor, etc.).
  14. ## Key Features
  15. - **Complete Google Sheets Integration**: Read, write, and manage spreadsheets
  16. - **Advanced Operations**: Batch operations, formatting, charts, and conditional formatting
  17. - **Flexible Authentication**: Support for both file-based and JSON string credentials
  18. - **Production Ready**: Built with TypeScript, comprehensive error handling, and full test coverage
  19. ## Requirements
  20. - [Node.js](https://nodejs.org/) v22 or higher
  21. - [Google Cloud Project](https://console.cloud.google.com) with Sheets API enabled
  22. - Service Account with JSON key file
  23. - [npm](https://www.npmjs.com/)
  24. ## Getting Started
  25. ### Quick Install (Recommended)
  26. Add the following config to your MCP client:
  27. ```json
  28. {
  29. "mcpServers": {
  30. "mcp-gsheets": {
  31. "command": "npx",
  32. "args": ["-y", "mcp-gsheets@latest"],
  33. "env": {
  34. "GOOGLE_PROJECT_ID": "your-project-id",
  35. "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
  36. }
  37. }
  38. }
  39. }
  40. ```
  41. > [!NOTE]
  42. > Using `mcp-gsheets@latest` ensures that your MCP client will always use the latest version of the MCP Google Sheets server.
  43. ### MCP Client Configuration
  44. <details>
  45. <summary>Claude Code</summary>
  46. Use the Claude Code CLI to add the MCP Google Sheets server (<a href="https://docs.anthropic.com/en/docs/claude-code/mcp">guide</a>):
  47. ```bash
  48. claude mcp add mcp-gsheets npx mcp-gsheets@latest
  49. ```
  50. After adding, edit your Claude Code config to add the required environment variables:
  51. ```json
  52. {
  53. "mcpServers": {
  54. "mcp-gsheets": {
  55. "command": "npx",
  56. "args": ["mcp-gsheets@latest"],
  57. "env": {
  58. "GOOGLE_PROJECT_ID": "your-project-id",
  59. "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
  60. }
  61. }
  62. }
  63. }
  64. ```
  65. </details>
  66. <details>
  67. <summary>Claude Desktop</summary>
  68. Add to your Claude Desktop config:
  69. - macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`
  70. - Windows: `%APPDATA%\Claude\claude_desktop_config.json`
  71. - Linux: `~/.config/claude/claude_desktop_config.json`
  72. ```json
  73. {
  74. "mcpServers": {
  75. "mcp-gsheets": {
  76. "command": "npx",
  77. "args": ["-y", "mcp-gsheets@latest"],
  78. "env": {
  79. "GOOGLE_PROJECT_ID": "your-project-id",
  80. "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
  81. }
  82. }
  83. }
  84. }
  85. ```
  86. </details>
  87. <details>
  88. <summary>Cursor</summary>
  89. Go to `Cursor Settings` → `MCP` → `New MCP Server`. Use the config provided above.
  90. </details>
  91. <details>
  92. <summary>Cline</summary>
  93. Follow https://docs.cline.bot/mcp/configuring-mcp-servers and use the config provided above.
  94. </details>
  95. <details>
  96. <summary>Other MCP Clients</summary>
  97. For other MCP clients, use the standard configuration format shown above. Ensure the `command` is set to `npx` and include the environment variables for Google Cloud authentication.
  98. </details>
  99. ### Google Cloud Setup
  100. 1. Go to [Google Cloud Console](https://console.cloud.google.com)
  101. 2. Create a new project or select existing
  102. 3. Enable Google Sheets API:
  103. - Navigate to "APIs & Services" → "Library"
  104. - Search for "Google Sheets API" and click "Enable"
  105. 4. Create Service Account:
  106. - Go to "APIs & Services" → "Credentials"
  107. - Click "Create Credentials" → "Service Account"
  108. - In the service accounts list, click the three dots in the `Actions` column → `Manage keys` → `Add key` → `Create new key` → select JSON format
  109. - Download the JSON key file
  110. 5. Share your spreadsheets:
  111. - Open your Google Sheet
  112. - Click Share and add the service account email (from JSON file)
  113. - Grant "Editor" permissions
  114. ### Alternative Authentication Methods
  115. #### Option 1: JSON String Authentication
  116. Instead of using a file path for credentials, you can provide the service account credentials directly as a JSON string. This is useful for containerized environments, CI/CD pipelines, or when you want to avoid managing credential files.
  117. ```json
  118. {
  119. "mcpServers": {
  120. "mcp-gsheets": {
  121. "command": "npx",
  122. "args": ["-y", "mcp-gsheets@latest"],
  123. "env": {
  124. "GOOGLE_PROJECT_ID": "your-project-id",
  125. "GOOGLE_SERVICE_ACCOUNT_KEY": "{\"type\":\"service_account\",\"project_id\":\"your-project\",\"private_key_id\":\"...\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\n...\\n-----END PRIVATE KEY-----\\n\",\"client_email\":\"...@....iam.gserviceaccount.com\",\"client_id\":\"...\",\"auth_uri\":\"https://accounts.google.com/o/oauth2/auth\",\"token_uri\":\"https://oauth2.googleapis.com/token\",\"auth_provider_x509_cert_url\":\"https://www.googleapis.com/oauth2/v1/certs\",\"client_x509_cert_url\":\"...\"}"
  126. }
  127. }
  128. }
  129. }
  130. ```
  131. **Note**: When using `GOOGLE_SERVICE_ACCOUNT_KEY`:
  132. - The entire JSON must be on a single line
  133. - All quotes must be escaped with backslashes
  134. - Newlines in the private key must be represented as `\\n`
  135. - If the JSON includes a `project_id`, you can omit `GOOGLE_PROJECT_ID`
  136. #### Option 2: Private Key Authentication (Simplified)
  137. For the most user-friendly approach, you can provide just the private key and email directly. This is the simplest method and requires only two fields from your service account JSON:
  138. ```json
  139. {
  140. "mcpServers": {
  141. "mcp-gsheets": {
  142. "command": "npx",
  143. "args": ["-y", "mcp-gsheets@latest"],
  144. "env": {
  145. "GOOGLE_PRIVATE_KEY": "-----BEGIN PRIVATE KEY-----\\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCgR6bvMNOUHZ29\\n+YgbVHAXsT/s+L/jnXTCB193zikCzspSBSfxLu8VRDjkNq9WUoDxizTATzMFNvNf\\n...\\n-----END PRIVATE KEY-----\\n",
  146. "GOOGLE_CLIENT_EMAIL": "spreadsheet@your-project.iam.gserviceaccount.com"
  147. }
  148. }
  149. }
  150. }
  151. ```
  152. **Note**: When using `GOOGLE_PRIVATE_KEY`:
  153. - Newlines in the private key should be represented as `\\n`
  154. - The private key must include the `-----BEGIN PRIVATE KEY-----` and `-----END PRIVATE KEY-----` markers
  155. - The client email should be the service account email from your JSON file
  156. - `GOOGLE_PROJECT_ID` is optional when using this method
  157. ## Local Development Setup
  158. If you want to develop or contribute to this project, you can clone and build it locally:
  159. ```bash
  160. # Clone the repository
  161. git clone https://github.com/freema/mcp-gsheets.git
  162. cd mcp-gsheets
  163. # Install dependencies
  164. npm install
  165. # Build the project
  166. npm run build
  167. ```
  168. ### Interactive Setup Script
  169. Run the interactive setup script to configure your local MCP client:
  170. ```bash
  171. npm run setup
  172. ```
  173. This will:
  174. - Guide you through the configuration
  175. - Automatically detect your Node.js installation (including nvm)
  176. - Find your Claude Desktop config
  177. - Create the proper JSON configuration
  178. - Optionally create a .env file for development
  179. ### Manual Local Configuration
  180. If you prefer manual configuration with a local build, add to your MCP client config:
  181. ```json
  182. {
  183. "mcpServers": {
  184. "mcp-gsheets": {
  185. "command": "node",
  186. "args": ["/absolute/path/to/mcp-gsheets/dist/index.js"],
  187. "env": {
  188. "GOOGLE_PROJECT_ID": "your-project-id",
  189. "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account-key.json"
  190. }
  191. }
  192. }
  193. }
  194. ```
  195. ## 📦 Build & Development
  196. ### Development Commands
  197. ```bash
  198. # Development mode with hot reload
  199. npm run dev
  200. # Build for production
  201. npm run build
  202. # Type checking
  203. npm run typecheck
  204. # Clean build artifacts
  205. npm run clean
  206. # Run MCP inspector for debugging
  207. npm run inspector
  208. # Run MCP inspector in development mode
  209. npm run inspector:dev
  210. ```
  211. ### Task Runner (Alternative)
  212. If you have [Task](https://taskfile.dev) installed:
  213. ```bash
  214. # Install dependencies
  215. task install
  216. # Build the project
  217. task build
  218. # Run in development mode
  219. task dev
  220. # Run linter
  221. task lint
  222. # Format code
  223. task fmt
  224. # Run all checks
  225. task check
  226. ```
  227. ### Development Setup
  228. 1. Create `.env` file for testing:
  229. ```bash
  230. cp .env.example .env
  231. # Edit .env with your credentials:
  232. # GOOGLE_PROJECT_ID=your-project-id
  233. # GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json
  234. # TEST_SPREADSHEET_ID=your-test-spreadsheet-id
  235. ```
  236. 2. Run in development mode:
  237. ```bash
  238. npm run dev # Watch mode with auto-reload
  239. ```
  240. ## 🎚️ Reducing context cost with toolsets
  241. All 44 tools together cost about **9,900 tokens of context in every session**,
  242. before the model does anything. Most workflows need a fraction of that.
  243. `GSHEETS_TOOLSETS` limits which tools the server exposes:
  244. ```json
  245. {
  246. "mcpServers": {
  247. "gsheets": {
  248. "command": "npx",
  249. "args": ["mcp-gsheets"],
  250. "env": {
  251. "GOOGLE_PROJECT_ID": "your-project-id",
  252. "GOOGLE_APPLICATION_CREDENTIALS": "/path/to/key.json",
  253. "GSHEETS_TOOLSETS": "core,charts"
  254. }
  255. }
  256. }
  257. }
  258. ```
  259. | Toolset | Tools | What it covers |
  260. |---|---|---|
  261. | `core` | 11 | Read/write values, metadata, sheet structure, create spreadsheet |
  262. | `sheets` | 9 | Sheet lifecycle, rows and columns |
  263. | `formatting` | 15 | Colours, borders, merges, conditional rules, links, dates |
  264. | `charts` | 3 | Create, update, delete charts |
  265. | `tables` | 4 | Native tables |
  266. | `analysis` | 2 | Full-sheet snapshot, range comparison |
  267. Measured `tools/list` cost:
  268. | Configuration | Tools | ≈ Tokens |
  269. |---|---|---|
  270. | _unset_ (default, all toolsets) | 44 | 9,875 |
  271. | `GSHEETS_TOOLSETS=core` | 11 | 1,986 |
  272. | `GSHEETS_TOOLSETS=core,sheets` | 20 | 3,548 |
  273. | `GSHEETS_READ_ONLY=true` | 16 | 3,599 |
  274. | `GSHEETS_TOOLSETS=core` + read-only | 6 | 912 |
  275. Notes:
  276. - **The default is unchanged** — leave `GSHEETS_TOOLSETS` unset and you get
  277. every tool, exactly as before.
  278. - **`core` is always included.** `GSHEETS_TOOLSETS=charts` means "charts as
  279. well as core", not "charts only" — without core the server cannot read a cell.
  280. - **A typo is a startup error**, not a silently smaller tool list.
  281. - `GSHEETS_READ_ONLY=true` drops every writing tool and can be combined with
  282. `GSHEETS_TOOLSETS`. It is enforced when a tool is called, not just when the
  283. list is built, so a client cannot write by naming a hidden tool.
  284. All tools also carry MCP annotations (`readOnlyHint`, `destructiveHint`,
  285. `idempotentHint`), so clients can skip confirmation prompts on reads and warn
  286. before destructive operations.
  287. ## 📋 Available Tools
  288. ### Reading Data
  289. | Tool | Description | Key Parameters |
  290. |------|-------------|----------------|
  291. | `sheets_get_values` | Read cell values from a single range | `spreadsheetId`, `range` (A1 notation), `valueRenderOption` |
  292. | `sheets_batch_get_values` | Read cell values from multiple ranges in one request | `spreadsheetId`, `ranges` (array of A1 ranges) |
  293. | `sheets_get_metadata` | Get spreadsheet metadata: title, locale, sheets list with IDs, row/column counts | `spreadsheetId` |
  294. | `sheets_check_access` | Verify that the service account can access a spreadsheet | `spreadsheetId` |
  295. ### Writing Data
  296. | Tool | Description | Key Parameters |
  297. |------|-------------|----------------|
  298. | `sheets_update_values` | Write values to a single range (overwrites existing content) | `spreadsheetId`, `range`, `values` (2D array), `valueInputOption` |
  299. | `sheets_batch_update_values` | Write values to multiple ranges in one request | `spreadsheetId`, `data` (array of `{range, values}`), `valueInputOption` |
  300. | `sheets_append_values` | Append rows after the last row of an existing table. **Default `insertDataOption` is `OVERWRITE`** — set `INSERT_ROWS` to push existing rows down | `spreadsheetId`, `range`, `values`, `valueInputOption`, `insertDataOption` |
  301. | `sheets_clear_values` | Clear all values in a range (preserves formatting) | `spreadsheetId`, `range` |
  302. | `sheets_insert_rows` | Insert blank or pre-filled rows at a specific position | `spreadsheetId`, `range` (anchor), `rows`, `position` (BEFORE/AFTER), `values` |
  303. | `sheets_delete_columns` | Delete one or more columns using a full-column A1 range | `spreadsheetId`, `range` (e.g. `Sheet1!B:D`) |
  304. | `sheets_delete_rows` | Delete one or more rows using a full-row A1 range | `spreadsheetId`, `range` (e.g. `Sheet1!2:4`) |
  305. | `sheets_insert_link` | Insert a hyperlink formula into a cell | `spreadsheetId`, `range`, `url`, `label` |
  306. | `sheets_insert_date` | Insert a date/datetime value formatted correctly into a cell | `spreadsheetId`, `range`, `date`, `format` |
  307. ### Sheet Management
  308. | Tool | Description | Key Parameters |
  309. |------|-------------|----------------|
  310. | `sheets_create_spreadsheet` | Create a new Google Sheets file | `title`, `sheets` (optional initial sheet configs) |
  311. | `sheets_insert_sheet` | Add a new sheet tab to an existing spreadsheet | `spreadsheetId`, `title`, `index` |
  312. | `sheets_delete_sheet` | Remove a sheet tab by its numeric sheet ID | `spreadsheetId`, `sheetId` |
  313. | `sheets_duplicate_sheet` | Copy a sheet within the same spreadsheet | `spreadsheetId`, `sheetId`, `newSheetName`, `insertSheetIndex` |
  314. | `sheets_copy_to` | Copy a sheet to a different spreadsheet | `spreadsheetId`, `sheetId`, `destinationSpreadsheetId` |
  315. | `sheets_update_sheet_properties` | Rename a sheet, change tab colour, toggle grid lines, etc. | `spreadsheetId`, `sheetId`, `properties` |
  316. | `sheets_batch_delete_sheets` | Delete multiple sheet tabs in one request | `spreadsheetId`, `sheetIds` (array) |
  317. ### Cell Formatting
  318. | Tool | Description | Key Parameters |
  319. |------|-------------|----------------|
  320. | `sheets_format_cells` | Apply background colour, font style, alignment and number format to a range | `spreadsheetId`, `range`, `format` |
  321. | `sheets_batch_format_cells` | Apply different formats to multiple ranges in one request | `spreadsheetId`, `requests` (array of `{range, format}`) |
  322. | `sheets_update_borders` | Set or remove borders on a range (style, width, colour per side) | `spreadsheetId`, `range`, `borders` |
  323. | `sheets_merge_cells` | Merge a range of cells | `spreadsheetId`, `range`, `mergeType` (MERGE_ALL / MERGE_COLUMNS / MERGE_ROWS) |
  324. | `sheets_unmerge_cells` | Unmerge previously merged cells in a range | `spreadsheetId`, `range` |
  325. | `sheets_add_conditional_formatting` | Add a conditional formatting rule (gradient or boolean) to a range | `spreadsheetId`, `range`, `rule` |
  326. ### Native Tables
  327. | Tool | Description | Key Parameters |
  328. |------|-------------|----------------|
  329. | `sheets_add_table` | Create a native Google Sheets table with typed columns and optional dropdown values | `spreadsheetId`, `sheetName`, `range`, `name`, `columns` |
  330. | `sheets_update_table` | Update an existing native Google Sheets table by table ID using an explicit field mask | `spreadsheetId`, `tableId`, `fields`, `name`, `range`, `columns` |
  331. | `sheets_delete_table` | Delete a native Google Sheets table by table ID | `spreadsheetId`, `tableId` |
  332. | `sheets_get_tables` | Read native tables for a spreadsheet or a specific sheet | `spreadsheetId`, `sheetName` |
  333. ### Charts
  334. | Tool | Description | Key Parameters |
  335. |------|-------------|----------------|
  336. | `sheets_create_chart` | Create a bar, line, pie, column or other chart on a sheet | `spreadsheetId`, `sheetId`, `chartSpec`, `position` |
  337. | `sheets_update_chart` | Modify an existing chart's spec or position | `spreadsheetId`, `chartId`, `chartSpec`, `position` |
  338. | `sheets_delete_chart` | Remove a chart from a spreadsheet | `spreadsheetId`, `chartId` |
  339. ### Read / Snapshot Tools
  340. | Tool | Description | Key Parameters |
  341. |------|-------------|----------------|
  342. | `sheets_get_merged_cells` | Return all merged cell ranges for a sheet, with A1 notation and raw GridRange coordinates | `spreadsheetId`, `sheetName` |
  343. | `sheets_get_sheet_dimensions` | Return column widths, row heights, frozen column/row counts, and hidden flags for every column and row | `spreadsheetId`, `sheetName` |
  344. | `sheets_get_sheet_formatting` | Read raw cell formatting (background colour, font, borders, alignment, number format) for a range without returning cell values | `spreadsheetId`, `range` |
  345. | `sheets_get_conditional_formatting` | Read all conditional formatting rules and banded (alternating-colour) ranges defined on a sheet | `spreadsheetId`, `sheetName` |
  346. | `sheets_get_sheet_structure` | Lightweight structural metadata only — no per-cell data. Returns dimensions, frozen rows/cols, tab colour, column widths, row heights, hidden columns/rows, and all merges in A1 notation. Single fast API call | `spreadsheetId`, `sheetName` |
  347. | `sheets_get_formatting_compact` | Read cell formatting for a range and return it as compact A1Range→format pairs (run-length encoded). Identical adjacent cells are collapsed into rectangular ranges — reduces output by 90 %+ compared to per-cell data | `spreadsheetId`, `sheetName`, `range`, `useEffectiveFormat`, `fields` |
  348. | `sheets_get_full_sheet_snapshot` | Master one-shot tool — returns all structural and formatting metadata (merges, dimensions, conditional formatting, and optionally cell formatting) in a single API call. Supports `fields` filter and `compactMode` to limit response size | `spreadsheetId`, `sheetName`, `includeFormattingRange`, `fields`, `compactMode` |
  349. | `sheets_get_basic_filter` | Read the Basic Filter (AutoFilter) configuration for a sheet, including filtered range, sort specs, and per-column filter criteria (hidden values, conditions, colour filters) | `spreadsheetId`, `sheetName` |
  350. | `sheets_get_data_validation` | Read data validation rules (checkboxes, dropdowns, custom formulas) from a sheet or range. Returns compact run-length-encoded list of unique rules grouped by cell ranges | `spreadsheetId`, `sheetName`, `range` |
  351. ## 🔧 Code Quality
  352. ### Linting
  353. ```bash
  354. # Run ESLint
  355. npm run lint
  356. # Fix auto-fixable issues
  357. npm run lint:fix
  358. ```
  359. ### Formatting
  360. ```bash
  361. # Check formatting with Prettier
  362. npm run format:check
  363. # Format code
  364. npm run format
  365. ```
  366. ### Type Checking
  367. ```bash
  368. # Run TypeScript type checking
  369. npm run typecheck
  370. ```
  371. ## ❗ Troubleshooting
  372. ### Common Issues
  373. **"Authentication failed"**
  374. - If using file-based auth: Verify JSON key path is absolute and correct
  375. - If using JSON string auth: Ensure JSON is properly escaped and valid
  376. - If using private key auth: Check that the private key includes BEGIN/END markers and newlines are escaped as `\\n`
  377. - Verify GOOGLE_CLIENT_EMAIL is a valid service account email
  378. - Check GOOGLE_PROJECT_ID matches your project (or is included in JSON for full JSON auth)
  379. - Ensure Sheets API is enabled
  380. **"Permission denied"**
  381. - Share spreadsheet with service account email
  382. - Service account needs "Editor" role
  383. - Check email in JSON file (client_email field)
  384. **"Spreadsheet not found"**
  385. - Verify spreadsheet ID from URL
  386. - Format: `https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit`
  387. **MCP Connection Issues**
  388. - Ensure you're using the built version (`dist/index.js`)
  389. - Check that Node.js path is correct in Claude Desktop config
  390. - Look for errors in Claude Desktop logs
  391. - Use `npm run inspector` to debug
  392. ## 🔍 Finding IDs
  393. ### Spreadsheet ID
  394. From the URL:
  395. ```
  396. https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
  397. ↑ This is the spreadsheet ID
  398. ```
  399. ### Sheet ID
  400. Use `sheets_get_metadata` to list all sheets with their IDs.
  401. ## 📝 Tips
  402. 1. Always test with a copy of your data
  403. 2. Use batch operations for better performance
  404. 3. Set appropriate permissions (read-only vs edit)
  405. 4. Check rate limits for large operations
  406. 5. Use `sheets_check_access` to verify permissions before operations
  407. ## 📘 Tool Details
  408. ### sheets_get_sheet_structure
  409. Returns lightweight structural/dimensional metadata for a sheet without any per-cell data. Much faster and cheaper than `sheets_get_full_sheet_snapshot` when you only need layout information.
  410. **Parameters:**
  411. - `spreadsheetId` (required): The ID of the spreadsheet
  412. - `sheetName` (required): Name of the sheet (tab)
  413. **Returns:** `sheetName`, `sheetId`, `sheetIndex`, `tabColor`, `tabColorStyle`, `dimensions` (`rowCount`, `columnCount`), `frozen` (`rowCount`, `columnCount`), `columnWidths` (array of pixel sizes), `rowHeights` (array of pixel sizes), `hiddenColumns` (0-based indices), `hiddenRows` (0-based indices), `mergeCount`, `merges` (A1 notation array)
  414. ---
  415. ### sheets_get_formatting_compact
  416. Read cell formatting for a range and return it as compact A1Range → format pairs. Adjacent cells with identical formatting are collapsed into rectangular ranges (run-length encoded), reducing output by 90 %+ compared to per-cell data.
  417. **Parameters:**
  418. - `spreadsheetId` (required): The ID of the spreadsheet
  419. - `sheetName` (required): Name of the sheet (tab)
  420. - `range` (required): Range without sheet prefix, e.g. `"A1:Z85"`
  421. - `useEffectiveFormat` (optional): `false` (default) = userEnteredFormat (only explicit overrides, smaller output); `true` = effectiveFormat (all inherited defaults)
  422. - `fields` (optional): Array of format field names to include, e.g. `["backgroundColor", "textFormat", "borders"]`
  423. **Returns:** `{ range, formatType, rangeCount, data: { "A1:C3": { backgroundColor: {...} }, ... } }`
  424. **Supported fields:** `backgroundColor`, `backgroundColorStyle`, `textFormat`, `horizontalAlignment`, `verticalAlignment`, `wrapStrategy`, `textRotation`, `numberFormat`, `padding`, `borders`
  425. ---
  426. ### sheets_get_full_sheet_snapshot
  427. Master one-shot tool that returns all structural and formatting metadata in a single API call.
  428. **Parameters:**
  429. - `spreadsheetId` (required): The ID of the spreadsheet
  430. - `sheetName` (required): Name of the sheet (tab)
  431. - `includeFormattingRange` (optional): If provided (e.g. `"A1:Z100"`), per-cell formatting is included in the response
  432. - `useEffectiveFormat` (optional): Use effectiveFormat instead of userEnteredFormat when including cell formatting (default: `false`)
  433. - `fields` (optional): Array of format field names to return, e.g. `["backgroundColor", "textFormat"]` — reduces API transfer size and response size
  434. - `compactMode` (optional): When `true`, identical adjacent cells are collapsed into rectangular ranges (RLE). Reduces a typical 85×28 sheet from ~60 000 lines to ~500 lines (default: `false`)
  435. ---
  436. ### sheets_insert_rows
  437. Insert new rows at a specific position in a spreadsheet with optional data.
  438. **Parameters:**
  439. - `spreadsheetId` (required): The ID of the spreadsheet
  440. - `range` (required): A1 notation anchor point where rows will be inserted (e.g., "Sheet1!A5")
  441. - `rows` (optional): Number of rows to insert (default: 1)
  442. - `position` (optional): 'BEFORE' or 'AFTER' the anchor row (default: 'BEFORE')
  443. - `inheritFromBefore` (optional): Whether to inherit formatting from the row before (default: false)
  444. - `values` (optional): 2D array of values to fill the newly inserted rows
  445. - `valueInputOption` (optional): 'RAW' or 'USER_ENTERED' (default: 'USER_ENTERED')
  446. **Examples:**
  447. ```javascript
  448. // Insert 1 empty row before row 5
  449. {
  450. "spreadsheetId": "your-spreadsheet-id",
  451. "range": "Sheet1!A5"
  452. }
  453. // Insert 3 rows after row 10 with data
  454. {
  455. "spreadsheetId": "your-spreadsheet-id",
  456. "range": "Sheet1!A10",
  457. "rows": 3,
  458. "position": "AFTER",
  459. "values": [
  460. ["John", "Doe", "john@example.com"],
  461. ["Jane", "Smith", "jane@example.com"],
  462. ["Bob", "Johnson", "bob@example.com"]
  463. ]
  464. }
  465. ```
  466. ### sheets_delete_columns
  467. Delete one or more columns from a sheet using a full-column A1 range.
  468. **Parameters:**
  469. - `spreadsheetId` (required): The ID of the spreadsheet
  470. - `range` (required): Full-column A1 range to delete (e.g., "Sheet1!B:D" or "Sheet1!C:C")
  471. **Examples:**
  472. ```javascript
  473. // Delete columns B through D from Sheet1
  474. {
  475. "spreadsheetId": "your-spreadsheet-id",
  476. "range": "Sheet1!B:D"
  477. }
  478. // Delete a single column from the first sheet
  479. {
  480. "spreadsheetId": "your-spreadsheet-id",
  481. "range": "C:C"
  482. }
  483. ```
  484. ### sheets_delete_rows
  485. Delete one or more rows from a sheet using a full-row A1 range.
  486. **Parameters:**
  487. - `spreadsheetId` (required): The ID of the spreadsheet
  488. - `range` (required): Full-row A1 range to delete (e.g., "Sheet1!2:4" or "Sheet1!3:3")
  489. **Examples:**
  490. ```javascript
  491. // Delete rows 2 through 4 from Sheet1
  492. {
  493. "spreadsheetId": "your-spreadsheet-id",
  494. "range": "Sheet1!2:4"
  495. }
  496. // Delete a single row from the first sheet
  497. {
  498. "spreadsheetId": "your-spreadsheet-id",
  499. "range": "3:3"
  500. }
  501. ```
  502. ## 📋 Changelog
  503. See [CHANGELOG.md](CHANGELOG.md) for a list of changes in each version.
  504. ## 🤝 Contributing
  505. 1. Fork the repository
  506. 2. Create your feature branch (`git checkout -b feature/amazing-feature`)
  507. 3. Run tests and linting (`npm run check`)
  508. 4. Commit your changes (`git commit -m 'Add some amazing feature'`)
  509. 5. Push to the branch (`git push origin feature/amazing-feature`)
  510. 6. Open a Pull Request
  511. ## 👤 Author
  512. **Tomáš Grásl** - [tomasgrasl.cz](https://www.tomasgrasl.cz/)
  513. ## 📄 License
  514. This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.