カレンダー予定の一括登録ミスを作成前に発見｜GASで行ごとの登録計画をdry-run監査

const CALENDAR_PLAN_CONFIG = Object.freeze({
  sourceSheet: 'イベント候補',
  auditSheet: '登録計画',
  targetStatus: '登録候補',
  maxRows: 200,
  dryRun: true,
});

function previewCalendarImportPlanFree() {
  if (CALENDAR_PLAN_CONFIG.dryRun !== true) {
    throw new Error('安全のためdryRun=true以外では実行できません。');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const source = spreadsheet.getSheetByName(CALENDAR_PLAN_CONFIG.sourceSheet);
  if (!source) {
    throw new Error('イベント候補シートが見つかりません。');
  }

  const values = source.getDataRange().getValues();
  if (values.length < 2) {
    throw new Error('確認するデータ行がありません。');
  }

  const headers = values[0].map(value => String(value).trim());
  const required = ['状態', '件名', '開始日時', '終了日時', 'カレンダーID'];
  const column = Object.fromEntries(required.map(name => [name, headers.indexOf(name)]));
  const missingHeaders = required.filter(name => column[name] < 0);
  if (missingHeaders.length) {
    throw new Error(`見出し不足: ${missingHeaders.join(', ')}`);
  }

  const candidates = values.slice(1)
    .map((row, index) => ({ row, rowNumber: index + 2 }))
    .filter(item => String(item.row[column['状態']]).trim() === CALENDAR_PLAN_CONFIG.targetStatus);

  if (candidates.length > CALENDAR_PLAN_CONFIG.maxRows) {
    throw new Error(`登録候補が上限${CALENDAR_PLAN_CONFIG.maxRows}件を超えています。`);
  }

  const seen = new Map();
  const results = candidates.map(item => {
    const title = String(item.row[column['件名']] ?? '').trim();
    const start = toValidDate_(item.row[column['開始日時']]);
    const end = toValidDate_(item.row[column['終了日時']]);
    const calendarId = String(item.row[column['カレンダーID']] ?? '').trim();
    const issues = [];

    if (!title) issues.push('件名が空です');
    if (title.length > 100) issues.push('件名が100文字を超えています');
    if (!start) issues.push('開始日時を日付として読めません');
    if (!end) issues.push('終了日時を日付として読めません');
    if (start && end && end.getTime() <= start.getTime()) {
      issues.push('終了日時が開始日時以前です');
    }
    if (!calendarId) issues.push('カレンダーIDが空です');

    const key = [
      normalizeText_(title),
      start ? start.toISOString() : '',
      end ? end.toISOString() : '',
      normalizeText_(calendarId),
    ].join('|');
    if (key !== '|||') {
      if (seen.has(key)) issues.push(`候補表内で${seen.get(key)}行目と重複しています`);
      else seen.set(key, item.rowNumber);
    }

    return [
      item.rowNumber,
      issues.length ? 'HOLD' : 'READY',
      issues.join(' / ') || '入力検査PASS',
      title ? '入力あり' : '空',
      start ? Utilities.formatDate(start, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm') : '',
      end ? Utilities.formatDate(end, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm') : '',
      calendarId ? '入力あり' : '空',
    ];
  });

  const audit = spreadsheet.getSheetByName(CALENDAR_PLAN_CONFIG.auditSheet)
    || spreadsheet.insertSheet(CALENDAR_PLAN_CONFIG.auditSheet);
  audit.clearContents();
  const output = [[
    '元の行番号', '判定', '理由', '件名', '開始日時', '終了日時', 'カレンダーID'
  ], ...results];
  audit.getRange(1, 1, output.length, output[0].length).setValues(output);

  const readyCount = results.filter(row => row[1] === 'READY').length;
  const holdCount = results.length - readyCount;
  console.log(JSON.stringify({ dryRun: true, checked: results.length, readyCount, holdCount }));
}

function toValidDate_(value) {
  const date = value instanceof Date ? value : new Date(value);
  return Number.isNaN(date.getTime()) ? null : date;
}

function normalizeText_(value) {
  return String(value ?? '').normalize('NFKC').trim().toLowerCase();
}