Apps Script 匯入對帳檔：同一份檔案重跑，不會多出重複列

/**
 * @OnlyCurrentDoc
 */

const HEADER = ["交易編號", "日期", "金額", "摘要"];

// 鍵：轉成文字、去頭尾空白。數字 123 與文字 "00123" 不是同一個鍵，所以交易編號欄要設成純文字
function normalizeKey(raw) {
  return raw === null || raw === undefined ? "" : String(raw).trim();
}

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

// 比對其他欄位用：日期比時間，其他比文字
function cellText(value) {
  return isDate(value) ? String(value.getTime()) : normalizeKey(value);
}

// 規劃：只分類，不寫任何東西
function planImport(existing, incoming) {
  const known = new Map();
  existing.forEach((row) => {
    const key = normalizeKey(row[0]);
    if (key !== "" && !known.has(key)) {
      known.set(key, row);
    }
  });
  const seen = new Set();
  const result = { toAdd: [], same: 0, conflicts: [], dupInFile: 0, emptyKey: 0 };
  incoming.forEach((row) => {
    const key = normalizeKey(row[0]);
    if (key === "") {
      result.emptyKey++;
      return;
    }
    if (seen.has(key)) {
      result.dupInFile++;
      return;
    }
    seen.add(key);
    const old = known.get(key);
    if (!old) {
      result.toAdd.push([key].concat(row.slice(1)));
    } else if (old.slice(1).every((value, i) => cellText(value) === cellText(row[i + 1]))) {
      result.same++;
    } else {
      result.conflicts.push({ key: key, existing: old, incoming: row });
    }
  });
  return result;
}

function getSheet(book, name) {
  const sheet = book.getSheetByName(name);
  if (!sheet) {
    throw new Error("找不到分頁「" + name + "」");
  }
  return sheet;
}

function checkHeader(sheet, name) {
  const actual = sheet.getRange(1, 1, 1, HEADER.length).getValues()[0];
  if (HEADER.some((title, i) => normalizeKey(actual[i]) !== title)) {
    throw new Error("「" + name + "」的表頭要依序是：" + HEADER.join("、"));
  }
}

function readRows(sheet, cols) {
  const last = sheet.getLastRow();
  return last < 2 ? [] : sheet.getRange(2, 1, last - 1, cols).getValues();
}

function describeRow(row, timeZone) {
  return row
    .slice(1)
    .map((value) => (isDate(value) ? Utilities.formatDate(value, timeZone, "yyyy-MM-dd") : String(value)))
    .join(" / ");
}

// 接在最後一列之後；第 1 欄先設成純文字，編號才不會被轉成數字
function appendRows(sheet, rows, cols) {
  if (rows.length === 0) {
    return;
  }
  const start = sheet.getLastRow() + 1;
  sheet.getRange(start, 1, rows.length, 1).setNumberFormat("@");
  sheet.getRange(start, 1, rows.length, cols).setValues(rows);
}

function runImport() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const source = getSheet(book, "匯入");
  const detail = getSheet(book, "對帳明細");
  const pending = getSheet(book, "待確認");
  checkHeader(source, "匯入");
  checkHeader(detail, "對帳明細");

  const plan = planImport(readRows(detail, 4), readRows(source, 4));
  const timeZone = book.getSpreadsheetTimeZone();
  const listed = new Set(readRows(pending, 3).map((row) => JSON.stringify(row)));
  const fresh = plan.conflicts
    .map((c) => [c.key, describeRow(c.existing, timeZone), describeRow(c.incoming, timeZone)])
    .filter((row) => !listed.has(JSON.stringify(row)));

  appendRows(detail, plan.toAdd, 4);
  appendRows(pending, fresh, 3);
  Logger.log(
    "新增 " + plan.toAdd.length +
      "、相同 " + plan.same +
      "、待確認 " + plan.conflicts.length + "（新列入 " + fresh.length + "）" +
      "、檔內重複 " + plan.dupInFile +
      "、空鍵 " + plan.emptyKey
  );
}