Gmailの自動ラベルが競合する前に確認｜GASで仕分けルールをdry-run監査

function auditGmailLabelRuleConflictsFree() {
  const CONFIG = {
    SHEET_NAME: 'LabelRules',
    MAX_RULES: 200,
    DRY_RUN: true
  };

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

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

  const values = sheet.getDataRange().getDisplayValues();
  if (values.length < 2) {
    throw new Error('監査するルールがありません。');
  }

  const headers = values[0].map(normalizeRuleText_);
  const required = ['priority', 'keyword', 'label', 'enabled'];
  required.forEach(function(name, index) {
    if (headers[index] !== name) {
      throw new Error('1行目の列名または列順を確認してください。');
    }
  });

  const rows = values.slice(1);
  if (rows.length > CONFIG.MAX_RULES) {
    throw new Error('一度に監査できるのは200行までです。');
  }

  const issues = [];
  const activeRules = [];
  const priorityRows = new Map();
  const keywordRows = new Map();

  rows.forEach(function(row, index) {
    const rowNumber = index + 2;
    const priorityText = String(row[0] || '').trim();
    const keyword = String(row[1] || '').trim();
    const label = String(row[2] || '').trim();
    const enabledText = normalizeRuleText_(row[3]);
    const isBlank = !priorityText && !keyword && !label && !enabledText;
    if (isBlank) return;

    if (!['true', 'false'].includes(enabledText)) {
      issues.push({ row: rowNumber, type: 'ENABLED_FORMAT' });
      return;
    }
    if (enabledText === 'false') return;

    const priority = Number(priorityText);
    if (!Number.isInteger(priority) || priority < 1 || priority > 999) {
      issues.push({ row: rowNumber, type: 'PRIORITY_INVALID' });
    }
    if (!keyword || keyword.length > 100) {
      issues.push({ row: rowNumber, type: 'KEYWORD_INVALID' });
    }
    if (!label || label.length > 225 || /[\u0000-\u001F]/.test(label)) {
      issues.push({ row: rowNumber, type: 'LABEL_INVALID' });
    }
    if (!Number.isInteger(priority) || !keyword || !label) return;

    const normalizedKeyword = normalizeRuleText_(keyword);
    if (priorityRows.has(priority)) {
      issues.push({ row: rowNumber, type: 'PRIORITY_DUPLICATE', withRow: priorityRows.get(priority) });
    } else {
      priorityRows.set(priority, rowNumber);
    }
    if (keywordRows.has(normalizedKeyword)) {
      issues.push({ row: rowNumber, type: 'KEYWORD_DUPLICATE', withRow: keywordRows.get(normalizedKeyword) });
    } else {
      keywordRows.set(normalizedKeyword, rowNumber);
    }
    activeRules.push({ row: rowNumber, priority: priority, keyword: normalizedKeyword });
  });

  for (let i = 0; i < activeRules.length; i++) {
    for (let j = i + 1; j < activeRules.length; j++) {
      const a = activeRules[i];
      const b = activeRules[j];
      if (a.keyword !== b.keyword && (a.keyword.includes(b.keyword) || b.keyword.includes(a.keyword))) {
        issues.push({ row: b.row, type: 'KEYWORD_OVERLAP', withRow: a.row });
      }
    }
  }

  const summary = {
    dryRun: true,
    scannedRows: rows.length,
    activeRules: activeRules.length,
    issueCount: issues.length,
    issues: issues
  };
  Logger.log(JSON.stringify(summary));
  return summary;
}

function normalizeRuleText_(value) {
  return String(value || '').normalize('NFKC').trim().toLowerCase();
}