Driveバックアップのコピー事故を実行前に防ぐ｜GASで計画表をdry-run監査

function auditDriveBackupPlan() {
  const config = {
    sheetName: 'バックアップ計画',
    maxRows: 200,
    dryRun: true
  };

  if (config.dryRun !== true) {
    throw new Error('安全のためdryRunはtrue固定です。');
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(config.sheetName);
  if (!sheet) {
    throw new Error('「バックアップ計画」シートが見つかりません。');
  }

  const expectedHeaders = [
    '計画名',
    '元フォルダID',
    '保存先フォルダID',
    '頻度',
    '保持世代数',
    '有効',
    '手動確認'
  ];
  const actualHeaders = sheet.getRange(1, 1, 1, expectedHeaders.length)
    .getDisplayValues()[0]
    .map(function(value) {
      return String(value).trim();
    });

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

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

  const rows = targetCount === 0
    ? []
    : sheet.getRange(2, 1, targetCount, expectedHeaders.length).getValues();
  const allowedFrequencies = ['毎日', '毎週', '毎月'];
  const seen = {};
  const issues = [];

  rows.forEach(function(row, index) {
    const rowNumber = index + 2;
    const name = String(row[0]).trim();
    const sourceId = String(row[1]).trim();
    const destinationId = String(row[2]).trim();
    const frequency = String(row[3]).trim();
    const generations = Number(row[4]);
    const enabled = row[5];
    const confirmed = String(row[6]).trim();

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

    if (!name) {
      issues.push({ row: rowNumber, code: 'NAME_EMPTY' });
    }
    if (!sourceId) {
      issues.push({ row: rowNumber, code: 'SOURCE_EMPTY' });
    }
    if (!destinationId) {
      issues.push({ row: rowNumber, code: 'DESTINATION_EMPTY' });
    }
    if (sourceId && destinationId && sourceId === destinationId) {
      issues.push({ row: rowNumber, code: 'SAME_FOLDER' });
    }
    if (allowedFrequencies.indexOf(frequency) === -1) {
      issues.push({ row: rowNumber, code: 'FREQUENCY_INVALID' });
    }
    if (!Number.isInteger(generations) || generations < 1 || generations > 30) {
      issues.push({ row: rowNumber, code: 'GENERATIONS_INVALID' });
    }
    if (enabled !== false) {
      issues.push({ row: rowNumber, code: 'ENABLED_NOT_FALSE' });
    }
    if (confirmed !== '済') {
      issues.push({ row: rowNumber, code: 'MANUAL_CHECK_MISSING' });
    }

    if (sourceId && destinationId && allowedFrequencies.indexOf(frequency) !== -1) {
      const planKey = sourceId + '|' + destinationId + '|' + frequency;
      if (seen[planKey]) {
        issues.push({ row: rowNumber, code: 'DUPLICATE_PLAN' });
      } else {
        seen[planKey] = true;
      }
    }
  });

  Logger.log('dry-run: true');
  Logger.log('確認行数: ' + targetCount);
  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: targetCount,
    issues: issues.length
  };
}