【GAS&ChatGPT】GASとAI連携で実現する最強の業務効率化スプレッドシート入門

function callChatGPT(prompt) {
  const apiKey = 'YOUR_API_KEY'; // APIキーを設定
  const apiUrl = 'https://api.openai.com/v1/chat/completions';
  
  const payload = {
    model: "gpt-4", // 利用するモデルを指定
    messages: [{role: "user", content: prompt}],
    temperature: 0.7
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  
  const response = UrlFetchApp.fetch(apiUrl, options);
  const responseData = JSON.parse(response.getContentText());
  
  return responseData.choices[0].message.content;
}

function analyzeReviewSentiment(row) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const review = sheet.getRange(row, 2).getValue(); // B列からレビューテキストを取得
  
  const prompt = `以下の顧客レビューの感情を分析し、「ポジティブ」「ネガティブ」「中立」のいずれかで回答してください。\n\nレビュー: "${review}"`;
  
  const sentiment = callChatGPT(prompt); // 先ほど作成した関数を呼び出し
  sheet.getRange(row, 3).setValue(sentiment); // C列に結果を書き込み
}

function cleanData(text) {
  if (!text) return "";
  
  // 余分な空白の削除
  text = text.trim().replace(/\s+/g, ' ');
  
  // HTML特殊文字の置換
  text = text.replace(/ /g, ' ')
             .replace(/&/g, '&')
             .replace(/&lt;/g, '<')
             .replace(/&gt;/g, '>');
  
  return text;
}

function parseAIResponse(response) {
  try {
    // JSON形式の応答をパース
    const data = JSON.parse(response);
    return data;
  } catch (e) {
    // JSON形式でない場合は単純なテキストとして処理
    Logger.log('応答をJSONとしてパースできませんでした: ' + e);
    return { text: response };
  }
}

function createSentimentChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange("C2:C100"); // 感情分析結果の範囲
  
  // 各カテゴリの数をカウント
  const values = dataRange.getValues();
  const counts = { 'ポジティブ': 0, 'ネガティブ': 0, '中立': 0 };
  
  values.forEach(row => {
    const sentiment = row[0];
    if (sentiment in counts) {
      counts[sentiment]++;
    }
  });
  
  // グラフ用のデータ範囲を作成
  const chartDataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ChartData") || 
                          SpreadsheetApp.getActiveSpreadsheet().insertSheet("ChartData");
  chartDataSheet.getRange("A1").setValue("感情");
  chartDataSheet.getRange("B1").setValue("件数");
  
  let row = 2;
  for (const sentiment in counts) {
    chartDataSheet.getRange(row, 1).setValue(sentiment);
    chartDataSheet.getRange(row, 2).setValue(counts[sentiment]);
    row++;
  }
  
  // グラフを作成
  const chartBuilder = sheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(chartDataSheet.getRange("A1:B" + (row - 1)))
    .setPosition(5, 5, 0, 0)
    .setOption('title', '感情分析結果')
    .setOption('legend', {position: 'none'});
  
  sheet.insertChart(chartBuilder.build());
}

function setupDailyReportTrigger() {
  // 既存のトリガーをすべて削除
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
  
  // 毎日午前9時に実行するトリガーを設定
  ScriptApp.newTrigger('generateDailyReport')
    .timeBased()
    .atHour(9)
    .everyDays(1)
    .create();
}

function processFeedback() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  // ヘッダー行を除いて処理
  for (let row = 2; row <= lastRow; row++) {
    // すでに処理済みの行はスキップ
    if (sheet.getRange(row, 4).getValue() !== '') continue;
    
    const feedback = sheet.getRange(row, 2).getValue();
    if (!feedback) continue;
    
    const prompt = `以下の顧客フィードバックを分析し、JSONフォーマットで以下の情報を返してください：
    1. カテゴリ：「製品品質」「価格」「サービス」「使いやすさ」「その他」のいずれか
    2. 感情：「ポジティブ」「ネガティブ」「中立」のいずれか
    3. 重要ポイント：フィードバックの最も重要なポイントを短く要約（30字以内）
    
    フィードバック: "${feedback}"
    
    回答形式:
    {
      "category": "カテゴリ",
      "sentiment": "感情",
      "keyPoint": "重要ポイント"
    }`;
    
    try {
      const response = callChatGPT(prompt);
      const result = JSON.parse(response);
      
      // 結果をシートに書き込み
      sheet.getRange(row, 3).setValue(result.category);
      sheet.getRange(row, 4).setValue(result.sentiment);
      sheet.getRange(row, 5).setValue(result.keyPoint);
      
      // APIの利用制限を考慮して少し待機
      Utilities.sleep(200);
    } catch (e) {
      Logger.log(`Error processing row ${row}: ${e}`);
      sheet.getRange(row, 3).setValue("エラー");
    }
  }
}

function generateSalesReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("SalesData");
  const reportSheet = ss.getSheetByName("Report") || ss.insertSheet("Report");
  
  // 売上データを取得
  const salesData = dataSheet.getDataRange().getValues();
  const headers = salesData.shift(); // ヘッダー行を除外
  
  // 売上データをテキスト形式に変換
  let salesDataText = "期間,製品,地域,売上額\n";
  salesData.forEach(row => {
    salesDataText += `${row[0]},${row[1]},${row[2]},${row[3]}\n`;
  });
  
  const prompt = `以下の売上データを分析し、今月の売上レポートを作成してください。
  以下の項目を含めてください：
  1. 全体的な売上の概要（前月比も含む）
  2. 最も売上が好調な製品と地域
  3. 注意が必要な下降トレンドがある製品や地域
  4. 来月に向けた推奨アクション
  
  売上データ:
  ${salesDataText}`;
  
  const reportText = callChatGPT(prompt);
  
  // レポートをシートに書き込み
  reportSheet.clear();
  reportSheet.getRange(1, 1).setValue("売上レポート");
  reportSheet.getRange(1, 1).setFontWeight("bold").setFontSize(14);
  reportSheet.getRange(3, 1).setValue(reportText);
  
  // 体裁を整える
  reportSheet.autoResizeColumn(1);
}

function generateCampaignIdeas() {
  const ui = SpreadsheetApp.getUi();
  
  // 製品情報と顧客特性の入力を取得
  const productResponse = ui.prompt("製品情報", "宣伝したい製品の詳細を入力してください:", ui.ButtonSet.OK_CANCEL);
  if (productResponse.getSelectedButton() == ui.Button.CANCEL) return;
  
  const targetResponse = ui.prompt("ターゲット顧客", "ターゲットとする顧客層の特性を入力してください:", ui.ButtonSet.OK_CANCEL);
  if (targetResponse.getSelectedButton() == ui.Button.CANCEL) return;
  
  const productInfo = productResponse.getResponseText();
  const targetInfo = targetResponse.getResponseText();
  
  const prompt = `以下の製品情報とターゲット顧客に基づいて、効果的なマーケティングキャンペーンのアイデアを5つ提案してください。各アイデアには、キャンペーン名、概要、主な訴求ポイント、推奨チャネルを含めてください。
  
  製品情報: ${productInfo}
  ターゲット顧客: ${targetInfo}`;
  
  const ideas = callChatGPT(prompt);
  
  // 結果を新しいシートに表示
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("キャンペーンアイデア");
  sheet.getRange(1, 1).setValue("マーケティングキャンペーンのアイデア");
  sheet.getRange(1, 1).setFontWeight("bold").setFontSize(14);
  sheet.getRange(3, 1).setValue(ideas);
  
  // 体裁を整える
  sheet.autoResizeColumn(1);
  
  ui.alert("完了", "キャンペーンアイデアが生成されました。", ui.ButtonSet.OK);
}

function setupErrorReporting() {
  // エラー発生時に管理者にメール通知
  try {
    // 何らかの処理
  } catch (e) {
    const adminEmail = "admin@example.com";
    const subject = "スプレッドシート自動化システムでエラーが発生しました";
    const body = `エラー内容: ${e.toString()}\n\nスタックトレース: ${e.stack}`;
    
    GmailApp.sendEmail(adminEmail, subject, body);
    Logger.log(e);
  }
}

function showCustomSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('AIアシスタント')
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

// Sidebar.html ファイル内例
// 

<div>
  <h2>AIアシスタント</h2>
  <p>テキストを分析するには、セルを選択して「分析」ボタンをクリックしてください。</p>
  <button onclick="analyzeSelection()">分析</button>
  <div id="result"></div>
  <script>
    function analyzeSelection() {
      google.script.run
        .withSuccessHandler(showResult)
        .analyzeSelectedText();
    }
    function showResult(result) {
      document.getElementById('result').innerHTML = result;
    }
  </script>
</div>

function analyzeAsExpert() {
  const text = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  
  const prompt = `あなたはマーケティングの専門家です。以下のテキストを、ターゲット顧客への訴求力という観点から分析してください。
  分析プロセスは以下のステップで行ってください：
  
  1. まずテキストの主要なメッセージと訴求ポイントを特定する
  2. 次に想定されるターゲット顧客層を推測する
  3. そのターゲット層にとってのメッセージの強みと弱みを評価する
  4. 最後に改善のための具体的な提案をする
  
  それぞれのステップを明確に分けて回答してください。
  
  テキスト: "${text}"`;
  
  return callChatGPT(prompt);
}

function processFormResponses() {
  const form = FormApp.openById('your_form_id');
  const formResponses = form.getResponses();
  
  // 最新の回答のみを処理
  const latestResponse = formResponses[formResponses.length - 1];
  const itemResponses = latestResponse.getItemResponses();
  
  let responseText = "";
  itemResponses.forEach(item => {
    responseText += `質問: ${item.getItem().getTitle()}\n回答: ${item.getResponse()}\n\n`;
  });
  
  const prompt = `以下のフォーム回答を分析し、重要なポイントと必要なフォローアップアクションを3点ずつ挙げてください。
  ${responseText}`;
  
  const analysis = callChatGPT(prompt);
  
  // 分析結果を担当者にメール送信
  GmailApp.sendEmail(
    "manager@example.com",
    "新規フォーム回答の分析",
    `新しいフォーム回答がありました。\n\n${responseText}\n\n分析結果:\n${analysis}`
  );
}

function securelyGetApiKey() {
  const scriptProperties = PropertiesService.getScriptProperties();
  return scriptProperties.getProperty('OPENAI_API_KEY');
}

function setApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('APIキーの設定', 'OpenAI APIキーを入力してください:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() == ui.Button.OK) {
    const apiKey = response.getResponseText();
    PropertiesService.getScriptProperties().setProperty('OPENAI_API_KEY', apiKey);
    ui.alert('APIキーが保存されました');
  }
}