質問者さんのコードやコメントから、「おそらくこういう動作がしたいのだろう」というのを推測して作ったコードが下記になります。（後半の「テストデータ前提」をご参照ください）
QUERYを使った「数式」を汎用的に「GASのコード」に変える手段はおそらく存在せず、結局「やりたい動作」を行うために個別にカスタマイズせざるを得ません。
数式を使っていれば、質問者さんの方でカスタマイズも容易であると考えました。
・mapやfilter等を駆使すればもっと短く・速くできるところはたくさんあるかもしれませんが、上記と同様の理由で、あえて冗長であったり、速度を犠牲にした書き方をしている部分があります。
・具体的な動作が読解し切れていないので、期待する動作と異なる部分があるかもしれませんが、御了承ください。
シート9の展開先開始行を変えたい場合は、コード中のOUTPUT_STARTの数字を変えて保存・実行してください。
js
1 function myFunctionXY ( ) {
2
3 var sheet = SpreadsheetApp . getActiveSpreadsheet ( ) . getActiveSheet ( ) ;
4 var sheet2 = SpreadsheetApp . getActiveSpreadsheet ( ) . getSheetByName ( 'シート9' ) ;
5
6 // 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
7 var OUTPUT_START = 150 ;
8
9 // 展開先に入っているデータを削除
10 sheet2 . getRange ( "A" + OUTPUT_START + ":L1000" ) . clearContent ( ) ;
11
12 // アクティブな範囲の配列を取得
13 var rangeList = getSprittedRange ( sheet . getActiveRangeList ( ) ) ;
14
15 // 展開先の最終行を格納する。
16 var lastRow = OUTPUT_START ;
17
18 // 結果が見つかったかどうか
19 var isFound = false ;
20
21 // 取得したアクティブな範囲をループで回す
22 for ( var i = 0 ; i < rangeList . length ; i ++ ) {
23 // 出力先行番号の設定。書き込み済みの行から１行空けて始める。
24 lastRow = sheet2 . getLastRow ( ) + 2 ;
25 // 最初だけはOUTPUT_START行目に設定
26 if ( lastRow <= OUTPUT_START + 1 ) lastRow = OUTPUT_START ;
27
28 sheet2 . getRange ( lastRow , 1 ) . setValue (
29 ` =query('シート9'!A2:U ${ OUTPUT_START - 1 } , `
30 + ` "where (D='"& ${ getAddress ( sheet , rangeList [ i ] . getRow ( ) - 1 + 1 , 3 ) } &"' `
31 + ` or L='"& ${ getAddress ( sheet , rangeList [ i ] . getRow ( ) - 1 + 1 , 3 ) } &"') `
32 + ` and year(A)="&text(substitute(substitute( ${ getAddress ( sheet , 1 , rangeList [ i ] . getColumn ( ) - 1 ) } ,"-",""),",","-"),"YYYY")&" `
33 + ` and month(A)+1="&text(substitute(substitute( ${ getAddress ( sheet , 1 , rangeList [ i ] . getColumn ( ) - 1 ) } ,"-",""),",","-"),"MM")&"") ` ) ;
34
35 // 結果が見つからない場合は消す。
36 if ( sheet2 . getRange ( lastRow , 1 ) . getValue ( ) === "#N/A" ||
37 sheet2 . getRange ( lastRow , 1 ) . getValue ( ) === "#VALUE!" ) {
38 sheet2 . getRange ( lastRow , 1 ) . clearContent ( )
39 } else {
40 // 結果が見つかった場合はフラグをtrueにしておく
41 isFound = true ;
42 }
43 }
44
45 if ( ! isFound ) {
46 sheet2 . getRange ( lastRow , 1 ) . setValue ( "見つかりませんでした" ) ;
47 }
48 }
49
50 62
63 function getAddress ( sheetObj , row , col ) {
64 if ( sheetObj == null ) {
65 throw Error ( "引数 sheetObjが指定されていません。" ) ;
66 }
67
68 if ( row < 1 ) {
69 throw Error ( "行番号は1以上の数字でなければなりません。与えられた行番号（row）＝ " + row ) ;
70 }
71
72 if ( col < 1 ) {
73 throw Error ( "列番号は1以上の数字でなければなりません。与えられた列番号（col）＝ " + col ) ;
74 }
75
76 return ` ' ${ sheetObj . getName ( ) } '! ${ sheetObj . getRange ( row , col ) . getA1Notation ( ) } ` ;
77 }
78
79
80
81 90
91 function getSprittedRange ( rangelist ) {
92 // 戻り値（個別セル範囲）を格納するための配列
93 const output = [ ] ;
94 for ( const range of rangelist . getRanges ( ) ) {
95 // RangeList が連続したセルの場合は、個別セル範囲に分割してoutput配列に追加
96 if ( range . getHeight ( ) != 1 || range . getWidth ( ) != 1 ) {
97 for ( let i = 1 ; i <= range . getHeight ( ) ; i ++ ) {
98 for ( let j = 1 ; j <= range . getWidth ( ) ; j ++ ) {
99 output . push ( range . getCell ( i , j ) ) ;
100 }
101 }
102 } else {
103 output . push ( range ) ;
104 }
105 }
106 return output ;
107 }
108
上記のコードの検証に当たっては、手元で下記のようなテストデータをつくり、動作することを確認しています。
テストデータ前提
１つのスプレッドシートに、「シート1」「シート9」の2つのシートがあり、それぞれ画像の通りの内容となっています。
シート1
シート9
・シート1には画像描画で作成した「実行」ボタンを配置しており、「スクリプトの割り当て 」によって、クリックすると「myFunctionXY」関数が実行されるように設定しています。
動作について
シート1のデータ範囲のセルを1個以上クリックした状態で、実行ボタンをクリックすると、下記の動作を行います。
(1)シート1 のアクティブなセルの行の Ｃ列 にあるID(「A***」)を読み取ります。１つ前の列の1行目 にある年月値(「2021,***」)を読み取ります。下記の条件 にあてはまる行を抽出する数式を、シート9の150行目以降に入力します。
text
1 抽出条件
2 ・シート9の Ｄ列 または Ｌ列が (1) で取得したIDと一致する
3 かつ
4 ・シート9の Ａ列 の日付の年・月が、(2) で取得した年月値と一致する
(4)クリックしたセルが複数ある場合は、残りのセルに対して同じ動作を順次繰り返します。
例
たとえば、シート1 の F12セル をアクティブにした状態で、実行ボタンをクリックすると、AAA12」2021.7」シート9 の中から、AAA12である行のうち、Ａ列の年月が 2021年7月 である行]
そうすると、該当するのは13行目なので、シート9の13行目のデータが シート9の150行目に展開されます。
複数の結果がある場合は1行ずつ空けて表示します。（下記はサンプルです）
補足
・別シートのセル値取得 別シート のセル値を読み取る場合、indirect関数を使用する必要はありません。'シート名'!セル番地 とすれば指定したシートの指定した番地の値を取得できます。GAS内で、指定した行・列から、A1セル形式に変更する手段 getA1Notation() という関数 を使うことで、A1セル形式の文字列に変換できます。
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(2, 5);
Logger.log(range.getA1Notation());
// "E2"
上記コード内のgetAddress()関数は、これを拡張し、
・隣接するセルを選択したときのセル分割
上記コードのgetSprittedRange()関数は、これを個別のセルに分割する関数です。
function getSprittedRange(rangelist) {
// 戻り値（個別セル範囲）を格納するための配列
const output = [];
for (const range of rangelist.getRanges()) {
// RangeList が連続したセルの場合は、個別セル範囲に分割してoutput配列に追加
if (range.getHeight() != 1 || range.getWidth() != 1) {
for (let i = 1; i <= range.getHeight(); i++) {
for (let j = 1; j <= range.getWidth(); j++) {
output.push(range.getCell(i, j));
}
}
} else {
output.push(range);
}
}
return output;
}
コメントでの追加質問に対する回答
、上記のシート１に相当するピボットテーブルのシートが複数あり、その際switch case break構文を使えば良いと教えていただいたのですが、上記コードにこれを組み込むにはどうすればよろしいでしょうか？（仮にシート２として表の位置は１行目D列に枠があると仮定していただけますと幸いです、これで私の思うものが完成する次第です）
シートごとに処理を変える場合の例です
// メニュー追加処理
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('script');
menu.addItem('仕訳呼出', 'onClickMenu');
menu.addToUi();
}
// メニューをクリックしたときに実行
function onClickMenu() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
switch (sheet.getName()){
case "シート1":
myFunctionSheet1(sheet)
break;
case "シート2":
myFunctionSheet2(sheet)
break;
case "シート3":
myFunctionSheet3(sheet)
break;
default: //上記以外の場合は何もしない
return;
}
// 以下に各シート共通処理を書く。
// ....
}
// シート１の処理コード
function myFunctionSheet1(sheet) {
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
var OUTPUT_START = 150;
....
～～以下略～～(シート１専用の処理)(中身は上記のコード中のmyFunctionXYを参考に書けばよい)
....
}
// シート２のコード
function myFunctionSheet2(sheet) {
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
var OUTPUT_START = 150;
....
～～略～～(シート２専用の処理)
//「1行目D列に枠がある」->where節の読み取り列を3から4に変えればよい（？）
sheet2.getRange(lastRow, 1).setValue(
`=query('シート9'!A2:U${OUTPUT_START - 1},`
+ `"where (D='"&${getAddress(sheet, rangeList[i].getRow() - 1 + 1, 4)}&"'`
+ ` or L='"&${getAddress(sheet, rangeList[i].getRow() - 1 + 1, 4)}&"')`
+ ` and year(A)="&text(substitute(substitute(${getAddress(sheet, 1, rangeList[i].getColumn() - 1)},"-",""),",","-"),"YYYY")&"`
+ ` and month(A)+1="&text(substitute(substitute(${getAddress(sheet, 1, rangeList[i].getColumn() - 1)},"-",""),",","-"),"MM")&"")`);
～～以下略～～
}
}
// シート３のコード
function myFunctionSheet3(sheet) {
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
var OUTPUT_START = 150;
....
～～略～～(シート３専用の処理)
}
}
function getAddress(sheetObj, row, col) {
略
}
function getSprittedRange(rangelist) {
略
}
Q&A
ベストアンサー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/10/10 08:06
退会済みユーザー
2021/10/10 08:28 編集
2021/10/10 09:56 編集
退会済みユーザー
2021/10/10 10:24
2021/10/10 12:03
退会済みユーザー
2021/10/10 12:20 編集
2021/10/10 13:21
退会済みユーザー
2021/10/10 14:54 編集
2021/10/10 15:13 編集