PDF保存後に範囲ずれへ気づく前に｜GASで出力設定をdry-run監査

function previewPdfExportPlanFree() {
  const config = {
    dryRun: true,
    settingsSheetName: 'PDF設定',
    resultSheetName: 'PDF_確認計画',
    maxRows: 200,
    allowedPaperSizes: ['A4', 'A3', 'LETTER'],
    allowedOrientations: ['縦', '横'],
    maxMarginMm: 30
  };

  if (config.dryRun !== true) {
    throw new Error('安全のためdryRun=true以外では実行できません。');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = spreadsheet.getSheetByName(config.settingsSheetName);
  if (!settingsSheet) {
    throw new Error('PDF設定シートが見つかりません。');
  }

  const values = settingsSheet.getDataRange().getDisplayValues();
  if (values.length < 2) {
    throw new Error('PDF設定シートに確認対象の行がありません。');
  }
  if (values.length - 1 > config.maxRows) {
    throw new Error('一度に確認できるのは' + config.maxRows + '行までです。');
  }

  const requiredHeaders = [
    '対象シート', '出力範囲', 'ファイル名', '用紙', '向き',
    '上余白mm', '下余白mm', '左余白mm', '右余白mm', '有効'
  ];
  const headerIndexes = buildHeaderIndexes_(values[0], requiredHeaders);
  const plan = [];

  values.slice(1).forEach(function(row, offset) {
    const rowNumber = offset + 2;
    const enabled = parseBoolean_(row[headerIndexes['有効']]);
    if (!enabled) {
      plan.push([rowNumber, 'SKIP', '', '', '', '有効がTRUEではありません']);
      return;
    }

    const sheetName = row[headerIndexes['対象シート']].trim();
    const requestedRange = row[headerIndexes['出力範囲']].trim();
    const fileName = row[headerIndexes['ファイル名']].trim();
    const paperSize = row[headerIndexes['用紙']].trim().toUpperCase();
    const orientation = row[headerIndexes['向き']].trim();
    const issues = [];

    if (!sheetName) issues.push('対象シートが空欄');
    const targetSheet = sheetName ? spreadsheet.getSheetByName(sheetName) : null;
    if (sheetName && !targetSheet) issues.push('対象シートが存在しない');

    if (!fileName) {
      issues.push('ファイル名が空欄');
    } else if (/[\\/:*?"<>|]/.test(fileName)) {
      issues.push('ファイル名に使用しにくい記号を含む');
    } else if (fileName.length > 120) {
      issues.push('ファイル名が120文字を超える');
    }

    if (config.allowedPaperSizes.indexOf(paperSize) === -1) {
      issues.push('用紙はA4・A3・LETTERのいずれか');
    }
    if (config.allowedOrientations.indexOf(orientation) === -1) {
      issues.push('向きは縦または横');
    }

    const marginLabels = ['上余白mm', '下余白mm', '左余白mm', '右余白mm'];
    marginLabels.forEach(function(label) {
      const raw = row[headerIndexes[label]].trim();
      const margin = Number(raw);
      if (raw === '' || !Number.isFinite(margin) || margin < 0 || margin > config.maxMarginMm) {
        issues.push(label + 'は0〜' + config.maxMarginMm + 'の数値');
      }
    });

    let checkedRange = '';
    let sizeSummary = '';
    if (targetSheet) {
      try {
        const range = requestedRange
          ? targetSheet.getRange(requestedRange)
          : targetSheet.getDataRange();
        checkedRange = range.getA1Notation();
        sizeSummary = range.getNumRows() + '行 × ' + range.getNumColumns() + '列';
        if (range.getNumRows() === 1 && range.getNumColumns() === 1 && range.isBlank()) {
          issues.push('出力範囲が空白セルのみ');
        }
      } catch (error) {
        issues.push('出力範囲をA1形式で確認');
      }
    }

    plan.push([
      rowNumber,
      issues.length ? 'HOLD' : 'READY',
      sheetName,
      checkedRange,
      sizeSummary,
      issues.join(' / ') || 'PDF作成前の確認へ進めます'
    ]);
  });

  writePdfPlan_(spreadsheet, config.resultSheetName, plan);
  const readyCount = plan.filter(function(row) { return row[1] === 'READY'; }).length;
  const holdCount = plan.filter(function(row) { return row[1] === 'HOLD'; }).length;
  const skipCount = plan.filter(function(row) { return row[1] === 'SKIP'; }).length;

  console.log(JSON.stringify({
    dryRun: true,
    checkedRows: plan.length,
    readyCount: readyCount,
    holdCount: holdCount,
    skipCount: skipCount
  }));
}

function buildHeaderIndexes_(headerRow, requiredHeaders) {
  const normalized = headerRow.map(function(value) { return String(value).trim(); });
  const duplicateHeaders = normalized.filter(function(value, index, all) {
    return value && all.indexOf(value) !== index;
  });
  if (duplicateHeaders.length) {
    throw new Error('同名の見出しがあります。');
  }

  const missingHeaders = requiredHeaders.filter(function(header) {
    return normalized.indexOf(header) === -1;
  });
  if (missingHeaders.length) {
    throw new Error('不足している見出し: ' + missingHeaders.join(', '));
  }

  return requiredHeaders.reduce(function(indexes, header) {
    indexes[header] = normalized.indexOf(header);
    return indexes;
  }, {});
}

function parseBoolean_(value) {
  return ['TRUE', '1', 'はい', '有効'].indexOf(String(value).trim().toUpperCase()) !== -1;
}

function writePdfPlan_(spreadsheet, sheetName, rows) {
  let resultSheet = spreadsheet.getSheetByName(sheetName);
  if (!resultSheet) resultSheet = spreadsheet.insertSheet(sheetName);
  resultSheet.clearContents();

  const output = [[
    '設定行', '判定', '対象シート', '確認範囲', '範囲サイズ', '確認事項'
  ]].concat(rows);
  resultSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
  resultSheet.setFrozenRows(1);
}