Google SheetsとGemini APIで問い合わせを自動分類・FAQ候補を出力する方法【Apps Script実装】

問い合わせデータをGoogle Sheetsに蓄積
↓
Apps Scriptで未分類の行を取得
↓
Gemini APIへ問い合わせ内容を送信
↓
Gemini APIが分類・要約・回答案をJSONで返す
↓
Apps Scriptで結果をGoogle Sheetsに書き戻す
↓
複数件をまとめてFAQ候補として別シートに出力
↓
人間が確認して正式FAQ化

function setProperties() {
  PropertiesService.getScriptProperties().setProperties({
    GEMINI_API_KEY: 'ここにGemini APIキー',
    GEMINI_MODEL: 'gemini-3.5-flash'
  });
}

function callGeminiForInquiry_(inquiryText, answerText) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const model = PropertiesService.getScriptProperties().getProperty('GEMINI_MODEL') || 'gemini-3.5-flash';

  const url = `https://generativelanguage.googleapis.com/v1beta/models/${model}:generateContent`;

  const prompt = `
あなたは企業の問い合わせ対応データを整理する業務アシスタントです。
以下の問い合わせ内容を、Google Sheetsに書き戻しやすいJSON形式で分類してください。

目的：
問い合わせ対応の属人化を減らし、FAQ候補や社内ナレッジとして活用するため。

分類カテゴリ候補：
- 商品について
- 料金について
- 納期について
- 注文・予約について
- 支払いについて
- 返品・交換について
- キャンセルについて
- トラブル対応
- 法人対応
- その他

条件：
- 個人情報は出力しない
- 未確認事項は断定しない
- 回答案は丁寧で簡潔にする
- 社内確認が必要な場合は明記する
- FAQ化に向いているかを判断する
- 料金、納期、返品、契約条件は特に慎重に扱う

問い合わせ内容：
${inquiryText}

既存の回答内容：
${answerText || 'なし'}
`;

  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: {
      responseFormat: {
        text: {
          mimeType: 'application/json',
          schema: {
            type: 'object',
            properties: {
              category: { type: 'string' },
              summary: { type: 'string' },
              faqCandidate: { type: 'boolean' },
              urgency: { type: 'string' },
              recommendedDepartment: { type: 'string' },
              draftAnswer: { type: 'string' },
              confirmationNeeded: { type: 'array', items: { type: 'string' } },
              caution: { type: 'string' }
            },
            required: ['category', 'summary', 'faqCandidate', 'urgency',
                       'recommendedDepartment', 'draftAnswer', 'confirmationNeeded', 'caution']
          }
        }
      }
    }
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-goog-api-key': apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const code = response.getResponseCode();
  const body = response.getContentText();

  if (code < 200 || code >= 300) {
    throw new Error(`Gemini API error: ${code} ${body}`);
  }

  const json = JSON.parse(body);
  const text = json.candidates?.[0]?.content?.parts?.[0]?.text;

  if (!text) throw new Error('Gemini API response does not contain text.');

  return JSON.parse(text);
}

function classifyInquiries() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('問い合わせ管理');
  const values = sheet.getDataRange().getValues();

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const inquiryText = row[3]; // D列：問い合わせ内容
    const answerText = row[4];  // E列：回答内容
    const processed = row[13]; // N列：AI処理済み

    if (!inquiryText) continue;
    if (processed === '済') continue;

    try {
      const result = callGeminiForInquiry_(inquiryText, answerText);

      sheet.getRange(i + 1, 7).setValue(result.category);
      sheet.getRange(i + 1, 8).setValue(result.summary);
      sheet.getRange(i + 1, 9).setValue(result.faqCandidate ? 'はい' : 'いいえ');
      sheet.getRange(i + 1, 10).setValue(result.recommendedDepartment);
      sheet.getRange(i + 1, 11).setValue(result.urgency);
      sheet.getRange(i + 1, 12).setValue(result.draftAnswer);
      sheet.getRange(i + 1, 13).setValue((result.confirmationNeeded || []).join('\n'));
      sheet.getRange(i + 1, 14).setValue('済');
      sheet.getRange(i + 1, 15).setValue(new Date());

      Utilities.sleep(1000);

    } catch (error) {
      sheet.getRange(i + 1, 14).setValue('エラー');
      sheet.getRange(i + 1, 15).setValue(new Date());
      logError_('classifyInquiries', error.message, i + 1);
    }
  }
}

function logError_(functionName, message, rowNumber) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('エラーログ');

  if (!sheet) {
    sheet = ss.insertSheet('エラーログ');
    sheet.appendRow(['日時', '関数名', '行番号', 'エラー内容']);
  }

  sheet.appendRow([new Date(), functionName, rowNumber || '', message]);
}

function generateFaqCandidates() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inquirySheet = ss.getSheetByName('問い合わせ管理');
  const faqSheet = ss.getSheetByName('FAQ候補') || ss.insertSheet('FAQ候補');
  const values = inquirySheet.getDataRange().getValues();
  const targetRows = [];

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    if (row[13] === '済' && row[8] === 'はい') {
      targetRows.push({
        rowNumber: i + 1,
        category: row[6],
        inquiryText: row[3],
        answerText: row[4]
      });
    }
  }

  if (targetRows.length === 0) {
    SpreadsheetApp.getUi().alert('FAQ候補にできる問い合わせがありません。');
    return;
  }

  const result = callGeminiForFaqCandidates_(targetRows);

  if (faqSheet.getLastRow() === 0) {
    faqSheet.appendRow(['作成日時', 'カテゴリ', 'よくある質問', '回答案', '根拠となる問い合わせ',
                        '社内確認事項', '公開可否', 'ステータス', '確認担当', '最終更新日']);
  }

  result.faqs.forEach(function(faq) {
    faqSheet.appendRow([
      new Date(), faq.category, faq.question, faq.draftAnswer,
      (faq.sourceRows || []).join(', '),
      (faq.confirmationNeeded || []).join('\n'),
      faq.publishability, '下書き', '', ''
    ]);
  });
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI問い合わせ処理')
    .addItem('未処理問い合わせをAI分類', 'classifyInquiries')
    .addItem('FAQ候補を生成', 'generateFaqCandidates')
    .addToUi();
}

商品について / 料金について / 納期について / 注文・予約について
支払いについて / 返品・交換について / キャンセルについて
トラブル対応 / 法人対応 / その他

下書き → 確認中 → 修正必要 → 公開可 / 社内限定 / 非公開

function classifyInquiriesWithLimit() {
  const limit = 30;
  let count = 0;
  // ... (通常のclassifyInquiriesと同じ処理)
  // ループ内に if (count >= limit) break; を追加
}

【連携名】問い合わせデータAI分類・FAQ候補生成
【目的】Google SheetsにためたデータをGemini APIで分類し、FAQ候補を作成する
【対象シート】問い合わせ管理 / 【出力先シート】FAQ候補
【実行方法】Google Sheetsメニュー「AI問い合わせ処理」から実行
【注意点】AI出力は下書き扱い。正式FAQ化前に人間が確認する。個人情報はAIに送らない
【確認担当】〇〇部 担当者名 / 【最終更新日】2026/05/20