🚀 緣由

在日常維護專案的過程中，我常需要追蹤 Google 試算表的異動情況。
一開始，我寫了一支簡單的 Apps Script，只能監控單一工作表 GID，若有變動就寄信通知。
雖然能解決當下需求，但很快遇到一些限制：

• 每個專案、部門都希望監控不同的試算表工作表。
• 收件人清單需要靈活調整，而不是程式碼裡硬編。
• 異動摘要如果太雜，通知信就會變得難以閱讀。

因此，我開始逐步改寫，讓它能更彈性化。

📝 需求

1. 多 GID 支援：不同專案的 GID 都能集中管理。
2. 收件人清單動態化：由一張「設定表」決定要通知誰。
3. 通知冷卻：避免風暴期狂寄信。
4. 通知內容可控：只想顯示「位置異動」，而非所有集合差異。

⚙️ 核心設計

設定表結構

我建立了一張專門的「收件設定表」（GID=1379581113），從第3列開始填：

程式會依照 A欄的 GID 去找對應的工作表，通知寄給 C欄的收件人，郵件標題則帶上 B欄的標籤。

開關控制

有時候我只想看「位置異動」，不想被新增刪除行刷爆。
於是我加了一個開關：

const SHOW_ADDED_REMOVED = false; 

只要把它改成 true，就能再顯示集合差異。

多 GID 流程

• checkAllTargets()：一次巡所有設定表裡的 GID。
• checkTargetByGid(gid)：只檢查單一 GID（方便測試）。
• initializeSnapshotAll()：所有 GID 建立快照（初始執行）。
• initializeSnapshotByGid(gid)：單一 GID 初始化。

🤖 適合詢問 GPT 的 Prompt

如果未來想做擴充，我會用這樣的 prompt：

請幫我在既有的 Apps Script 裡，針對「設定表」再新增一欄位（例如 D 欄），
用來控制每個 GID 的通知冷卻時間，並讓程式能根據該欄位動態調整。

💻 範例完整程式碼

/************** 可調整常數 **************/
const SPREADSHEET_ID = '...'; // 試算表 ID
const ALERT_SHEET_GID = 1379581113; // 「GID/標籤/Email 列表」所在工作表 GID
const MAX_DIFF_PREVIEW = 10;        // 通知信中最多顯示幾筆異動摘要
const MIN_NOTIFY_INTERVAL_MIN = 3;   // 每個 GID 的通知冷卻（分鐘）
const SHOW_ADDED_REMOVED = false;  // 預設只顯示「位置異動」。要看新增/刪除就改成 true


// 設定表欄位（0-based）
const COL_GID   = 0; // A 欄
const COL_LABEL = 1; // B 欄（會放進標題括號）
const COL_EMAIL = 2; // C 欄（收件者）
// 讀取起始列（第3列開始）
const START_ROW_INDEX = 2;
/****************************************/

/** 建議把時間觸發器掛這個：會自動找設定表的所有 GID 逐一檢查 */
function checkAllTargets() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const targetGids = getDistinctTargetGids_(ss);
  if (!targetGids.length) {
    throw new Error(`在 gid=${ALERT_SHEET_GID} 的 A欄（第3列起）找不到任何 GID`);
  }

  for (const gid of targetGids) {
    try {
      checkTarget_(ss, gid);
    } catch (err) {
      const pack = getAlertPackForGid_(ss, gid); // {emails, labelText}
      if (pack.emails.length) {
        MailApp.sendEmail(
          pack.emails.join(','),
          `⚠️ 監控失敗（${pack.labelText || '未命名'}）`,
          `錯誤訊息：${String(err && err.stack || err)}\n\n請確認工作表是否存在、權限是否正確。`
        );
      }
      console.error(`GID ${gid} 失敗：`, err);
    }
  }
}

/** 手動檢查單一 GID（除錯/臨時用） */
function checkTargetByGid(gid) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  checkTarget_(ss, String(gid));
}

/** 初始化：為所有 GID 建立快照（先手動跑一次） */
function initializeSnapshotAll() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const gids = getDistinctTargetGids_(ss);
  gids.forEach(gid => initializeSnapshotByGid(gid));
}

/** 初始化：只為單一 GID 建立快照（手動跑） */
function initializeSnapshotByGid(gid) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = getSheetByGid_(ss, Number(gid));
  if (!sheet) throw new Error(`找不到指定工作表（gid=${gid}）`);

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

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

  Logger.log(`初始化完成 gid=${gid}（${sheet.getName()}）`);
}

/* ================== 核心邏輯（每個 GID） ================== */

function checkTarget_(ss, targetGid) {
  const props = PropertiesService.getScriptProperties();
  const now = new Date();

  // 每個 GID 的冷卻
  const lastKey = `last_notify_iso_${targetGid}`;
  const lastNotifyIso = props.getProperty(lastKey) || '';
  if (lastNotifyIso) {
    const last = new Date(lastNotifyIso);
    const mins = (now - last) / 60e3;
    if (mins < MIN_NOTIFY_INTERVAL_MIN) {
      console.log(`⏸ gid=${targetGid} 冷卻中：距上次通知僅 ${mins.toFixed(1)} 分鐘`);
      return;
    }
  }

  const sheet = getSheetByGid_(ss, Number(targetGid));
  const pack = getAlertPackForGid_(ss, targetGid); // {emails, labelText}
  const recipients = pack.emails;
  const labelText = pack.labelText || `gid=${targetGid}`;

  if (!recipients.length) {
    throw new Error(`gid=${targetGid} 的收件名單為空，請在設定表（gid=${ALERT_SHEET_GID}）第3列起填 A欄(GID)/B欄(標籤)/C欄(Email)`);
  }

  if (!sheet) {
    MailApp.sendEmail(
      recipients.join(','),
      `⚠️ 偵測失敗（${labelText}）`,
      `目標試算表ID：${SPREADSHEET_ID}\nGID：${targetGid}\n請確認 GID 是否正確。`
    );
    return;
  }

  // 取值（用 displayValues 比較直覺，避免公式導致差異）
  const values = sheet.getDataRange().getDisplayValues();
  const currentHash = hash2Hex_(values);

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

  const prevHash = props.getProperty(propsKeyHash) || '';
  if (!prevHash) {
    MailApp.sendEmail(
      recipients.join(','),
      `✅ 已初始化監控（${labelText}）`,
      `已建立基準快照。\n工作表：${sheet.getName()} (gid=${targetGid})\n目前雜湊：${currentHash}\n\n之後將由排程偵測異動。`
    );
    props.setProperty(propsKeyHash, currentHash);
    props.setProperty(propsKeySnapshot, JSON.stringify(values));
    props.setProperty(lastKey, now.toISOString());
    return;
  }

  if (prevHash === currentHash) {
    console.log(`✅ 無變更（gid=${targetGid}）`);
    return;
  }

  // 有變動 → 計算差異
  const prevSnapshot = JSON.parse(props.getProperty(propsKeySnapshot) || '[]');
  const diff = diffMatrix_(prevSnapshot, values);

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

  MailApp.sendEmail(recipients.join(','), subject, body);

  // 更新基準
  props.setProperty(propsKeyHash, currentHash);
  props.setProperty(propsKeySnapshot, JSON.stringify(values));
  props.setProperty(lastKey, now.toISOString());
}

/* ================== 小工具區 ================== */

/** 從設定表讀 A 欄（第3列起）取得所有不同的 GID（字串） */
function getDistinctTargetGids_(ss) {
  const sh = getSheetByGid_(ss, ALERT_SHEET_GID);
  if (!sh) throw new Error(`找不到收件設定表（gid=${ALERT_SHEET_GID}）`);
  const rows = sh.getDataRange().getDisplayValues();

  const s = new Set();
  for (let i = START_ROW_INDEX; i < rows.length; i++) {
    const gid = String(rows[i][COL_GID] || '').trim();
    if (!gid) continue;
    if (!/^\d+$/.test(gid)) continue; // 只接受純數字
    s.add(gid);
  }
  return Array.from(s);
}

/** 依 GID 取 Email 與標籤集合
 *  - rows[START_ROW_INDEX..] 內，A欄=targetGid → 取 B欄(標籤)、C欄(Email)
 *  - Email/標籤各自去重；標題括號使用所有標籤串成字串
 */
function getAlertPackForGid_(ss, targetGid) {
  const sh = getSheetByGid_(ss, ALERT_SHEET_GID);
  if (!sh) throw new Error(`找不到收件設定表（gid=${ALERT_SHEET_GID}）`);
  const rows = sh.getDataRange().getDisplayValues();

  const emailSet = new Set();
  const labelSet = new Set();

  for (let i = START_ROW_INDEX; i < rows.length; i++) {
    const gidCell = String(rows[i][COL_GID] || '').trim();
    if (gidCell !== String(targetGid)) continue;

    const label = String(rows[i][COL_LABEL] || '').trim();
    const email = String(rows[i][COL_EMAIL] || '').trim();

    if (label) labelSet.add(label);
    if (email && isValidEmail_(email)) emailSet.add(email);
  }

  const labelText = Array.from(labelSet).join(', ');
  return { emails: Array.from(emailSet), labelText };
}

/** 依 GID 取得工作表 */
function getSheetByGid_(ss, gid) {
  const sheets = ss.getSheets();
  for (const sh of sheets) {
    if (sh.getSheetId() === Number(gid)) return sh;
  }
  return null;
}

/** 將二維陣列做雜湊（SHA-256） */
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('');
}

/** 簡易 diff */
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++) {
    const a = prevLines[i] || '';
    const b = currLines[i] || '';
    if (a !== b) modified.push({ row: i + 1, before: a, after: b });
  }

  const prevSet = new Set(prevLines);
  const currSet = new Set(currLines);
  const added = [];
  for (const line of currSet) if (!prevSet.has(line)) added.push(line);
  const removed = [];
  for (const line of prevSet) if (!currSet.has(line)) removed.push(line);

  return { modified, added, removed };
}

function renderDiffPreview_(diff, limit) {
  const lines = [];

  // 位置異動（同列內容變更）— 永遠顯示
  if (diff.modified && diff.modified.length > 0) {
    lines.push(`▪ 位置異動（${diff.modified.length}）`);
    diff.modified.slice(0, limit).forEach((it, i) => {
      lines.push(`  ${i + 1}. 第 ${it.row} 列`);
      lines.push(`     前：${truncate_(it.before)}`);
      lines.push(`     後：${truncate_(it.after)}`);
    });
    if (diff.modified.length > limit) {
      lines.push(`  …（其餘 ${diff.modified.length - limit} 筆省略）`);
    }
    lines.push('');
  }

  // 集合差異（新增/刪除）— 受開關控制
  if (SHOW_ADDED_REMOVED) {
    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.added, s => truncate_(s));
    pushLimited('刪除行（集合差異）', diff.removed, s => truncate_(s));
  }

  return lines.length ? lines.join('\n') : '（找不到可顯示的差異）';
}


function truncate_(s, max = 180) {
  if (typeof s !== 'string') s = String(s);
  return s.length > max ? s.slice(0, max) + '…' : s;
}

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

function isValidEmail_(s) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(s);
}