Apps Script 時間觸發器：每週一早上自動寄出值班表

/**
 * @OnlyCurrentDoc
 */

const SHEET_NAME = "值班表";
const HANDLER = "sendWeeklyRoster";
const TEST_RECIPIENT = "you@example.com"; // 換成你要收信的信箱；收件者寫在程式裡，不從儲存格讀

function isDate(value) {
  return Object.prototype.toString.call(value) === "[object Date]";
}

// 這一天所屬那一週的週一 00:00（週日算前一個週一）
function mondayOf(date) {
  const monday = new Date(date.getFullYear(), date.getMonth(), date.getDate());
  monday.setDate(monday.getDate() - ((monday.getDay() + 6) % 7));
  return monday;
}

function formatDay(date) {
  const pad = (n) => (n < 10 ? "0" + n : String(n));
  return date.getFullYear() + "-" + pad(date.getMonth() + 1) + "-" + pad(date.getDate());
}

// 把某個時間點換成指定時區看到的年月日（本地午夜），腳本時區與試算表時區不同時才不會差一天
function dayInZone(date, timeZone) {
  const [year, month, day] = Utilities.formatDate(date, timeZone, "yyyy-MM-dd").split("-").map(Number);
  return new Date(year, month - 1, day);
}

// rows 每列：[週一日期, 值班人, 備援]；只比年月日，找不到回傳 null
function pickWeekRow(rows, monday) {
  const found = rows.find((row) => isDate(row[0]) && mondayOf(row[0]).getTime() === monday.getTime());
  return found || null;
}

function buildRosterMessage(row, monday) {
  return {
    subject: "本週值班表（" + formatDay(monday) + " 起）",
    body: "值班人：" + row[1] + "\n備援：" + row[2],
  };
}

// 觸發器要執行的函式：用「現在」算出這週的週一，找到那一列才寄
function sendWeeklyRoster() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = book.getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error("找不到分頁「" + SHEET_NAME + "」");
  }
  const timeZone = book.getSpreadsheetTimeZone();
  const monday = mondayOf(dayInZone(new Date(), timeZone));
  const last = sheet.getLastRow();
  const rows = (last < 2 ? [] : sheet.getRange(2, 1, last - 1, 3).getValues()).map((row) =>
    isDate(row[0]) ? [dayInZone(row[0], timeZone), row[1], row[2]] : row
  );
  const row = pickWeekRow(rows, monday);
  if (row === null) {
    Logger.log("值班表沒有 " + formatDay(monday) + " 這週，不寄信");
    return;
  }
  const message = buildRosterMessage(row, monday);
  MailApp.sendEmail(TEST_RECIPIENT, message.subject, message.body);
  Logger.log("已寄出：" + message.subject);
}

// 只刪自己這個函式的觸發器，回傳刪了幾個
function removeWeeklyTrigger() {
  let removed = 0;
  ScriptApp.getProjectTriggers().forEach((trigger) => {
    if (trigger.getHandlerFunction() === HANDLER) {
      ScriptApp.deleteTrigger(trigger);
      removed++;
    }
  });
  return removed;
}

// 先清掉舊的再建，連按幾次都只會留一個
function installWeeklyTrigger() {
  removeWeeklyTrigger();
  ScriptApp.newTrigger(HANDLER).timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(8).create();
}

function listTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  if (triggers.length === 0) {
    Logger.log("沒有觸發器");
    return;
  }
  triggers.forEach((trigger) => {
    Logger.log(trigger.getHandlerFunction() + " / " + trigger.getEventType());
  });
}