Google Apps Scriptは使ってるとわかりますが、非常に動作が遅いです。おまけにローカルPC内で動作するVBAのようにプログラムのコードを組むと、大きなバッチ処理をさせているようなケースでは、間違いなくタイムアウトするでしょう。「起動時間の最大値を超えました」といったエラーが出るはずです。これは5分というタイムアウトが設定されているため、途中で処理が止まってしまい、スクリプトが継続できないわけです。メソッドの無駄遣いを減らし、効率よくバッチ処理を出来るようにコーディングをしても、限界があります。

そうなると、大きな処理は2回3回に分けて人間が実行しないといけないのか?というと、2回分程度ならそれでも良いのですが、実際の処理がどれくらい掛かるのかがわからないケースや、サーバ側の問題で遅延してるケースなどではそれも確実とは言えません。今回はこの5分の壁を超える為のスクリプトの組み方を考えてみたいと思います。今回の肝はトリガーの設置と、処理するルーチンをリロードした時に上手く途中から開始できるように組んであげることにあります。

使用するクラスとメソッド・制限

※今回はサンプルになるスプレッドシートがありません。膨大な量のダミーデータを用意できなかったので・・・・

また、Google Apps Scriptの制限としてG Suite Basicプランの場合は以下のことも考慮する必要があります。

  1. Triggerにて実行できるトータル時間は6時間/1日です。フリーアカウントだと90分/1日となっています。
  2. UrlFetchAppを実行出来る回数は、10万回/1日です。
  3. スプレッドシートへの書き込み可能セル数は、200万セルです。
  4. POSTにてアップロード可能なファイルサイズは最大10MBです。
  5. スクリプトプロパティへの値の格納は、JSON形式に変換しておけば、割と大きなデータを格納可能です。

考え方

様々なケースがあるので一概にコレというコードを記述できるわけじゃないのですが、どれについても言えるのは以下の数点です。今回は単純に別のスプレッドシートにあるデータを回収して、自分の1つのシートに集約してまとめて貼り付けるというスクリプトで考えて見たいと思います。

  1. 処理を行う対象はきちんとテーブル化しておく事(例えば、データの取込先一覧のような形でシートにテーブル形式で用意しておく事)
  2. 処理対象テーブルには固有のIDを振っておくこと。これがないと、2回目以降でどこから再開したらよいか判断がつかない為。今回はIDの代わりに何レコード目なのかを6.のスクリプトプロパティに格納する方法で代用しています。個人的にはこちらがオススメ。
  3. 開始時間と開始してからの経過時間の間を取って、稼働時間を取得するルーチンを用意しておく。
  4. 稼働時間が5分に迫る場合には、処理を中断して、6.のスクリプトプロパティにそれぞれ値を格納して、自分自身のルーチンをトリガーに登録するコードを実行するようにする。1分後くらいで十分です。
  5. 実際の単位当たりの時間がわかるなら、最初の実行時にタスクを分割しちゃって、いきなりスクリプトトリガーにタスクを登録しちゃってもいいとは思いますけれどね。
  6. 必須のスクリプトプロパティとして、どこまで処理を完了済みなのか格納するスクリプトプロパティを作っておく。ここには2.の固有のIDを格納しておく。自分の場合は、ここは、次に開始するIDを入れるようにしてある。
  7. 全ての処理が完了したかどうかを確認するための変数を作っておく。この値がtrueの場合の時だけ、設置したトリガーを削除するルーチンを回す。なくても困らないですけれどね。
  8. 最後に全ての処理が完了したら、全トリガーの削除とスクリプトプロパティを初期化するコードを実行して完了となります。

図:簡単なフローチャート

ソースコード

今回作成したコードは、「flielistシートに登録済みの100を超えるスプレッドシートの、databaseシートを取得して、自分自身のtestシートに書き込む」という処理をこの手法で実行してみました。およそ完了まで5回ほどスクリプトは実行しないといけませんでした。今回の5分の壁を超える手法によって整備しましたら、きちんと100シート全110,000レコードを取得し、無事に書き込みが完了しました。トリガーは1分後に再起動するように仕込んであります。

ポイント

  • スクリプトトリガーを使用してるので、SpreadsheetAppなどでは、getActiveSpreadsheetやgetUiなどは使えませんのでご注意ください。スクリプトが止まってしまいます。openByIdなどで代用したりしましょう。
  • スクリプトトリガーでなぜか、ミリセカンド後に実行(after(60*1000)など)でトリガーを設置してもなぜか、発動しなかったので、1分毎実行と起動直後にトリガー全削除を組み合わせて、1分後に再起動予約をしています。
  • どうしても完了報告が欲しい場合には、MailAppなどを利用して、完了した旨をメールさせるとか、Logger.logの中身をどこかに書き出すなどの手法が効果的です。どこまで完了し、例えばどこでエラーが発生したのかがわかります。
  • 配列データは一発で書き込むようにしています。5分を超えられるからといって、メソッドの使用は限りなく少なくするようにしないと効果が薄いですから。
  • メインとなる処理ルーチンは、途中から開始することを前提に作ります。なので途中から始める場合にどんな値が必要なのか?を考えると作りやすくなります。今回のソースで言えば、forループ付近が肝になります。
  • サンプルコードで巨大なデータを取得して貼り付ける場合、取得するのは非常に高速に取得できるのですが、貼り付けが物凄く遅いので、これでもタイムオーバーしてしまうケースがあります。その場合、まとめて貼り付けるのではなく、わざとシート単位で貼り付けるコードにデチューンすると、上手く5分の壁を超えられるようになります。30,000レコード・10カラムを貼り付けるケースではこのままでは失敗します。

関連リンク

共有してみる: