#!/usr/bin/env node
const { Server } = require("@modelcontextprotocol/sdk/server/index.js");
const {
StdioServerTransport,
} = require("@modelcontextprotocol/sdk/server/stdio.js");
const {
CallToolRequestSchema,
ListToolsRequestSchema,
Tool,
} = require("@modelcontextprotocol/sdk/types.js");
const { google } = require("googleapis");
const fs = require("fs");
const path = require("path");
require("dotenv").config();
// Google Sheets API setup
let sheetsClient = null;
let authClient = null;
async function initializeGoogleAuth() {
try {
// Читаем учетные данные из переменной окружения или файла
const credentialsPath = process.env.GOOGLE_CREDENTIALS_PATH || "./credentials.json";
if (!fs.existsSync(credentialsPath)) {
throw new Error(`Credentials file not found at ${credentialsPath}`);
}
const credentials = JSON.parse(fs.readFileSync(credentialsPath, "utf8"));
const auth = new google.auth.GoogleAuth({
credentials: credentials,
scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});
authClient = await auth.getClient();
sheetsClient = google.sheets({ version: "v4", auth: authClient });
console.error("✓ Google Sheets API initialized");
} catch (error) {
console.error("✗ Failed to initialize Google Sheets API:", error.message);
process.exit(1);
}
}
// Tool: Read data from Google Sheets
async function readGoogleSheet(spreadsheetId, range) {
try {
const response = await sheetsClient.spreadsheets.values.get({
spreadsheetId,
range,
});
const values = response.data.values || [];
return {
success: true,
data: values,
range: response.data.range,
rowCount: values.length,
columnCount: values.length > 0 ? values[0].length : 0,
};
} catch (error) {
return {
success: false,
error: error.message,
};
}
}
// Tool: Write data to Google Sheets
async function writeGoogleSheet(spreadsheetId, range, values) {
try {
const response = await sheetsClient.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: "USER_ENTERED",
requestBody: {
values: values,
},
});
return {
success: true,
updatedRows: response.data.updatedRows,
updatedColumns: response.data.updatedColumns,
updatedCells: response.data.updatedCells,
};
} catch (error) {
return {
success: false,
error: error.message,
};
}
}
// Tool: Append data to Google Sheets
async function appendGoogleSheet(spreadsheetId, range, values) {
try {
const response = await sheetsClient.spreadsheets.values.append({
spreadsheetId,
range,
valueInputOption: "USER_ENTERED",
requestBody: {
values: values,
},
});
return {
success: true,
updatedRows: response.data.updates.updatedRows,
updatedColumns: response.data.updates.updatedColumns,
updatedCells: response.data.updates.updatedCells,
};
} catch (error) {
return {
success: false,
error: error.message,
};
}
}
// Tool: Get spreadsheet metadata
async function getSpreadsheetMetadata(spreadsheetId) {
try {
const response = await sheetsClient.spreadsheets.get({
spreadsheetId,
});
const sheets = response.data.sheets.map((sheet) => ({
title: sheet.properties.title,
sheetId: sheet.properties.sheetId,
gridProperties: sheet.properties.gridProperties,
}));
return {
success: true,
spreadsheetId: response.data.spreadsheetId,
title: response.data.properties.title,
sheets: sheets,
};
} catch (error) {
return {
success: false,
error: error.message,
};
}
}
// Tool: Clear cells
async function clearGoogleSheetCells(spreadsheetId, range) {
try {
const response = await sheetsClient.spreadsheets.values.clear({
spreadsheetId,
range,
});
return {
success: true,
clearedRange: response.data.clearedRange,
};
} catch (error) {
return {
success: false,
error: error.message,
};
}
}
// MCP Server setup
const server = new Server(
{
name: "google-sheets-mcp",
version: "1.0.0",
},
{
capabilities: {
tools: {},
},
}
);
// Handle tool listing
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: "read_sheet",
description:
"Read data from a Google Sheet. Returns cell values from the specified range.",
inputSchema: {
type: "object",
properties: {
spreadsheet_id: {
type: "string",
description:
"The ID of the Google Sheet (from the URL: docs.google.com/spreadsheets/d/{ID})",
},
range: {
type: "string",
description:
"The range to read (e.g., 'Sheet1!A1:D10' or 'A1:D10' for current sheet)",
},
},
required: ["spreadsheet_id", "range"],
},
},
{
name: "write_sheet",
description:
"Write data to a Google Sheet. Overwrites existing data in the specified range.",
inputSchema: {
type: "object",
properties: {
spreadsheet_id: {
type: "string",
description:
"The ID of the Google Sheet (from the URL: docs.google.com/spreadsheets/d/{ID})",
},
range: {
type: "string",
description:
"The range to write to (e.g., 'Sheet1!A1' or 'A1' for current sheet)",
},
values: {
type: "array",
description:
"Array of rows to write. Each row is an array of values.",
items: {
type: "array",
items: {},
},
},
},
required: ["spreadsheet_id", "range", "values"],
},
},
{
name: "append_sheet",
description:
"Append data to a Google Sheet. Adds new rows at the end of existing data.",
inputSchema: {
type: "object",
properties: {
spreadsheet_id: {
type: "string",
description:
"The ID of the Google Sheet (from the URL: docs.google.com/spreadsheets/d/{ID})",
},
range: {
type: "string",
description:
"The range to append to (e.g., 'Sheet1!A:A' or 'A:A' for all rows)",
},
values: {
type: "array",
description:
"Array of rows to append. Each row is an array of values.",
items: {
type: "array",
items: {},
},
},
},
required: ["spreadsheet_id", "range", "values"],
},
},
{
name: "get_metadata",
description:
"Get metadata about a Google Sheet including sheet names and properties.",
inputSchema: {
type: "object",
properties: {
spreadsheet_id: {
type: "string",
description:
"The ID of the Google Sheet (from the URL: docs.google.com/spreadsheets/d/{ID})",
},
},
required: ["spreadsheet_id"],
},
},
{
name: "clear_sheet",
description: "Clear data from a specified range in a Google Sheet.",
inputSchema: {
type: "object",
properties: {
spreadsheet_id: {
type: "string",
description:
"The ID of the Google Sheet (from the URL: docs.google.com/spreadsheets/d/{ID})",
},
range: {
type: "string",
description:
"The range to clear (e.g., 'Sheet1!A1:D10' or 'A1:D10')",
},
},
required: ["spreadsheet_id", "range"],
},
},
],
};
});
// Handle tool execution
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request;
try {
let result;
switch (name) {
case "read_sheet":
result = await readGoogleSheet(args.spreadsheet_id, args.range);
break;
case "write_sheet":
result = await writeGoogleSheet(
args.spreadsheet_id,
args.range,
args.values
);
break;
case "append_sheet":
result = await appendGoogleSheet(
args.spreadsheet_id,
args.range,
args.values
);
break;
case "get_metadata":
result = await getSpreadsheetMetadata(args.spreadsheet_id);
break;
case "clear_sheet":
result = await clearGoogleSheetCells(args.spreadsheet_id, args.range);
break;
default:
return {
content: [
{
type: "text",
text: `Unknown tool: ${name}`,
},
],
isError: true,
};
}
return {
content: [
{
type: "text",
text: JSON.stringify(result, null, 2),
},
],
};
} catch (error) {
return {
content: [
{
type: "text",
text: `Error executing tool: ${error.message}`,
},
],
isError: true,
};
}
});
// Main initialization
async function main() {
const transport = new StdioServerTransport();
await initializeGoogleAuth();
await server.connect(transport);
console.error("Google Sheets MCP Server running on stdio");
}
main().catch((error) => {
console.error("Fatal error:", error);
process.exit(1);
});
