Google Sheets Data Adapter Service (MCP Endpoint)

This repository hosts an implementation of an MCP server dedicated to abstracting and delivering data residing within Google Spreadsheets directly to large language models (LLMs) via the Model Context Protocol framework.

Core Capabilities

Retrieval of high-level metadata pertaining to specified spreadsheets (e.g., sheet listings, dimensional properties).
Extraction of cell content from a designated worksheet, meticulously formatted into standardized Markdown tables.
Seamless integration point for MCP-enabled clients, such as the local Claude Desktop application.

Setup Procedure

bash

Obtain the repository files

git clone https://github.com/your-username/spreadsheet-mcp-server.git cd spreadsheet-mcp-server

Install required Node.js dependencies

npm install

Initialize configuration template

cp .env.example .env

Crucially, populate .env with the GAS_WEB_APP_URL and the necessary GAS_API_KEY

Compile the source code

npm run build

Configuration Variables

The server relies on the following environment variables for its external communication:

GAS_WEB_APP_URL: The deployment endpoint URL for the corresponding Google Apps Script (GAS) Web Application.
GAS_API_KEY: An authentication credential required for securing API interactions with the GAS backend.

Configuration is typically managed within the .env file:

GAS_WEB_APP_URL=https://script.google.com/macros/s/your-deployment-id/exec GAS_API_KEY=your-api-key

If these environment variables are absent during execution, the server defaults to a simulated (mock) operational mode, bypassing any connection to live Google Sheet resources.

Operational Usage

Local Execution

Initiate the server process independently:

bash npm start

Integration with Desktop Client (e.g., Claude for Desktop)

Modify the client's configuration file (claude_desktop_config.json) to include this service definition:

{ "mcpServers": { "spreadsheet_data_loader": { "command": "node", "args": ["/build/index.js"] } } }

To inject the required credentials directly into the client execution environment, utilize the env field:

{ "mcpServers": { "spreadsheet_data_loader": { "command": "node", "args": ["/build/index.js"], "env": { "GAS_WEB_APP_URL": "https://script.google.com/macros/s/your-deployment-id/exec", "GAS_API_KEY": "your-api-key" } } } }

Configuration file locations for reference: - macOS: ~/Library/Application Support/Claude/claude_desktop_config.json - Windows: %AppData%\Claude\claude_desktop_config.json

Verification via MCP Inspector

Testing the deployed endpoint using the protocol inspector utility:

bash npx @modelcontextprotocol/inspector node build/index.js

Exported Toolset

fetchSpreadsheetMetadata

Retrieves fundamental identifying information and a roster of associated sheets within a specified document.

Input Parameters: - documentUri: The web address (URL) pointing to the target Google Spreadsheet.

Output: - Aggregate metadata including the document title, unique identifier, and a list of constituent sheets, detailing their respective row and column counts.

extractSheetContent

Fetches and formats the tabular data from an explicitly named worksheet.

Input Parameters: - documentUri: The URL of the spreadsheet. - sheetIdentifier: The precise name corresponding to the desired tab/sheet.

Output: - The contents of the specified sheet, presented in a fully compliant Markdown table structure.

Development Lifecycle

Code Organization

src/ ├── entrypoint.ts # Primary server initiation logic ├── mcp_host.ts # MCP server configuration setup ├── settings.ts # Configuration loading and environment variable management ├── core_tools/ # Implementation modules for exposed tools │ ├── getMetadata.ts │ ├── getTableData.ts │ └── index.ts ├── data_access/ # Backend communication layer (GAS interaction) │ ├── spec.md # API interaction definitions │ ├── google_connector.ts │ └── types.ts └── helpers/ # Auxiliary utility functions └── markdown_formatter.ts

Testing Regimen

bash

Execute all unit and integration tests

npm test

Run tests continuously upon file changes

npm run test:watch

Dependency on Google Apps Script (GAS)

This service is architected to communicate with Google's infrastructure via a custom-developed GAS Web App for operational data fetching:

A dedicated Google Apps Script Web App must be deployed (refer to the official GAS Web App Guide).
The GAS project must contain custom backend routines capable of accessing and serving spreadsheet data (see documentation within data_access/spec.md).
Secure linkage is established by furnishing the GAS_WEB_APP_URL and GAS_API_KEY environment settings.

This architecture effectively circumvents the complexities of direct OAuth flows, thereby streamlining spreadsheet access control and enhancing security posture.

Operation without these configuration parameters triggers the aforementioned mock data source.

Licensing

Licensed under the terms of the MIT License.

--- WIKIPEDIA CONTEXT --- Business management tools encompass the comprehensive set of applications, protocols, analytical solutions, and organizational frameworks utilized by entities to navigate fluid market conditions, maintain competitive advantage, and systematically enhance operational efficacy. These solutions span departmental requirements, categorizing into areas such as strategic planning, workflow control, recordkeeping, human resource management, analytical decision support, and performance monitoring. Modern business tooling has undergone rapid transformation, driven by technological acceleration, creating a challenge in selecting optimal solutions for specific corporate demands, often fueled by the constant pressure to reduce expenditure and escalate revenue streams while deeply understanding and meeting customer expectations.

Historical analysis, such as the 2013 Bain & Company survey, indicates key priorities reflected in tool adoption globally, including strategic planning frameworks, Customer Relationship Management (CRM) systems, employee feedback mechanisms, performance benchmarking, Balanced Scorecard implementation, core competency identification, outsourcing management, organizational change programs, Supply Chain Management (SCM) integration, and formalized mission/vision articulation.

Business software, or applications deployed by enterprise users, serves to automate, measure, and precisely execute diverse corporate functions. This evolution proceeded from foundational Management Information Systems (MIS) to comprehensive Enterprise Resource Planning (ERP) suites, later incorporating CRM functionalities, and ultimately migrating towards cloud-centric operational management platforms. Value creation beyond mere IT expenditure hinges critically on both the proficiency of the system's deployment and the diligence applied in selecting and tailoring the appropriate technical instruments to organizational necessities.