GASでスプレッドシートの処理が遅いです。改善できるかどうか、スクリプトを見ていただきたいです。

Question

スプレッドシートのデータをslackの指定チャンネルに投稿するスクリプトなのですが、
どうも遅いです。
早くできる箇所があればお教えください。

【概要】
Googleフォームからの回答結果がリンク先のスプレッドシートに集約されます。
１時間に60回答程です。
データ項目数が多い為、データを分けてimportrangeで複数の別ファイルに読み込ませています。

集計結果や投稿内容そのもの等、結果を文字列結合させたセル内容をslackの指定チャンネルに投稿させています。

スクリプトを埋め込んでいる（バインドさせている）ファイルには、queryやimportrange等を多用していますので、
そもそもこの時点で処理に時間が掛かっていることは承知しております。

処理は指定シートで行っており、目視ではデータは表示されているのですが、
処理が遅く、かつ回答も随時増える為、処理が遅延してしまっています。

出来れば投稿から数分以内にはslack投稿させたいです。

※ライブラリとしてSlackAppを使用しています。

function doPost() {

  var token = PropertiesService.getScriptProperties().getProperty('SLACK_ACCESS_TOKEN');
  var app = SlackApp.create(token);
  var message;
  var bot_name = "slackのbot名称";
  var bot_icon = "https://xxxx.png";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名を指定しています。");

  var columnIVals = sheet.getRange('I:I').getValues(); // I列の値を配列で取得
  var ILastRow = columnIVals.filter(String).length;  //空白を除き、配列の数を取得

  
   Logger.log(ILastRow);
  
  for(var i = 1; i <= ILastRow; i++) {
    if(!sheet.getRange(i, 12).getValue()){ 
        message = sheet.getRange(i, 11).getValue();
      sheet.getRange(i, 12).setValue(true);

    Logger.log(message);

    var branch = sheet.getRange(i,8).getValue();

    Logger.log(branch);

      if(branch=="A支店")
      {
        app.postMessage("#A支店チャンネル", message, {
         username: bot_name,
         icon_url: bot_icon,
       })
      }
      else if(branch=="B支店")
      {
        app.postMessage("#B支店チャンネル", message, {
          username: bot_name,
          icon_url: bot_icon,
        })
      }
      else if(branch=="C支店")
      {
        app.postMessage("#C支店チャンネル", message, {
          username: bot_name,
          icon_url: bot_icon,
        })
      }
      else{
        app.postMessage("#テスト用チャンネル", message, {
          username: bot_name,
          icon_url: bot_icon,
        })

      }
    }
  }
};

以上です。どうぞよろしくお願いいたします。

Comments

[alphase]

コードのインデントをもう少し綺麗にしていただけないでしょうか？それぞれ閉じかっこがどこにあるのかちょっとわかりにくいです。

[tak123]

すみません！インデントが反映されないので、削除依頼しております。

[tak123]

自己流で申し訳ありませんが、インデントを反映してみました。
よろしくお願いいたします。

[macaron_xxx]

あなたの自己流のインデント辞めたほうがいいですよ。
まったく意味がわからないインデントです。

[tak123]

macaron_xxxさん、ありがとうございます。
直してみました。いかがでしょうか？

[macaron_xxx]

多少マシになりましたけど…。
GASのスクリプトエディタを使っているのであれば、Shift+Tabで自動インデントしてくれるので、それを利用してください。

Answer 1

ほんとにこのコードの実行が「数分以内」を超えるほど遅延していると仮定したうえでの回答。

遅いのは

for(var i = 1; i <= ILastRow; i++) {
    if(!sheet.getRange(i, 12).getValue()){ //12列目（L列）に処理が終わるとTRUEを入れるようにしています。ブランクの時にforが動作します。
        message = sheet.getRange(i, 11).getValue();
      sheet.getRange(i, 12).setValue(true);

ここです。
forの内部でapiコールをしているので、遅いです。forの手前でRangeに対してgetValuesをしてobject[][]にして、これをループすれば早くなると思います。
また、スラックへの投稿も同期的に待つので、支店ごとにまとめて投稿すれば、それはそれで早くなると思います。

そもそも集計をしていなくて、かつ時間差なく知りたいという話からすれば、フォームに投稿がある都度スラックに投げるスクリプトを書いて、フォーム送信時トリガーをかければよいはず。なぜそうしないのかわからないです(そうすれば毎回過去の回答をスキップしたり、過去の回答にtrueを書いたりしなくてすむ)。

このシート内にimportxmlがないなら、他のシートにあっても処理には影響しないと思います。

余談ですが、処理が数分を超えて長ければgsuiteだとしても打ち切られるはず(具体的な制限は覚えてないので切られてないだけかも)なので、ほんとにこれが遅いのかは計測すべき(推測するな計測せよは工学の基本なので)。
関連してなんでdoPostなのかわからないんですが、時間主導トリガーの間隔を狭めりゃいいのでは？

Comments

[tak123]

for内部のapiコールに時間が掛かる件、ありがとうございます。

フォームにスクリプトをバインドさせていない理由ですが、
１つのフォームだけからの回答内容だけでなく、
複数のフォームからの回答内容に加え、そのデータを集計した数値も含めて、
文字列結合させた内容をslackに投稿する必要がある為です。

また余談の部分のgsuiteだと打ち切られる件、仰る通りです。
Apps Scriptの実行数で処理時間が分かるのですが、
スプレッドシート上のデータ処理（Query、Importrange等）が少ない状態だと、
数秒から1分以内で動いていたものの、長い時で打ち切りの30分程掛かっています。

また、投稿がリアルタイムでどんどん増えてしまうので、時間主導トリガーの間隔を狭めてしまうと、
現状のスクリプトのまま（ご指摘のforの手前でRangeに対してgetValuesをしてobject[][]にして、これをループをする前の、私が投稿した状態のままという意味です。）ですと、
前処理が終わる前にトリガーが発動してしまい、slackに同内容が多重投稿されてしまいます。

このような状態ですが、forの手前でRangeに対してgetValuesをしてobject[][]にして、これをループさせても、trueがない行のセルだけ投稿することは可能でしょうか？

[papinianus]

行数が多いならそれなりに早くなると思います。

ただお話からすると、スクリプトが動作するシートにもシート関数があるので、どこまで早くなるのかはやってみないとわからないです。正直インポートしているシートではスクリプトかけたことないので。
まあ普通に開いて、importxmlやqueryが動作するレベルなら打ち切られなくはなるようにも思います。

というか30分以上かかるようなら、gasやめたほうが良くないですかね。GCPの他の機能を検討するレベルだと思います。
それかqueryとかimportxmlでやってる集計をスクリプトにして、それらのシート関数を排除するとか、スクリプトが動くシートに過去データを溜め込まないようにして、12列目がtrueの(この処理にとっては)邪魔でしかないデータを排除するとか。
→具体的に、後者はtrueの書き込み先を参照元にしてqueryでそもそもtrueの奴が引用されないようにするとか、スクリプトが動くシートは値コピーにして、trueを書くのではなく消していくとか

間隔狭めたときに多重送信されるのは確かにおっしゃる通りでした。考えが甘かったです。すみません。
→スクリプトは最初に取得した時点のデータを保持するので、最初にgetValuesで全部取得して、すぐさま12列目を全行trueにしてflushして、そのあとゆっくり取得したデータをぐるぐるまわす。ファイルオープンからflushまではスクリプトロックをかける、などとする必要がありますね。大変。

フォームでやってない理由について複数のフォームたから、は理由にならないですね。gasでやるなら処理を分散するしかないので似たスクリプトがばらまかられるのは状況次第ではやむなしです。
集計をslackに投げるとのことですが、何でもかんでもgasと同じように何でもかんでもスラックに、しようとしてないですかね。少なくとも集計を文字化してスラックに投げるというのは何か変な気がします

[tak123]

大変丁寧にご対応いただき、ありがとうございました。
情報を分散させて処理スピードを上げる方向に転換します。
papinianusさんをベストアンサーとさせていただきます。

Answer 2

• Logger.log が実際の稼動で不要であれば消すと良いです。
• branch を評価するif文のところで、支店名の評価をあいまいな評価 == にしていますが、この評価式はやや遅いです。文字列が入っているとわかりきっているのであれば、厳密な評価 === にすると少しは早くなります。
• app.PostMessage()に渡す、 username と icon_url は、どの処理でも常に同じでしょうか？であれば、このオブジェクトを変数に入れて使い回せば少し早くなります。
• if文が３分岐以上あると言うことですから、 switch~case を使った方が良いと思います。

こういうのは割とちりつもですので、普段から気をつけてみると良いかもしれませんね。

Comments

[alphase]

低評価の方は何が低評価なのか教えていただきたいですね。

[papinianus]

google scriptの特性について考慮の程度が不十分だと考えます。
タグでjsを付ける質問者もいますが、この方はつけていません。

厳密比較を改めた場合を計測したことは無いですが、for内部で毎回api呼び出しした場合に比べて優位な差が出るとは思えないです。
仮に今日から明日までの一日に60*24で1440件データが増えることでΔ遅延するとして===,switch,username,icon_urlの変更を今日入れたことで明日Δ分抑止できているほどの改善を生まないと思います。

[alphase]

なるほど、ありがとうございます。確かに、for内部で毎回api呼び出しする処理を改める方が、より処理速度の向上になりますね。（これは、私の確認・指摘不足でした）

厳密比較ですが、利用することで僅かではありますが処理が早くなります。「誤解」と言われて低評価をつけられると、何の貢献にもならないと見なされたかと少々腑に落ちません。
処理速度の向上でもっと目を向けるべき場所がある、ということであればコメントをいただきたかったです。

[papinianus]

コメントも書いたので、アンデクリメントしました

[alphase]

ご意見ありがとうございました。質問に関係ないところで、お時間を取らせてしまい、失礼いたしました。

[macaron_xxx]

厳密比較について、少し興味が湧いたので、計測しました。
10億回のループで7回トライしました。
1回目：== > === 30s
2回目：== > === 12s
3回目：== < === 22s
4回目：== > === 18s
5回目：== < === 06s
6回目：== < === 03s
7回目：== > === 25s

平均すると厳密比較の方が早いですが
曖昧比較の方が早い場合もあり、なんとも言えない結果に。。

GASでは誤差の範囲を出ないのかも。

[tak123]

皆様、それぞれご検討いただき、ありがとうございます。
それぞれ参考になりましたので、高評価を付けさせていただきました。
もう少し様々な方のご意見を賜りたく、延長させていただきました。

[tak123]

macaron_xxxさん、貴重なデータありがとうございます！