Apps Script 批次讀寫：一次讀、整理、一次寫回客戶名單

/**
 * @OnlyCurrentDoc
 */

// 把可辨識的台灣手機整理成 0912-345-678；其他原樣保留並標記 ok=false
function normalizePhone(raw) {
  const original = String(raw === null || raw === undefined ? "" : raw);
  let digits = original.replace(/\D/g, "");
  if (digits.length === 12 && digits.startsWith("886")) {
    digits = "0" + digits.slice(3);
  } else if (digits.length === 9 && digits.startsWith("9")) {
    digits = "0" + digits; // 在試算表輸入成數字時，前導 0 會消失
  }
  if (/^09\d{8}$/.test(digits)) {
    return { value: digits.slice(0, 4) + "-" + digits.slice(4, 7) + "-" + digits.slice(7), ok: true };
  }
  return { value: original, ok: false };
}

// 輸入 [姓名, 電話, 地區] 的二維陣列，回傳整理後的陣列與計數；不改變列數與欄數
function cleanCustomerRows(rows) {
  let changed = 0;
  let flagged = 0;
  const cleaned = rows.map((row) => {
    if (row.every((cell) => cell === "" || cell === null)) {
      return row.slice();
    }
    const name = String(row[0]).trim();
    const phone = normalizePhone(row[1]);
    const next = [name, phone.ok ? phone.value : row[1], row[2]]; // 認不出的電話連型別都不動
    if (String(row[1]) !== "" && !phone.ok) {
      flagged++;
    }
    if (next.some((cell, i) => cell !== row[i])) {
      changed++;
    }
    return next;
  });
  return { rows: cleaned, changed: changed, flagged: flagged };
}

function runCleanCustomers() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("客戶名單");
  if (!sheet) {
    throw new Error("找不到「客戶名單」分頁。");
  }
  const header = sheet.getRange("A1:C1").getValues()[0];
  if (header.join("|") !== "姓名|電話|地區") {
    throw new Error("客戶名單 A1:C1 必須依序是：姓名、電話、地區。");
  }
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    return;
  }
  const range = sheet.getRange("A2:C" + lastRow);
  const result = cleanCustomerRows(range.getValues());
  range.setValues(result.rows);
  Logger.log("整理 " + result.rows.length + " 列：修改 " + result.changed + "、需人工檢查 " + result.flagged);
}