タスク表の余分な空白を集計前に発見｜GASで文字列をdry-run監査

const TASK_TEXT_AUDIT_CONFIG = Object.freeze({
  sheetName: 'Tasks',
  headerRow: 1,
  maxRows: 100,
  dryRun: true,
  expectedHeaders: ['タスクID', 'タスク名', '区分', '状態']
});

function auditTaskTextWhitespaceFree() {
  const config = TASK_TEXT_AUDIT_CONFIG;
  validateTaskTextAuditConfig_(config);

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

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

  const headerValues = sheet
    .getRange(config.headerRow, 1, 1, config.expectedHeaders.length)
    .getDisplayValues()[0];
  const missingHeaders = config.expectedHeaders.filter((name, index) => {
    return String(headerValues[index] || '').trim() !== name;
  });
  if (missingHeaders.length > 0) {
    throw new Error('見出しが一致しません: ' + missingHeaders.join(', '));
  }

  const lastRow = sheet.getLastRow();
  if (lastRow <= config.headerRow) {
    console.log('対象0件です。');
    return;
  }

  const rowCount = Math.min(
    lastRow - config.headerRow,
    config.maxRows
  );
  const rows = sheet
    .getRange(config.headerRow + 1, 1, rowCount, 4)
    .getDisplayValues();
  const issues = [];
  const seenTaskIds = new Set();

  rows.forEach((row, offset) => {
    const sheetRow = config.headerRow + 1 + offset;
    const taskId = String(row[0] || '');

    if (!taskId.trim()) {
      issues.push({
        row: sheetRow,
        column: 'タスクID',
        reason: '空欄'
      });
    } else {
      const normalizedId = normalizeForCompare_(taskId);
      if (seenTaskIds.has(normalizedId)) {
        issues.push({
          row: sheetRow,
          column: 'タスクID',
          reason: '正規化後に重複'
        });
      }
      seenTaskIds.add(normalizedId);
    }

    config.expectedHeaders.forEach((header, columnIndex) => {
      const value = String(row[columnIndex] || '');
      detectTextIssues_(value).forEach(reason => {
        issues.push({
          row: sheetRow,
          column: header,
          reason
        });
      });
    });
  });

  const summary = issues.reduce((counts, issue) => {
    counts[issue.reason] = (counts[issue.reason] || 0) + 1;
    return counts;
  }, {});

  issues.slice(0, 100).forEach(issue => {
    console.log(
      `行${issue.row} / ${issue.column} / ${issue.reason}`
    );
  });
  console.log(JSON.stringify({
    checkedRows: rowCount,
    issueCount: issues.length,
    summary
  }));
  console.log('セル値・担当者名・タスク名はログへ出していません。');
}

function validateTaskTextAuditConfig_(config) {
  if (config.dryRun !== true) {
    throw new Error('dryRunはtrue固定です。');
  }
  if (!Number.isInteger(config.maxRows) ||
      config.maxRows < 1 ||
      config.maxRows > 100) {
    throw new Error('maxRowsは1〜100の整数にしてください。');
  }
  if (!Array.isArray(config.expectedHeaders) ||
      config.expectedHeaders.length !== 4) {
    throw new Error('expectedHeadersは4列にしてください。');
  }
}

function detectTextIssues_(value) {
  const reasons = [];
  if (!value) return reasons;
  if (/^[ \t]|[ \t]$/.test(value)) {
    reasons.push('先頭または末尾に半角空白');
  }
  if (/^　|　$/.test(value)) {
    reasons.push('先頭または末尾に全角空白');
  }
  if (/ {2,}/.test(value)) {
    reasons.push('半角空白が連続');
  }
  if (/　{2,}/.test(value)) {
    reasons.push('全角空白が連続');
  }
  if (/[\r\n]/.test(value)) {
    reasons.push('改行を含む');
  }
  if (/[\u200B-\u200D\uFEFF]/.test(value)) {
    reasons.push('ゼロ幅文字を含む');
  }
  return reasons;
}

function normalizeForCompare_(value) {
  return String(value || '')
    .normalize('NFKC')
    .replace(/[\u200B-\u200D\uFEFF]/g, '')
    .replace(/\s+/g, ' ')
    .trim()
    .toLowerCase();
}