SpreadJSの「AI関数」でAI対応のスプレッドシートを実現する

<script src="gc.spread.sheets.ai.x.x.x.min.js"></script>

import '@mescius/spread-sheets-ai-addon';

project/
├── server.js
├── package.json
├── .env
└── public/
    ├── index.html
    ├── css/
    ｜  ├── gc.spread.sheets.excel2013white.19.0.6.css
    ｜  └── styles.css
    └── scripts/
        ├── app.js
        ├── plugins/
        ｜  └── gc.spread.sheets.ai.19.0.6.min.js
        └── resources/
            └── gc.spread.sheets.resources.ja.19.0.6.min.js

npm init -y

npm install express dotenv @google/genai

{
・・・（中略）・・・
  "author": "",
  "license": "ISC",
  "type": "module",
・・・（中略）・・・

import express from "express";
import { GoogleGenAI } from "@google/genai";
import dotenv from "dotenv";

// .env ファイルから環境変数を読み込む
dotenv.config();

const app = express();
const port = process.env.PORT || 3000;

// JSON リクエストボディをパース
app.use(express.json());
// 静的ファイルを public フォルダから配信
app.use(express.static("public"));

// Google GenAI クライアントを初期化
const ai = new GoogleGenAI({ apiKey: process.env.AI_API_KEY });
const defaultModel = process.env.AI_MODEL || "gemini-2.5-flash";

// OpenAIのメッセージ形式をGoogleGenAIのcontents形式に変換
const convertMessages = (messages) => {
  const systemParts = [];
  const contents = [];

  for (const msg of messages) {
    if (msg.role === "system") {
      // systemメッセージは最初のuserメッセージに結合する
      systemParts.push(msg.content);
    } else {
      contents.push({
        role: msg.role === "assistant" ? "model" : "user",
        parts: [{ text: msg.content }],
      });
    }
  }

  if (systemParts.length > 0 && contents.length > 0 && contents[0].role === "user") {
    contents[0].parts[0].text = systemParts.join("\n\n") + "\n\n" + contents[0].parts[0].text;
  }

  return { contents };
};

// AI連携用APIエンドポイント
app.post("/api/queryAI", async (req, res) => {
  try {
    const body = req.body;
    const model = body.model || defaultModel;
    const { contents } = convertMessages(body.messages || []);

    const params = {
      model,
      contents,
      config: {
        temperature: body.temperature ?? 0.7,
        maxOutputTokens: body.max_tokens ?? 4096,
      },
    };

    if (body.stream) {
      await handleStream(params, res);
    } else {
      await handleNonStream(params, model, res);
    }
  } catch (error) {
    handleError(error, res, req.body.stream);
  }
});

// ストリーミング応答をSSE形式で返す
const handleStream = async (params, res) => {
  res.setHeader("Content-Type", "text/event-stream");
  res.setHeader("Cache-Control", "no-cache");
  res.setHeader("Connection", "keep-alive");

  try {
    const response = await ai.models.generateContentStream(params);
    const created = Math.floor(Date.now() / 1000);
    const requestId = "gemini-" + Date.now();

    for await (const chunk of response) {
      const text = chunk.text || "";
      if (!text) continue;

      const openaiChunk = {
        id: requestId,
        object: "chat.completion.chunk",
        created,
        model: params.model,
        choices: [{
          index: 0,
          delta: { content: text },
          finish_reason: null,
        }],
      };

      res.write(`data: ${JSON.stringify(openaiChunk)}\n\n`);
    }

    // ストリームの終了を通知
    res.write("data: [DONE]\n\n");
    res.end();
  } catch (error) {
    res.write(`data: ${JSON.stringify({ error: error.message })}\n\n`);
    res.end();
  }
};

// 非ストリーミング応答をJSONで返す
const handleNonStream = async (params, model, res) => {
  const response = await ai.models.generateContent(params);
  const text = response.text || "";

  res.setHeader("Content-Type", "application/json");
  res.setHeader("Cache-Control", "no-store");
  res.status(200).json({
    id: "gemini-" + Date.now(),
    object: "chat.completion",
    created: Math.floor(Date.now() / 1000),
    model,
    choices: [{
      index: 0,
      message: { role: "assistant", content: text },
      finish_reason: "stop",
    }],
    usage: { prompt_tokens: 0, completion_tokens: 0, total_tokens: 0 },
  });
};

// エラー処理:ストリーミングか通常レスポンスかで返し方を切り替える
const handleError = (error, res, isStream) => {
  console.error("Error:", error);
  if (isStream) {
    res.write(`data: ${JSON.stringify({ error: error.message })}\n\n`);
    res.end();
  } else {
    res.status(500).json({ error: error.message });
  }
};

// サーバーを起動
app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

AI_API_KEY=入手したgemini apiキーをここに記述します
AI_MODEL=gemini-2.5-flash
PORT=3000

<!DOCTYPE html>
<html>

<head>
  <meta charset="UTF-8">
  <script src="scripts/gc.spread.sheets.all.19.0.6.min.js"></script>
  <script src="scripts/plugins/gc.spread.sheets.ai.19.0.6.min.js"></script>
  <script src="scripts/resources/gc.spread.sheets.resources.ja.19.0.6.min.js"></script>
  <link href="css/gc.spread.sheets.excel2013white.19.0.6.css" rel="stylesheet" />
  <link href="css/styles.css" rel="stylesheet" />
  <script src="scripts/app.js"></script>
</head>

<body>
  <div id="ss"></div>
</body>

</html>

// 名前空間のエイリアス名
const spreadNS = GC.Spread.Sheets;

// ライセンスキーとカルチャの設定
//spreadNS.LicenseKey = 'ここにSpreadJSのライセンスキーを設定します';
GC.Spread.Common.CultureManager.culture("ja-jp");

document.addEventListener("DOMContentLoaded", () => {
  // SpreadJSの生成
  const spread = new spreadNS.Workbook("ss");
  spread.options.allowDynamicArray = true;
  injectAI(spread);
  initSheet(spread);
});

// Sheet1の設定
const initSheet = (spread) => {
  // データの設定
  const data = [
    ['英語の原文'],
    ['Do in Rome as the Romans do.'],
    ['All is well that ends well.'],
    ['The early bird catches the worm.'],
    ['Misfortunes never come singly.'],
  ];

  // シートの基本設定
  const sheet = spread.getSheet(0);
  sheet.defaults.colWidth = 20;
  sheet.setColumnWidth(1, 270);
  sheet.setColumnWidth(3, 350);

  // 項目ヘッダのスタイル
  const headerStyle = new spreadNS.Style();
  headerStyle.backColor = "royalblue";
  headerStyle.foreColor = "white";
  headerStyle.hAlign = spreadNS.HorizontalAlign.center;

  // 英語の原文
  sheet.setArray(1, 1, data);
  sheet.setStyle(1, 1, headerStyle);
  sheet.getRange(1, 1, 5, 1).cellPadding("0 0 0 5");
  sheet.getRange(1, 1, 5, 1).setBorder(
    new GC.Spread.Sheets.LineBorder("royalblue", spreadNS.LineStyle.medium),
    { outline: true }
  );

  // 日本語訳（SJS.AI.TRANSLATE関数の動作確認用）
  const watermark1 = '=SJS.AI.TRANSLATE(B3:B6,"日本語")';
  sheet.setValue(7, 1, "日本語訳");
  sheet.setStyle(7, 1, headerStyle);
  sheet.getRange(7, 1, 5, 1).setBorder(
    new GC.Spread.Sheets.LineBorder("royalblue", spreadNS.LineStyle.medium),
    { outline: true }
  );
  sheet.getCell(8, 1).watermark(watermark1);

  // 感情分析（SJS.AI.TEXTSENTIMENT関数の動作確認用）
  const watermark2 = '=SJS.AI.TEXTSENTIMENT(B9:B12,"良い","悪い","普通")';
  sheet.setValue(7, 3, "感情分析");
  sheet.setStyle(7, 3, headerStyle);
  sheet.getRange(7, 3, 5, 1).setBorder(
    new GC.Spread.Sheets.LineBorder("royalblue", spreadNS.LineStyle.medium),
    { outline: true }
  );
  sheet.getRange(8, 3, 4, 1).hAlign(spreadNS.HorizontalAlign.center);
  sheet.getCell(8, 3).watermark(watermark2);

  // クエリの設定（SJS.AI.QUERY関数の動作確認用）
  const watermark3 = '=SJS.AI.QUERY("このことわざの意味を簡潔に教えてください",B9:B12)';
  sheet.setValue(13, 1, "任意のクエリ");
  sheet.getCell(13, 1).setStyle(headerStyle);
  sheet.getRange(13, 1, 5, 3).setBorder(
    new GC.Spread.Sheets.LineBorder("royalblue", spreadNS.LineStyle.medium),
    { outline: true }
  );
  sheet.addSpan(13, 1, 1, 3, spreadNS.SheetArea.viewport);
  sheet.getCell(14, 1).watermark(watermark3);
}

// AIリクエストをサーバーを経由してAIサービスに送信
const serverCallback = async (requestBody) => {
  const response = await fetch("/api/queryAI", {
    method: 'POST',
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(requestBody),
  });
  if (!response.ok) {
    throw new Error(`HTTP error! status: ${response.status}`);
  }
  return response;
};

const injectAI = (spread) => {
  spread.injectAI(serverCallback);
}

/* SpreadJSのスタイル */
#ss {
  inline-size: 740px;
  block-size: 430px;
  border: 1px solid silver;
}

node server.js

=SJS.AI.TRANSLATE(B3:B6,"日本語")

=SJS.AI.TEXTSENTIMENT(B9:B12,"良い","悪い","普通")

=SJS.AI.QUERY("このことわざの意味を簡潔に教えてください",B9:B12)