完了したはずのタスクが期限切れになる前に｜GASで状態と期限をdry-run監査

function auditTaskStatusAndDeadlineFree() {
  const config = {
    sheetName: 'Tasks',
    headerRow: 1,
    maxRows: 500,
    dryRun: true,
    writeReport: false,
    reportSheetName: 'TaskAuditReport'
  };

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

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

  const lastRow = source.getLastRow();
  const lastColumn = source.getLastColumn();
  if (lastRow <= config.headerRow) {
    throw new Error('確認するデータ行がありません。');
  }
  if (lastRow - config.headerRow > config.maxRows) {
    throw new Error('対象が上限を超えています。maxRows を確認してください。');
  }

  const values = source
    .getRange(config.headerRow, 1, lastRow - config.headerRow + 1, lastColumn)
    .getValues();
  const headers = values[0].map(value => String(value).trim());
  const required = ['タスクID', 'タスク名', '状態', '期限', '完了日', '優先度'];
  const index = {};
  required.forEach(name => {
    const column = headers.indexOf(name);
    if (column === -1) throw new Error('必須列がありません: ' + name);
    index[name] = column;
  });

  const allowedStatuses = new Set(['未着手', '進行中', '保留', '完了']);
  const allowedPriorities = new Set(['高', '中', '低']);
  const seenIds = new Map();
  const issues = [];
  const today = startOfDay_(new Date());

  values.slice(1).forEach((row, offset) => {
    const rowNumber = config.headerRow + offset + 1;
    const taskId = String(row[index['タスクID']] ?? '').trim();
    const taskName = String(row[index['タスク名']] ?? '').trim();
    const status = String(row[index['状態']] ?? '').trim();
    const priority = String(row[index['優先度']] ?? '').trim();
    const dueDate = parseTaskDate_(row[index['期限']]);
    const completedDate = parseTaskDate_(row[index['完了日']]);

    if (!taskId && !taskName && !status && !dueDate && !completedDate && !priority) return;

    if (!taskId) {
      addTaskIssue_(issues, rowNumber, 'ERROR', 'タスクIDが空欄');
    } else if (seenIds.has(taskId)) {
      addTaskIssue_(issues, rowNumber, 'ERROR', 'タスクIDが重複', '先頭行: ' + seenIds.get(taskId));
    } else {
      seenIds.set(taskId, rowNumber);
    }

    if (!taskName) addTaskIssue_(issues, rowNumber, 'ERROR', 'タスク名が空欄');
    if (!allowedStatuses.has(status)) {
      addTaskIssue_(issues, rowNumber, 'ERROR', '状態が候補外');
    }
    if (!allowedPriorities.has(priority)) {
      addTaskIssue_(issues, rowNumber, 'WARN', '優先度が候補外');
    }

    const rawDueDate = row[index['期限']];
    const rawCompletedDate = row[index['完了日']];
    if (rawDueDate !== '' && !dueDate) {
      addTaskIssue_(issues, rowNumber, 'ERROR', '期限の日付形式を確認');
    }
    if (rawCompletedDate !== '' && !completedDate) {
      addTaskIssue_(issues, rowNumber, 'ERROR', '完了日の日付形式を確認');
    }

    if (status !== '完了' && dueDate && startOfDay_(dueDate) < today) {
      addTaskIssue_(issues, rowNumber, 'WARN', '未完了のまま期限超過');
    }
    if (status === '完了' && !completedDate) {
      addTaskIssue_(issues, rowNumber, 'WARN', '完了なのに完了日が空欄');
    }
    if (status !== '完了' && completedDate) {
      addTaskIssue_(issues, rowNumber, 'WARN', '未完了なのに完了日あり');
    }
    if (dueDate && completedDate && startOfDay_(completedDate) < startOfDay_(dueDate)) {
      addTaskIssue_(issues, rowNumber, 'INFO', '期限より前に完了。意図を確認');
    }
  });

  const summary = issues.reduce((result, issue) => {
    result[issue.level] = (result[issue.level] || 0) + 1;
    return result;
  }, {});
  console.log(JSON.stringify({
    checkedRows: values.length - 1,
    issueCount: issues.length,
    summary,
    issueRows: [...new Set(issues.map(issue => issue.rowNumber))],
    note: 'タスク名・担当者・メモ本文はログへ出していません。'
  }));

  if (config.writeReport === true) {
    writeTaskAuditReport_(ss, config.reportSheetName, issues);
  }
}

function validateTaskAuditConfig_(config) {
  if (!config || typeof config !== 'object') throw new Error('設定が不正です。');
  if (!config.sheetName) throw new Error('sheetName を指定してください。');
  if (!Number.isInteger(config.headerRow) || config.headerRow < 1) {
    throw new Error('headerRow は1以上の整数にしてください。');
  }
  if (!Number.isInteger(config.maxRows) || config.maxRows < 1 || config.maxRows > 5000) {
    throw new Error('maxRows は1〜5000にしてください。');
  }
  if (config.dryRun !== true) throw new Error('dryRun は true 固定です。');
}

function parseTaskDate_(value) {
  if (value === '' || value == null) return null;
  if (value instanceof Date && !Number.isNaN(value.getTime())) return value;
  const text = String(value).trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) return null;
  const date = new Date(text + 'T00:00:00');
  return Number.isNaN(date.getTime()) ? null : date;
}

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

function addTaskIssue_(issues, rowNumber, level, reason, detail) {
  issues.push({ rowNumber, level, reason, detail: detail || '' });
}

function writeTaskAuditReport_(ss, sheetName, issues) {
  let report = ss.getSheetByName(sheetName);
  if (!report) report = ss.insertSheet(sheetName);
  report.clearContents();
  const rows = [['行番号', '重要度', '確認内容', '補足']]
    .concat(issues.map(issue => [issue.rowNumber, issue.level, issue.reason, issue.detail]));
  report.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}