note記事の月次振り返りで集計漏れを防ぐ｜GASで分析データをdry-run監査

function auditNoteMonthlyAnalysisInput() {
  const config = {
    sheetName: '記事分析',
    targetMonth: '2026-09',
    maxRows: 500,
    dryRun: true
  };

  if (config.dryRun !== true) {
    throw new Error('安全のためdryRunはtrue固定です。');
  }
  if (!/^\d{4}-(0[1-9]|1[0-2])$/.test(config.targetMonth)) {
    throw new Error('targetMonthはYYYY-MM形式で指定してください。');
  }

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

  const headers = [
    '公開日',
    '記事ID',
    '状態',
    '閲覧数',
    'スキ数',
    'コメント数',
    '集計対象'
  ];
  const actualHeaders = sheet.getRange(1, 1, 1, headers.length)
    .getDisplayValues()[0]
    .map(function(value) {
      return String(value).trim();
    });

  const headerMismatch = headers.some(function(header, index) {
    return actualHeaders[index] !== header;
  });
  if (headerMismatch) {
    throw new Error('A1:G1の見出しを手順どおりにしてください。');
  }

  const rowCount = Math.max(0, sheet.getLastRow() - 1);
  if (rowCount > config.maxRows) {
    throw new Error('確認対象が500行を超えています。範囲を分けてください。');
  }

  const rows = rowCount === 0
    ? []
    : sheet.getRange(2, 1, rowCount, headers.length).getValues();
  const allowedStatus = ['公開', '下書き', '非公開'];
  const allowedTarget = ['対象', '除外'];
  const seenArticleIds = {};
  const issues = [];

  function addIssue(row, code) {
    issues.push({ row: row, code: code });
  }

  function isValidDate(value) {
    return value instanceof Date && !Number.isNaN(value.getTime());
  }

  function isNonNegativeInteger(value) {
    return typeof value === 'number' &&
      Number.isInteger(value) &&
      value >= 0;
  }

  rows.forEach(function(row, index) {
    const rowNumber = index + 2;
    const publishedAt = row[0];
    const articleId = String(row[1]).trim();
    const status = String(row[2]).trim();
    const views = row[3];
    const likes = row[4];
    const comments = row[5];
    const target = String(row[6]).trim();

    if (row.every(function(value) { return value === ''; })) {
      return;
    }

    if (!isValidDate(publishedAt)) {
      addIssue(rowNumber, 'PUBLISHED_DATE_INVALID');
    }
    if (!articleId) {
      addIssue(rowNumber, 'ARTICLE_ID_EMPTY');
    } else if (seenArticleIds[articleId]) {
      addIssue(rowNumber, 'ARTICLE_ID_DUPLICATE');
    } else {
      seenArticleIds[articleId] = true;
    }
    if (allowedStatus.indexOf(status) === -1) {
      addIssue(rowNumber, 'STATUS_INVALID');
    }
    if (!isNonNegativeInteger(views)) {
      addIssue(rowNumber, 'VIEWS_INVALID');
    }
    if (!isNonNegativeInteger(likes)) {
      addIssue(rowNumber, 'LIKES_INVALID');
    }
    if (!isNonNegativeInteger(comments)) {
      addIssue(rowNumber, 'COMMENTS_INVALID');
    }
    if (allowedTarget.indexOf(target) === -1) {
      addIssue(rowNumber, 'TARGET_FLAG_INVALID');
    }

    if (target === '対象' && isValidDate(publishedAt)) {
      const month = Utilities.formatDate(
        publishedAt,
        Session.getScriptTimeZone(),
        'yyyy-MM'
      );
      if (month !== config.targetMonth) {
        addIssue(rowNumber, 'TARGET_MONTH_MISMATCH');
      }
    }
  });

  Logger.log('dry-run: true');
  Logger.log('確認行数: ' + rowCount);
  if (issues.length === 0) {
    Logger.log('入力上の不整合: 0件');
  } else {
    issues.forEach(function(issue) {
      Logger.log('行' + issue.row + ': ' + issue.code);
    });
  }
  Logger.log('記事IDや閲覧数などの実値はログへ出していません。');

  return {
    status: issues.length === 0 ? 'OK' : 'HOLD',
    checked: rowCount,
    issues: issues.length
  };
}