Google Apps Scriptの処理速度が遅いため、配列に格納してまとめて処理を実行したい

実現したいこと

現在1行ずつ処理しているコードを
配列を使用しまとめて処理するコードに改善できればと思っていますが、知識がなく困っております。

現在は5行更新するだけで144461msかかります。

そもそも配列を使用し処理可能なのかを含めご教授いただければ幸いです。

該当のソースコード

function input() {
  //処理速度計測開始
  console.time('test')

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName("●●●");  // シートの取得
  const lastRow = sheet.getLastRow();
    
  for(let i = 2; i <= lastRow; i++) {

    const Status = sheet.getRange(i, 1).getValue()

    if (Status == "●●●") {

      if(!sheet.getRange(i, 5).getValue()){
        
        //関数の指定
        let str = `=SUM(QUERY(IMPORTRANGE("ブックID","シート名"),"select Col● where Col●='"&●${i}&"' and Col● = date '"& TEXT(●${i},"YYYY-MM-DD") &"'",0))`
        
        //関数の入力
        sheet.getRange(i, 5).setFormula(str);

      } else {
        //デバッグ用
        console.log(sheet.getRange(i, 1).getValue());
      }
    }
    //値貼りにする
    let val = sheet.getRange(i, 5).getValue()
    sheet.getRange(i, 5).setValue(val)
  }
  //処理速度計測終了
  console.timeEnd('test')
}

teratail1234

2022/04/04 11:46

追記です。 1度に100行以上を更新する事があるため、処理速度が遅く困っているという主旨になります。

int32_t

2022/04/04 15:50

console.time() timeEnd() をもっと入れて、どの行が遅いのか調べられますか? 変数 Status がループ中ずっと固定なのは意図通りですか?

teratail1234

2022/04/05 00:55 編集

ご連絡ありがとうございます。 console.time() timeEnd() をもっと入れて、どの行が遅いのか調べられますか? →下記が2000~3000msで最も遅かったです。　let val = sheet.getRange(i, 5).getValue() その他は5~10ms程度でした。変数 Status がループ中ずっと固定なのは意図通りですか? →すみません。ミスです。修正しました。

行動規範の内容に同意します

回答3件

こんにちは。
数式とGASのコードからシートの要件を勝手に推察したのですが、

1列目のステータスが処理待ちならば5列目に集計結果を入力する

で、あるならば

1.IMPORTRANGEの参照先シートを参照するシートを追加(2の関数内では'参照先シート'と仮称)
(=シートを追加してIMPORTRANGEで参照先シート全範囲を予め取得しておく、IMPORTRANGEの取得範囲が多くて重いならGASで関数セット&値貼り付けをバッチ処理化)

2.あらかじめ5列目に以下のような関数をセット($A2はフィルダウンで行可変で対応)
=IF($A2="(処理が必要なステータス名)",SUMIFS('参照先シート'!$合計範囲列,'条件範囲列1',条件1,'条件範囲列2',条件2の日付？),)
=>要はA列に処理が必要なステータスが入ってたらSUMIFSの処理で質問文記載のQUERY関数の代替処理ができるのでは？という想定です。

値貼り付けに関してだけはGASで以下のように記述

GAS
1  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名');
2  const lastRow = ss.getRange(1,5,10000,1).getValues().filter(String).length;//最大10000行かつ途中に空白行が無い前提で最終行の位置を取得
3  const val = ss.getRange(1,5,lastRow).getValues();//空白ではない範囲の値を取得
4  ss.getRange(1,5,lastRow).setValues(val);

このようにすれば
A列に処理が必要なステータス入力時→SUMIFSで結果が返る
値貼り付け処理→SUMIFSで値が入っていれば値貼り付けされる(今のままだとエラーの場合も入ってしまいますが)

投稿2022/04/07 03:07