Googleスプレッドシートを自動化するとき、「セルの数式を取得して処理したい！」ってことあるよね？ そんなときに役立つのが getFormulaR1C1() ばい！ 今回は、これをどう活用するか、わかりやすく解説するさ。
getFormulaR1C1()って何？
スプレッドシートのセルには、計算式が設定できるけど、それをスクリプトから取得したいときに使うメソッドが getFormulaR1C1() じゃ。普通の getFormula() との違いは、数式を R1C1表記 で取得できること。
A1表記とR1C1表記の違い
| 表記方法 | A1表記 | R1C1表記 |
|---|---|---|
| 例 | B5 | R5C2 |
| 相対参照 | B2 | R[-3]C |
R1C1 は 行（Row）と列（Column）を数値で指定する方法 で、相対参照を使うと動的な範囲指定ができるさ。
getFormulaR1C1()の基本的な使い方
function getFormulaExample() {
const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange("B5");
const formula = cell.getFormulaR1C1();
Logger.log(formula);
}
例えば、B5セルに =SUM(A2:A4) が入ってる場合、getFormulaR1C1() で取得すると =SUM(R[-3]C[-1]:R[-1]C[-1]) って返ってくるばい。
範囲の数式を一括取得する方法
セルが1つだけじゃなくて、複数のセル範囲の数式を一気に取得するには、 getFormulasR1C1() を使うといいち。
function getMultipleFormulas() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("B5:C6");
const formulas = range.getFormulasR1C1();
Logger.log(formulas);
}
この方法を使うと、2次元配列で数式が返ってくるから、効率的に処理できるばい。
getFormulaR1C1()の応用
1. 数式を監視して変更をログに記録
function trackFormulaChanges() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1:Z100");
const formulas = range.getFormulasR1C1();
formulas.forEach((row, rowIndex) => {
row.forEach((formula, colIndex) => {
if (formula.includes("IMPORTRANGE")) {
Logger.log(`外部参照が R${rowIndex+1}C${colIndex+1} にあるばい: ${formula}`);
}
});
});
}
2. 動的な数式を挿入
function insertDynamicFormulas() {
const sheet = SpreadsheetApp.getActiveSheet();
const startRow = 5;
const numRows = 30;
const baseFormula = "=SUM(R[0]C[-3]:R[0]C[-1])";
const formulas = Array(numRows).fill([baseFormula]);
sheet.getRange(startRow, 4, numRows, 1)
.setFormulasR1C1(formulas);
}
このスクリプトを実行すると、指定範囲のセルに動的な数式を一括設定できるっちゃ。
getFormulaR1C1()を活用するとできること
✅ 相対参照を活用したダイナミックな数式処理
✅ 範囲の数式を一括取得・設定して処理を効率化
✅ 変更監視システムを作って数式の異常をチェック
GASでスプレッドシートをもっと便利に使いたいなら、getFormulaR1C1() は覚えておくべき機能ばい！
コメント