回答編集履歴
10
追加
answer
CHANGED
|
@@ -168,11 +168,11 @@
|
|
|
168
168
|
たとえば、シート1 の F12セル をアクティブにした状態で、実行ボタンをクリックすると、
|
|
169
169
|
・アクティブセルのＣ列は「`AAA12`」
|
|
170
170
|
・アクティブセルの1つ前の列の1行目は「`2021.7`」
|
|
171
|
+
|
|
171
172
|
なので、
|
|
172
173
|
**シート9**の中から、
|
|
173
174
|
「Ｄ列またはＬ列が`AAA12`である行のうち、Ａ列の年月が `2021年7月` である行]
|
|
174
175
|
を探します。
|
|
175
|
-
|
|
176
176
|
|
|
177
177
|
そうすると、該当するのは13行目なので、シート9の13行目のデータが シート9の150行目に展開されます。
|
|
178
178
|
|
|
@@ -223,4 +223,96 @@
|
|
|
223
223
|
}
|
|
224
224
|
return output;
|
|
225
225
|
}
|
|
226
|
+
```
|
|
227
|
+
|
|
228
|
+
-----
|
|
229
|
+
# コメントでの追加質問に対する回答
|
|
230
|
+
> 、上記のシート１に相当するピボットテーブルのシートが複数あり、その際switch case break構文を使えば良いと教えていただいたのですが、上記コードにこれを組み込むにはどうすればよろしいでしょうか？（仮にシート２として表の位置は１行目D列に枠があると仮定していただけますと幸いです、これで私の思うものが完成する次第です）
|
|
231
|
+
|
|
232
|
+
|
|
233
|
+
シートごとに処理を変える場合の例です
|
|
234
|
+
（共通処理として出せる部分は多々あるかと思いますが、「各シートの処理で具体的にどのような差異があるのか」について全く明記されていない以上、こちらでは回答できかねます。
|
|
235
|
+
下記は共通処理も重複して書くことで、最低限動くであろうという例となります）
|
|
236
|
+
```
|
|
237
|
+
// メニュー追加処理
|
|
238
|
+
function onOpen() {
|
|
239
|
+
var ui = SpreadsheetApp.getUi();
|
|
240
|
+
var menu = ui.createMenu('script');
|
|
241
|
+
menu.addItem('仕訳呼出', 'onClickMenu');
|
|
242
|
+
menu.addToUi();
|
|
243
|
+
}
|
|
244
|
+
|
|
245
|
+
// メニューをクリックしたときに実行
|
|
246
|
+
function onClickMenu() {
|
|
247
|
+
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
|
|
248
|
+
switch (sheet.getName()){
|
|
249
|
+
case "シート1":
|
|
250
|
+
myFunctionSheet1(sheet)
|
|
251
|
+
break;
|
|
252
|
+
|
|
253
|
+
case "シート2":
|
|
254
|
+
myFunctionSheet2(sheet)
|
|
255
|
+
break;
|
|
256
|
+
|
|
257
|
+
case "シート3":
|
|
258
|
+
myFunctionSheet3(sheet)
|
|
259
|
+
break;
|
|
260
|
+
|
|
261
|
+
default: //上記以外の場合は何もしない
|
|
262
|
+
return;
|
|
263
|
+
}
|
|
264
|
+
// 以下に各シート共通処理を書く。
|
|
265
|
+
// ....
|
|
266
|
+
}
|
|
267
|
+
|
|
268
|
+
|
|
269
|
+
// シート１の処理コード
|
|
270
|
+
function myFunctionSheet1(sheet) {
|
|
271
|
+
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
|
|
272
|
+
|
|
273
|
+
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
|
|
274
|
+
var OUTPUT_START = 150;
|
|
275
|
+
....
|
|
276
|
+
～～以下略～～(シート１専用の処理)(中身は上記のコード中のmyFunctionXYを参考に書けばよい)
|
|
277
|
+
....
|
|
278
|
+
}
|
|
279
|
+
|
|
280
|
+
// シート２のコード
|
|
281
|
+
function myFunctionSheet2(sheet) {
|
|
282
|
+
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
|
|
283
|
+
|
|
284
|
+
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
|
|
285
|
+
var OUTPUT_START = 150;
|
|
286
|
+
....
|
|
287
|
+
～～略～～(シート２専用の処理)
|
|
288
|
+
//「1行目D列に枠がある」->where節の読み取り列を3から4に変えればよい（？）
|
|
289
|
+
sheet2.getRange(lastRow, 1).setValue(
|
|
290
|
+
`=query('シート9'!A2:U${OUTPUT_START - 1},`
|
|
291
|
+
+ `"where (D='"&${getAddress(sheet, rangeList[i].getRow() - 1 + 1, 4)}&"'`
|
|
292
|
+
+ ` or L='"&${getAddress(sheet, rangeList[i].getRow() - 1 + 1, 4)}&"')`
|
|
293
|
+
+ ` and year(A)="&text(substitute(substitute(${getAddress(sheet, 1, rangeList[i].getColumn() - 1)},"-",""),",","-"),"YYYY")&"`
|
|
294
|
+
+ ` and month(A)+1="&text(substitute(substitute(${getAddress(sheet, 1, rangeList[i].getColumn() - 1)},"-",""),",","-"),"MM")&"")`);
|
|
295
|
+
～～以下略～～
|
|
296
|
+
}
|
|
297
|
+
}
|
|
298
|
+
|
|
299
|
+
// シート３のコード
|
|
300
|
+
function myFunctionSheet3(sheet) {
|
|
301
|
+
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート9');
|
|
302
|
+
|
|
303
|
+
// 展開先の行番号(例：150目以降に展開したい場合「150」を指定）
|
|
304
|
+
var OUTPUT_START = 150;
|
|
305
|
+
....
|
|
306
|
+
～～略～～(シート３専用の処理)
|
|
307
|
+
}
|
|
308
|
+
}
|
|
309
|
+
|
|
310
|
+
|
|
311
|
+
function getAddress(sheetObj, row, col) {
|
|
312
|
+
略
|
|
313
|
+
}
|
|
314
|
+
|
|
315
|
+
function getSprittedRange(rangelist) {
|
|
316
|
+
略
|
|
317
|
+
}
|
|
226
318
|
```
|
9
修正
answer
CHANGED
|
@@ -108,7 +108,7 @@
|
|
|
108
108
|
* getActiveRangeList()で取得した、アクティブセルのRangeList
|
|
109
109
|
*
|
|
110
110
|
* 戻り値：
|
|
111
|
-
* Range : 分割後の個別セル範囲
|
|
111
|
+
* Range[] : 分割後の個別セル範囲（Range）を格納した配列
|
|
112
112
|
*****************************************************/
|
|
113
113
|
function getSprittedRange(rangelist) {
|
|
114
114
|
// 戻り値（個別セル範囲）を格納するための配列
|
8
修正
answer
CHANGED
|
@@ -172,7 +172,7 @@
|
|
|
172
172
|
**シート9**の中から、
|
|
173
173
|
「Ｄ列またはＬ列が`AAA12`である行のうち、Ａ列の年月が `2021年7月` である行]
|
|
174
174
|
を探します。
|
|
175
|
-
|
|
176
176
|
|
|
177
177
|
そうすると、該当するのは13行目なので、シート9の13行目のデータが シート9の150行目に展開されます。
|
|
178
178
|
|
7
修正
answer
CHANGED
|
@@ -83,7 +83,7 @@
|
|
|
83
83
|
* （シート名の両側にはシングルクォーテーションが入ります）
|
|
84
84
|
*****************************************************/
|
|
85
85
|
function getAddress(sheetObj, row, col) {
|
|
86
|
-
if (sheetObj
|
|
86
|
+
if (sheetObj == null) {
|
|
87
87
|
throw Error("引数 sheetObjが指定されていません。");
|
|
88
88
|
}
|
|
89
89
|
|
6
answer
CHANGED
|
@@ -6,7 +6,6 @@
|
|
|
6
6
|
QUERY関数そのものはスプレッドシート上の数式でしか利用できません。
|
|
7
7
|
|
|
8
8
|
現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、この場合は、まず「どのような動作にしたいのか」をしっかり固める必要があると思います。
|
|
9
|
-
（組んだ後で「実は本当はこういう動作がしたい」「うまく動かない」等言われても、また一からやり直しになってしまいます）
|
|
10
9
|
|
|
11
10
|
QUERYを使った「数式」を汎用的に「GASのコード」に変える手段はおそらく存在せず、結局「やりたい動作」を行うために個別にカスタマイズせざるを得ません。
|
|
12
11
|
|
5
answer
CHANGED
|
@@ -5,12 +5,13 @@
|
|
|
5
5
|
|
|
6
6
|
QUERY関数そのものはスプレッドシート上の数式でしか利用できません。
|
|
7
7
|
|
|
8
|
-
現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、まず「どのような動作にしたいのか」を固め
|
|
8
|
+
現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、この場合は、まず「どのような動作にしたいのか」をしっかり固める必要があると思います。
|
|
9
|
-
|
|
10
|
-
|
|
9
|
+
（組んだ後で「実は本当はこういう動作がしたい」「うまく動かない」等言われても、また一からやり直しになってしまいます）
|
|
11
10
|
|
|
12
|
-
|
|
11
|
+
QUERYを使った「数式」を汎用的に「GASのコード」に変える手段はおそらく存在せず、結局「やりたい動作」を行うために個別にカスタマイズせざるを得ません。
|
|
13
12
|
|
|
13
|
+
数式を使っていれば、質問者さんの方でカスタマイズも容易であると考えました。
|
|
14
|
+
|
|
14
15
|
・mapやfilter等を駆使すればもっと短く・速くできるところはたくさんあるかもしれませんが、上記と同様の理由で、あえて冗長であったり、速度を犠牲にした書き方をしている部分があります。
|
|
15
16
|
（元のmyFunctionXY()の中では、constやletを使った書き方も使用していません）
|
|
16
17
|
|
4
answer
CHANGED
|
@@ -149,18 +149,21 @@
|
|
|
149
149
|
|
|
150
150
|
# 動作について
|
|
151
151
|
|
|
152
|
-
シート1のデータ範囲のセルをクリックし
|
|
152
|
+
シート1のデータ範囲のセルを1個以上クリックした状態で、実行ボタンをクリックすると、下記の動作を行います。
|
|
153
153
|
|
|
154
154
|
(1)シート1 のアクティブなセルの行の **Ｃ列** にあるID(「`A***`」)を読み取ります。
|
|
155
155
|
(2)シート1 のアクティブなセルの**１つ前の列の1行目**にある年月値(「`2021,***`」)を読み取ります。
|
|
156
|
-
(3)シート9 の A1～
|
|
156
|
+
(3)シート9 の A1～L149 の範囲から、**下記の条件**にあてはまる行を抽出する数式を、シート9の150行目以降に入力します。
|
|
157
157
|
```text
|
|
158
158
|
抽出条件
|
|
159
159
|
・シート9の Ｄ列 または Ｌ列が (1) で取得したIDと一致する
|
|
160
160
|
かつ
|
|
161
161
|
・シート9の Ａ列 の日付の年・月が、(2) で取得した年月値と一致する
|
|
162
162
|
```
|
|
163
|
-
|
|
163
|
+
(4)クリックしたセルが複数ある場合は、残りのセルに対して同じ動作を順次繰り返します。
|
|
164
|
+
それぞれのセルについて検索結果が見つかった場合は、シート9の既存の結果行から1行空けて数式を追記します。
|
|
165
|
+
|
|
166
|
+
|
|
164
167
|
# 例
|
|
165
168
|
たとえば、シート1 の F12セル をアクティブにした状態で、実行ボタンをクリックすると、
|
|
166
169
|
・アクティブセルのＣ列は「`AAA12`」
|
3
answer
CHANGED
|
@@ -194,7 +194,10 @@
|
|
|
194
194
|
Logger.log(range.getA1Notation());
|
|
195
195
|
// "E2"
|
|
196
196
|
```
|
|
197
|
+
上記コード内のgetAddress()関数は、これを拡張し、
|
|
198
|
+
シートオブジェクト・行番号・列番号を与えると、対応する「シート名!A1」形式の文字列を返す関数としています。
|
|
197
199
|
|
|
200
|
+
|
|
198
201
|
・**隣接するセルを選択したときのセル分割**
|
|
199
202
|
Googleスプレッドシートの仕様として、隣接するセルをControlクリックで複数選択すると、１つのセルとして扱われてしまうことがあります。（少なくとも Windows10上のChromium Edgeではそうなっています）
|
|
200
203
|
|
2
answer
CHANGED
|
@@ -16,6 +16,9 @@
|
|
|
16
16
|
|
|
17
17
|
・具体的な動作が読解し切れていないので、期待する動作と異なる部分があるかもしれませんが、御了承ください。
|
|
18
18
|
（動作検証環境：Windows 10 ／Chromium Edge／GAS：V8エンジン有効）
|
|
19
|
+
|
|
20
|
+
シート9の展開先開始行を変えたい場合は、コード中の`OUTPUT_START`の数字を変えて保存・実行してください。
|
|
21
|
+
|
|
19
22
|
```js
|
|
20
23
|
function myFunctionXY() {
|
|
21
24
|
|
1
追加
answer
CHANGED
|
@@ -15,7 +15,7 @@
|
|
|
15
15
|
（元のmyFunctionXY()の中では、constやletを使った書き方も使用していません）
|
|
16
16
|
|
|
17
17
|
・具体的な動作が読解し切れていないので、期待する動作と異なる部分があるかもしれませんが、御了承ください。
|
|
18
|
-
|
|
18
|
+
（動作検証環境：Windows 10 ／Chromium Edge／GAS：V8エンジン有効）
|
|
19
19
|
```js
|
|
20
20
|
function myFunctionXY() {
|
|
21
21
|
|
|
@@ -172,4 +172,46 @@
|
|
|
172
172
|
|
|
173
173
|
|
|
174
174
|
複数の結果がある場合は1行ずつ空けて表示します。（下記はサンプルです）
|
|
175
|
-
|
|
175
|
+
|
|
176
|
+
|
|
177
|
+
---
|
|
178
|
+
# 補足
|
|
179
|
+
・**別シートのセル値取得**
|
|
180
|
+
スプレッドシート上の数式で、同じスプレッドシート上の**別シート**のセル値を読み取る場合、indirect関数を使用する必要はありません。
|
|
181
|
+
単純に`'シート名'!セル番地` とすれば指定したシートの指定した番地の値を取得できます。
|
|
182
|
+
|
|
183
|
+
|
|
184
|
+
・**GAS内で、指定した行・列から、A1セル形式に変更する手段**
|
|
185
|
+
[getA1Notation() という関数](https://developers.google.com/apps-script/reference/spreadsheet/range#geta1notation)を使うことで、A1セル形式の文字列に変換できます。
|
|
186
|
+
```
|
|
187
|
+
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
188
|
+
var sheet = ss.getSheets()[0];
|
|
189
|
+
var range = sheet.getRange(2, 5);
|
|
190
|
+
|
|
191
|
+
Logger.log(range.getA1Notation());
|
|
192
|
+
// "E2"
|
|
193
|
+
```
|
|
194
|
+
|
|
195
|
+
・**隣接するセルを選択したときのセル分割**
|
|
196
|
+
Googleスプレッドシートの仕様として、隣接するセルをControlクリックで複数選択すると、１つのセルとして扱われてしまうことがあります。（少なくとも Windows10上のChromium Edgeではそうなっています）
|
|
197
|
+
|
|
198
|
+
上記コードのgetSprittedRange()関数は、これを個別のセルに分割する関数です。
|
|
199
|
+
```
|
|
200
|
+
function getSprittedRange(rangelist) {
|
|
201
|
+
// 戻り値（個別セル範囲）を格納するための配列
|
|
202
|
+
const output = [];
|
|
203
|
+
for (const range of rangelist.getRanges()) {
|
|
204
|
+
// RangeList が連続したセルの場合は、個別セル範囲に分割してoutput配列に追加
|
|
205
|
+
if (range.getHeight() != 1 || range.getWidth() != 1) {
|
|
206
|
+
for (let i = 1; i <= range.getHeight(); i++) {
|
|
207
|
+
for (let j = 1; j <= range.getWidth(); j++) {
|
|
208
|
+
output.push(range.getCell(i, j));
|
|
209
|
+
}
|
|
210
|
+
}
|
|
211
|
+
} else {
|
|
212
|
+
output.push(range);
|
|
213
|
+
}
|
|
214
|
+
}
|
|
215
|
+
return output;
|
|
216
|
+
}
|
|
217
|
+
```
|