iT邦幫忙

3

📊 監聽 Google Sheet 試算表異動,不用再主動查看有沒有變更了。

  • 分享至 

  • xImage
  •  

https://ithelp.ithome.com.tw/upload/images/20250822/20155103IZudxEg4KU.png

🌱 緣由

在日常專案管理中,我經常需要追蹤一份公開的問題回報清單。
由於表單會被不同人同時編輯,我想在有人修改內容時,能即時收到 Gmail
通知。

這樣不需要一直開著試算表,就能快速掌握更新狀況。


🎯 需求

我的需求可以簡化為三點:\

  1. 指定工作表(GID):只監控清單所在的那個工作表,而不是整份檔案。\
  2. 週期性檢查:不用即時觸發,而是設定「每 5 分鐘」檢查一次。\
  3. 寄送通知:只要發現異動,就自動寄出 Gmail,內文要有異動摘要。

⚙️ 核心設計

我採用的設計方式是 時間驅動 + 雜湊比對

  • 初始化:第一次手動執行
    initializeSnapshot(),建立基準快照(hash + JSON)。\
  • 定期檢查:由時間觸發器呼叫 checkSheetChanges()。\
  • 雜湊判斷:將整份工作表內容轉成 SHA-256
    hash,若和上次不同,表示有異動。\
  • 差異比對:將前後快照做簡單 diff,列出修改、新增、刪除的行。\
  • 通知:若有異動,就寄出
    Gmail,內容包含表單網址、工作表名稱、時間與異動摘要。

📌 我有加上「冷卻機制」避免短時間被洗版;但若使用者已設定 5
分鐘時間觸發,冷卻可視情況移除。


💻 範例完整程式碼

以下程式可以直接貼到 script.new
使用,只要修改最上方常數即可:

/************** 需要修改的常數 **************/
const SPREADSHEET_ID = '...'; // 試算表 ID
const TARGET_SHEET_GID = 184321134; // 目標工作表 GID
const ALERT_EMAIL = 'your_email@example.com'; // 通知信箱
const MAX_DIFF_PREVIEW = 10;  // 通知信中最多顯示幾筆摘要
const MIN_NOTIFY_INTERVAL_MIN = 3; // 通知冷卻(分鐘)
/*******************************************/

function checkSheetChanges() {
  const props = PropertiesService.getScriptProperties();
  const now = new Date();
  const lastNotifyIso = props.getProperty('last_notify_iso') || '';

  if (lastNotifyIso) {
    const last = new Date(lastNotifyIso);
    const mins = (now - last) / 60e3;
    if (mins < MIN_NOTIFY_INTERVAL_MIN) {
      console.log(`⏸ 冷卻中:距上次通知僅 ${mins.toFixed(1)} 分鐘`);
      return;
    }
  }

  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = getSheetByGid_(ss, TARGET_SHEET_GID);
  if (!sheet) {
    sendMail_("⚠️ 偵測失敗:找不到工作表", `試算表ID: ${SPREADSHEET_ID}
GID: ${TARGET_SHEET_GID}`);
    return;
  }

  const values = sheet.getDataRange().getDisplayValues();
  const currentHash = hash2Hex_(values);

  const propsKeyHash = `hash_${TARGET_SHEET_GID}`;
  const propsKeySnapshot = `snapshot_${TARGET_SHEET_GID}`;

  const prevHash = props.getProperty(propsKeyHash) || '';
  if (!prevHash) {
    sendMail_("✅ 初始化監控", `建立基準快照,工作表: ${sheet.getName()}
目前雜湊: ${currentHash}`);
    props.setProperty(propsKeyHash, currentHash);
    props.setProperty(propsKeySnapshot, JSON.stringify(values));
    props.setProperty('last_notify_iso', now.toISOString());
    return;
  }

  if (prevHash === currentHash) {
    console.log('✅ 無變更');
    return;
  }

  const prevSnapshot = JSON.parse(props.getProperty(propsKeySnapshot) || '[]');
  const diff = diffMatrix_(prevSnapshot, values);

  const subject = "📢 試算表異動通知";
  const body = [
    `試算表:https://docs.google.com/spreadsheets/d/${SPREADSHEET_ID}/edit#gid=${TARGET_SHEET_GID}`,
    `工作表名稱:${sheet.getName()}`,
    `觸發時間:${formatDateTime_(now)}`,
    '',
    `👉 異動摘要(最多顯示 ${MAX_DIFF_PREVIEW} 筆):`,
    renderDiffPreview_(diff, MAX_DIFF_PREVIEW),
    '',
    '— 這封信由 Apps Script 自動發送 —'
  ].join('\n');

  sendMail_(subject, body);

  props.setProperty(propsKeyHash, currentHash);
  props.setProperty(propsKeySnapshot, JSON.stringify(values));
  props.setProperty('last_notify_iso', now.toISOString());
}

function initializeSnapshot() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = getSheetByGid_(ss, TARGET_SHEET_GID);
  const values = sheet.getDataRange().getDisplayValues();
  const currentHash = hash2Hex_(values);

  const props = PropertiesService.getScriptProperties();
  props.setProperty(`hash_${TARGET_SHEET_GID}`, currentHash);
  props.setProperty(`snapshot_${TARGET_SHEET_GID}`, JSON.stringify(values));
  props.setProperty('last_notify_iso', new Date().toISOString());

  Logger.log('初始化完成');
}

/* ========== 小工具區 ========== */
function getSheetByGid_(ss, gid) {
  return ss.getSheets().find(sh => sh.getSheetId() === Number(gid)) || null;
}

function hash2Hex_(matrix) {
  const flat = matrix.map(r => r.join('\u241F')).join('\u241E');
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    Utilities.newBlob(flat, 'text/plain').getBytes()
  );
  return digest.map(b => (b + 256) % 256).map(b => b.toString(16).padStart(2, '0')).join('');
}

function diffMatrix_(prev, curr) {
  const prevLines = prev.map(r => r.join(' | '));
  const currLines = curr.map(r => r.join(' | '));

  const maxLen = Math.max(prevLines.length, currLines.length);
  const modified = [];
  for (let i = 0; i < maxLen; i++) {
    if ((prevLines[i] || '') !== (currLines[i] || '')) {
      modified.push({ row: i + 1, before: prevLines[i], after: currLines[i] });
    }
  }

  const added = currLines.filter(line => !prevLines.includes(line));
  const removed = prevLines.filter(line => !currLines.includes(line));

  return { modified, added, removed };
}

function renderDiffPreview_(diff, limit) {
  const lines = [];
  const pushLimited = (title, arr, mapper) => {
    if (!arr || arr.length === 0) return;
    lines.push(`▪ ${title}(${arr.length})`);
    arr.slice(0, limit).forEach((item, i) => lines.push(`  ${i + 1}. ${mapper(item)}`));
    if (arr.length > limit) lines.push(`  …(其餘 ${arr.length - limit} 筆省略)`);
    lines.push('');
  };
  pushLimited('位置異動', diff.modified, it => `第 ${it.row} 列\n     前:${it.before}\n     後:${it.after}`);
  pushLimited('新增行', diff.added, s => s);
  pushLimited('刪除行', diff.removed, s => s);
  return lines.length ? lines.join('\n') : '(找不到可顯示的差異)';
}

function formatDateTime_(d) {
  return Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
}

function sendMail_(subject, body) {
  MailApp.sendEmail(ALERT_EMAIL, subject, body);
}

圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
Kailis
iT邦研究生 1 級 ‧ 2025-08-26 16:13:49

既然是專案管理用.. 那為什麼不用專案管理系統, 有人更新時, 你可以收到通知, 也可以隨時查看進度及問題詳細說明 , 用試算表是...?

我要留言

立即登入留言