スプレッドシートの入力ミスを公開前に発見｜GASでルール表をdry-run監査

function auditInputRulesFree() {
  const config = {
    dataSheetName: '公開準備',
    ruleSheetName: '入力ルール',
    idHeader: '管理ID',
    maxRows: 200,
    maxIssues: 50,
    dryRun: true,
  };

  if (config.dryRun !== true) {
    throw new Error('安全のためdryRun=trueで実行してください。');
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(config.dataSheetName);
  const ruleSheet = ss.getSheetByName(config.ruleSheetName);
  if (!dataSheet || !ruleSheet) {
    throw new Error('公開準備または入力ルールシートが見つかりません。');
  }

  const dataLastRow = dataSheet.getLastRow();
  const dataLastColumn = dataSheet.getLastColumn();
  const ruleLastRow = ruleSheet.getLastRow();
  const ruleLastColumn = ruleSheet.getLastColumn();
  if (dataLastRow < 1 || dataLastColumn < 1) {
    throw new Error('公開準備シートに見出しがありません。');
  }
  if (ruleLastRow < 2 || ruleLastColumn < 4) {
    throw new Error('入力ルールシートに確認条件がありません。');
  }
  if (dataLastRow - 1 > config.maxRows) {
    throw new Error('対象が上限を超えています。コピー環境で分割してください。');
  }

  const data = dataSheet
    .getRange(1, 1, dataLastRow, dataLastColumn)
    .getValues();
  const ruleData = ruleSheet
    .getRange(1, 1, ruleLastRow, ruleLastColumn)
    .getValues();

  const dataHeaders = normalizeHeaders_(data[0]);
  const ruleHeaders = normalizeHeaders_(ruleData[0]);
  assertUniqueHeaders_(dataHeaders, '公開準備');
  assertUniqueHeaders_(ruleHeaders, '入力ルール');

  const requiredRuleHeaders = ['列名', '種類', '必須', '候補'];
  const missingRuleHeaders = requiredRuleHeaders.filter(
    header => !ruleHeaders.includes(header)
  );
  if (missingRuleHeaders.length > 0) {
    throw new Error('入力ルールの見出し不足: ' + missingRuleHeaders.join(','));
  }

  const idColumn = dataHeaders.indexOf(config.idHeader);
  if (idColumn < 0) {
    throw new Error('管理ID列が見つかりません。');
  }

  const ruleColumn = Object.fromEntries(
    requiredRuleHeaders.map(header => [header, ruleHeaders.indexOf(header)])
  );
  const rules = [];
  const seenColumns = new Set();

  for (let rowIndex = 1; rowIndex < ruleData.length; rowIndex++) {
    const row = ruleData[rowIndex];
    const columnName = String(row[ruleColumn['列名']] ?? '').trim();
    const type = String(row[ruleColumn['種類']] ?? '').trim().toUpperCase();
    const requiredText = String(row[ruleColumn['必須']] ?? '').trim().toUpperCase();
    const optionText = String(row[ruleColumn['候補']] ?? '').trim();
    if (!columnName && !type && !requiredText && !optionText) continue;

    if (!columnName || !type || !['TRUE', 'FALSE'].includes(requiredText)) {
      throw new Error('入力ルールの' + (rowIndex + 1) + '行目を確認してください。');
    }
    if (!['TEXT', 'LIST', 'DATE', 'NUMBER'].includes(type)) {
      throw new Error('未対応の種類です: ' + type);
    }
    if (seenColumns.has(columnName)) {
      throw new Error('入力ルールの列名が重複しています: ' + columnName);
    }

    const dataColumn = dataHeaders.indexOf(columnName);
    if (dataColumn < 0) {
      throw new Error('公開準備に列がありません: ' + columnName);
    }

    const options = optionText
      ? optionText.split(',').map(value => value.trim()).filter(Boolean)
      : [];
    if (type === 'LIST' && options.length === 0) {
      throw new Error('LISTには候補が必要です: ' + columnName);
    }
    if (new Set(options).size !== options.length) {
      throw new Error('候補が重複しています: ' + columnName);
    }

    rules.push({
      columnName,
      dataColumn,
      type,
      required: requiredText === 'TRUE',
      options,
    });
    seenColumns.add(columnName);
  }

  if (rules.length === 0) {
    throw new Error('有効な入力ルールがありません。');
  }

  const issues = [];
  let checkedRows = 0;
  let skippedBlankRows = 0;

  for (let rowIndex = 1; rowIndex < data.length; rowIndex++) {
    const row = data[rowIndex];
    const idValue = row[idColumn];
    if (isBlank_(idValue)) {
      skippedBlankRows++;
      continue;
    }
    checkedRows++;

    for (const rule of rules) {
      const value = row[rule.dataColumn];
      const reason = validateValue_(value, rule);
      if (reason) {
        issues.push({
          row: rowIndex + 1,
          column: rule.columnName,
          reason,
        });
      }
    }
  }

  const output = {
    dryRun: true,
    checkedRows,
    skippedBlankRows,
    ruleCount: rules.length,
    issueCount: issues.length,
    issues: issues.slice(0, config.maxIssues),
    truncated: issues.length > config.maxIssues,
  };
  console.log(JSON.stringify(output));
  return output;
}

function validateValue_(value, rule) {
  if (isBlank_(value)) {
    return rule.required ? '必須欄が空です' : '';
  }
  if (rule.type === 'TEXT') {
    return String(value).trim() ? '' : '文字列として確認できません';
  }
  if (rule.type === 'LIST') {
    return rule.options.includes(String(value).trim())
      ? ''
      : '候補にない値です';
  }
  if (rule.type === 'DATE') {
    return value instanceof Date && !Number.isNaN(value.getTime())
      ? ''
      : '日付として確認できません';
  }
  if (rule.type === 'NUMBER') {
    return typeof value === 'number' && Number.isFinite(value)
      ? ''
      : '数値として確認できません';
  }
  return '未対応の種類です';
}

function normalizeHeaders_(headers) {
  return headers.map(value => String(value ?? '').trim());
}

function assertUniqueHeaders_(headers, sheetName) {
  if (headers.some(header => !header)) {
    throw new Error(sheetName + 'の見出しに空欄があります。');
  }
  if (new Set(headers).size !== headers.length) {
    throw new Error(sheetName + 'の見出しが重複しています。');
  }
}

function isBlank_(value) {
  return value === '' || value === null || value === undefined;
}