GASでスプレッドシートの指定セルから数式を取得して活用する方法

Google Apps Script（GAS）を使ってスプレッドシートを自動化したい？そげんときに役立つのがgetFormula()メソッドばい！これは、セルに設定されとる数式を文字列として取得できる便利な機能たい。

「このセルにどんな数式が入っとるんやろ？」そんなときにサクッと使えるけん、しっかりマスターしてスプレッドシートをもっと便利にしよう！

詳解！Ｇｏｏｇｌｅ　Ａｐｐｓ　Ｓｃｒｉｐｔ完全入門ＧｏｏｇｌｅアプリケーションとＧｏｏｇｌｅ　Ｗｏｒ第３版/秀和システム/高橋宣成

posted with カエレバ

楽天市場

Amazon

Yahooショッピング

1. getFormula()って何？
2. 他のメソッドとどう違うと？
3. getFormula()の活用テクニック
1. 3.1 数式のバックアップを取ろう！
2. 3.2 どのセルがどこを参照しているか分析！
4. 大量のデータを効率的に処理する！
5. まとめ

1. getFormula()って何？

まずは基本から！このメソッドを使えば、指定したセルの数式を取得できるとよ。

const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange("B2");
const formula = cell.getFormula();
Logger.log(formula); // "=SUM(A1:A10)" などが出力されるばい！

もしB2に数式がなかったら、空文字""が返ってくるけん注意しよう。

2. 他のメソッドとどう違うと？

GASには数式を取得するメソッドが他にもあるばい！

メソッド名	取得範囲	表記法	戻り値型
getFormula()	単一セル	A1	String
getFormulas()	範囲	A1	String[][]
getFormulaR1C1()	単一セル	R1C1	String
getFormulasR1C1()	範囲	R1C1	String[][]

範囲指定したいときはgetFormulas()やgetFormulasR1C1()を使うと効率的たい！

3. getFormula()の活用テクニック

3.1 数式のバックアップを取ろう！

スプレッドシートの数式を別のシートにバックアップして、誤って上書きしても復元できるようにしよう！

function backupFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');
  const sourceRange = sheet.getRange("A1:D10");
  const formulas = sourceRange.getFormulas();

  const backupSheet = ss.getSheetByName('Backup') || ss.insertSheet('Backup');
  backupSheet.getRange("A1:D10").setFormulas(formulas);
}

3.2 どのセルがどこを参照しているか分析！

シート内の数式を解析して、どのセルがどのセルを参照しているかを可視化するスクリプトばい。

function analyzeFormulaDependencies() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  const formulas = range.getFormulas();

  const dependencyMap = {};

  formulas.forEach((row, rowIndex) => {
    row.forEach((formula, colIndex) => {
      if (formula) {
        const matches = formula.match(/[A-Z]+\d+/g) || [];
        dependencyMap[`${colIndex + 1}-${rowIndex + 1}`] = matches;
      }
    });
  });
  
  console.log(JSON.stringify(dependencyMap, null, 2));
}

4. 大量のデータを効率的に処理する！

スプレッドシートのデータが大量にある場合、一括で取得して処理する方が速いばい！

function optimizedBatchProcessing() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('LargeData');
  const dataRange = sheet.getDataRange();
  
  const formulas = dataRange.getFormulas();
  const values = dataRange.getValues();
  
  const processedData = formulas.map((row, i) => {
    return row.map((formula, j) => {
      return formula ? formula : values[i][j];
    });
  });
  
  sheet.getRange(1, 1, processedData.length, processedData[0].length)
       .setFormulas(processedData);
}