GASの実行速度が遅い問題を解決する方法〜Google Apps Script〜

メモ

はじめに

この記事はGoogle Apps Script(以降、GAS)のコードの実行速度に悩むユーザーを対象に、なぜ遅いのかという原因とその改善方法を提示することを目的とします。

前提

・GAS から Spreadsheet を操作します。

・ここでの実行速度とは、関数の実行開始から終了までに要した時間(秒数)のことを指します。

・100個の値をSpreadsheetから取得し、加工した上でSpreadsheetに保存します。

ゴールイメージ

改善前 – 計測結果 18秒

改善後 – 計測結果 0.07秒

*データの量や処理の複雑さなどで実行速度は変化する点に注意してください。

遅さの原因は何か?

API連携するリクエスト回数が多すぎる場合、遅延の原因となる。

GASは、Spreadsheetを操作する際にAPI連携しています。例えば、GASが、Spreadsheetの値を取得する場合、Spreadsheetに対してgetリクエストし、値を保存する場合はpostリクエストしている、とイメージすると良いでしょう。

短時間でのリクエスト回数が多すぎる場合、Spreadsheet側でリクエスト数の調整がかかるため、実行速度が遅くなってしまいます。

実装のポイント

いかにAPI連携の回数を減らすことができるか

原因への対策としては最も直接的な対応です。GASからSpreadsheetへのリクエストの数を減らすことで実行速度を改善することが可能です。例えば、使用するメソッドを変更する場合、

getValue() メソッドを getValues() メソッドに変更する。

setValue() メソッドを setValues() メソッドに変更する。

などが考えられます。

繰り返し使用する値は変数化すること

一度、関数内でSpreadsheetから取得した値は、変数に保存し、繰り返し実行することがないように注意しましょう。変数に保存した値であれば、それをどれだけ繰り返して利用しても遅延の対象にはなりません。

実装コード

改善前

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = spreadsheet.getSheetByName("list");

function runFunction1() {
  // 計測用:計測開始
  var start = new Date() / 1000;

  //一つずつ取得して返す。
  //100回 繰り返します。
  var row;
  for(var i = 0; i < 100; i++) {
    //行数を取得する。
    row = i + 1;
    //値を取得する。
    var value = listSheet.getRange(row, 1).getValue();
    //加工する。
    var double = value * 2;
    //値を保存する。
    listSheet.getRange(row, 2).setValue(double);
  }

  //計測用:計測終了
  var end = new Date() / 1000;
  console.log(end - start);
};

改善後

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = spreadsheet.getSheetByName("list");

function runFunction2() {
  // 計測用:計測開始
  var start = new Date() / 1000;

  //一つずつ取得して返す。
  //100回 繰り返します。
  //値を一括で取得する。
  var getArray = listSheet.getRange(1, 1, 100, 1).getValues(); //二次元配列:  [[1, 2, 3, 4, 5, 6, ....]]
  //取得した二次元配列を一次元配列に変換する。
  var array = Array.prototype.concat.apply([], getArray);
  //保存用の配列を用意する。二次元配列とする。
  var setArray = [];

  for(var i = 0; i < array.length; i++) {
    //行数を取得する。
    //row = i + 1;
    //値を取得する。
    var value = array[i];
    //加工する。
    var double = value * 2;
    //値を保存する。
    setArray.push([
      double
    ]);
  }
  //spreadsheet に反映する。
  listSheet.getRange(1, 2, setArray.length, setArray[0].length).setValues(setArray);
  
  //計測用:計測終了
  var end = new Date() / 1000;
  console.log(end - start);
};

解説

改善前は、for文の繰り返し処理の中getValue() メソッド と setValue() メソッド を実行しています。これは、繰り返しの回数分だけGASがSpreadsheetにAPIリクエストを実行します。また、getValue() や setValue() のメソッドは範囲をセル単位で指定することから、セル数分だけAPIリクエストが必要となります。これらの結果、100個のデータの処理に実行速度が18秒もかかってしまいます。

一方、改善後のコードでは、値の取得 と 値の保存 が for文の繰り返し処理の外 にあることがわかります。こうすることで、繰り返し処理の中ではデータの加工に集中しています。メソッドは、getValue() から getValues()、setValue()からsetValues()に変更し、セル単位での値の取得と保存ではなく、 範囲単位でまとめて処理を実行しています。これらの結果、API連携にリクエストが値の取得と保存で1回ずつとなり、実行速度を短縮することができました。

コメント