GAS 特定条件のレコードから、さらに値を抽出して別シートに書き込む

Question

前提・実現したいこと

現在在庫管理の簡易的なシステムをスプレッドシートで作成しています。
マスタシートの特定行の値を検索して、条件に合えばそのレコードの中からさらに要素を抽出して
別シートに表示させるというシステムを作成しています。

初心者なもので、どうしてもfor文の中で何度もgetValueとsetValueを繰り返す文しか思いつかず、困っています。
実際に下記のソースで希望通りの動作自体はするのですが、1000レコードくらいある中からたかだか50レコードほどをget,setするだけで約2分ほどかかってしまいます。今後さらにデータが増えたときのことを考えるとあまりに時間がかかることが予想されるので、なんとかAPIの呼び出し回数を少なくして、動作時間の短縮ができないか考えているんですが、方法がわかりませんので質問させていただきました。

|列1|列2|列3|4|列5|列6|列7|列8|列9|列10|列11|列12|列13|列14|列15|
|:--|:--:|--:|
||||

列14に入力されている文字列が”未”だったら、1,2,4,5,6,7,13,15を別シートの最終行に追加していきたいというイメージです。

1行づつ追加ではなく2次元配列でごっそりまるごとセットすればというとこまでは考えられるんですが、実現方法がわかりません。

該当のソースコード

//マスタから検索して表示させる
function syoukyaku_before(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var tana = sheet.getSheetByName("setsubi_tana");
var a_mst = sheet.getSheetByName("setsubi_a_masta")

//マスタの最後の行を取得
var last_row = a_mst.getDataRange().getLastRow();

//マスタのレコード14列目の文字列から”未”のものだけを選別して、値を取得しそれぞれセットする
for(var i=1;i<=last_row;i++){
var genka = a_mst.getRange(i,14).getValue()
//未なら
if(genka == "未"){

  //15要素ある中の、別シートに表示させたい値だけをそれぞれ取得
  var A = a_mst.getRange(i,1).getValue()
  var B = a_mst.getRange(i,2).getValue()
  var C = a_mst.getRange(i,4).getValue()
  var D = a_mst.getRange(i,5).getValue()
  var E = a_mst.getRange(i,6).getValue()
  var F = a_mst.getRange(i,7).getValue()
  var G = a_mst.getRange(i,13).getValue()
  var H = a_mst.getRange(i,15).getValue()

  //取得した値を棚卸シートのデータ最後の行に入れてく
  tana.appendRow([A,B,C,D,E,F,G,H]);

}

}
}

試したこと

検索対象をすべて配列に入れてその中から対象だったものの行番号を返したりできないもんかと色々考えたりしたんですが、
行番号の検索は複数はできなかったりで行き詰ってしまいました。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[papinianus]

なぜgetValueとsetValueではだめなのですか？(実際にはappendRowをしてますが)

[yamadaman_q]

ご返信ありがとうございます。実際に上記のソースで希望通りの動作自体はするのですが、1000レコードくらいある中からたかだか50レコードほどをget,setするだけで約2分ほどかかってしまいます。今後さらにデータが増えたときのことを考えるとあまりに時間がかかることが予想されるので、なんとかAPIの呼び出し回数を少なくして、動作時間の短縮ができないか考えているんですが、方法がわかりませんので質問させていただきました。

[papinianus]

追記ありがとうございます。理解しました。その辺の事情は、質問に記載することをおすすめします(gasでは実行時間の制限がありますので、必要な手当であり困っている背景があることも理解しました)。現在の質問では、問題を特定せずに「添削してくれ」という風な意図の質問にも思えてしまいました。失礼しました。

[yamadaman_q]

こちらこそ失礼しました。質問の修正をさせていただきます。頭が悪いせいかアドバイスをいただいても中々理解できず解決できずにいます。

Answer 1

こんな感じの処理になると思います。

1. マスタシートの全行を二次元配列に格納する。getValues()
2. 14列目が”未”の行のみ抽出する。filter()
3. 棚卸シートに必要な列のみ抽出する。map()
4. 棚卸シートの最終行に追加する。setValues()

追記

これはサンプルです。

function syoukyaku_before(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var tana = sheet.getSheetByName("setsubi_tana");
  var a_mst = sheet.getSheetByName("setsubi_a_masta")
  
  const inventory = a_mst.getDataRange().getValues()
    .filter(function(e){return e[13] === '未'})
    .map(function(e){
      const columns = [0,1,3,4,5,6,12,14], row = [];
      for (var i = 0; i < columns.length; i++) row.push(e[columns[i]]);
      return row;
    });
 
  if (inventory.length) {
    tana.getRange(tana.getLastRow()+1, 1, inventory.length, inventory[0].length).setValues(inventory);
  }
}

このサンプルだと、配列アクセスにe[13]とかe[0]とかで分かりづらいですね。
せめてカラム名でアクセスできるようにしてみます。
これでコードの可読性が向上し、後々発生するであろう変更（カラム追加等）にも比較的容易に対応することができます。

function syoukyaku_before(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var tana = sheet.getSheetByName("setsubi_tana");
  var a_mst = sheet.getSheetByName("setsubi_a_masta")
  
  const master = a_mst.getDataRange().getValues();

  // カラム名で配列アクセスするためのインデックスを作成する
  // ※前提条件：１行目は見出しであること
  // ※列1～列15はサンプルのカラム名です。実際のカラム名に修正してください。
  const header = master.shift(), cIndex = {}; 
  for (var i = 0; i < header.length; i++) cIndex[header[i]] = i;

  const inventory = master
    .filter(function(e){return e[cIndex['列14']] === '未'})
    .map(function(e){
      const columns = [
        cIndex['列1'],
        cIndex['列2'],
        cIndex['列4'],
        cIndex['列5'],
        cIndex['列6'],
        cIndex['列7'],
        cIndex['列13'],
        cIndex['列15'],
      ], row = [];
      for (var i = 0; i < columns.length; i++) row.push(e[columns[i]]);
      return row;
    });
 
  if (inventory.length) {
    tana.getRange(tana.getLastRow()+1, 1, inventory.length, inventory[0].length).setValues(inventory);
  }
}

Comments

[yamadaman_q]

返信ありがとうございます。
早速頂いたアドバイスをもとにfilterやmapなど使ったことないAPIを調べています。

2次元配列で格納された[[あ,い,未],[う,え,未],[お、か、未]]のような配列から”未”が含まれるものだけ取得しかたがいまいち理解できず苦戦しています。

[shozi3]

var master = [['D00','D01','D02'],['D10','D11','未'],['D20','D21','D22']];
master.filter(function(e){Logger.log(e)});
このコードの実行結果を確認してください。
この場合、3列目はe[2]で参照できます。

[yamadaman_q]

ありがとうございます。今後の運用も考えてカラムで参照できるように考えていただくなどを加味してベストアンサーにさせていただきました。本当に助かりました。ありがとうございます。

Answer 2

function syoukyaku_before() {
  const valsMst = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("setsubi_a_masta").getDataRange().getValues(); //元になるデータ
  const res = [[row[0],row[1],row[3],row[4],row[5],row[6],row[12],row[14],] for each (row in valsMst) if (row[13] === "未")]; //N列が未のものから1,2,4,5,6,7,13,15列を取得
  var tana = sheet.getSheetByName("setsubi_tana");
  var last_row = tana.getLastRow();
  tana.getRange(last_row, 1, res.length, 8).setValues(res); //tanaに追記
}

Comments

[yamadaman_q]

ありがとうございます。今後の参考になります。またわからないことがあればぜひお願いします。