スプレッドシート集計の数え間違いを更新前に発見｜GASで状態別件数をdry-run確認

function auditSpreadsheetSummaryFree() {
  const CONFIG = {
    DRY_RUN: true,
    SHEET_NAME: '公開準備',
    MAX_ROWS: 500,
    ALLOWED_STATUSES: ['未着手', '作業中', '確認待ち', '完了']
  };

  if (CONFIG.DRY_RUN !== true) {
    throw new Error('この無料例はDRY_RUN=trueのまま使用してください。');
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    throw new Error('公開準備シートが見つかりません。');
  }

  const values = sheet.getDataRange().getDisplayValues();
  if (values.length < 2) {
    throw new Error('見出し行の下にテストデータを1行以上用意してください。');
  }

  const expectedHeaders = ['項目ID', '区分', '状態', '担当', '期限'];
  const actualHeaders = values[0].map(function (value) {
    return String(value).normalize('NFKC').trim();
  });
  const headersMatch = expectedHeaders.every(function (header, index) {
    return actualHeaders[index] === header;
  });
  if (!headersMatch) {
    throw new Error('1行目の見出し名または列順が違います。');
  }

  const rows = values.slice(1).filter(function (row) {
    return row.some(function (value) {
      return String(value).trim() !== '';
    });
  });
  if (rows.length > CONFIG.MAX_ROWS) {
    throw new Error('確認上限を超えました。対象を分けて再実行してください。');
  }

  const seenIds = {};
  const summary = {};
  const issues = [];
  let validRows = 0;

  rows.forEach(function (row, index) {
    const rowNumber = index + 2;
    const itemId = String(row[0]).normalize('NFKC').trim();
    const category = String(row[1]).normalize('NFKC').trim();
    const status = String(row[2]).normalize('NFKC').trim();
    const owner = String(row[3]).normalize('NFKC').trim();
    const dueDate = String(row[4]).normalize('NFKC').trim();
    const codes = [];

    if (!itemId || !category || !status || !owner) codes.push('REQUIRED_EMPTY');
    if (itemId && seenIds[itemId]) codes.push('DUPLICATE_ITEM_ID');
    if (itemId) seenIds[itemId] = true;
    if (status && CONFIG.ALLOWED_STATUSES.indexOf(status) === -1) codes.push('STATUS_NOT_ALLOWED');
    if (dueDate && !isValidIsoDate_(dueDate)) codes.push('DATE_FORMAT_INVALID');

    if (codes.length) {
      issues.push({ row: rowNumber, codes: codes });
      return;
    }

    if (!summary[category]) summary[category] = {};
    summary[category][status] = (summary[category][status] || 0) + 1;
    validRows += 1;
  });

  const issueCounts = issues.reduce(function (counts, issue) {
    issue.codes.forEach(function (code) {
      counts[code] = (counts[code] || 0) + 1;
    });
    return counts;
  }, {});

  const result = {
    dryRun: true,
    checkedRows: rows.length,
    validRows: validRows,
    issueRows: issues.length,
    issueCounts: issueCounts,
    issues: issues.slice(0, 50),
    summary: summary
  };

  console.log(JSON.stringify(result));
  return result;
}

function isValidIsoDate_(value) {
  const match = String(value).match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (!match) return false;
  const year = Number(match[1]);
  const month = Number(match[2]);
  const day = Number(match[3]);
  const date = new Date(Date.UTC(year, month - 1, day));
  return date.getUTCFullYear() === year
    && date.getUTCMonth() === month - 1
    && date.getUTCDate() === day;
}