import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js";
import {
ListSheetsSchema,
GetSchemaSchema,
QueryRowsSchema,
handleToolCall,
} from "./tools.js";
import { initializeSheetsClient } from "./sheets.js";
import type { IncomingMessage, ServerResponse } from "node:http";
const server = new McpServer({
name: "google-sheets-mcp",
version: "1.0.0",
});
// Register tools using Zod schemas
server.tool(
"list_sheets",
"List all sheet names (tabs) in a Google Spreadsheet. Use this to discover available sheets before querying data.",
ListSheetsSchema.shape,
async (args) => {
const result = await handleToolCall("list_sheets", args);
return {
content: [{ type: "text", text: JSON.stringify(result, null, 2) }],
};
}
);
server.tool(
"get_schema",
"Get the column names (headers) for a specific sheet. Returns the first row of the sheet which typically contains column headers.",
GetSchemaSchema.shape,
async (args) => {
const result = await handleToolCall("get_schema", args);
return {
content: [{ type: "text", text: JSON.stringify(result, null, 2) }],
};
}
);
server.tool(
"query_rows",
"Fetch rows of data from a sheet. Returns data as objects with column names as keys. Supports pagination via limit and offset.",
QueryRowsSchema.shape,
async (args) => {
const result = await handleToolCall("query_rows", args);
return {
content: [{ type: "text", text: JSON.stringify(result, null, 2) }],
};
}
);
// Initialize Google Sheets client on first request
let initialized = false;
function ensureInitialized() {
if (!initialized) {
initializeSheetsClient();
initialized = true;
}
}
// Cloud Function HTTP entry point
export async function googleSheetsMcp(
req: IncomingMessage & { body?: unknown; rawBody?: Buffer },
res: ServerResponse
): Promise<void> {
// Handle CORS preflight
if (req.method === "OPTIONS") {
res.writeHead(204, {
"Access-Control-Allow-Origin": "*",
"Access-Control-Allow-Methods": "POST, OPTIONS",
"Access-Control-Allow-Headers": "Content-Type",
});
res.end();
return;
}
// Only accept POST requests
if (req.method !== "POST") {
res.writeHead(405, { "Content-Type": "application/json" });
res.end(JSON.stringify({ error: "Method not allowed" }));
return;
}
try {
ensureInitialized();
// Create a stateless transport for this request
const transport = new StreamableHTTPServerTransport({
sessionIdGenerator: undefined, // Stateless mode
});
// Connect server to transport
await server.connect(transport);
// Handle the request
await transport.handleRequest(req, res, req.body);
} catch (error) {
console.error("MCP request error:", error);
if (!res.headersSent) {
res.writeHead(500, { "Content-Type": "application/json" });
res.end(
JSON.stringify({
error: error instanceof Error ? error.message : "Internal server error",
})
);
}
}
}
// Export for Cloud Functions
export { googleSheetsMcp as handler };
