注文ステータスの飛び越しを更新前に発見｜GASで状態遷移をdry-run監査

function auditOrderStatusTransitionFree() {
  const config = {
    sheetName: '注文確認',
    headerRow: 1,
    dryRun: true,
    writeSummary: false,
    summarySheetName: 'AuditSummary',
    allowedStatuses: ['未入金', '入金済', '発送準備', '発送済', '完了'],
    maxRows: 1000
  };

  validateOrderAuditConfig_(config);

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(config.sheetName);
  if (!sheet) {
    throw new Error('対象シート「' + config.sheetName + '」が見つかりません。');
  }

  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  if (lastRow <= config.headerRow) {
    Logger.log('確認対象は0件です。');
    return { checked: 0, ok: 0, warning: 0, errors: 0 };
  }

  const targetCount = lastRow - config.headerRow;
  if (targetCount > config.maxRows) {
    throw new Error(
      '確認対象が上限の' + config.maxRows + '行を超えています。' +
      'コピー環境で範囲を分けて確認してください。'
    );
  }

  const values = sheet
    .getRange(config.headerRow, 1, targetCount + 1, lastColumn)
    .getValues();

  const headers = values[0].map(function(value) {
    return String(value).trim();
  });
  const requiredHeaders = ['注文ID', '現在状態', '次の状態', '更新予定日'];
  const indexes = {};

  requiredHeaders.forEach(function(header) {
    const index = headers.indexOf(header);
    if (index === -1) {
      throw new Error('必須列「' + header + '」がありません。');
    }
    indexes[header] = index;
  });

  const statusIndex = {};
  config.allowedStatuses.forEach(function(status, index) {
    statusIndex[status] = index;
  });

  const seenOrderKeys = {};
  const issues = [];
  let okCount = 0;

  values.slice(1).forEach(function(row, offset) {
    const rowNumber = config.headerRow + offset + 1;
    const orderId = String(row[indexes['注文ID']] || '').trim();
    const currentStatus = String(row[indexes['現在状態']] || '').trim();
    const nextStatus = String(row[indexes['次の状態']] || '').trim();
    const plannedAt = row[indexes['更新予定日']];
    const rowIssues = [];

    if (!orderId) {
      rowIssues.push('ORDER_ID_EMPTY');
    } else {
      const orderKey = hashOrderKey_(orderId);
      if (seenOrderKeys[orderKey]) {
        rowIssues.push('ORDER_ID_DUPLICATE');
      }
      seenOrderKeys[orderKey] = true;
    }

    if (!Object.prototype.hasOwnProperty.call(statusIndex, currentStatus)) {
      rowIssues.push('CURRENT_STATUS_INVALID');
    }

    if (nextStatus) {
      if (!Object.prototype.hasOwnProperty.call(statusIndex, nextStatus)) {
        rowIssues.push('NEXT_STATUS_INVALID');
      } else if (Object.prototype.hasOwnProperty.call(statusIndex, currentStatus)) {
        const difference = statusIndex[nextStatus] - statusIndex[currentStatus];
        if (difference > 1) {
          rowIssues.push('STATUS_SKIPPED');
        } else if (difference < 0) {
          rowIssues.push('STATUS_REVERSED');
        } else if (difference === 0) {
          rowIssues.push('STATUS_UNCHANGED');
        }
      }
    }

    if (plannedAt !== '' && !isValidOrderAuditDate_(plannedAt)) {
      rowIssues.push('PLANNED_DATE_INVALID');
    }

    if (rowIssues.length === 0) {
      okCount += 1;
      return;
    }

    rowIssues.forEach(function(code) {
      issues.push({ row: rowNumber, code: code });
    });
  });

  const summary = buildOrderAuditSummary_(targetCount, okCount, issues);

  Logger.log('dry-run: ' + config.dryRun);
  Logger.log('確認行数: ' + summary.checked);
  Logger.log('問題なし行数: ' + summary.ok);
  Logger.log('確認対象行数: ' + summary.warningRows);
  Logger.log('指摘件数: ' + summary.issueCount);
  issues.forEach(function(issue) {
    Logger.log('行' + issue.row + ': ' + issue.code);
  });
  Logger.log('注文ID・購入者情報・住所・商品名はログへ出していません。');

  if (config.writeSummary === true) {
    writeOrderAuditSummary_(spreadsheet, config, summary);
  }

  return summary;
}

function validateOrderAuditConfig_(config) {
  if (config.dryRun !== true) {
    throw new Error('安全のためdryRunはtrue固定です。');
  }
  if (!config.sheetName || !Array.isArray(config.allowedStatuses)) {
    throw new Error('シート名または状態一覧の設定が不正です。');
  }
  if (config.allowedStatuses.length < 2) {
    throw new Error('状態一覧は2件以上必要です。');
  }
  if (!Number.isInteger(config.maxRows) || config.maxRows < 1) {
    throw new Error('maxRowsは1以上の整数にしてください。');
  }
}

function hashOrderKey_(orderId) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    orderId,
    Utilities.Charset.UTF_8
  );
  return digest.map(function(byte) {
    const normalized = byte < 0 ? byte + 256 : byte;
    return ('0' + normalized.toString(16)).slice(-2);
  }).join('');
}

function isValidOrderAuditDate_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return !isNaN(value.getTime());
  }
  if (typeof value !== 'string') {
    return false;
  }
  const text = value.trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    return false;
  }
  const parts = text.split('-').map(Number);
  const date = new Date(parts[0], parts[1] - 1, parts[2]);
  return date.getFullYear() === parts[0] &&
    date.getMonth() === parts[1] - 1 &&
    date.getDate() === parts[2];
}

function buildOrderAuditSummary_(checked, ok, issues) {
  const warningRows = {};
  const countsByCode = {};
  issues.forEach(function(issue) {
    warningRows[issue.row] = true;
    countsByCode[issue.code] = (countsByCode[issue.code] || 0) + 1;
  });
  return {
    checked: checked,
    ok: ok,
    warningRows: Object.keys(warningRows).length,
    issueCount: issues.length,
    countsByCode: countsByCode
  };
}

function writeOrderAuditSummary_(spreadsheet, config, summary) {
  let summarySheet = spreadsheet.getSheetByName(config.summarySheetName);
  if (!summarySheet) {
    summarySheet = spreadsheet.insertSheet(config.summarySheetName);
  }
  summarySheet.clearContents();
  summarySheet.getRange(1, 1, 5, 2).setValues([
    ['確認日時', new Date()],
    ['確認行数', summary.checked],
    ['問題なし行数', summary.ok],
    ['確認対象行数', summary.warningRows],
    ['指摘件数', summary.issueCount]
  ]);
}