GASを使った『文字列の部分一致→特定の文字を含んでいる場合削除』の動作が安定しない原因についてご助言お願いします。

前提

GASを使用してメール送信を行うための一覧を抽出するプログラムを考えています。
プログラミングに関してはほとんど初心者です。

実現したいこと

フォームにてメールアドレス等情報を入力してもらう
↓
（GAS）その一覧のD列を判定し、SチームとTチームに分ける
↓
（GAS）Tチームの情報を別シートに転記

抽出する技術がないため、シートの情報をすべてコピーしたのち、D列にSが含まれていた場合行を削除したら同様のことができるのではないのかと考えました。

文字列の判定は一覧の下から行っており、その前にＤ列をソートしてＳが下に来るようにしています。

おこがましいですが、今回のプログラムに関してもご助言いただきたいですが、行の削除ではない抽出に関してもご助言いただけたら嬉しいです。

発生している問題・エラーメッセージ

具体的にGASでエラーが起きているわけではありません。
開始から終了まで実行はできます。が、動作が不安定です。

①10秒でプログラムが終了したとき
→Sチームが除外されておらず、ただシート内容がコピーされただけ
②40秒前後でプログラムが終了したとき
→想定通りの動作をしている

①と②がおおよそ交互に起こります。

該当のソースコード

function アーカイブ() {

// 対象のスプレッドシートを指定

let maildb = SpreadsheetApp.openById("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊"); 
let maildb_sheet = maildb.getSheetByName("アドレス"); 
let copydb_sheet = maildb.getSheetByName("Tチーム"); 
var maildata_lastRow = maildb_sheet.getLastRow();
var copydata_lastRow = copydb_sheet.getLastRow();
var maildata_lastCol = maildb_sheet.getLastColumn();

maildb_sheet.sort(4, false);
copydb_sheet.clear();
copydb_sheet.insertRowsAfter(1,30);

maildb_sheet.getRange(1,1,maildata_lastRow,maildata_lastCol).copyTo(copydb_sheet.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  maildb_sheet.getRange('A1').activate();
  maildb_sheet.getCurrentCell().setValue('タイムスタンプ');　// コピー状態の解除？？

const array = ["S","s"]
  for (const i in array) {
    for (let j = copydata_lastRow; j > 1; j--) {
      let range = copydb_sheet.getRange('D' + j);
      let value = range.getDisplayValue();
      if (value.indexOf(array[i]) != -1) {
        let start_row = j;
        let num_row = 1;
        copydb_sheet.deleteRows(start_row, num_row);
      };
    };
  };


}

画像

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

回答1件

ベストアンサー

不具合の原因

検証していませんので確信はありませんがおそらく原因は、結論から言うと「GASの実行上限時間を超えている」のだと思います。

GASには一度の処理で可能な実行時間の上限があります。
処理する行数やセル数が多いほど時間がかかり、処理が終わる前に時間オーバーになり途中で終了しまうことがあります。
質問者様の処理する行数・セル数が実行制限時間ちょうどくらいの量なので、うまくいくときとうまくいかないときがあるのかもしれません。

解決策

処理速度を向上させ、制限時間内に終わらせる必要があります。

そしてGASの処理速度で一番影響があるのが「GASとスプレッドシートの通信回数」です。
通信回数を減らせば処理速度が速くなります。

例えばデータが全部で100行（うち、Sが50行）あった場合、
質問者様のコードは１セルずつ値を判定していますので、判定するだけで100回通信しています。
削除も1行ずつしていますので、さらに50回通信することになります。
合計150回の通信です。

私が以下でご提案いたしますコードは、
一括で値を取得（通信1回） → （Sを削除したデータに裏側で書き換え） → 一括で値を出力（通信1回）
ですので、合計で2回の通信のみです。

コード例

↓「アドレス」シート

↓「Tチーム」シート（転記後）

javascript
1function extractTteam() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3
4  // 「アドレス」シートを取得
5  const addressSheet = ss.getSheetByName('アドレス');
6  // 全データを一括で取得 → D列の1文字目を小文字化した上で、「t」のもののみにデータを絞る
7  const values = addressSheet.getDataRange().getValues().filter(record => record[3][0].toLowerCase() === 't');
8
9  // 「Tチーム」シートを取得
10  const tTeamSheet = ss.getSheetByName('Tチーム');
11  // 2行目からデータを一括で貼り付ける
12  tTeamSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
13}