GASの処理速度を圧倒的に高速化する方法

はじめに

この記事は「Ateam LifeDesign Advent Calendar 2023」で完走賞を狙って25記事書いているうちの18日目の記事です。今年も完走目指して頑張るぞ！

みなさんはGASで色々な処理を書いていて「処理遅いなー」と思ったことはありませんか？今回アドカレで書いているようなデータ量を取り扱う場合なら書き方によらずそこまで処理速度はかわらないと思いますが、実務で使ってるようなデータ量が多いものだと実行時間がめちゃくちゃかかるということが結構頻繁に起きがちです。
今日はGASの処理速度を高速化するための方法について見ていきたいと思います。

そもそもなぜ処理速度が遅くなるのか

GASの処理が遅くなる一番よくある原因がAPIの呼び出し回数です。GASを書いていてよく使うgetActiveSpreadsheetやgetValue、setValue、getRangeなどはすべてAPIあり、API呼び出しは時間を要するため、複数のAPIリクエストをループ内で頻繁に行うと、処理時間が増える可能性があります。

具体例

今回はこのようなシートを用意して、セルの中に書かれてる数字をそれぞれ1プラスしたものを書き込むスクリプトを作りながら処理が遅いものと早いものを比較していこうと思います。

処理速度の測り方

JavaScriptのコンソールメソッドとして提供されているconsole.time()メソッドは、特定の処理の実行時間を計測することができるので、今回はこのメソッドを使います。

console.time("処理Aの時間計測");

console.time()を使って計測を開始します。引数には計測の名前（識別子）を指定します。

console.timeEnd("処理Aの時間計測");

console.timeEnd()を使って計測を終了します。引数には開始時に指定した識別子を渡します。このメソッドが実行されると計測開始からの経過時間がコンソールに表示されます。

それでは早速書き方の違いによる処理速度の違いがどれくらいあるのか見ながら、高速化について触れていきます。

遅い処理の例

コード

function inefficientLoop() {
  console.time("Inefficient Loop");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");

  for (let i = 1; i <= sheet.getLastRow(); i++) {
    for (let j = 1; j <= sheet.getLastColumn(); j++) {
      sheet.getRange(i, j).setValue(sheet.getRange(i, j).getValue() + 1);
    }
  }
  console.timeEnd("Inefficient Loop");
}

実行結果

今回はとにかく時間がかかるように処理を書いています。

軽微なところではありますがforの条件をsheet.getLastRow();のように書いているのでループ回数分getLastRow()が呼び出されます。その次のsheet.getLastColumn()についても同様ですね。これを改善するにはforより前でgetLastRow()・getLastColumn()それぞれの値を1度変数に代入しておくのが良いでしょう。そうすれば代入時の1度のみAPIがコールされることになるので処理が早くなります。

処理の中で一番時間がかかっているのはsheet.getRange(i, j).setValue(sheet.getRange(i, j).getValue() + 1);この部分です。二重のfor文でこの処理自体が100回実行されますが、1回の実行でgetRange・setValue・getRange・getValueと4回APIをコールするのでこの処理だけでAPIのコールが400回行われてしまいます。
これについてはgetValuesで範囲内のセルの値を一度に持ってきて、setValues(配列)で配列を一気にセルに書き込む方法があるので後述の高速化の方で詳しく見ていきましょう。

速い処理の例

コード

function efficientLoop() {
  console.time("Efficient Loop");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const range = sheet.getRange(1, 1, lastRow, lastColumn);
  const values = range.getValues();
  
  var newValues = [];
  for (let i = 0; i < values.length; i++) {
    newValues[i] = [];
    for (let j = 0; j < values[i].length; j++) {
      newValues[i][j] = values[i][j] + 1;
    }
  }
  sheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

  console.timeEnd("Efficient Loop");
}

実行結果

先程は31,000msかかっていたのが約1/50に短縮することができました。

それではコードを詳しく見ていきましょう。

  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const range = sheet.getRange(1, 1, lastRow, lastColumn);
  const values = range.getValues();
  
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      // ここに処理
    }
  }

先程はforでループをしながらgetLastRowとgetLastColumnを呼び出し続けていましたが、今回はまずgetRangeで必要なデータ範囲を取得してその後その範囲内にあるセルの値をgetValuesで一気に取得しています。getValuesで取得した値は二次元配列で格納されます。

|  A  |  B  |  C  |
|-----|-----|-----|
|  1  |  2  |  3  |
|  4  |  5  |  6  |
|  7  |  8  |  9  |

たとえばシートにこのように値が入っていてgetValuesで取得するとその中身は

[
  [1, 2, 3],
  [4, 5, 6],
  [7, 8, 9]
]

このような形で入ってきます。なのでたとえば上記例でgetValuesした値を配列testに代入した場合、test[0][0]だと1がtest[2][1]だと8が返ってきます。

  for (let i = 0; i < values.length; i++) {
    newValues[i] = [];
    for (let j = 0; j < values[i].length; j++) {
      newValues[i][j] = values[i][j] + 1;
    }
  }
  sheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

そして高速化のもう一つのポイントがこの部分です。先程はループ処理の中でsetValueを呼び出して1セルずつ書き込みをしていました。ただそれだとループ回数分だけAPIがコールされてしまうので、ループ内では計算した結果をnewValuesという配列に格納するだけにして、ループが終わったあとにsetValues(newValues)することで指定された範囲のセルの値を一気に書き換えるようにしています。こうすることで書き込みを行うためのAPIのコールは1度だけの実行で完了するようになります。

最後に

今日はGASの処理速度を高速化する方法について実例を見ながら解説していきました。基本的にはsetValues・getValuesを使って、必要なセルをまとめて操作するようにしましょう！そうすることで処理速度が圧倒的に速くなります◎