[GAS]GASでセルに式を入力しよう

どうも。つじけ（tsujikenzo）です。今日は単発で「GASでセルに式を入力しよう」をお届けします。

この記事は、#Effective GoogleAppsScriptタグがついております。

今日のアジェンダ
残高を計算したい
appendRow()メソッドで行を追加する
setValue()メソッドで追加する
setValues()メソッドで追加する
まとめ
1. ノンプログラマーのためのEffectiveGoogleAppsScript

今日のアジェンダ

残高を計算したい
appendRow()メソッドで追加する
setValue()メソッドで追加する
setValues()メソッドで追加する

今日の記事は、こちらのスプレッドシートのセルアドレスリテラル考察を読んでいただけると、より知識が深まると思います。

残高を計算したい

スプレッドシートを使っていて、「前日の残高に今日の収入を足して、今日の支出を引いた残高を表示したい」ということがあると思います。

ARRAYFORMULA関数をセルに入力する方法もありますが、こちらGASブログでございます。GASでセルに式を入力してみましょう。

appendRow()メソッドで行を追加する

「新しい行データが発生したら、最終行に追加したい」というときは、appendRow()メソッドが使えます。

新しい行の残高は、常に、1つ上の行の残高を参照します。

このように、「現在地の1つ上」のようなセルを指定するばあいは、相対参照であるR1C1Notationが便利です。

R1C1Notationは、セルアドレスリテラルには使えませんので、INDIRECT関数と組み合わせて記述します。

内側（R1C1Notation）をダブルクォーテーションで、外側（式全体）をシングルクオーテーションで挟むのがポイントです。※逆にするとエラーになります。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const value = [100, 90, '=INDIRECT("R[-1]C[0]",false)+INDIRECT("R[0]C[-2]",false)-INDIRECT("R[0]C[-1]",false)'];
  sheet.appendRow(value)
}

このように、セルに数式が入力されます。

setValue()メソッドで追加する

setValue()メソッドでセルに式を入力すると、INDIRECT関数を省略できます。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const value = [100, 90, '=R[-1]C[0] + R[0]C[-2] - R[0]C[-1]'];

  for (let i = 0; i < value.length; i++) {
    sheet.getRange(7, i + 1, 1, 1).setValue(value[i]);
  }
}

このように、セルアドレスとして、式が入力されています。

これは、公式リファレンスに記載されているとおり、「setValue(s)()メソッドに＝が含まれる値を渡すばあいは、formulaとして解釈しますよ」という仕様によるものです。

setValues()メソッドで追加する

GASで行データをスプレッドシートに追加するさいは、for文をまわしたりしません。

行データは2次元配列に加工して、setValues()メソッドでいきましょう。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const value = [[100, 90, '=R[-1]C[0] + R[0]C[-2] - R[0]C[-1]']];
  sheet.getRange(sheet.getLastRow() + 1, 1, value.length, value[0].length).setValues(value);
}