Google Apps Script setFormula スプレッドシートに数式を入力してみる

こんにちは、ばばーるです。

前回のおさらい

前回はsetValue(s)を紹介しました。

• setValues: 複数セルの範囲を指定して、そのセルに2次元配列を入力する
• setValue: 範囲の先頭の値を入力する
• getRangeの範囲を指定すればスプレッドシート上の好きな場所に値を入力できる
• setValueには数式も代入できる(setFormulaという関数もある）

404 NOT FOUND | Algorithm

無料でプログラミングを学ぼう

ideallifebody.com

今回はsetFormulaでスプレッドシートに数式を入力して見ましょう。

setFormula(formula)

A1表記のセルに数式を入力することができます。式はA1表記でなければなりません。

実際に数式を入力していきましょう。まずは以下のようなスプレッドシートを用意します。りんごの個数をC5にvlookupで出力してみましょう。

そして、こんな感じのスクリプトを書いていきます。

function myFunction() {
  const id = 'あなたのスプレッドシートid';                       //1行目
  const ss = SpreadsheetApp.openById(id);                    //2行目
  const sheet = ss.getSheetByName('シート1');                 //3行目

  const cell = sheet.getRange("C5");                         //4行目
  const formula = ("=vlookup(\"りんご\", A1:B3, 2, false)");  //5行目
  cell.setFormula(formula);                                  //6行目
}

1~3行目:スプレッドシートのシート1にアクセスします
4行目: 数式を入力するセルを指定します
5行目: ここで変数formulaにvlookup関数を設定します
6行目: setFormulaでC5のセルに”=vlookup(“りんご”,A1:B3,2,false)”を代入します

実行するとC5のセルにvlookup関数が入力され、3が表示されます。

文字列を含む数式は” “の範囲に注意して書きましょう。

スプレッドシートと同じ書き方の”りんご”でスクリプトを走らせると、以下のようなエラーが出てしまいます。

setFormulaR1C1(formula)

セルに数式を入力することができます。指定された式はR1C1表記します。

先ほどと同じスプレッドシートを用意します。

今回はC5のセルにB1~B3を足すsum関数を入力するスクリプトを書いていきましょう。

function myFunction() {
  const id = 'あなたのスプレッドシートid';                    //1行目
  const ss = SpreadsheetApp.openById(id);                 //2行目
  const sheet = ss.getSheetByName('シート1');              //3行目
  const cell = sheet.getRange("C5");                      //4行目
  const formula = ("=SUM(R[-4]C[-1]:R[-2]C[-1])")         //5行目
  cell.setFormulaR1C1(formula);                           //6行目
}

一つ前のsetFormulaで紹介したスクリプトとほぼ同じです。このスクリプトを実行すると、C5に”=sum(B1:B3)”が入力され、12が表示されます。

B1はC5のセルからすると行(Row)方向に-4移動、列(Column)方向に-1移動した位置にあるので、R[-4]C[-1]と表記します。
B3はC5のセルからすると行(Row)方向に-2移動、列(Column)方向に-1移動した位置にあるので、R[-2]C[-1]と表記します。

そのため、R1C1表記では”SUM(B1:B3)”は”SUM(R[-4]C[-1]:R[-2]C[-1])”と表現されます。

• 

• 

setFormulas(formulas)

A1表記で指定された範囲に、2次元配列の数式を入力することができます。指定されたセル範囲と配列の次元が一致している必要があります。

例えば、以下のシートを用意してA5~C5に1~3行目の合計(SUM)を、A6~C6に1~3行目の平均値(AVERAGE)を列ごとに計算するスクリプトを書いてみましょう。

用意したスプレッドシートはこちら

スクリプトはこんな感じ。

function myFunction() {
  const id = 'あなたのスプレッドシートid';                             //1行目
  const ss = SpreadsheetApp.openById(id);                          //2行目
  const sheet = ss.getSheetByName('シート1');                       //3行目

  const formulas = [                                               //4行目
    ["=SUM(A2:A4)", "=SUM(B2:B4)", "=SUM(C2:C4)"],                 //5行目
    ["=AVERAGE(A2:A4)", "=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)"]      //6行目
  ];                                                               //7行目
  
  const cell = sheet.getRange("A5:C6");                            //8行目
  cell.setFormulas(formulas);                                      //9行目
}

実行すると、5、6行目に式が挿入されて、計算結果が表示されます。

setFormulasR1C1(formulas)

複数の数式を指定されたセルに入力することができます。指定されたセル範囲と配列の次元が一致している必要があります。指定された式はR1C1表記である必要があります。

先ほどと同じスプレッドシートを用意します。

そして、先ほどと同様にA5~C5に1~3行目の合計(SUM)を、A6~C6に1~3行目の平均値(AVERAGE)を列ごとに計算するスクリプトを書いてみましょう。

function myFunction() {
  const id = 'あなたのスプレッドシートid';                             //1行目
  const ss = SpreadsheetApp.openById(id);                          //2行目
  const sheet = ss.getSheetByName('シート1');                       //3行目
   
  const sumOfRowsAbove = "=SUM(R[-4]C[0]:R[-2]C[0])";              //4行目
  const averageOfRowsAbove = "=AVERAGE(R[-4]C[0]:R[-2]C[0])";      //5行目

  const formulas = [                                               //6行目
    [sumOfRowsAbove, sumOfRowsAbove, sumOfRowsAbove],              //7行目
    [averageOfRowsAbove, averageOfRowsAbove, averageOfRowsAbove]   //8行目
  ];                                                               //9行目
  
  const cell = sheet.getRange("A5:C6");                            //10行目
  cell.setFormulasR1C1(formulas);                                  //11行目
} 

先ほどと同様の結果が出てきます。

まとめ

今回はsetFormulaをご紹介しました。

• setValueと同様にsetFourmulaでスプレッドシートに数式を挿入することができる
• A1表記とR1C1表記の2つの書き方がある
• 2次元配列で複数の式を挿入することができる
• 複数の式を入れる場合は配列とセルの指定範囲の数が一致しないといけない

次回は今まで散々使ってきましたが、const, let,varについてご紹介します。

404 NOT FOUND | Algorithm

無料でプログラミングを学ぼう

ideallifebody.com