トップ Google Apps Scriptに関する質問複数アクティブセル指定した時に、これらのセルの値を取得して数式に代入して展開する方法について

編集履歴

回答編集履歴

追加

2021/10/10 14:14

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -338,6 +338,8 @@
 ・アクティブセルの1つ前の列の1行目は「`2021.7`」
+![イメージ説明](b28f36165578fa1067e0967ad2ae97cb.png)
 なので、
 **シート9**の中から、
@@ -346,8 +348,6 @@
 を探します。
-![イメージ説明](b28f36165578fa1067e0967ad2ae97cb.png)
 そうすると、該当するのは13行目なので、シート9の13行目のデータが シート9の150行目に展開されます。
@@ -449,3 +449,187 @@
 }
 ```
+-----
+# コメントでの追加質問に対する回答
+> 、上記のシート１に相当するピボットテーブルのシートが複数あり、その際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) {
+略
+}
+```

修正

2021/10/10 14:14

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -218,7 +218,7 @@
 * 戻り値：
-*  Range : 分割後の個別セル範囲
+*  Range[] : 分割後の個別セル範囲（Range）を格納した配列
 *****************************************************/

修正

2021/10/10 04:52

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -346,7 +346,7 @@
 を探します。
-![イメージ説明](ff6a62463130d3d623b264b61941bdda.png)
+![イメージ説明](b28f36165578fa1067e0967ad2ae97cb.png)

修正

2021/10/10 04:41

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -168,7 +168,7 @@
 function getAddress(sheetObj, row, col) {
-  if (sheetObj < 1) {
+  if (sheetObj == null) {
     throw Error("引数 sheetObjが指定されていません。");

2021/10/10 04:35

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -14,8 +14,6 @@
 現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、この場合は、まず「どのような動作にしたいのか」をしっかり固める必要があると思います。
-（組んだ後で「実は本当はこういう動作がしたい」「うまく動かない」等言われても、また一からやり直しになってしまいます）
 QUERYを使った「数式」を汎用的に「GASのコード」に変える手段はおそらく存在せず、結局「やりたい動作」を行うために個別にカスタマイズせざるを得ません。

2021/10/10 04:34

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -12,15 +12,17 @@
-現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、まず「どのような動作にしたいのか」を固めた上でないと、同等の動作を組むことはできません。
+現状のQUERYを使った数式と同じ動作をGASで実装することも可能ですが、この場合は、まず「どのような動作にしたいのか」をしっかり固める必要があると思います。
+（組んだ後で「実は本当はこういう動作がしたい」「うまく動かない」等言われても、また一からやり直しになってしまいます）
-QUERYを使った数式を汎用的にGASのコードに変える手段はなく、結局「やりたい動作」を行うための個別のカスタマイズとせざるを得ません。
+QUERYを使った「数式」を汎用的に「GASのコード」に変える手段はおそらく存在せず、結局「やりたい動作」を行うために個別にカスタマイズせざるを得ません。
-　数式を使っていれば、質問者さんの方でカスタマイズも容易であると考えました。
+数式を使っていれば、質問者さんの方でカスタマイズも容易であると考えました。

2021/10/10 04:31

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -300,7 +300,7 @@
-シート1のデータ範囲のセルをクリックして、実行ボタンをクリックすると、下記の動作を行います。
+シート1のデータ範囲のセルを1個以上クリックした状態で、実行ボタンをクリックすると、下記の動作を行います。
@@ -308,7 +308,7 @@
 (2)シート1 のアクティブなセルの**１つ前の列の1行目**にある年月値(「`2021,***`」)を読み取ります。
-(3)シート9 の A1～A149 の範囲から、**下記の条件**にあてはまる行を抽出し、シート9の150行目以降に展開します。
+(3)シート9 の A1～L149 の範囲から、**下記の条件**にあてはまる行を抽出する数式を、シート9の150行目以降に入力します。
 ```text
@@ -322,7 +322,13 @@
 ```
+(4)クリックしたセルが複数ある場合は、残りのセルに対して同じ動作を順次繰り返します。
+それぞれのセルについて検索結果が見つかった場合は、シート9の既存の結果行から1行空けて数式を追記します。
 # 例

2021/10/10 04:26

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -390,6 +390,12 @@
 ```
+上記コード内のgetAddress()関数は、これを拡張し、
+シートオブジェクト・行番号・列番号を与えると、対応する「シート名!A1」形式の文字列を返す関数としています。
 ・**隣接するセルを選択したときのセル分割**

2021/10/10 04:10

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -34,6 +34,12 @@
 （動作検証環境：Windows 10 ／Chromium Edge／GAS：V8エンジン有効）
+シート9の展開先開始行を変えたい場合は、コード中の`OUTPUT_START`の数字を変えて保存・実行してください。
 ```js
 function myFunctionXY() {

追加

2021/10/10 04:05

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -32,7 +32,7 @@
 ・具体的な動作が読解し切れていないので、期待する動作と異なる部分があるかもしれませんが、御了承ください。
+（動作検証環境：Windows 10 ／Chromium Edge／GAS：V8エンジン有効）
 ```js
@@ -347,3 +347,87 @@
 複数の結果がある場合は1行ずつ空けて表示します。（下記はサンプルです）
 ![イメージ説明](e74dcaa5cc67f09d835aeaa1f7f7059b.png)
+---
+# 補足
+・**別シートのセル値取得**
+スプレッドシート上の数式で、同じスプレッドシート上の**別シート**のセル値を読み取る場合、indirect関数を使用する必要はありません。
+単純に`'シート名'!セル番地` とすれば指定したシートの指定した番地の値を取得できます。
+・**GAS内で、指定した行・列から、A1セル形式に変更する手段**
+[getA1Notation() という関数](https://developers.google.com/apps-script/reference/spreadsheet/range#geta1notation)を使うことで、A1セル形式の文字列に変換できます。
+```
+var ss = SpreadsheetApp.getActiveSpreadsheet();
+var sheet = ss.getSheets()[0];
+var range = sheet.getRange(2, 5);
+Logger.log(range.getA1Notation());
+// "E2"
+```
+・**隣接するセルを選択したときのセル分割**
+Googleスプレッドシートの仕様として、隣接するセルをControlクリックで複数選択すると、１つのセルとして扱われてしまうことがあります。（少なくとも Windows10上のChromium Edgeではそうなっています）
+上記コードの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;
+}
+```