数式を一括入力して列ずれする前に｜GASで参照先をdry-run監査

function auditFormulaPlanFree() {
  const config = {
    planSheetName: 'FormulaPlan',
    dryRun: true,
    maxPlans: 100,
    allowedTypes: ['IF_BLANK', 'SUM_RANGE']
  };

  if (config.dryRun !== true) {
    throw new Error('この無料例は dryRun: true のまま実行してください。');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const planSheet = spreadsheet.getSheetByName(config.planSheetName);
  if (!planSheet) {
    throw new Error('FormulaPlanシートが見つかりません。');
  }

  const values = planSheet.getDataRange().getDisplayValues();
  if (values.length < 2) {
    throw new Error('見出しの下に設計行を1件以上入力してください。');
  }
  if (values.length - 1 > config.maxPlans) {
    throw new Error('最初は100件以内に絞って確認してください。');
  }

  const expectedHeaders = ['対象シート', '出力列', '関数種別', '参照列', '開始行', '終了行'];
  const headers = values[0].map(value => String(value).trim());
  const missingHeaders = expectedHeaders.filter(name => !headers.includes(name));
  if (missingHeaders.length > 0) {
    throw new Error('不足している見出し: ' + missingHeaders.join(', '));
  }

  const index = Object.fromEntries(expectedHeaders.map(name => [name, headers.indexOf(name)]));
  const results = [];

  values.slice(1).forEach((row, offset) => {
    const planRow = offset + 2;
    const targetSheetName = String(row[index['対象シート']] || '').trim();
    const outputColumn = normalizeColumnFree_(row[index['出力列']]);
    const formulaType = String(row[index['関数種別']] || '').trim().toUpperCase();
    const referenceColumn = normalizeColumnFree_(row[index['参照列']]);
    const startRow = Number(row[index['開始行']]);
    const endRow = Number(row[index['終了行']]);

    if (![targetSheetName, outputColumn, formulaType, referenceColumn].every(Boolean)) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '必須項目に空欄があります' });
      return;
    }
    if (!config.allowedTypes.includes(formulaType)) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '未対応の関数種別です' });
      return;
    }
    if (!isSafeColumnFree_(outputColumn) || !isSafeColumnFree_(referenceColumn)) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '列はA〜ZZZの範囲で指定してください' });
      return;
    }
    if (!Number.isInteger(startRow) || !Number.isInteger(endRow) || startRow < 2 || endRow < startRow) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '行範囲を確認してください' });
      return;
    }
    if (endRow - startRow + 1 > 1000) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '最初は1000行以内に絞ってください' });
      return;
    }

    const targetSheet = spreadsheet.getSheetByName(targetSheetName);
    if (!targetSheet) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '対象シートが見つかりません' });
      return;
    }

    const maxColumn = targetSheet.getMaxColumns();
    const outputColumnNumber = columnToNumberFree_(outputColumn);
    const referenceColumnNumber = columnToNumberFree_(referenceColumn);
    if (outputColumnNumber > maxColumn || referenceColumnNumber > maxColumn) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '対象シートの列数を超えています' });
      return;
    }
    if (outputColumn === referenceColumn) {
      results.push({ planRow, status: 'INPUT_HOLD', reason: '出力列と参照列が同じです' });
      return;
    }

    const preview = buildFormulaPreviewFree_(formulaType, targetSheetName, referenceColumn, startRow, endRow);
    results.push({
      planRow,
      status: 'PREVIEW_ONLY',
      targetRange: outputColumn + startRow + ':' + outputColumn + endRow,
      formulaPreview: preview
    });
  });

  const summary = {
    dryRun: config.dryRun,
    checkedPlans: values.length - 1,
    previewCount: results.filter(item => item.status === 'PREVIEW_ONLY').length,
    holdCount: results.filter(item => item.status === 'INPUT_HOLD').length,
    sourceUpdated: false,
    formulaWritten: false
  };
  console.log(JSON.stringify(summary));
  results.forEach(item => console.log(JSON.stringify(item)));
  console.log('セル値・注文内容・顧客情報はログへ出していません。');
}

function normalizeColumnFree_(value) {
  return String(value || '').trim().toUpperCase();
}

function isSafeColumnFree_(column) {
  return /^[A-Z]{1,3}$/.test(column) && columnToNumberFree_(column) <= 18278;
}

function columnToNumberFree_(column) {
  return column.split('').reduce((total, char) => total * 26 + char.charCodeAt(0) - 64, 0);
}

function quoteSheetNameFree_(name) {
  return "'" + String(name).replace(/'/g, "''") + "'";
}

function buildFormulaPreviewFree_(type, sheetName, referenceColumn, startRow, endRow) {
  const sheet = quoteSheetNameFree_(sheetName);
  if (type === 'IF_BLANK') {
    return '=IF(' + sheet + '!' + referenceColumn + startRow + '="","","確認済み")';
  }
  return '=SUM(' + sheet + '!' + referenceColumn + startRow + ':' + referenceColumn + endRow + ')';
}