GASでスプレッドシート処理を高速化！getFormulas()によるAPI最適化完全ガイド

// 【NG例】100個のセルがあれば100回APIを呼び出す遅いコード
function slowFormulaCheck(range) {
  // このループが実行されるたびにAPI呼び出しが発生
  for (let i = 1; i <= range.getNumRows(); i++) {
    for (let j = 1; j <= range.getNumColumns(); j++) {
      const formula = range.getCell(i, j).getFormula();
      if (formula) {
        // 何らかの処理
      }
    }
  }
}

// 【OK例】API呼び出しは最初の1回だけ！爆速コード
function fastFormulaCheck(range) {
  // API呼び出しはここだけ！
  const formulas = range.getFormulas(); 
  
  // 取得後はメモリ上で高速にループ処理
  formulas.forEach(row => {
    row.forEach(formula => {
      if (formula) {
        // 何らかの処理
      }
    });
  });
}

function getFormulasExample() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('サンプル');
  const range = sheet.getRange("A1:C3");
  
  // A1:C3の範囲の数式を一括取得
  const formulas = range.getFormulas();
  
  // 取得した二次元配列をログに出力
  console.log(formulas);
  // 出力例:
  // [["=SUM(D1:E1)", "=TODAY()",       ""],
  //  ["",             "=SUM(D2:E2)", "=A1*1.1"],
  //  ["=A2+B2",       "",            ""]]
}

function auditAllFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  let reportData = [['シート名', 'セル番地', '数式']]; // ヘッダー行

  allSheets.forEach(sheet => {
    const sheetName = sheet.getName();
    // シートにデータがなければスキップ
    if (sheet.getLastRow() === 0) return;

    const dataRange = sheet.getDataRange();
    const formulas = dataRange.getFormulas();
    
    formulas.forEach((row, rIndex) => {
      row.forEach((formula, cIndex) => {
        if (formula) {
          // getCellのインデックスは1から始まるため +1 する
          const cellA1 = dataRange.getCell(rIndex + 1, cIndex + 1).getA1Notation();
          reportData.push([sheetName, cellA1, formula]);
        }
      });
    });
  });
  
  if (reportData.length > 1) {
    // 新しいシートに監査結果を出力
    const reportSheet = ss.insertSheet('数式監査レポート_' + new Date().toISOString());
    reportSheet.getRange(1, 1, reportData.length, reportData[0].length).setValues(reportData);
    reportSheet.setFrozenRows(1); // 1行目を固定
    reportSheet.autoResizeColumns(1, 3); // 列幅を自動調整
    SpreadsheetApp.getUi().alert('数式監査レポートを作成しました。');
  } else {
    SpreadsheetApp.getUi().alert('数式は検出されませんでした。');
  }
}

function generateMonthlyReport() {
  const ss = SpreadsheetApp.getActive();
  const templateSheet = ss.getSheetByName('レポートテンプレート');
  if (!templateSheet) {
    SpreadsheetApp.getUi().alert('「レポートテンプレート」シートが見つかりません。');
    return;
  }
  
  const targetYear = '2025';
  const targetMonth = '02';
  const reportSheetName = `${targetYear}年${targetMonth}月レポート`;
  
  // 既存のレポートシートがあれば削除
  const oldSheet = ss.getSheetByName(reportSheetName);
  if (oldSheet) ss.deleteSheet(oldSheet);
  
  const reportSheet = ss.insertSheet(reportSheetName);
  
  // 1. テンプレートから数式を"一括取得"
  const templateFormulas = templateSheet.getDataRange().getFormulas();
  
  // 2. メモリ上でプレースホルダーを置換（高速）
  const reportFormulas = templateFormulas.map(row =>
    row.map(formula => {
      if (!formula) return "";
      return formula
        .replace(/\{\{year\}\}/g, targetYear)
        .replace(/\{\{month\}\}/g, targetMonth);
    })
  );
  
  // 3. レポートシートに数式を"一括設定"
  reportSheet.getRange(1, 1, reportFormulas.length, reportFormulas[0].length)
             .setFormulas(reportFormulas);
  
  SpreadsheetApp.getUi().alert(`「${reportSheetName}」の生成が完了しました。`);
}