index.ts•8.49 kB
#!/usr/bin/env node
import { config } from "dotenv";
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListToolsRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import { google } from "googleapis";
import path from "path";
// 環境変数の読み込み（mcp.jsonの設定を優先するため、.envはフォールバックとして使用）
config();
// Google Sheets API の設定
// サービスアカウントキーファイルのパスを指定
const auth = new google.auth.GoogleAuth({
keyFile: process.env.GOOGLE_APPLICATION_CREDENTIALS,
scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});
// MCPサーバーの作成
const server = new Server(
{
name: "google-sheets-mcp",
version: "1.0.0",
},
{
capabilities: {
tools: {},
},
}
);
// 利用可能なツールの定義
const tools = [
{
name: "read_sheet",
description: "スプレッドシートの指定された範囲のデータを読み取ります",
inputSchema: {
type: "object",
properties: {
spreadsheetId: {
type: "string",
description: "スプレッドシートのID",
},
range: {
type: "string",
description: "読み取る範囲（例: 'Sheet1!A1:C10'）",
},
},
required: ["spreadsheetId", "range"],
},
},
{
name: "write_sheet",
description: "スプレッドシートの指定された範囲にデータを書き込みます",
inputSchema: {
type: "object",
properties: {
spreadsheetId: {
type: "string",
description: "スプレッドシートのID",
},
range: {
type: "string",
description: "書き込む範囲（例: 'Sheet1!A1:C10'）",
},
values: {
type: "array",
items: {
type: "array",
items: {
type: "string",
},
},
description: "書き込むデータ（2次元配列）",
},
},
required: ["spreadsheetId", "range", "values"],
},
},
{
name: "append_row",
description: "スプレッドシートの最後に新しい行を追加します",
inputSchema: {
type: "object",
properties: {
spreadsheetId: {
type: "string",
description: "スプレッドシートのID",
},
sheetName: {
type: "string",
description: "シート名",
},
values: {
type: "array",
items: {
type: "string",
},
description: "追加する行のデータ",
},
},
required: ["spreadsheetId", "sheetName", "values"],
},
},
{
name: "get_sheet_info",
description: "スプレッドシートの基本情報を取得します",
inputSchema: {
type: "object",
properties: {
spreadsheetId: {
type: "string",
description: "スプレッドシートのID",
},
},
required: ["spreadsheetId"],
},
},
{
name: "create_sheet",
description: "新しいシートを作成します",
inputSchema: {
type: "object",
properties: {
spreadsheetId: {
type: "string",
description: "スプレッドシートのID",
},
sheetName: {
type: "string",
description: "作成するシート名",
},
},
required: ["spreadsheetId", "sheetName"],
},
},
];
// ツール一覧の取得
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools,
};
});
// ツールの実行
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
if (!args) {
throw new Error("Arguments are required");
}
try {
switch (name) {
case "read_sheet":
return await readSheet(args.spreadsheetId as string, args.range as string);
case "write_sheet":
return await writeSheet(
args.spreadsheetId as string,
args.range as string,
args.values as string[][]
);
case "append_row":
return await appendRow(
args.spreadsheetId as string,
args.sheetName as string,
args.values as string[]
);
case "get_sheet_info":
return await getSheetInfo(args.spreadsheetId as string);
case "create_sheet":
return await createSheet(args.spreadsheetId as string, args.sheetName as string);
default:
throw new Error(`Unknown tool: ${name}`);
}
} catch (error) {
return {
content: [
{
type: "text",
text: `エラー: ${error instanceof Error ? error.message : String(error)}`,
},
],
isError: true,
};
}
});
// スプレッドシートの読み取り
async function readSheet(spreadsheetId: string, range: string) {
const authClient = await auth.getClient();
const sheets = google.sheets({ version: "v4", auth: authClient as any });
const response = await sheets.spreadsheets.values.get({
spreadsheetId,
range,
});
return {
content: [
{
type: "text",
text: JSON.stringify(response.data.values || [], null, 2),
},
],
};
}
// スプレッドシートへの書き込み
async function writeSheet(
spreadsheetId: string,
range: string,
values: string[][]
) {
const authClient = await auth.getClient();
const sheets = google.sheets({ version: "v4", auth: authClient as any });
const response = await sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: "RAW",
requestBody: {
values,
},
});
return {
content: [
{
type: "text",
text: `データが正常に書き込まれました。更新されたセル数: ${response.data.updatedCells}`,
},
],
};
}
// 行の追加
async function appendRow(
spreadsheetId: string,
sheetName: string,
values: string[]
) {
const authClient = await auth.getClient();
const sheets = google.sheets({ version: "v4", auth: authClient as any });
const response = await sheets.spreadsheets.values.append({
spreadsheetId,
range: `${sheetName}!A:Z`,
valueInputOption: "RAW",
requestBody: {
values: [values],
},
});
return {
content: [
{
type: "text",
text: `行が正常に追加されました。更新されたセル数: ${response.data.updates?.updatedCells}`,
},
],
};
}
// スプレッドシート情報の取得
async function getSheetInfo(spreadsheetId: string) {
const authClient = await auth.getClient();
const sheets = google.sheets({ version: "v4", auth: authClient as any });
const response = await sheets.spreadsheets.get({
spreadsheetId,
});
const sheetsInfo = response.data.sheets?.map((sheet) => ({
title: sheet.properties?.title,
sheetId: sheet.properties?.sheetId,
rowCount: sheet.properties?.gridProperties?.rowCount,
columnCount: sheet.properties?.gridProperties?.columnCount,
}));
return {
content: [
{
type: "text",
text: JSON.stringify(
{
title: response.data.properties?.title,
sheets: sheetsInfo,
},
null,
2
),
},
],
};
}
// シートの作成
async function createSheet(spreadsheetId: string, sheetName: string) {
const authClient = await auth.getClient();
const sheets = google.sheets({ version: "v4", auth: authClient as any });
const response = await sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [
{
addSheet: {
properties: {
title: sheetName,
},
},
},
],
},
});
return {
content: [
{
type: "text",
text: `シート "${sheetName}" が正常に作成されました。`,
},
],
};
}
// サーバーの起動
async function main() {
const transport = new StdioServerTransport();
await server.connect(transport);
console.error("Google Sheets MCP Server started");
}
main().catch((error) => {
console.error("Server error:", error);
process.exit(1);
});