2018年11月24日 / 最終更新日時 : 2024年12月31日 officeの杜 Google

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

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

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

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

目次 [隠す]

1 使用するクラスとメソッド・制限
2 サンプルスプレッドシートの注意点
3 考え方
4 ソースコード
- 4.1 スクラッチで書く場合
- 4.2 簡単に記述出来るライブラリ登場
5 ポイント
6 関連リンク

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

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

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

※2022年9月、これとは別の手法を作成しました。以下のエントリーを御覧ください。

サンプルスプレッドシートの注意点

今回のサンプルスプレッドシートは以下のような仕様になっています。メニューからファイルリストの作成、データの取得、データのクリアが可能になっています。

指定のフォルダID内にある10個のサンプルデータのファイルリストを持っています。
ファイルリストに従ってデータの取得を行うのが今回のメインとなるスクリプトです。
通常は5分を超えたらトリガーをセットして、1分後に途中から開始させるわけですが、V8にて速度向上したため、今回のテストに限り、2分で切っています。通常は4分で終わると思います。
但し、処理速度が早いと2分でもきちんと完了してしまうことがあります。
サンプルデータは１個あたり3列x50000行 = 15万セルのデータの塊を10個連続取得して、自身のdataシートへ追記型でコピーします。
終了時に総スクリプト実行時間を返します。
サンプルデータ側にもダミーデータ生成用のスクリプトをいれてありますが、今回は割愛します。
追記型であるため、２回目テストをするまえに、データのクリアをしておいてください。およそ10ファイルで150万セル分のデータを取得します。

考え方

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

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

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

ソースコード

スクラッチで書く場合

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

//ファイルリストからデータを取得する
function testget() {
  //既存のトリガーを削除しておく
  deleteTrigger();
  
  //ファイルリストシートを取得
  var mvsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = mvsheet.getSheetByName("filelist");
  var filelist = sheet.getRange("A2:C").getValues();
  var ui = SpreadsheetApp.getUi();

  //書き込み先シートの指定
  var ss = mvsheet.getSheetByName("data");
  
  //配列の用意
  var dataman = []; 
  
  //トリガー用のフラグ変数と値の取得
  var Properties = PropertiesService.getScriptProperties();
  var taskman = parseInt(Properties.getProperty("task"));
  var endflag = true;
  
  //taskmanの値を判定(値がない時は0とする）
  if(taskman >= 0){
    //特になにもしない
  }else{
    taskman = 0;
    Properties.setProperty("task", 0);
  }

  //時刻格納用の変数
  var starttime = new Date();
  
  //taskmanが0の時だけ総開始時間を記録する
  if(taskman == 0){
    Properties.setProperty("scriptstart", starttime);
  }

  //taskmanの番号からスタートする
  for(var i = taskman;i<filelist.length;i++){
      //現在時刻を取得する
      var nowtime = new Date();
      
      //時刻の比較をしてオーバーしそうだったら、トリガー追加で処理を停止
      var nowdiff = parseInt((nowtime.getTime()-starttime.getTime())/(1000*60));
      
      //6分を超えていたら中断処理（今回はテストで2分で中断。本来は5分を指定）
      if(nowdiff >= 2){
        //配列データをシートの最終行以下に書き出す
        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をひとつ取り出す（B列）
      var spreadman = filelist[i][1];
      
      //databaseシートの値を配列にぶっこむ
      var kinoko = SpreadsheetApp.openById(spreadman).getSheetByName("data").getRange("A2:C").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);
    
    //スクリプトの総稼働時間を算出
    var nowtime = new Date();
    Logger.log(nowtime);
    var scripttime = new Date(Properties.getProperty("scriptstart"));
    var difftime = parseInt((nowtime.getTime()-scripttime.getTime())/(1000*60));

    //終了処理
    ui.alert("処理が完了しました。スクリプトの総稼働時間は" + difftime + "分でした。");
    
  }
}

//トリガーを全削除する関数
function deleteTrigger() {
  var allTriggers = ScriptApp.getProjectTriggers();
  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 cleardata(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data").getRange("A2:C");
  var ui = SpreadsheetApp.getUi();
  
  ss.clearContent();
  SpreadsheetApp.flush();

  ui.alert("データ消去しました。")

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

//ファイルリストからデータを取得する

function testget() {

//既存のトリガーを削除しておく

deleteTrigger();

//ファイルリストシートを取得

var mvsheet = SpreadsheetApp.getActiveSpreadsheet();

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

var filelist = sheet.getRange("A2:C").getValues();

var ui = SpreadsheetApp.getUi();

//書き込み先シートの指定

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

//配列の用意

var dataman = [];

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

var Properties = PropertiesService.getScriptProperties();

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

var endflag = true;

//taskmanの値を判定(値がない時は0とする）

if(taskman >= 0){

//特になにもしない

}else{

taskman = 0;

Properties.setProperty("task", 0);

}

//時刻格納用の変数

var starttime = new Date();

//taskmanが0の時だけ総開始時間を記録する

if(taskman == 0){

Properties.setProperty("scriptstart", starttime);

}

//taskmanの番号からスタートする

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

//現在時刻を取得する

var nowtime = new Date();

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

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

//6分を超えていたら中断処理（今回はテストで2分で中断。本来は5分を指定）

if(nowdiff >= 2){

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

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をひとつ取り出す（B列）

var spreadman = filelist[i][1];

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

var kinoko = SpreadsheetApp.openById(spreadman).getSheetByName("data").getRange("A2:C").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);

//スクリプトの総稼働時間を算出

var nowtime = new Date();

Logger.log(nowtime);

var scripttime = new Date(Properties.getProperty("scriptstart"));

var difftime = parseInt((nowtime.getTime()-scripttime.getTime())/(1000*60));

//終了処理

ui.alert("処理が完了しました。スクリプトの総稼働時間は" + difftime + "分でした。");

}

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

function deleteTrigger() {

var allTriggers = ScriptApp.getProjectTriggers();

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 cleardata(){

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data").getRange("A2:C");

var ui = SpreadsheetApp.getUi();

ss.clearContent();

SpreadsheetApp.flush();

ui.alert("データ消去しました。")

}

簡単に記述出来るライブラリ登場

6分を超える為のテクニックですが、なかなかテクニカルな仕組みであるため、ハードルが高いのは事実です。これを少し簡単に書けるようにしてくれるライブラリが登場しました。スクリプトIDは「1aFT6J1RlHtYIj1_O5jqz0iHInI3kOyIXZ1Xr1WeVXYCZgJ8X5on6MO9-」を追加し、コード本体はここから閲覧する事が可能です。

サイトでは詳しくコードの記述方法が開示されていますので、どうしても6分を超えるコードを書く必要がある人は一見の価値はあるのではないかと思います。

ポイント

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