SEのせまい物置小屋: [GAS] スプレッドシートの値参照、値設定のパフォーマンス

Google Apps Scriptを使ってGoogleスプレッドシートの内容を編集する、という場面がよくあります。
しかし、ここでExcelVBAの様に素直に書くと、ちょっと痛い目を見る事があります。

例えば↓のようにスプレッドシートのA列の値とB列の値を加算してC列に結果を入れる処理を考えます。
※100行あるとします。あ、関数書きゃいいだろ、ってのはここでは無しね（・∀・）

普通に書くとこんな感じになると思います。

function func01() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var row = 1; row <= 100; row++) {
    // C列に設定する値を計算（A列の値+B列の値）
    var answer = sheet.getRange(row, 1).getValue() 
               + sheet.getRange(row, 2).getValue();
    // 計算結果をC列に設定
    sheet.getRange(row, 3).setValue(answer);
  }
}

これで実行すると当然結果は正しいものとなりますが・・・

たった100行処理するのに18秒以上・・・(´･ω･`)Excelﾉﾎｳｶﾞｲｲﾖﾈ･･･

原因は
・sheet.getRange(row, 1).getValue()
・sheet.getRange(row, 2).getValue()
・sheet.getRange(row, 3).setValue(answer)
をそれぞれ100回呼び出してしまっていることです。
これらは全て実行されるたびにスプレッドシートとのやり取りが発生するため、
パフォーマンスに影響が出てしまいます。

問題点を改善したソースがこちら。

function func02() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // 100行2列の値を一気に取得
  var values = sheet.getRange(1, 1, 100, 2).getValues();
  // 行配列を定義
  var rows = [];
  for (var row = 0; row < 100; row++) {
    // 列配列を定義
    var cols = [];
    // C列に設定する値を計算（A列の値+B列の値）
    var answer = values[row][0] + values[row][1];
    // 列配列に計算結果を設定
    cols.push(answer);
    // 行配列に列配列を設定
    rows.push(cols);
  }
  // 計算結果をC列に設定
  sheet.getRange(1, 3, 100, 1).setValues(rows);
}

そして実行結果のログがこちら。

圧倒的じゃないか、我が軍は・・・

同じ行を処理するのにたった0.3秒ちょい。
これはスプレッドシートの参照を全値取得、全値設定の2回のみにしているからです。

スプレッドシートのGoogle Apps Scriptでパフォーマンスに悩んでいる方は
まずスプレッドシートの参照回数を見直せないかどうかを検討してみてはいかがでしょうか。