GAS 処理速度を速めるためのコツを教えてください

Question

GAS初心者です
よろしくお願い致します

teratailさんで質問をしながら進めてまいりました
みなさんのご協力に感謝いたします

今回はGASの処理時間の工夫について教えていただきたく、質問いたします

「フォームの回答 1」というシートがGoogleフォームの回答が反映されているシートで、2500程度の行数と7列のあるデータを扱います

そのシートのデータを1日の終わりに、指定された各スプレッドシートにコピーして、「フォームの回答 1」のシートはデータを削除して翌日を迎える

という流れを作りたいと考えています

現状、下記のスクリプトの記述で、GW後からほぼ毎日動作してくれているのですが、5/16はエラー（Exception:ドキュメントにアクセス中にスプレッドシートのサービスがタイムアウトしました）が出ました

「6分以内に処理を終えないといけない」という問題だと思い、トリガーの設定ページで処理時間の確認をしました

すると、エラーがでた5/16は93.745秒
それ以外の日も565秒〜808秒かかりながらも処理が完了していると表示されていました

トリガーは1日の終わりの23:00〜00:00に発動するように設定しています

6分の壁ということであれば、360秒が限界であり、この処理時間がエラーの原因になっているのだろうと自分としては予想しましたが、解決策を見出すことができませんでした

どうぞよろしくお願い致します

処理の内容としては、回答データは行ごとに個人のデータが回収されているので、行ごとに指定されたスプレッドシートにコピーをしたいです

以下、前回の質問のURLです
https://teratail.com/questions/336191

function myFunction() {
  //コピー元になるデータを取得
  var ss        = SpreadsheetApp.openById('Googleフォームの回答回収スプレッドシート');
  var sh        = ss.getSheetByName('フォームの回答 1');
  var _lastRow  = sh.getLastRow();
  var data      = sh.getRange(2, 1, _lastRow, 7).getValues();
  data = sh.getRange("A2:G").getValues();


  var copymasterdata  = sh.getRange(2,1,_lastRow,5).getValues();
  var copymastersh    = ss.getSheetByName('マスターデータ保管');
  var _lastRowmasterPlus1  = copymastersh.getLastRow()+1;

  copymastersh.getRange(_lastRowmasterPlus1,1,copymasterdata.length,5).setValues(copymasterdata);

  var classidsh   = ss.getSheetByName('IDキー');
  var classiddata = classidsh.getDataRange().getValues();
  // キーごとにデータを集約するための連想配列
  var arrays = {};
  // キーシートのシートIDをarraysのキーに設定
  for (var i = 0; i < classiddata.length; i++){
    arrays[classiddata[i][0]] = [];
  }

  // データを各シートIDごとに集約する。
  for (var i = 0; i < data.length; i++){
    line = data[i];
    // キーデータのある6列目～7列目を順番に繰り返す。
    for(var j = 5; j < 7; j++){
    // 6列目または7列目のキーがarraysに存在するならば、
    // そのキーに対応するデータをpush
    if(line[j] in arrays){
        arrays[line[j]].push(line.slice(0,5));
      }
    }
  }

  // 振り分け先の各スプレッドシート共通のシート名
  var targetSheetName = 'データ保管';

  // 集約したデータを各スプレッドシートに書き込む
  for (var i = 0; i < classiddata.length; i++){
    var classid = classiddata[i][0];
    var copyss = SpreadsheetApp.openById(classid);
    var copysh = copyss.getSheetByName(targetSheetName);
    if (copysh == null){
      Logger.log(`エラー：スプレッドシート[ID=${classid}] に「${targetSheetName}」という名前のシートが存在しません。`)
      return;
    }
    var copy_lastRowPlus1 = copysh.getLastRow() + 1;  // 書き込み先の最終行+1
    var copydata = arrays[classid];  // キーを指定して書き込むデータを格納。
    if (copydata == null || copydata.length < 1) continue; // エラー対策。データがない場合はスキップ。
    var lastColumn = 5  // カラム数を設定
    var lastRow = copydata.length;        // 行数を取得
    copysh.getRange(copy_lastRowPlus1, 2, lastRow, lastColumn).setValues(copydata);
  }
  Logger.log('処理終了');


//Logger.log(_lastRow);
  sh.deleteRows(3,_lastRow-2);
  sh.getRange('A2:G2').clearContent();

  //二次元配列に埋め込む関数をセット
  var formformula   = [
    [
      `=arrayformula(vlookup(B2:B,'名簿'!A:J,10,0))`,
      `=arrayformula(vlookup(B2:B,'名簿'!A:K,11,0))`
    ]
  ];

  //関数を埋め込む
  sh.getRange(2,6,1,2).setValues(formformula);
}

以下、ダミーファイルによるスプレッドシートの構成です

これは「IDキー」のシートで、タイトルなどはなく、検索したいスプレッドシートキーのみを入力しています

写真では「データ保管用」となっていますが、実際には「データ保管」という名前のシートを使用しています

以下、2021/5/18 21:06 追記

振りレッドシートに含まれているシートのダミー画像です
全部で5つのシートで構成されています
それぞれのシートに関数が組み込まれているので、画像に内容を書き込みました

1. データ保管
2. today
3. 項目1
4. 項目3
5. query

todayシート

項目1シート

項目3シート

queryシート

Comments

[退会済みユーザー]

前の御質問の追記で
「F列とG列には、別シートからarrayformula(vlookup)という関数が2行目に入っており、回答がされていくと同時に所属するシートIDが割り当てられるようにしてあります」

「F列はAグループから、G列はBグループからIDを検索した方がスピードアップできそうなイメージはあったのですが、どうして良いのかわからなかったので、AグループとBグループのIDをまとめてA列に並べて、A列1列のみのIDシート（classidsh）を現在は利用しています。」

ここの部分ですが、文章だけでは正直、正確なイメージが分かりにくいです。（どのシートのＧ列？Ａグループとは？Ｂグループとは？）

前にもコメントしましたが、単純に「１シート内のベタ貼り付けのデータ」を別のシートに振り分けコピーするのであれば、
私の環境（CPUが特別早いわけでもない特に普通のノートパソコンです。ネットも早くないです）で2500件を100グループ以上に分けるのも、70秒くらいで処理を終えられていました。

質問者さんの環境で時間がかかる原因は、単純な１シート内のベタで貼り付けられたデータの処理ではなく、「各シートを連携し、シート元（またはシート先）に数式を入れている」ということがあるのだと思われます。
つまり、貼付処理ごとに数式計算処理が走っているために時間がかかっている可能性があります。
しかしながら、全体がどういう状態であるのか示していただかないことには、再現できず解析できないため、解決のしようがないと思います。

したがって、可能であれば下記を示していただきたく思います。
・関係するシートの画面キャプチャ及び各シートの意味（そのシートは何をするためのシートかという説明）
・シート内に入っている数式のキャプチャ（及びその数式の意味・目的）
・もし質問のスクリプトが一部でも省略したものであるならば、省略していない実際のスクリプト（省略した部分がバグや遅延の要因になっていることもあるため）

個人情報の問題でそのまま載せられないならば、ダミーの適当なデータを使用し、「同じようなデータがあと２５００行続きます」という説明で構いません。
知りたいのは、スクリプトで処理しようとしている各シート・関係する数式の場所とその数式の具体的な内容、すなわちまとめると"問題となっている処理の、正確かつ具体的な全体像"ということです。

[Kazuya_s]

qnoirさん、いつもありがとうございます

現在ダミーファイルを用意していますが、名簿ファイルにもvlookup関数が複数埋め込まれており、ご指摘の通り処理速度を遅くしていた可能性を見つけられました

いったん、不要なvlookupを削除し、値として貼り付けてみました

そしてダミーファイルを用意します

[退会済みユーザー]

お手数おかけします。
前の御質問で、私の回答中に、テスト時に使っていた余計な１行が入っていたので、削除しました。
（７行目の「data = sh.getRange("A2:G").getValues();」です。）すみません。

[退会済みユーザー]

振り分け先（貼り付け先）の各スプレッドシートには数式が全く入っていないという理解でよろしいでしょうか？
→質問文より、Ａ列に数式が入っていることは確認しました。

（追加質問）各振り分け先のスプレッドシートには「データ保管」シート１枚しか存在せず、同じスプレッドシート内の他のシートや、他のスプレッドシートから数式で参照されてはいない、という理解でよろしいでしょうか。

[Kazuya_s]

振り分け先のスプレッドシートには「データ保管」とその他に4つのシートがあります

その他の4つのシートは「データ保管」シートから関数でデータを拾うことになっています

[退会済みユーザー]

具体的にどのような関数・範囲でしょうか？
貼り付け先が他のシートから計算式（関数）で参照されている場合、貼り付けるたびに再計算が発生するため、遅くなると思います。

[Kazuya_s]

度々、ありがとうございます

振り分け先のスプレッドシートのダミーファイルを作りにくいので、まず文面で説明させていただきます

1. 所属メンバーの名簿があり、importrangeとvlookupでGoogleフォームと連動している「フォームの回答 1」シートからデータを検索しています

2. 縦軸に名簿、横軸に日付（5/18のようなm/d形式）。同一スプレッドシート内の「データ保管」シートのA列にある検索値（日付&メールアドレス）からvlookupで「項目1」のデータを検索しています

3. 2.と同様のシートで、読み出す内容が異なるシートで「項目3」を検索しています

4. query関数を用いて、「データ保管」シートの中で「項目2」に入力があったデータを検索しています

以上の構成となっております

したがいまして、3つのシートが「データ保管」シートを参照・検索しています

[macaron_xxx]

とりあえずログに処理時間を吐き出して、ボトルネックがどこかを探しましょう。

Answer 1

みなさん短くするご提案なので、
別のご提案としてgoolge Workspaceにすると制限が伸びて30分になります。
どうにも時間内で収まらなさそうであれば、有料版も検討の価値ありです。700円くらいなので。

処理が制限時間ギリギリということであれば、
各シートへ貼り付ける前のデータを一時的にどこかのシート保存しておき、
時間をずらしたトリガーで貼り付けという方法もありです。

Comments

[Kazuya_s]

Tatsunosukeさん、お返事ありがとうございます

現在、Google Workspace for educationの登録となっています

そうなると、6分の壁が関係ないから800秒くらいのスクリプトが走って完了まで動き続けてくれているということなのでしょうか

[Tatsunosuke]

すみません、ちょっとお待ちください。制限が、６分になってます。
ちょっと確認いたします。

[Tatsunosuke]

全てのアカウントが6分に変更されたようです。
大変失礼しました。

となると、やはり処理を分けるのがよろしいかと思います。

[Tatsunosuke]

ただ、僕の方もいくつか6分を超える処理が走るものがあるのですが、今のところトラブルの連絡が上がってこないので暫定処置として制限の6分を過ぎても問題ないのかも知れません。

[Kazuya_s]

なるほど
やはり無料版なので、いずれかの形で6分の壁を越えなくてはならないのですね
私の場合、作業を3つか4つに分ければ良いということですよね

[Tatsunosuke]

時間かがかかるのは、SSへのアクセス(主に読み出し、書き込み)なので、ここが分割できれば問題ないと思います。

もし自分がやるとしたら、
◼️データを集計用の関数
①各シートに貼り付けるデータをまとめる。
②一時保存としてssに保存

終了

◼️貼り付け用の関数
2時間毎に起動するようにトリガーを設定。
①開始時間取得
②一時保存データを取得
データが空でなければ↓
③各シートへ貼り付ける
④途中で時間が5分を経過したら、
一時保存のデータを一時保存用のシートに保存する。
貼り付けが完了した配列は削除。

一時保存用のシートに値がある限りは、貼り付ける用の関数が処理し続けるので、24h/2時間*5分=60分は回せます。

確かgasの一日制限も60分だったような。

万が一これでも処理しきれないと言うことになると、そもそものシステムの設計を見直す必要があります。

Answer 2

同じようなシート構成・数式構成で、2500行、７列、100個のクラス分けで
トリガーを設置して何回かやってみましたが、いずれも手元では2-3分以内で終了しました。

データの貼り付け先シートが複数のシートから数式で参照されているとのことだったため
再計算により遅くなるかもしれないと思ったのですが、
現状シートを開かない限り再計算されないように思われるため、
スクリプト上での単純な貼り付けであればそこまで処理速度に影響はないようです。

質問文によれば「90秒でタイムアウトになることもあれば、6分以上（565秒〜808秒）かかっても終わることもある」とのことで、謎です。（特に90秒でタイムアウトの件）

環境要因があるのかもしれません。

6分以上の時間がかかる前提で、
Google Apps Scriptで6分の壁（タイムアウト）を突破する
を参考に、タイムアウト時間内で処理を分割するという手法が適用できるかもしれませんが、
私の力では実装できませんでした。

なお、前回では記載が無かった「貼り付け先の『データ保管』シートのＡ列に数式が入っている」という点に関してですが
この状態で前のスクリプトを実行すると、既存データの続きではなく、表示画面の最後尾にデータが書き込まれてしまうと思いました。(自分の環境ではそうなった)
（=arrayformura(b2:b～)が設定されているシートに、getLastRow()関数を適用した場合、データのある最終行ではなく、ワークシートの最大範囲の最終行が返ってくるため）

したがって、下記のように修正した方がよいかもしれません。
（これ自体は処理スピードに関係ないですが・・・）

function myFunction2() {
  //コピー元になるデータを取得
  var ss        = SpreadsheetApp.openById('Googleフォームの回答回収スプレッドシート');
  var sh        = ss.getSheetByName('フォームの回答 1');
  var _lastRow  = sh.getLastRow();
  var data      = sh.getRange(2, 1, _lastRow, 7).getValues();
-  data = sh.getRange("A2:G").getValues(); // テスト用に誤って書いていたコードのため削除

  var copymasterdata  = sh.getRange(2,1,_lastRow,5).getValues();
  var copymastersh    = ss.getSheetByName('マスターデータ保管');
  var _lastRowmasterPlus1  = copymastersh.getLastRow()+1;

  copymastersh.getRange(_lastRowmasterPlus1,1,copymasterdata.length,5).setValues(copymasterdata);

  var classidsh   = ss.getSheetByName('IDキー');
  var classiddata = classidsh.getDataRange().getValues();
  // キーごとにデータを集約するための連想配列
  var arrays = {};
  // キーシートのシートIDをarraysのキーに設定
  for (var i = 0; i < classiddata.length; i++){
    arrays[classiddata[i][0]] = [];
  }

  // データを各シートIDごとに集約する。
  for (var i = 0; i < data.length; i++){
    line = data[i];
    // キーデータのある6列目～7列目を順番に繰り返す。
    for(var j = 5; j < 7; j++){
    // 6列目または7列目のキーがarraysに存在するならば、
    // そのキーに対応するデータをpush
    if(line[j] in arrays){
        arrays[line[j]].push(line.slice(0,5));
      }
    }
  }

  // 振り分け先の各スプレッドシート共通のシート名
  var targetSheetName = 'データ保管';

  // 集約したデータを各スプレッドシートに書き込む
  for (var i = 0; i < classiddata.length; i++) {
    var classid = classiddata[i][0];
    if (classid == null || classid == 0) continue;
    var copyss = SpreadsheetApp.openById(classid);
    var copysh = copyss.getSheetByName(targetSheetName);
    if (copysh == null) {
      Logger.log(`エラー：スプレッドシート[ID=${classid}] に「${targetSheetName}」という名前のシートが存在しません。`);
      continue;
    }
    // キーを指定して書き込むデータをcopyataに格納。
-    var copy_lastRowPlus1 = copysh.getLastRow() + 1;  // 削除
// ↓のようにしないと、既存データの続きにデータが書き込まれないと思われます。
+    var copy_lastRowPlus1 = copysh.getRange("B:B").getValues().filter(String).length + 1; // 書き込み先の最終行+1

    var copydata = arrays[classid]; // キーを指定して書き込むデータを格納。
    if (copydata == null || copydata.length < 1) continue; // エラー対策。データがない場合はスキップ。
    var lastColumn = 5; // カラム数を設定
    var lastRow = copydata.length; // 行数を取得

    copysh.getRange(copy_lastRowPlus1, 2, lastRow, lastColumn).setValues(copydata);

  }
  Logger.log("処理終了");

  //Logger.log(_lastRow);
  sh.deleteRows(3,_lastRow-2);
  sh.getRange('A2:G2').clearContent();

  //二次元配列に埋め込む関数をセット
  var formformula   = [
  [
      `=arrayformula(vlookup(B2:B,'名簿'!A:J,10,0))`,
      `=arrayformula(vlookup(B2:B,'名簿'!A:K,11,0))`
  ]
  ];

  //関数を埋め込む
  sh.getRange(2,6,1,2).setValues(formformula);
}

（以上、根本的な解決とはならず申し訳ございません。）

Comments

[Kazuya_s]

色々と実験までしてくださり、いつもありがとうございます。

貼り付け先の「データ保管」シートは、これまでの蓄積があります

日付ごとにこぴーをしているので、5/18の昼時点では5/17までのデータがあり、5/18の23:00-0:00に振り分けコピーがされます

5/17のデータがすでにあるため、getLastRow()に問題はありません

エラーのデータが伸びてしまうこともありましたが、余計な行を削除してしまうことでなぜか解決し、新しいデータを貼り付けても無駄なエラー行が作成されることがなくなりました

このような状況ですが、書き加えていただいたfilterスクリプトを入れた方が安心な感じになりますか？

[退会済みユーザー]

蓄積があるということですね。現状で問題がないなら特に変えなくても大丈夫だと思います。

[Kazuya_s]

なるほど
ありがとうございます