積読リストの見直し期限切れを放置する前に｜GASで登録日と次回確認日をdry-run監査

function auditWatchlistReviewDatesFree() {
  const config = {
    sourceSheetName: 'Watchlist',
    reportSheetName: 'ReviewAudit',
    staleAfterDays: 30,
    maxRows: 1000,
    dryRun: true,
    writeReport: false,
    completedStatuses: ['完了', '視聴済み']
  };

  validateWatchlistAuditConfig_(config);

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

  const lastRow = source.getLastRow();
  if (lastRow < 2) {
    console.log('監査対象は0件です。見出しの下へデータを入力してください。');
    return;
  }

  const targetRows = lastRow - 1;
  if (targetRows > config.maxRows) {
    throw new Error(
      '対象行が上限を超えています。少量で確認してから maxRows を調整してください。' +
      ' rows=' + targetRows
    );
  }

  const headers = source.getRange(1, 1, 1, 5).getDisplayValues()[0];
  const expectedHeaders = ['タイトル', '区分', '登録日', '次回確認日', '状態'];
  expectedHeaders.forEach(function(expected, index) {
    if (normalizeWatchlistText_(headers[index]) !== expected) {
      throw new Error(
        '見出しが一致しません。' + (index + 1) + '列目は「' + expected + '」にしてください。'
      );
    }
  });

  const values = source.getRange(2, 1, targetRows, 5).getValues();
  const today = startOfLocalDay_(new Date());
  const findings = [];
  const summary = {
    checked: 0,
    skippedCompleted: 0,
    invalid: 0,
    overdue: 0,
    staleWithoutReviewDate: 0,
    ok: 0
  };

  values.forEach(function(row, offset) {
    const rowNumber = offset + 2;
    const title = normalizeWatchlistText_(row[0]);
    const itemType = normalizeWatchlistText_(row[1]) || '未設定';
    const registeredAt = parseWatchlistDate_(row[2]);
    const reviewAt = parseWatchlistDate_(row[3]);
    const status = normalizeWatchlistText_(row[4]);

    if (!title && !row[2] && !row[3] && !status) return;
    summary.checked += 1;

    if (config.completedStatuses.includes(status)) {
      summary.skippedCompleted += 1;
      return;
    }

    const reasons = [];
    let elapsedDays = '';

    if (!title) reasons.push('タイトル未入力');
    if (row[2] && !registeredAt) reasons.push('登録日の形式不正');
    if (row[3] && !reviewAt) reasons.push('次回確認日の形式不正');

    if (registeredAt) {
      elapsedDays = diffDays_(registeredAt, today);
      if (elapsedDays < 0) reasons.push('登録日が未来');
    }

    if (reviewAt) {
      const overdueDays = diffDays_(reviewAt, today);
      if (overdueDays > 0) {
        reasons.push('次回確認日を' + overdueDays + '日超過');
        summary.overdue += 1;
      }
    } else if (registeredAt && elapsedDays > config.staleAfterDays) {
      reasons.push('次回確認日なしで登録から' + elapsedDays + '日経過');
      summary.staleWithoutReviewDate += 1;
    }

    if (reasons.length === 0) {
      summary.ok += 1;
      return;
    }

    if (reasons.some(function(reason) { return reason.includes('未入力') || reason.includes('形式不正') || reason.includes('未来'); })) {
      summary.invalid += 1;
    }

    findings.push([
      rowNumber,
      itemType,
      elapsedDays,
      reasons.join(' / ')
    ]);
  });

  console.log(JSON.stringify({
    mode: config.dryRun ? 'DRY_RUN' : 'REPORT_ONLY',
    summary: summary,
    findingRows: findings.map(function(item) { return item[0]; })
  }));
  console.log('タイトル・URL・メモ本文はログへ出していません。');

  if (config.dryRun === true || config.writeReport !== true) {
    console.log('dry-runのためシートへ書き込みません。要確認件数=' + findings.length);
    return;
  }

  writeWatchlistAuditReport_(spreadsheet, config.reportSheetName, findings, today);
}

function validateWatchlistAuditConfig_(config) {
  if (!config || typeof config !== 'object') {
    throw new Error('configがありません。');
  }
  if (!config.sourceSheetName || !config.reportSheetName) {
    throw new Error('sourceSheetNameとreportSheetNameを指定してください。');
  }
  if (config.sourceSheetName === config.reportSheetName) {
    throw new Error('元シートとレポートシートは別名にしてください。');
  }
  if (!Number.isInteger(config.staleAfterDays) || config.staleAfterDays < 1 || config.staleAfterDays > 3650) {
    throw new Error('staleAfterDaysは1〜3650の整数にしてください。');
  }
  if (!Number.isInteger(config.maxRows) || config.maxRows < 1 || config.maxRows > 5000) {
    throw new Error('maxRowsは1〜5000の整数にしてください。');
  }
  if (!Array.isArray(config.completedStatuses) || config.completedStatuses.length === 0) {
    throw new Error('completedStatusesを1件以上指定してください。');
  }
}

function parseWatchlistDate_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return startOfLocalDay_(value);
  }
  if (typeof value !== 'string' || !value.trim()) return null;
  const matched = value.trim().match(/^(\d{4})[\/-](\d{1,2})[\/-](\d{1,2})$/);
  if (!matched) return null;
  const parsed = new Date(Number(matched[1]), Number(matched[2]) - 1, Number(matched[3]));
  if (
    parsed.getFullYear() !== Number(matched[1]) ||
    parsed.getMonth() !== Number(matched[2]) - 1 ||
    parsed.getDate() !== Number(matched[3])
  ) return null;
  return startOfLocalDay_(parsed);
}

function startOfLocalDay_(date) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

function diffDays_(fromDate, toDate) {
  const oneDay = 24 * 60 * 60 * 1000;
  return Math.floor((toDate.getTime() - fromDate.getTime()) / oneDay);
}

function normalizeWatchlistText_(value) {
  return String(value == null ? '' : value).trim();
}

function writeWatchlistAuditReport_(spreadsheet, reportSheetName, findings, checkedAt) {
  let report = spreadsheet.getSheetByName(reportSheetName);
  if (!report) report = spreadsheet.insertSheet(reportSheetName);
  report.clearContents();
  report.getRange(1, 1, 1, 5).setValues([[
    '確認日', '元の行番号', '区分', '登録からの日数', '判定理由'
  ]]);
  if (findings.length === 0) return;
  const rows = findings.map(function(item) {
    return [checkedAt, item[0], item[1], item[2], item[3]];
  });
  report.getRange(2, 1, rows.length, 5).setValues(rows);
}