1 篇文章 含有標籤「Google試算表」

/**
 * 職場 AI 加速術：課程專屬 LINE BOT 助教
 * 功能：文字/圖片問答、5 則上下文記憶、Flex Message 輪播卡片回覆、聯外按鈕動態生成、進階心法建議
 */

//  =========================================================================
// 1. 基本設定
// =========================================================================
const SCRIPT_PROPERTIES = PropertiesService.getScriptProperties();
const LINE_CHANNEL_ACCESS_TOKEN = SCRIPT_PROPERTIES.getProperty('LINE_CHANNEL_ACCESS_TOKEN');
const GEMINI_API_KEY = SCRIPT_PROPERTIES.getProperty('GEMINI_API_KEY');
const SPREADSHEET_ID = SCRIPT_PROPERTIES.getProperty('SPREADSHEET_ID');
const WEBHOOK_SECRET = SCRIPT_PROPERTIES.getProperty('WEBHOOK_SECRET');

const SHEET_NAME_HISTORY = '聊天紀錄';
const SHEET_NAME_QA = 'QA知識庫';
const GEMINI_MODEL = 'gemini-3.1-flash-lite-preview';

function assertRequiredConfig() {
  const missingKeys = [];

  if (!LINE_CHANNEL_ACCESS_TOKEN) missingKeys.push('LINE_CHANNEL_ACCESS_TOKEN');
  if (!GEMINI_API_KEY) missingKeys.push('GEMINI_API_KEY');
  if (!SPREADSHEET_ID) missingKeys.push('SPREADSHEET_ID');

  if (missingKeys.length > 0) {
    throw new Error(`缺少必要的指令碼屬性：${missingKeys.join(', ')}`);
  }
}

function isValidWebhookRequest(e) {
  if (!WEBHOOK_SECRET) {
    return true;
  }

  return e && e.parameter && e.parameter.key === WEBHOOK_SECRET;
}

// =========================================================================
// 2. 主程式入口：接收 LINE 的 Webhook 事件
// =========================================================================
function doPost(e) {
  try {
    assertRequiredConfig();

    if (!isValidWebhookRequest(e)) {
      console.warn('Webhook secret 驗證失敗');
      return ContentService.createTextOutput('Forbidden');
    }

    const eventData = JSON.parse(e.postData.contents);
    const events = eventData.events;

    if (!events || events.length === 0) {
      return ContentService.createTextOutput('OK');
    }

    for (let i = 0; i < events.length; i++) {
      const event = events[i];
      const replyToken = event.replyToken;
      const userId = event.source.userId;

      if (event.type !== 'message') {
        continue;
      }

      let userMessage = '';
      let base64Image = null;

      if (event.message.type === 'text') {
        userMessage = event.message.text;
      } else if (event.message.type === 'image') {
        base64Image = getLineImageContent(event.message.id);
        userMessage = '請分析這張圖片，並解答我的問題或提供課程相關建議。';
      } else {
        continue;
      }

      const history = getHistory(userId);
      const aiResponse = callGeminiAPI(userMessage, base64Image, history);

      history.push({ role: 'user', parts: [{ text: userMessage }] });
      history.push({ role: 'model', parts: [{ text: JSON.stringify(aiResponse) }] });
      saveHistory(userId, history);
      saveLogToSheet(userId, userMessage, aiResponse);
      replyWithFlexMessage(replyToken, aiResponse);
    }

    return ContentService.createTextOutput('OK');
  } catch (error) {
    console.error(error);
    return ContentService.createTextOutput('Error');
  }
}

// =========================================================================
// 3. LINE API 溝通相關函式
// =========================================================================

function getLineImageContent(messageId) {
  const url = `https://api-data.line.me/v2/bot/message/${messageId}/content`;
  const options = {
    method: 'get',
    headers: {
      Authorization: `Bearer ${LINE_CHANNEL_ACCESS_TOKEN}`,
    },
  };

  const response = UrlFetchApp.fetch(url, options);
  const blob = response.getBlob();
  return Utilities.base64Encode(blob.getBytes());
}

function replyWithFlexMessage(replyToken, aiData) {
  const url = 'https://api.line.me/v2/bot/message/reply';
  const bubbles = [];

  const answerBubble = {
    type: 'bubble',
    header: {
      type: 'box',
      layout: 'vertical',
      contents: [
        {
          type: 'text',
          text: '👨‍🏫 助教解答',
          weight: 'bold',
          size: 'xl',
          color: '#ffffff',
        },
      ],
      backgroundColor: '#2980b9',
    },
    body: {
      type: 'box',
      layout: 'vertical',
      contents: [
        {
          type: 'text',
          text: aiData.answer,
          wrap: true,
          size: 'md',
        },
      ],
    },
  };

  if (aiData.action_url && aiData.action_url.startsWith('http')) {
    answerBubble.footer = {
      type: 'box',
      layout: 'vertical',
      spacing: 'sm',
      contents: [
        {
          type: 'button',
          style: 'primary',
          color: '#2980b9',
          height: 'sm',
          action: {
            type: 'uri',
            label: aiData.action_label || '🔗 點擊前往',
            uri: aiData.action_url,
          },
        },
      ],
      flex: 0,
    };
  }

  bubbles.push(answerBubble);

  if (aiData.tips && aiData.tips.length > 0) {
    aiData.tips.forEach(function (tip, index) {
      bubbles.push({
        type: 'bubble',
        header: {
          type: 'box',
          layout: 'vertical',
          contents: [
            {
              type: 'text',
              text: `💡 優化心法 ${index + 1}`,
              weight: 'bold',
              size: 'lg',
              color: '#ffffff',
            },
          ],
          backgroundColor: '#e67e22',
        },
        body: {
          type: 'box',
          layout: 'vertical',
          contents: [
            {
              type: 'text',
              text: tip.title,
              weight: 'bold',
              size: 'md',
              color: '#d35400',
              wrap: true,
            },
            {
              type: 'separator',
              margin: 'md',
            },
            {
              type: 'text',
              text: tip.desc,
              wrap: true,
              size: 'sm',
              margin: 'md',
            },
          ],
        },
      });
    });
  }

  const payload = {
    replyToken: replyToken,
    messages: [
      {
        type: 'flex',
        altText: '助教回覆了您的問題！',
        contents: {
          type: 'carousel',
          contents: bubbles,
        },
      },
    ],
  };

  const options = {
    method: 'post',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${LINE_CHANNEL_ACCESS_TOKEN}`,
    },
    payload: JSON.stringify(payload),
  };

  UrlFetchApp.fetch(url, options);
}

// =========================================================================
// 4. Gemini AI 溝通相關函式
// =========================================================================

function callGeminiAPI(userMessage, base64Image, history) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent?key=${GEMINI_API_KEY}`;
  const qaContext = getQAKnowledge();

  const systemInstruction = `
你是「職場 AI 加速術：NotebookLM 與雲端工具應用」課程的專屬 AI 助教。
你的任務是解決學員關於 Google 表單、Google 試算表（公式、樞紐分析、圖表）、NotebookLM 與 GAS 自動化的問題。
如果使用者上傳了圖片（例如報錯截圖或資料截圖），請仔細觀察圖片內容找出錯誤或給予建議。

${qaContext}

【回覆格式嚴格限制】
你必須直接回傳一個純 JSON 格式的物件，不能包含 markdown 的 json 區塊標籤，格式如下：
{
  "answer": "直接且親切地回答使用者的問題或報錯原因...",
  "action_label": "按鈕文字（若解答中需要提供網址，請給簡短行動呼籲；若無網址請留空）",
  "action_url": "若解答中需要提供網址連結請填入此處（需為 http 或 https 開頭；若無網址請留空）",
  "tips": [
    {
      "title": "進階心法標題",
      "desc": "如何做得更好的具體建議或觀念說明..."
    }
  ]
}
`;

  const currentMessageParts = [];

  if (base64Image) {
    currentMessageParts.push({
      inlineData: {
        mimeType: 'image/jpeg',
        data: base64Image,
      },
    });
  }

  currentMessageParts.push({ text: userMessage });

  const contents = history.concat([
    {
      role: 'user',
      parts: currentMessageParts,
    },
  ]);

  const payload = {
    systemInstruction: {
      parts: [{ text: systemInstruction }],
    },
    contents: contents,
    generationConfig: {
      responseMimeType: 'application/json',
    },
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());

  try {
    const responseText = json.candidates[0].content.parts[0].text;
    return JSON.parse(responseText);
  } catch (e) {
    return {
      answer: '抱歉，助教剛剛有點當機，請再試一次或換個說法詢問喔！',
      action_label: '',
      action_url: '',
      tips: [],
    };
  }
}

// =========================================================================
// 5. 對話紀錄（上下文）快取管理
// =========================================================================

function getHistory(userId) {
  const cache = CacheService.getScriptCache();
  const historyStr = cache.get(`history_${userId}`);

  if (historyStr) {
    return JSON.parse(historyStr);
  }

  return [];
}

function saveHistory(userId, newHistory) {
  const cache = CacheService.getScriptCache();

  if (newHistory.length > 10) {
    newHistory = newHistory.slice(newHistory.length - 10);
  }

  cache.put(`history_${userId}`, JSON.stringify(newHistory), 1800);
}

// =========================================================================
// 6. Google 試算表整合函式
// =========================================================================

function getQAKnowledge() {
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME_QA);

    if (!sheet) {
      return '';
    }

    const data = sheet.getDataRange().getValues();

    if (data.length <= 1) {
      return '';
    }

    let qaString = '【以下為課程專屬 QA 知識庫，若學員詢問相關問題，請優先參考以下內容並整合進回覆：】\n';

    for (let i = 1; i < data.length; i++) {
      const question = data[i][0];
      const answer = data[i][1];

      if (question && answer) {
        qaString += `Q: ${question}\nA: ${answer}\n\n`;
      }
    }

    return qaString;
  } catch (e) {
    console.error('讀取 QA 知識庫失敗:', e);
    return '';
  }
}

function saveLogToSheet(userId, userMessage, aiResponse) {
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME_HISTORY);

    if (!sheet) {
      return;
    }

    const timestamp = new Date();
    const aiAnswerText = aiResponse.answer || '無文字回應';
    sheet.appendRow([timestamp, userId, userMessage, aiAnswerText]);
  } catch (e) {
    console.error('儲存紀錄至試算表失敗:', e);
  }
}