スプレッドシート内のデータを検索してヒットした行を複数行コピーする

Question

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

2500程度の行数と7列のあるデータを扱います

1行目がタイトルです

各行にコピー先を示すキーが6列目と7列目にあります

例えば、
2行目にはAとD
3行目にはBのみ
4行目にはAとC　のような感じです

6列目には必ずキーがあるのですが、7列目のキーは4分の1程度が空欄です

コピーしたいデータはキーの手前までの2500行×5列です

キーは別シートで一覧にしてあり、for構文でひとつずつ順番に呼び出して、該当する行をコピー先にコピーしたいです

データの分量があり、setValuesでデータをコピーしていきましたが、500行くらいで6分の壁にぶつかり、スクリプトを終了することができませんでした

次に、配列ごとコピーできれば処理速度が上がると思い、pushを使用してみましたがうまくいきませんでした

サンプルデータに2つの異なるキーを用意してAのキーは2行分、Bのキーは3行分になるようにしました

Logger.log(data[j])で得られる結果は、
[Aに該当する配列で1つ目の行]
[[Aに該当する配列で1つ目の行][Aに該当する配列で2つ目の行]]
[]
[Bに該当する配列で1つ目の行]
[[Bに該当する配列で1つ目の行][Bに該当する配列で2つ目の行]]
[[Bに該当する配列で1つ目の行][Bに該当する配列で2つ目の行][Bに該当する配列で3つ目の行]]
[]
という感じで出力されました

最初からキーに該当するデータが二次元配列で取得することができれば、データのコピーの時間が短縮できるのではないかと考えてこのように挑戦しましたが、うまくいきませんでした

また、各キーに該当するデータを抽出した後、空欄の配列が出力されている意味がわからなかったので、ご教授いただけるとありがたいです

あとは、最後まで辿り着けていないのですが、コピー先の「データ保管」というシートのgetLastRow()+1に二次元配列ごとコピーしたいと思っているのですが、貼り付けるデータの行数を指定しなくてはならないと思うのですが、いろんなデータを.lengthで表示してみましたが、今回の2や3という結果を得ることができませんでした

よろしくお願い致します

function myFunction() {
  //コピー元になるデータを取得
  var ss        = SpreadsheetApp.openById('コピー元ID');
  var sh        = ss.getSheetByName('コピー元シート名');
  var _lastRow  = sh.getLastRow();
  var data      = sh.getRange(2,1,_lastRow,7).getValues();


//以下の4行をマスターデータの保管用として挿入しました（5/4）
  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();

  for (var i = 0 ; i < classiddata.length ; i++){
    var classid   = classiddata[i][0];

    Logger.log(classid);

  var copydata = [];
    for (var j = 0 ; j < data.length ; j++){
      if(data[j][5] == classid){
　　　　copydata.push(data[j])

      Logger.log(data[j]);
      Logger.log(copydata);

      var copyss  = SpreadsheetApp.openById(classid);
      var copysh  = copyss.getSheetByName('データ保管');
      var copy_lastRowPlus1 = copysh.getLastRow()+1;
      }
    }

//以下の7行をコピー終了後に1日で回収したデータを削除して、また翌日分を改めてデータ回収するように削除のスクリプトと、埋め込み関数のスクリプトを挿入しました（5/4）
//Logger.log(_lastRow);
  sh.deleteRows(2,_lastRow-1);

  //二次元配列に埋め込む関数をセット
  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/5/6 09:24 追記
5/4の夜はデータがすべてのシートに書き出されている様子でしたが、5/5の夜は全部コピーできていない様子でした。
スクリプトのエラーが出ていたのでそちらも載せます
起動
21/05/04 23:21

関数
myFunction

エラー メッセージ
同時呼び出しの数が多すぎます: スプレッドシート

トリガー
time-based

End
21/05/04 23:33

トリガーで動き出してから、すぐにエラーが出ているので、私が書き足した部分が問題になっているのではないかと思います。ただ、5/4の夜はこのスクリプトで問題なく動きましたが、5/5の夜はダメでした

確認していただけるとありがたいです

2021/5/3 08:57追記
元データと呼んでいるデータは以下のようなスタイルで、A列からE列まではGoogleフォームでの回答結果です。

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

F列とG列は重複しないそれぞれのグループからのIDが割り当てられています
F列はAグループの57個のIDがあります
G列はBグループの53個のIDがあります

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

Comments

[退会済みユーザー]

「キーがまとめられているシート」の画像（構造）はどうなっていますか？
（当初の質問文に記載のなかった「arrayformula(vlookup)という関数が2行目に入っている」というのは、書こうとしているプログラムにかかわる重要な追加情報になってしまうと思うのですが）

「キーがまとめられているシート」に関して、「AグループとBグループのIDをまとめてA列に並べて、A列1列のみのIDシート（classidsh）を現在は利用しています。」というのは、単純に、「フォームの回答１」シートの6列目から7列目をコピーして貼り付けている」のでしょうか。
それともさらに別のシートから数式を組んで参照しているのでしょうか。

[Kazuya_s]

名簿シート
「メールアドレス、AグループのID、BグループのID」
メールアドレスに対して、それぞれのIDが振られています

フォームの回答 1
F2とG2にarrayformula(vlookup)があり、名簿シートからメールアドレスに対応するIDを検索して表示しています

いま、質問させていただいているスクリプトは、フォームの回答 1に埋め込んでいます

実際の運用としては、スクリプトが夜中に時間でトリガーで動き、それぞれのシートに書き込みをして、最後にフォームの回答1にあるデータを削除し、翌日からまたその日の分の回答がフォームの回答 1に蓄積していく。ということを考えています。

[退会済みユーザー]

こちらで100個のスプレッドシートを生成し、対応する100個のシートIDを「フォームの回答 1」と同じような2500行のデータの6列目・7列目にArrayFormulaを用いてランダムに配置(6列目は全部の行が埋まるように、7列目は半分がID、残りが空白）し、実行してみましたが、70秒弱で振り分け処理が完了しました。
特にArrayFormulaが遅くなる原因ではなさそうです。

[Kazuya_s]

qnoirさん

丁寧に教えてくださり、ありがとうございます
qnoirさんが実行して70秒程度なのに、私が実行をすると4分程度かかった理由はわからないのですが、先ほど、実際に実行してsetValuesの書き込みも試してみましたが、無事に書き出しがされていました

ありがとうございます

[退会済みユーザー]

「同時呼び出しの数が多すぎます: スプレッドシート」のエラーですが、スクリプトの同時実行数３０を超えているせいだと思われます。
同時にスクリプトが走るようなトリガーを設定しているファイルが複数存在している（古いファイルを削除し忘れている）とかではありませんか？

[Kazuya_s]

たびたびありがとうございます
これまでもたくさんトリガーを設定したりしましたが、失敗作などを含めて基本的にトリガーは削除しているので、そんなに多くないと思います
5/4と5/5でトリガーの数を増やしたことも特にありません
とりあえずトリガー設定がないか確認をしてみようと思います
ありがとうございます

[Kazuya_s]

ただいまトリガーの設定を確認しました
他のスプレッドシートと結びついているものがあり、5つほどトリガーの削除をしました
今日の夜にうまく動くことを祈っております
またこちらで報告をさせてください
よろしくお願い致します

Answer 1

データを一括取得し、連想配列（下記では「arrays」という名前）に、シートIDごとにデータを振り分けて格納。
書き込む際に配列で一括して書き込むことで高速化できます。

（下記で2500行を3つのシートに振り分けるのに約4秒）
（質問文には明記されていませんが、行ごとに6列目（＆存在していれば7列目）にあるシートIDを判定して振り分ける前提です。）

function myFunction() {
  //コピー元になるデータを取得
  var ss        = SpreadsheetApp.openById('コピー元ID');
  var sh        = ss.getSheetByName('コピー元シート名');
  var _lastRow  = sh.getLastRow();
  var data      = sh.getRange(2, 1, _lastRow, 7).getValues();

  var classidsh   = ss.getSheetByName('キーとなる別シートのシート名');
  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}」という名前のシートが存在しません。`)
      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, 1, lastRow, lastColumn).setValues(copydata);
  }
  Logger.log('処理終了');
}

Comments

[Kazuya_s]

qnoirさん
ご回答いただきありがとうございます

いま、試しにとりあえずコピーして、シートIDだけ変更して動かしてみたのですが、以下のエラーが出ました

**********
6:04:45 エラー
TypeError: Cannot read property 'length' of undefined
myFunction @ コード.gs:45
**********
45行目は
var lastColumn = copydata[0].length; // カラム数を取得
という構文でした

自分なりにも研究してみるのですが、後半は特に解読に時間がかかりそうなので、どうぞご教授ください

よろしくお願い致します

[退会済みユーザー]

最後から6～７行目を修正しました。
なお、コメントのエラーは、質問文の文面から読み取れる範囲で自作したデータでは、再現できませんでした。
（つまり質問文面から読み取ってつくったこちらのデータと、実際の質問者さんの手元のデータ構造が異なっているためにエラーが発生した可能性があるということです）
もし修正してもまだエラーが発生する場合は、質問欄に各シート（元データおよびキーが書かれているシート）のスクリーンキャプチャを掲載していただければ修正に役立つかもしれません。

[Kazuya_s]

追記です

実際のデータは6列目はAグループのキーID、7列目はBグループのキーIDというデータになっています
AグループとBグループは重複のないキーIDです
ただし、すべてのデータにAグループのキーは振られていますが、Bグループのキーは振られているデータもあれば空欄もある、ということになっています

[Kazuya_s]

quoirさん

最後のsetValuesの記述だけ//で隠してみたところ、1700行が4分で最後まで処理が終了しました
ありがとうございます

次はsetValuesまで含めて実行してみようと思います
楽しみです

[Kazuya_s]

最後のsetValuesのところでデータがつまづいてしまいました
悔しいです

8:32:20 エラー
Exception: The number of rows in the range must be at least 1.
myFunction @ コード.gs:51

[退会済みユーザー]

エラー対策部分を修正してみました。
最後から6行目
if (copydata == null || copydata.length < 1) continue;

先のコメントにも書いたように「質問文面から読み取ってつくったこちらのデータと、実際の質問者さんの手元のデータ構造が異なっているためにエラーが発生した可能性があります」
これ以上は元のデータを見てみないとわからないと思います。

[Kazuya_s]

夜にトリガーを設定して、無事にコピーがされていました
ありがとうございました