Google Apps Scriptは大変便利なスクリプトなのですが、連続稼働時間に最大6分という制限があり、それを超えると「起動時間の最大値を超えました」とのメッセージが出てスクリプトは勝手に止まってしまいます。これを回避するテクニックとしてはスクリプトトリガーを利用した突破方法があるのですが、実装は結構大変ですし、何より早く終わるに越したことはありません。しかし、スクリプトを高速化する為にはGoogle Apps Script流の書き方というものがあるので、今回それをまとめてみました。

なるべく普段から意識して書くようにすると、機能追加時に痛い目をみずに済みます(実際、単純なシートデータを集めるスクリプトで、集めるシートが増えてあっという間にスクリプトが破綻して困った思い出があります)。今回使用するスプレッドシートのダミーデータはMockarooを利用いたしました。

今回使用するスプレッドシート

APIの呼び出し回数を減らす

ここでいうAPIとは、SpreadsheetAppであったり、DocumentAppであったりするアレです。それにぶら下がるgetSheetByNameであったり、getRange、getValuesなども同じで、これらGoogle Apps Script特有のAPIの呼び出しは可能な限り削る事、それが高速化では真っ先にあげられることです。どうしてもプログラミング初心者時代には、やってしまいがちなのですが、VBAなどではそれほど速度低下はしないのですが、Google Apps ScriptでAPIの呼び出しを頻繁に行うと物凄く遅くなります。

例えば、SpreadsheetApp.getActiveSpreadsheet().getSheetByName().getRange().getValues()という1文があった場合、これだけで4回APIを呼び出してる事になります。呼び出したら、なるべく取得した値を使うようにするのが今回のキモです。今回は事例として、特定のIDに合致するレコードをシートから探し出し、そのIDに含まれてる企業名を返すものを作ってみました。特定のIDは一番最後のIDを使う事にしました。(シートのレコード数は1500ですが、有効なレコードは1000です。他は空白行である為)。

※実行時間は毎回変わります。また、同じ処理の実行でも2度目の値が良くなるのはキャッシュされてる為と思われます。

※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「最悪な事例」および「API呼び出しを改良」を実行します。

遅いコードの事例

わざとらしく最悪なコードで書いてみました。1つずつセルの値を取得しては次のセルへといったような効率の悪い検索を行っています。

実行結果は、160秒ほど。激遅です。

改良したコードの事例

少し整理して、APIの呼び出しを減らしました。改良点は以下の通りです。

  • getLastRowで最終行の値を取得して変数に入れてループを回しています。値はlengthに格納しています。【19行目】
  • ループ内での値の取得もAPI呼び出ししていますが、少し改良をしました。【24行目】

実行結果は、80秒ほど。それでもまだまだ遅いですね。改良の余地はまだまだあります。

データの取得と操作

さて、まずAPIの呼び出しを減らす方法で若干スピードアップしました。しかし、まだ実用レベルに至っていません。改良したと言っても、改良コードの内容にはまだまだ課題が残っています。主にその課題点を一気に列挙してみたいと思います。

※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「一番良い事例」を実行します。

セル単位で値を読むような書き方は行わない

主にVBAなどのコードでよく見かけるのが、プログラムと言うよりも人間の行動を忠実に書いてるようなマクロ的なソースコードです。つまり、1個セルの値を読んでチェックし、次のセルに移動してまた値を読み込んでチェックする。しかし、VBAのようにローカルで動くマシンであればそれでも良いのですが、Google Apps Scriptではその度にAPIを発行しなければなりません。よって、この部分はデータとしてガツッと取得して、配列データをチェックする方式に変えてあげる必要があります。例えば、

こんな具合になります。getValuesでシートデータを配列で取得出来ます。また、A2:Aの範囲で取得してるので、getLastRowの値から-1しています。配列から値を取り出す時は、for文にあるiを使うので、cntとして宣言してあったカウンタは不要になります。

forループ内を最適化

forループ内にも課題が残っています。今回のシートはデータ自体は999行で残りはただの空白行です。その為、以下の問題点が残ってる事になります。

  1. 該当のレコードを見つけてもループは最後の行まで見にいくようになってる(getLastRowではデータの有効範囲を自動で判断してくれるので、999行で取得出来ます)。
  2. なんらかの条件で空白行まで読みにいくような場合、1500行全部を読みに行ってしまう。

そこで、きちんとデータを見つけたらそこでループを脱出させてやり、また、空白行が来たら止めるような処置を追加してあげると余計な処理をせずに済みます。ループ脱出はbreak;文を入れてあげるだけ。空白チェックは普通にtempid == “”でチェック出来ます。

ここまでの内容を反映したコードだと、実行結果は0.35秒でした。APIの呼び出しを減らし、配列から値をチェックする方式に変えるとここまで劇的に速度が向上します。

その他

その他、より高速になるように改善する上でのポイントは

  1. getDataRangegetRangeByNameを使うとAPIの使用をより少なくする事が可能です。
  2. シートを年度毎に分割するか、年度データ蓄積専用の別のシートを用意しておく

getDataRangeはデータが有効な行を自動判定してデータ範囲を指定してくれるメソッドです。対してgetRangeByNameは予めスプレッドシートに「名前付き範囲」を設定してる時にその設定の範囲を拾ってくるメソッドです。構文は、SpreadsheetApp.getActiveSpreadsheet().getRangeByName(“名前付き範囲名”).getValues()といった感じになります。名前付き範囲は、データの増減のない、固定的なシートに対して設定を行います。

また、2.についてですが、1枚のシートに何年もデータを溜め込んでる場合、必ずしもすでに終わった前の年のデータは必要なかったりします。これらは年度の切替時にでも、別のシートに退避させれば、参照するレコード数を減らせるので、結果的に高速化に繋がります。自分の場合、年度単位で過去ログシートへ移動や、フォーム等で承認済みレコードは過去ログシートへ退避するようにスクリプトを書いています。

データの書込と検索

配列のデータや値類をスプレッドシートへ書き込むシーンも多くあるかと思います。例えば、同じスタイルのシートのデータをいくつも回収して一纏めにして収集するだとか、シートデータをコピーしてくるなどなど。ここでスクリプトの速度に大きな差が生まれる点があります。今回は、使用するスプレッドシートのDummyシートからcopymanシートへデータをコピーする事例です。

※テスト実行は、スプレッドシートのメニューより「テスト」⇒「書込テスト」⇒「1行ずつ挿入」および「まとめて挿入」を実行します。

遅いコードの事例

すでにセル単位でデータを書き込む真似はしません。APIの呼び出し回数を減らすの項目でそれは立証されました。という事で書込で次に使用されるであろうメソッドが「appendRow」。1次元配列のデータをスプレッドシートの最後の行に自動で追加してくれる優れもので、フォームなど1行のレコードを挿入する場合に効果的です。

大量のデータを挿入する場合、appendRowでは1行ずつ挿入する事になるので、遅いです。実行結果は、88秒でした。

改良したコードの事例

1行ずつではかなり時間が掛かる上に、データ量に比例して処理時間が伸びてしまいます。そこでこの方法を配列まるごと一発で書き込む方法を取ります。複数のレコードをappendRowと同じく最終行に追加するスタイルです。

表示はデータ量によってモタツキますが、実行結果自体は0.80秒でした。本来、取得したスプレッドシート自体が配列なので、そのまま書込が出来ますが条件を一緒にするために、書き込み用配列にわざわざpushさせています。

HTML Serviceを活用する

このセクションで紹介する方法は、高速化すると言うよりは、6分の実行リミットを回避して作業を行わせる事のできるテクニックです。HTML Serviceで生成したHTMLはクライアントのマシン上で動作してるので、JavaScriptの実行時間に制限はありません。ただし、この処理を一発で行わせるには、同期処理が必要になるので、上手く前の処理が終わったら書込ルーチンを実行するようにしないと、データの回収が終わっていないのに、GAS側へ投げられてしまうので注意が必要です。

※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「HTML Serviceで取得する」および「Visualization APIで取得」を実行します。

単純な使用例

  1. GAS側からシートのリストを取得する。
  2. Google Apps Script側で処理を担当する関数は連続処理ではなく単一の処理を行うだけである(あるシートのデータの塊を取得してHTML側へ返すだけとか。
  3. 故に、HTML側にシートのIDを所持し、リストにしたがって2.へIDを投げ、返ってきた値を配列にpush(もしくは、Array.prototype.push.apply)するコードを記述する。
  4. 最終的に一塊になった配列データをGAS側に送り返してシートに書き込ませる

こんな具合です。常にGAS側とやり取りをしていますが、GAS側での実際の処理は、単一シートデータの取得と塊の書込ですので、1つ1つが6分に到達する事がなければ問題なく連続処理を投げる事が可能です。今回のサンプルでは、ダミーのシート(10カラム1000レコードを10枚)をおよそ32秒で取得出来ています。

※データの処理をGAS側でやらせず、HTML Serviceで生成したHTML上にて、JavaScriptにやらせるのがポイントです。

※google.script.run.withSuccessHandlerを使用しています。この関数の実行は非同期で実行されるので、並列処理っぽい作業も行えます。

GAS側コード

HTML側コード

Promiseとタイマーで同期処理

前項の単純な事例はGAS側はデータの取得を行ってHTML側で処理をすることを目的としたものですが、逆にHTML側はタイマー管理を行うことに徹して、処理はGAS側で行わせる場合にはPromiseによる同期処理を実現する必要があります。しかし、google.script.runは非同期にバンバン実行されてしまうのと、ループ処理もまた同じなので上手にループを回しつつ、書き込みを考慮してタイマーを掛け、順繰りにgoogle.script.runを実行するようにすると、GAS側で連続処理をせずとも回す事が可能です。

  1. HTML側はPromiseとTimerにてgoogle.script.runの実行と3秒間のウェイトを実現する
  2. GAS側から1000レコードずつ取得する
  3. 別のシートに追記でコピーする
  4. ループの回数分だけ2.〜3.を実行する
  5. 完了したらダイアログを閉じる
  6. これを本来非同期で実行されてしまうgoogle.script.runの実行を同期的に実行できるようになります。

GAS側コード

  • 1000件取ってコピーをしたら完了するルーチンです。
  • 初期値のcntはHTML側から受け取ります。これがレコード取得開始位置にもなります。
  • 1回目はタイトル行があるのを考慮して、rangeの取得方法を変えています。
  • データの読み書きは一発で行っています。

HTML側コード

  • 今回、Promiseとループ、そして3秒のタイマーを実現するコードはこちらのサイトからお借りしました。
  • ボタンクリックでdispが実行され、今回2000レコードなのでループ回数を1として指定(つまり2回実行)。
  • loop関数は引数の実行関数名、初期カウンタ、ループ回数を受け取り、if文で判定して再帰的に自分自身を実行しています。
  • ループ回数に達するとgoogle.script.host.closeでダイアログを閉じています。
  • 実際に処理を行うaction関数にて、setTimeoutでスリープを(msで指定)、レコード転記プロセスにgoogle.script.run.withSuccessHandlerをcallbackする形で組み込んでいます。
  • GAS側で処理を完了しcallbackしたら、グローバル変数のカウンタに1000を加えている。
  • GAS側関数実行時に引数としてグローバル変数のカウンタを渡しています。

Visualization APIを使用する

データの塊を回収するような作業に於いて、GASでやらせるとタイムオーバーしてしまうようなケースでも、HTML Service上でVisualization APIを使用する事でタイムオーバーと関係なく処理を行わせる事が可能です。

  1. GAS側からシートのリストを取得する。
  2. Visualization APIを利用してデータの塊を取得する処理を連続処理させる。取得データは配列に格納する
  3. 最終的に一塊になった配列データをGAS側に送り返してシートに書き込ませる。

こんな具合です。GAS側でやってる事は書込処理だけですので、よほど大量過ぎるデータでなければタイムアウトしないはずです。Visualization APIはHTML側で動作していますので、データの取得でタイムアウトは発生しません。実行してみた結果は、26秒でした。

※何故かこちらのやり方の場合、HTML側からGAS側へ配列データを渡す時に「Failed due to illegal value in property」というエラーが出るので、HTML側でJSON.stringify(masterdata)として渡して上げて、GAS側でJSON.parse(array)として受け取らせてます。他は全く同じコードです。

GAS側コード

HTML側コード

シート関数等で出来る事はなるべくやらせる

この項目は、Google Apps Scriptの高速化というよりは、いかにGoogle Apps Scriptを使わずに済ませるか?という事をまとめた項目です。なんでもかんでもGoogle Apps Scriptで処理をするというのは正しい事ではありません。また、データの塊取得のような大きな作業の場合、横着して日計データをガッツリ取ってくるような事をすると、直ぐにタイムオーバーになってしまいます。そこで、これらGoogle Apps Scriptで処理をする前段階で作業を行わせて、Scriptの処理速度を結果的に向上させようという事を研究し記述しています。

データは関数で集計しておく

各種データの集計と収集を行うようなスプレッドシートの場合、取得するデータ量が多すぎる場合、様々な高速化の努力をしていても、タイムアウトする事がままあります。自分の実験では、10カラム30,000件のデータ収集して、自分のスプレッドシートに書き込むルーチンを作った所、タイムアウトしてしまいました。データは日計表で集計自体は月次集計として行っています。このようなケースの場合、以下の改善点が見込めます。

  • 必要な最終データは月次集計データであるので、予め各シートで集計されてる結果を、スクリプトは収集するように変更する。
  • 各シートのデータ集計は独自の関数ではなく、スプレッドシート標準装備の関数を使用して行う。独自の関数を使用した場合、処理が間に合わずデータを取り損ねる。スプレッドシート標準の関数の場合その恐れがない。
  • 複雑な集計については、QUERY関数を使用し、場合によってはPivot指定をしてクロス集計を行わせる。

この改善を行った結果、タイムアウトしていた処理は、20秒で完了し、目的の月次集計表が作れるようになりました。データを処理すると言っても、データのデカイ塊である必要性がないのならスプレッドシートの関数にやらせるようにしましょう。

複数のシートにしない

1つのスプレッドシートにデータを蓄積するシートを複数にしてるケースがあります。理由は同じ日計表であっても入力する値が異なるという理由でカラムの数が違ったりしてる為です。故にシート名に例えばコードを振って、101 = スーパーの売上、102 = レジャー施設の売上といったような分離をしてる人も多いでしょう。確かに手入力をするのであれば、これでも良いと思います。しかし、シートが増えて、且つスプレッドシートが増えると、それを一纏めにする為に回収するスクリプトは、何度もAPIを叩かなければいけません。

このケースの場合、以下の改善が見込めます。

  1. スプレッドシート手入力をやめて、HTML ServiceでUIを作ってあげる(リッチなGUIアプリを作るを参照
  2. シートを分離させずに1枚のシートにカラム名をツケずにコードを入れる列を設けて、同居させてしまう(コードでそのレコードが何の項目なのかは分かる)
  3. 集計を行う場合にはQuery関数を使用し、Pivotにしてから取得するようにする。

この改善を行う事によって、シートデータの取得に掛かるAPIの呼び出しは1回で済みます。よって、シート数が4でスプレッドシートの数が10ある場合、合計で40回も掛かる作業が10回で済む事になります。自分の場合、シート数が10でスプレッドシートの数が30もある為、全部で300回も呼び出しをしていたのが30回で済んだので大幅に時間の削減が出来、タイムアウトしなくなりました。

条件付き書式設定を活用する

ウェブ上のGoogle Apps Scriptの高速化に関するサンプルコードに於いて、セルの色を色々弄る例が出ていますが、このコードはまだGoogle Spreadsheetにまともな条件付き書式設定がない時代のものです。現在は、条件付き書式設定に於いて数式がバッチリ使用する事ができるので、スクリプトでこういった作業を行うシーンはグッと減っています。よって、ビジュアル面でもしこういったコードを利用してる場合、スクリプトは辞めて、条件付き書式設定を使うようにしましょう。

その分、集計後に余計な作業をスクリプトでやる必要性がなくなります。

setFormulaを活用しよう

データの取得元の数々のシートには例えば商品コードとそれに纏わるカラムがマスタという形で格納されてると思われます。しかし、これらは決して効率の良いやり方ではありません。あくまでもそうしてるのは人間が見た時にわかりやすくする為にそうしてるのであって、データ取得上はコードと数量のようなデータのみで良いはずです。しかし、それだけだと集計表を作った時に見た目が数字の羅列で分かりにくい。

という事で活用するのがsetFormulasです。この項目はすでに「起動時に関数を自動補完する」にて紹介してるものです。これを利用することで以下の改善が見込めます。

  1. データ取得元からは最低限のカラムのみに絞ってデータの塊を取得するだけで済む。
  2. 取得したデータ配列に列を加える形で、setFormulasにて数式を挿入しておいてあげる。
  3. データ集計シートにマスターを設けて起き、setFormulasにて、vlookupさせるようにしておく。
  4. setFormulasを加えた配列データをシートに書き込む事でスピードアップに繋がる

集計後は、vlookup式によって自動的に自分が持ってるマスタの値をひっぱってきてくれるので、これによって全部のカラムを取得したものを再現する事ができます。取得カラム数が少なければ書き込むスピードの向上にも繋がるので、集計シートにもマスタシートを持たせるようにしましょう。

帳票類を生成する時の注意点

スプレッドシートのレコードデータを元に、例えば納品書であったり申込書、申請書類をテンプレートのスプレッドシートの該当の箇所にsetValueしてPDF化といったような作業をしてる人は結構いると思います。自分もこの手は実によく利用します。しかし、ここにも改善点があります。

  1. 各セルに対してsetValueをする場合、入力する項目の数に比例してAPIの呼び出し回数が増える事になる。

フォームの項目が10ある場合、setValueでテンプレートシートに書き込む回数は、10となるわけです。ここで以下の仕様に変更する事でどんなにフォーム項目が増えようとも、書込回数を1にする事が可能です。

  • 値をピンポイントにセルに入れるより1レコード入れて、参照させるようにする

という事です。帳票のメイン画面とは別のシートを用意し、そこに1レコード入れてあげるだけ。メイン画面はそのレコードから数式で呼び出せばいいだけです。ですので、1レコードをポンと入れただけで帳票完成になりスピードアップに繋がります。もちろん、PDFにする時はパラメータとしてメイン画面のシートのみを指定して上げれば問題ありません。帳票印刷系では必須のテクニックですね。

※納品書のような複数レコードの場合はもう1シート用意してそこにデータを書き加え、メイン画面ではFilter関数でデータ呼び出すようにすると良いでしょう。

スクリプトトリガーで予め自動処理をさせておく

シートデータの回収を1~10までやるのではなく、予めスクリプトトリガーで深夜にでも自動でプレ処理をやらせてしまうという方法もアリでしょう。データ収集シート側はその結果を拾えば良いので、結果的にスピードの向上に繋がります(トータルの作業時間は変わらないわけですが、タイムアウトせずに済むのと、実際に作業してる時間はスクリプトトリガーによる自動実行を除いた時間になるわけですから)。

当たり前ですが、スクリプトトリガーでやらせる処理がタイムアウトになるのであれば無意味です。スクリプトトリガーでやらせる処理は比較的小規模なモノに留めておきましょう。また、スクリプトトリガーはウェブアプリケーションの時と同じで、getActivveSpreadsheet()ではシートを参照できないので、自分自身のシートIDを予めスクリプトプロパティやグローバル変数に配置しておく必要性があります。

onEditでちょっとした処理は随時実行させてしまう

Google Apps Scriptには、onOpenやdoGetの他にも、onEditという特別なイベントが用意されています。これはVBAで言う所のAfterUpdateに相当するものです。しかし、編集時に必ず実行されてしまう項目なので、特定のセルや列でだけ作動させるという事が出来ません。シート全体でどこかで編集があると発動してしまいます。故に、onEditを活用する場合にはちょっとした注意が必要です。

よって、onEditで発動させる内容には必ず「参照してるセルの内容」を元に「条件分岐で処理」を記述しなければなりません。また、負荷の高い処理をやらせる事も出来ません。どこか弄る度に大きな処理が発動していては作業の邪魔になってしまいます。この高速化は地味で小規模なものなので、積極的に高速化の為に使うという事はせず、使うシーンを選びましょう。コードとしては以下の様な感じになります。

onEditの引数eには色々なセルに関する情報が詰まってるので、それを分解して判定をさせ、処理を実行するわけです。getColumnで列番号、getRowで行番号が取得できるので、特定のセルでのみ発動といった事も可能でしょう。

こういった処理を行っておく事で、Google Apps Script内でその処理に該当する部分は削除が出来、結果的にスピードアップに繋がります。

その他のポイント

その他の高速化ポイントとして、以下のようなものがあります。

  • グローバル配列にデータを格納する方法は、スピードの向上にはなります。しかし副作用として、現時点でのデータと取得時のデータの間に乖離が生じる可能性があるので、使い所はそれほど多くはありません。
  • JavaScriptでできる事はそちらでやらせるようにする。
  • 配列値のチェックについて、array.indexOfよりforループで調べたほうが早い
  • 特定の項目についてチェックを行うようなルーチンの場合、ランダムなデータのまま扱わず、予めスプレッドシートデータをソートしておいてから作業を行わせると、速度アップに繋がります。
  • データの回収系プログラムでは、自分自身のシートにimportRange関数にて予め他のシートをリンクさせておくというテクニックも高速化にプラスになります。

関連リンク

共有してみる: