CData's Read-Only MCP Server Implementation for Google Sheets

:warning: Restricted Functionality Notice: This particular server deployment is strictly read-only. For comprehensive data manipulation capabilities (read, write, modification, and action execution) coupled with a simpler deployment process, please investigate the complimentary CData MCP Server for Google Sheets (Beta release).

Core Objective

This read-only MCP endpoint was engineered to empower Large Language Models (LLMs), such as Claude Desktop, with the ability to execute queries against live data sourced from Google Sheets. This functionality is underpinned by the robust CData JDBC Driver for Google Sheets.

The CData JDBC Driver acts as an abstraction layer, presenting Google Sheets as conventional relational SQL structures. This server component encapsulates that driver, exposing the spreadsheet data through a streamlined MCP interface. Consequently, LLMs can retrieve up-to-the-minute information simply by posing conversational questions—no SQL construction is necessary.

Deployment Instructions

1. Obtain the source code repository: bash git clone https://github.com/cdatasoftware/google-sheets-mcp-server-by-cdata.git cd google-sheets-mcp-server-by-cdata

2. Compile the server artifact: bash mvn clean install

   This action generates the executable JAR: CDataMCP-jar-with-dependencies.jar 3. Secure and install the CData JDBC Driver for Google Sheets: https://www.cdata.com/drivers/gsheets/download/jdbc 4. License the CData JDBC Driver: * Locate the installation path, typically: * (Windows) C:\Program Files\CData\CData JDBC Driver for Google Sheets\ * (Mac/Linux) /Applications/CData JDBC Driver for Google Sheets/ * Execute the licensing utility: java -jar cdata.jdbc.googlesheets.jar --license * Provide your credentials and input "TRIAL" or your purchased activation key. 5. Define the data source connection via a properties file (e.g., google-sheets.prp): * This file must specify the following parameters: * Prefix: Identifier prefix for the exposed tools. * ServerName: The logical name of this server instance. * ServerVersion: Version designation. * DriverPath: Absolute location of the JDBC driver JAR file. * DriverClass: Fully qualified name of the JDBC driver class (e.g., cdata.jdbc.googlesheets.GoogleSheetsDriver). * JdbcUrl: The connection string for the CData JDBC Driver. * Tables: Optionally list specific tables; leave empty () for comprehensive access. env Prefix=googlesheets ServerName=CDataGoogleSheets ServerVersion=1.0 DriverPath=PATH\TO\cdata.jdbc.googlesheets.jar DriverClass=cdata.jdbc.googlesheets.GoogleSheetsDriver JdbcUrl=jdbc:googlesheets:InitiateOAuth=GETANDREFRESH; Tables=

Integration with AI Clients (e.g., Claude Desktop)

1. Construct or update the client configuration file (e.g., claude_desktop_config.json) to incorporate this new MCP service definition within the mcpServers block.

   Windows Execution Example:

   { "mcpServers": { "{classname_dash}": { "command": "PATH\TO\java.exe", "args": [ "-jar", "PATH\TO\CDataMCP-jar-with-dependencies.jar", "PATH\TO\google-sheets.prp" ] }, ... } }

   Linux/Mac Execution Example:

   { "mcpServers": { "{classname_dash}": { "command": "/PATH/TO/java", "args": [ "-jar", "/PATH/TO/CDataMCP-jar-with-dependencies.jar", "/PATH/TO/google-sheets.prp" ] }, ... } }

   • If necessary, relocate the configuration file to the client's required directory structure: Windows Relocation: bash cp C:\PATH\TO\claude_desktop_config.json %APPDATA%\Claude\claude_desktop_config.json

   Linux/Mac Relocation: bash cp /PATH/TO/claude_desktop_config.json /Users/{user}/Library/Application\ Support/Claude/claude_desktop_config.json'

2. Restart or reload the AI client application for the newly defined MCP servers to become discoverable.

Crucial Note: A complete shutdown (and verification via Task/Activity Monitor) of the client application may be mandatory for new MCP services to register correctly.

Direct Server Execution

Execute the server independently using the following command structure: bash java -jar /PATH/TO/CDataMCP-jar-with-dependencies.jar /PATH/TO/Salesforce.prp

Constraint: This server communicates via standard input/output (stdio), restricting its use exclusively to clients operating on the identical local machine.

Interaction Methodology

Upon successful configuration, the AI client gains access to integrated functions for interacting with the underlying data structure. Explicit invocation of these functions is generally unnecessary; users should phrase their data needs conversationally.

Example Inquiries: * "Analyze the relationship between our finalized sales opportunities and the industry sectors of the associated accounts." * "Quantify the volume of pending support cases within the designated SUPPORT initiative." * "Enumerate my scheduled appointments for the current day."

Available Functions & Specifications

Tool naming conventions utilize {servername} as a placeholder for the identifier defined in the configuration file (e.g., {classname_dash}). * {servername}_get_tables: Fetches the catalog of accessible data sets. Columns can then be retrieved using {servername}_get_columns. Output is provided in CSV format, beginning with a header row. * {servername}_get_columns: Retrieves the attribute list for a specified data set. Reference {servername}_get_tables to identify available sets. Output format is CSV, including column headers. * {servername}_run_query: Facilitates the execution of arbitrary SQL SELECT statements against the data source.

JSON-RPC Invocation Examples

For programmatic interactions bypassing the AI client interface (adhering to JSON-RPC 2.0 standards), utilize these payload templates:

Example: Retrieving Table List

{ "jsonrpc": "2.0", "id": 1, "method": "tools/call", "params": { "name": "google_sheets_get_tables", "arguments": {} } }

Example: Fetching Schema for 'Account'

{ "jsonrpc": "2.0", "id": 2, "method": "tools/call", "params": { "name": "google_sheets_get_columns", "arguments": { "table": "Account" } } }

Example: Executing a Custom SQL Select

{ "jsonrpc": "2.0", "id": 3, "method": "tools/call", "params": { "name": "google_sheets_run_query", "arguments": { "sql": "SELECT * FROM [Account] WHERE [IsDeleted] = true" } } }

Common Issue Resolution

1. Server Not Visible in Client: Ensure the host application (e.g., Claude Desktop) was fully terminated (check system process monitors) before restarting.
2. Data Retrieval Failure: Verify the connection string copied into the properties (.prp) file is identical to the one validated using the Connection String builder utility.
3. Connection Errors to Source: For connectivity issues, seek assistance from the CData Support Team.
4. MCP Server Performance/Feedback: Engage with the broader user base or provide input via the CData Community.

Licensing Information

This MCP server software is distributed under the terms of the MIT License. Full rights to utilize, modify, and disseminate the software are granted, contingent upon adherence to the stipulations detailed in the accompanying LICENSE file.

Comprehensive Connector Support List

[Table listing all supported data sources omitted for brevity, starting with Access and ending with Zuora and dozens more...]

WIKIPEDIA CONTEXT (XMLHttpRequest): XMLHttpRequest (XHR) provides a JavaScript object interface enabling HTTP requests to be dispatched from a web browser to a web server. Its methods allow client-side applications to initiate server communication post-page load and receive subsequent data. XHR is fundamental to asynchronous web interactions (Ajax), contrasting with earlier methods like form submissions or link navigation that typically mandated page replacement.