質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

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

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 911

score 3

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);
}

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

フォームの回答 1

マスターデータ保管

名簿

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

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

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

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

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

today
todayシート

項目1
項目1シート

項目3
項目3シート

query
queryシート

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • qnoir

    2021/05/18 19:17

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

    キャンセル

  • Kazuya_s

    2021/05/18 20:42

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

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

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

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

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

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

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

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

    キャンセル

  • macaron_xxx

    2021/05/19 08:53

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

    キャンセル

回答 2

0

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

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

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

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

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

なお、前回では記載が無かった「貼り付け先の『データ保管』シートのA列に数式が入っている」という点に関してですが
この状態で前のスクリプトを実行すると、既存データの続きではなく、表示画面の最後尾にデータが書き込まれてしまうと思いました。(自分の環境ではそうなった)
(=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);
}

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/05/18 22:11

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

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

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

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

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

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

    キャンセル

  • 2021/05/18 22:16

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

    キャンセル

  • 2021/05/19 18:55

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

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/05/19 17:56

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

    キャンセル

  • 2021/05/19 18:08

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

    キャンセル

  • 2021/05/20 12:53

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


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

    終了

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

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問