Google Apps Scriptで6分の壁（タイムアウト）を突破する 🌴 officeの杜

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

まずは高速化するためのテクニックを身に着け、それでも足りない場合にこのテクニックを使いましょう。

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

1 使用するクラスとメソッド・制限
2 考え方
3 ソースコード
4 ポイント
5 関連リンク
- 5.1 関連記事

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

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

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

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

※G Suite Business以上の場合、6分ではなく30分まで拡大されています

考え方

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

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

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

ソースコード

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

function testget() {
  //色々変数を宣言
  deleteTrigger();
  var mvsheet = SpreadsheetApp.openById("ここに、このスプレッドシートのIDを入力");
  var sheet = mvsheet.getSheetByName("filelist");
  var ss = mvsheet.getSheetByName("test");
  var filelist = sheet.getRange("B2:B").getValues();
  var dataman = new Array(); 
  var Properties = PropertiesService.getScriptProperties();
  
  //トリガー用のフラグ変数と値の取得
  var taskman = parseInt(Properties.getProperty("task"));
  var endflag = true;

  //時刻格納用の変数
  var starttime = new Date();
  
  for(var i = taskman;i<filelist.length;i++){
      //現在時刻を取得する
      var nowtime = new Date();
      
      //時刻の比較をしてオーバーしそうだったら、トリガー追加で処理を停止
      var nowdiff = parseInt((nowtime.getTime()-starttime.getTime())/(1000*60));
      
      //6分を超えていたら中断処理
      if(nowdiff >= 5){
        //配列データをシートの最終行以下に書き出す
        var sheetlastRow = ss.getLastRow() + 1;   
        var lastColumn = dataman[1].length;　　//カラムの数を取得する
        var lastRow = dataman.length;  　　　　//行の数を取得する
        ss.getRange(sheetlastRow,1,lastRow,lastColumn).setValues(dataman);          
        
        //次の処理のトリガーを設置,プロパティにtask番号を登録しプログラムを停止
        Properties.setProperty("task",taskman);
        setTrigger();
        endflag = false;
        return;
      }
      
      //ファイルIDをひとつ取り出す
      var spreadman = filelist[i][0];
      
      //databaseシートの値を配列にぶっこむ
      var kinoko = SpreadsheetApp.openById(spreadman).getSheetByName("database").getRange("A2:F").getValues();
      Array.prototype.push.apply(dataman,kinoko);

      //次の行番号を格納しておく
      taskman = Number(taskman) + 1;
      
  }    
  
  //タスクが全部終了した時の処理
  if(endflag == true){
    //配列データをシートの最終行以下に書き出す
    var sheetlastRow = ss.getLastRow() + 1; 
    var lastColumn = dataman[1].length;　　//カラムの数を取得する
    var lastRow = dataman.length;  　　　　//行の数を取得する
    ss.getRange(sheetlastRow,1,lastRow,lastColumn).setValues(dataman);  
    
    //プロジェクトトリガーを全削除
    deleteTrigger();
    
    //トリガー用変数の初期化
    Properties.setProperty("task",0);
  }
}

//トリガーを全削除する関数
function deleteTrigger() {
  var allTriggers = ScriptApp.getScriptTriggers();
  for(var i=0; i < allTriggers.length; i++) {
      ScriptApp.deleteTrigger(allTriggers[i]);
  }
}

//トリガーを設置する関数（testget関数を実行予定として登録）
function setTrigger(){
   var triggerman = ScriptApp.newTrigger("testget")
                   .timeBased()
                   .everyMinutes(1)
                   .create();
}

function testget() {

//色々変数を宣言

deleteTrigger();

var mvsheet = SpreadsheetApp.openById("ここに、このスプレッドシートのIDを入力");

var sheet = mvsheet.getSheetByName("filelist");

var ss = mvsheet.getSheetByName("test");

var filelist = sheet.getRange("B2:B").getValues();

var dataman = new Array();

var Properties = PropertiesService.getScriptProperties();

//トリガー用のフラグ変数と値の取得

var taskman = parseInt(Properties.getProperty("task"));

var endflag = true;

//時刻格納用の変数

var starttime = new Date();

for(var i = taskman;i<filelist.length;i++){

//現在時刻を取得する

var nowtime = new Date();

//時刻の比較をしてオーバーしそうだったら、トリガー追加で処理を停止

var nowdiff = parseInt((nowtime.getTime()-starttime.getTime())/(1000*60));

//6分を超えていたら中断処理

if(nowdiff >= 5){

//配列データをシートの最終行以下に書き出す

var sheetlastRow = ss.getLastRow() + 1;

var lastColumn = dataman[1].length;　　//カラムの数を取得する

var lastRow = dataman.length; 　　　　//行の数を取得する

ss.getRange(sheetlastRow,1,lastRow,lastColumn).setValues(dataman);

//次の処理のトリガーを設置,プロパティにtask番号を登録しプログラムを停止

Properties.setProperty("task",taskman);

setTrigger();

endflag = false;

return;

}

//ファイルIDをひとつ取り出す

var spreadman = filelist[i][0];

//databaseシートの値を配列にぶっこむ

var kinoko = SpreadsheetApp.openById(spreadman).getSheetByName("database").getRange("A2:F").getValues();

Array.prototype.push.apply(dataman,kinoko);

//次の行番号を格納しておく

taskman = Number(taskman) + 1;

}

//タスクが全部終了した時の処理

if(endflag == true){

//配列データをシートの最終行以下に書き出す

var sheetlastRow = ss.getLastRow() + 1;

var lastColumn = dataman[1].length;　　//カラムの数を取得する

var lastRow = dataman.length; 　　　　//行の数を取得する

ss.getRange(sheetlastRow,1,lastRow,lastColumn).setValues(dataman);

//プロジェクトトリガーを全削除

deleteTrigger();

//トリガー用変数の初期化

Properties.setProperty("task",0);

}

//トリガーを全削除する関数

function deleteTrigger() {

var allTriggers = ScriptApp.getScriptTriggers();

for(var i=0; i < allTriggers.length; i++) {

ScriptApp.deleteTrigger(allTriggers[i]);

}

//トリガーを設置する関数（testget関数を実行予定として登録）

function setTrigger(){

var triggerman = ScriptApp.newTrigger("testget")

.timeBased()

.everyMinutes(1)

.create();

}

ポイント

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

MENU

Google Apps Scriptで6分の壁（タイムアウト）を突破する

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

考え方

ソースコード

ポイント

関連リンク

関連記事

コメント

コメントを残すコメントをキャンセル

G Suite加入用プロモコード

最近の投稿

表示数

人気の投稿とページ

おすすめ

G Suite関連リンク

目次

MENU

Google Apps Scriptで6分の壁（タイムアウト）を突破する

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

考え方

ソースコード

ポイント

関連リンク

関連記事

コメント

コメントを残す コメントをキャンセル

G Suite加入用プロモコード

最近の投稿

表示数

人気の投稿とページ

おすすめ

G Suite関連リンク

目次

コメントを残すコメントをキャンセル