[GAS][Googleスプレッドシート]　あるセルの値と一致しているものだけ別シートに抽出する方法

やりたいこと

・allシートには以下のような表があります。

オレンジ枠のID,担当者,メールアドレスの表から
水色枠の抜き出したいIDに記載のある文字と一致しているものがあれば全て抜き出して
それを別シートへコピーする　というのをスクリプト実行でやりたいというのが本件になります。

表内で=FILTER関数であれば以下のようにすれば抽出可能なのですが、

=FILTER(A4:C, COUNTIF(A4:C,E4:E))

スクリプトでの書き方が分からず苦戦しています。

現在は以下のように、A4:Cの項目を別シートへそのままコピーするという、
スクリプト実行まではできています。

function copy() {
 var ss = SpreadsheetApp.openById('スプレッドシートID');
// 該当スプレッドシートの範囲を取得
 var copyFrom = ss.getSheetByName('all').getRange('A4:C');
// コピー先スプレッドシートとコピー開始セルを指定
 var copyToSheet = ss.getSheetByName('select').getRange('A2');
   
// copyFromを =FILTER(A4:C, COUNTIF(A4:C,E4:E))の条件に一致したものだけにしたい
  copyFrom.copyTo(copyToSheet);
}

copyFrom.copyTo(copyToSheet)としておりますが、
ここのcopyFromを=FILTER(A4:C, COUNTIF(A4:C,E4:E))のものだけにしたいのですが
なかなかうまくいかず、質問投稿させていただいた次第です。

詳しい方がおりましたら、ご教示いただけますと幸いです。
よろしくお願い致します。

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

回答1件

ベストアンサー

表内で=FILTER関数であれば以下のようにすれば抽出可能なのですが、
=FILTER(A4:C, COUNTIF(A4:C,E4:E))

この関数式では要件を満たさない気がしますがそれはともかく、Range.setFormula を使えば GAS から関数式を設定できます。

Class Range > setFormula

https://developers.google.com/apps-script/reference/spreadsheet/range?hl=en#setformulaformula

JavaScript
1function copy() {
2  const ss = SpreadsheetApp.openById("スプレッドシートID");
3
4  //"select"シートの"A2"セルに関数式を設定する
5  const formula = `=FILTER(all!A4:C, COUNTIF(all!A4:C, all!E4:E))`;
6  ss.getSheetByName("select").getRange("A2").setFormula(formula);
7}

元データを自分でフィルタしてから"select"シートへコピーする方法もあります。

JavaScript
1function copy2() {
2  const ss = SpreadsheetApp.openById("スプレッドシートID");
3  const allSheet = ss.getSheetByName("all");
4
5  const values = allSheet.getRange("A4:C").getValues();
6  const selectedIDs = allSheet.getRange("E4:E").getValues().flat();
7
8  const selectedValues = values.filter(rowData =>
9    selectedIDs.includes(rowData[0])
10  );
11
12  const selectSheet = ss.getSheetByName("select");
13  selectSheet.getRange("A2:C").clearContent();
14  if (0 < selectedValues.length) {
15    selectSheet
16      .getRange(2, 1, selectedValues.length, 3)
17      .setValues(selectedValues);
18  }
19}