重複行を削除する前に残す行を確認｜GASで候補をdry-run一覧化

const DUPLICATE_PLAN_CONFIG = {
  sourceSheetName: 'データ',
  keyHeaders: ['管理ID'],
  updatedAtHeader: '更新日時',
  maxRows: 1000,
  dryRun: true,
  reportPrefix: '重複確認'
};

function previewDuplicateKeepPlanFree() {
  const config = DUPLICATE_PLAN_CONFIG;
  if (config.dryRun !== true) {
    throw new Error('安全のためdryRun=true以外では実行できません。');
  }
  if (!Array.isArray(config.keyHeaders) || config.keyHeaders.length === 0) {
    throw new Error('keyHeadersを1列以上指定してください。');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const source = spreadsheet.getSheetByName(config.sourceSheetName);
  if (!source) {
    throw new Error('元シートが見つかりません: ' + config.sourceSheetName);
  }

  const values = source.getDataRange().getValues();
  if (values.length < 2) {
    throw new Error('見出し行と1行以上のデータを用意してください。');
  }
  if (values.length - 1 > config.maxRows) {
    throw new Error('確認上限を超えています。行数: ' + (values.length - 1));
  }

  const headers = values[0].map(normalizeCellFree_);
  const requiredHeaders = config.keyHeaders.concat([config.updatedAtHeader]);
  const missingHeaders = requiredHeaders.filter(function(header) {
    return headers.indexOf(normalizeCellFree_(header)) === -1;
  });
  if (missingHeaders.length > 0) {
    throw new Error('不足している見出し: ' + missingHeaders.join(', '));
  }
  if (new Set(headers).size !== headers.length) {
    throw new Error('同名の見出しがあります。1行目を確認してください。');
  }

  const keyIndexes = config.keyHeaders.map(function(header) {
    return headers.indexOf(normalizeCellFree_(header));
  });
  const updatedAtIndex = headers.indexOf(normalizeCellFree_(config.updatedAtHeader));
  const groups = new Map();
  const invalidRows = [];

  values.slice(1).forEach(function(row, offset) {
    const rowNumber = offset + 2;
    if (row.every(function(value) { return normalizeCellFree_(value) === ''; })) return;

    const keyParts = keyIndexes.map(function(index) {
      return normalizeCellFree_(row[index]).toLowerCase();
    });
    if (keyParts.some(function(value) { return value === ''; })) {
      invalidRows.push(rowNumber);
      return;
    }

    const normalizedKey = keyParts.join('|');
    const item = {
      rowNumber: rowNumber,
      keyHash: shortHashFree_(normalizedKey),
      updatedAt: toTimestampFree_(row[updatedAtIndex]),
      filledCount: row.filter(function(value) {
        return normalizeCellFree_(value) !== '';
      }).length
    };
    if (!groups.has(normalizedKey)) groups.set(normalizedKey, []);
    groups.get(normalizedKey).push(item);
  });

  const reportRows = [];
  let duplicateGroups = 0;
  groups.forEach(function(items) {
    if (items.length < 2) return;
    duplicateGroups += 1;
    items.sort(compareKeepCandidateFree_);
    items.forEach(function(item, index) {
      reportRows.push([
        item.keyHash,
        item.rowNumber,
        index === 0 ? '残す候補' : '要確認',
        index === 0 ? '更新日時と入力数を比較した先頭候補' : '削除せず内容を目視確認',
        item.updatedAt === null ? '無効または空欄' : '有効',
        item.filledCount
      ]);
    });
  });

  const timeZone = Session.getScriptTimeZone() || 'Asia/Tokyo';
  const suffix = Utilities.formatDate(new Date(), timeZone, 'yyyyMMdd_HHmmss');
  const reportName = config.reportPrefix + '_' + suffix;
  if (spreadsheet.getSheetByName(reportName)) {
    throw new Error('同名の確認シートがあります。時間を空けて再実行してください。');
  }
  const report = spreadsheet.insertSheet(reportName);
  const output = [[
    'キー識別子', '元シート行', '判定', '確認理由', '更新日時', '入力済みセル数'
  ]].concat(reportRows);
  report.getRange(1, 1, output.length, output[0].length).setValues(output);
  report.setFrozenRows(1);

  const summary = {
    checkedRows: values.length - 1,
    duplicateGroups: duplicateGroups,
    reportRows: reportRows.length,
    invalidKeyRows: invalidRows.length,
    sourceChanged: false,
    reportSheet: reportName,
    dryRun: true
  };
  console.log(JSON.stringify(summary));
  return summary;
}

function compareKeepCandidateFree_(a, b) {
  const aTime = a.updatedAt === null ? -1 : a.updatedAt;
  const bTime = b.updatedAt === null ? -1 : b.updatedAt;
  if (aTime !== bTime) return bTime - aTime;
  if (a.filledCount !== b.filledCount) return b.filledCount - a.filledCount;
  return a.rowNumber - b.rowNumber;
}

function normalizeCellFree_(value) {
  return String(value === null || value === undefined ? '' : value)
    .normalize('NFKC')
    .replace(/\s+/g, ' ')
    .trim();
}

function toTimestampFree_(value) {
  if (value === '' || value === null || value === undefined) return null;
  const date = value instanceof Date ? value : new Date(value);
  const timestamp = date.getTime();
  return Number.isFinite(timestamp) ? timestamp : null;
}

function shortHashFree_(text) {
  const bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    text,
    Utilities.Charset.UTF_8
  );
  return bytes.map(function(byte) {
    return ('0' + ((byte + 256) % 256).toString(16)).slice(-2);
  }).join('').slice(0, 12);
}