在日常專案管理中,我經常需要追蹤一份公開的問題回報清單。
由於表單會被不同人同時編輯,我想在有人修改內容時,能即時收到 Gmail
通知。
這樣不需要一直開著試算表,就能快速掌握更新狀況。
我的需求可以簡化為三點:\
我採用的設計方式是 時間驅動 + 雜湊比對:
initializeSnapshot()
,建立基準快照(hash + JSON)。\checkSheetChanges()
。\📌 我有加上「冷卻機制」避免短時間被洗版;但若使用者已設定 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);
}
既然是專案管理用.. 那為什麼不用專案管理系統, 有人更新時, 你可以收到通知, 也可以隨時查看進度及問題詳細說明 , 用試算表是...?