質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.42%

  • Google Apps Script

    1000questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 238

yamadaman_q

score 3

前提・実現したいこと

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

初心者なもので、どうしても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/ツールのバージョンなど)

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2018/12/10 09:56

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

    キャンセル

  • yamadaman_q

    2018/12/10 10:11

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

    キャンセル

  • papinianus

    2018/12/10 10:19

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

    キャンセル

  • yamadaman_q

    2018/12/10 10:22

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

    キャンセル

回答 2

checkベストアンサー

+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行目は見出しであること
  // ※列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);
  }
}

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/10 08:54

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

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

    キャンセル

  • 2018/12/10 09:48

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

    キャンセル

  • 2018/12/10 13:55

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

    キャンセル

0

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に追記
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/10 13:55

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

    キャンセル

同じタグがついた質問を見る

  • Google Apps Script

    1000questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。