Google Apps Script (GAS) でこのセル範囲のセルの数式を取得・設定する方法を説明します。
Rangeクラスのメソッドを利用した手順について説明します。
Google Apps Scriptの基本的な使い方については下記をご覧ください。
GASによるスプレッドシート操作の概要
GASでスプレッドシートを操作するための「SpreadSheetサービス」は、主に下記のクラスで構成されています。
| クラス | 説明 |
|---|---|
| SpreadSheetApp | SpreadSheetサービスの親クラス |
| Spreadsheet | スプレッドシートを操作するクラス |
| Sheet | シートを操作するクラス |
| Range | セル範囲を操作するクラス |
それぞれ下記のような位置付けになります。
「SpreadSheetApp」→「SpreadSheet」→「Sheet」→「Range」という順の階層構造になっています。操作対象に応じて、下の階層にたどってオブジェクトを取得して利用します。
このページでは、上記の中のRangeクラスの下記のメソッドについて説明します。
| メソッド | 戻り値 | 説明 |
|---|---|---|
| getFormula() | String | この範囲の左上セルの数式を取得する |
| getFormulas() | String[][] | この範囲のセルの数式を配列で一括取得する |
| getFormulaR1C1() | String | この範囲の左上セルの数式をR1C1表記で取得する |
| getFormulasR1C1() | String[][] | この範囲のセルの数式をR1C1表記の配列で一括取得する |
| setFormula(formula) | Range | この範囲のセルの数式を一括設定する |
| setFormulas(formulas) | Range | この範囲のセルの数式を配列で一括設定する |
| setFormulaR1C1(formula) | Range | この範囲のセルの数式をR1C1表記で一括設定する |
| setFormulasR1C1(formulas) | Range | この範囲のセルの数式をR1C1表記の配列で一括設定する |
この範囲のセルの数式を取得する
このセル範囲のセルの数式を取得するメソッドを説明します。
この範囲の左上セルの数式を取得する：getFormula()
このセル範囲の左上セルの数式を取得する方法です。数式のないセルは空の文字列を返します。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("E2:E4"); console.log(testRange.getFormula()); } |
下記のファイルで実行してみます。黄色背景は数式です。
下記のような実行結果になります。左上セルの数式を取得できます。
|
1 |
=SUM(B2:D2) |
この範囲のセルの数式を配列で一括取得する：getFormulas()
このセル範囲のセルの数式を一括取得する方法です。数式のないセルは空の文字列を返します。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("D2:E3"); console.log(testRange.getFormulas()); } |
下記のファイルで実行してみます。黄色背景は数式です。
下記のような実行結果になります。セルの数式を取得できます。数式のないセルは空の文字列を返します。
|
1 |
[ [ '', '=SUM(B2:D2)' ], [ '', '=SUM(B3:D3)' ] ] |
この範囲の左上セルの数式をR1C1表記で取得する：getFormulaR1C1()
このセル範囲の左上セルの数式をR1C1表記で取得する方法です。数式のないセルは空の文字列を返します。
R1C1表記は、セルの行番号と列番号を使用してセルを参照する方法で、例えば”R1C1″、”R2C2″、”R3C3″のように表記します。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("E2:E4"); console.log(testRange.getFormulaR1C1()); } |
下記のファイルで実行してみます。黄色背景は数式です。
下記のような実行結果になります。左上セルの数式をR1C1表記で取得できます。
|
1 |
=SUM(R[0]C[-3]:R[0]C[-1]) |
この範囲のセルの数式をR1C1表記の配列で一括取得する：getFormulasR1C1()
このセル範囲のセルの数式をR1C1表記の配列で一括取得する方法です。数式のないセルは空の文字列を返します。
R1C1表記は、セルの行番号と列番号を使用してセルを参照する方法で、例えば”R1C1″、”R2C2″、”R3C3″のように表記します。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("D2:E3"); console.log(testRange.getFormulasR1C1()); } |
下記のファイルで実行してみます。黄色背景は数式です。
下記のような実行結果になります。セルの数式をR1C1表記の配列で一括取得できます。数式のないセルは空の文字列を返します。
|
1 2 |
[ [ '', '=SUM(R[0]C[-3]:R[0]C[-1])' ], [ '', '=SUM(R[0]C[-3]:R[0]C[-1])' ] ] |
この範囲のセルの数式を設定する
このセル範囲のセルの数式を設定するメソッドを説明します。
この範囲のセルの数式を一括設定する：setFormula(formula)
このセル範囲のセルの数式を一括設定する方法です。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("E2:E4"); testRange.setFormula("=SUM(B2:D2)") } |
下記のファイルで実行してみます。赤枠の部分に数式を設定します。
下記のような実行結果になります。指定した数式を一括設定できます。
「=SUM(B2:D2)」を指定したにもかかわらず、後続のセルは「=SUM(B3:D3)」「=SUM(B4:D4)」と自動的にセル位置をずらしてくれるのがわかります。
当然、「=SUM(B$2:D$2)」を指定した場合は、セル位置をずらさずに設定します。
この範囲のセルの数式を配列で一括設定する：setFormulas(formulas)
このセル範囲のセルの数式を配列で一括設定する方法です。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("F2:F4"); testRange.setFormulas([["=E2/B7"], ["=E3/B7"], ["=E4/B7"]]); } |
下記のファイルで実行してみます。赤枠の部分に数式を設定します。
下記のような実行結果になります。配列で指定した数式を一括設定できます。
なお、上記の例の場合、setFormulaメソッドで「”E2/$B$7″」を指定することとでも同じことを実現できます。
この範囲のセルの数式をR1C1表記で一括設定する：setFormulaR1C1(formula)
このセル範囲のセルの数式をR1C1表記で一括設定する方法です。
R1C1表記は、セルの行番号と列番号を使用してセルを参照する方法で、例えば”R1C1″、”R2C2″、”R3C3″のように表記します。
下記のように実行します。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("E2:E4"); testRange.setFormulaR1C1("=SUM(R[0]C[-3]:R[0]C[-1])"); } |
下記のファイルで実行してみます。赤枠の部分に数式を設定します。
下記のような実行結果になります。
R1C1表記で指定しますが、設定後の数式は、それぞれ「=SUM(B2:D2)」「=SUM(B3:D3)」「=SUM(B4:D4)」のように通常の数式の表記になります。
この範囲のセルの数式をR1C1表記の配列で一括設定する：setFormulasR1C1(formulas)
このセル範囲のセルの数式をR1C1表記の配列で一括設定する方法です。
R1C1表記は、セルの行番号と列番号を使用してセルを参照する方法で、例えば”R1C1″、”R2C2″、”R3C3″のように表記します。
下記のように実行します。
|
1 2 3 4 5 6 7 8 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getRange("E2:F4"); testRange.setFormulasR1C1([["=SUM(R[0]C[-3]:R[0]C[-1])","=R[0]C[-1]/R[5]C[-4]"], ["=SUM(R[0]C[-3]:R[0]C[-1])","=R[0]C[-1]/R[4]C[-4]"], ["=SUM(R[0]C[-3]:R[0]C[-1])","=R[0]C[-1]/R[3]C[-4]"]]); } |
下記のファイルで実行してみます。赤枠の部分に数式を設定します。
下記のような実行結果になります。
R1C1表記で指定しますが、設定後の数式は、それぞれ「=SUM(B2:D2)」「=SUM(B3:D3)」「=SUM(B4:D4)」のように通常の数式になります。
まとめ
Google Apps Script (GAS) でこのセル範囲のセルの数式を取得・設定する方法を説明しました。
GASは無料で利用できてとても便利なツールです。
Googleスプレッドシート等のGoogleアプリケーションを利用されている方は、GASを習得することで作業を大きく効率化できます。
GASの基本構文を知りたい方は、下記もご覧ください。
