teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップGoogle スプレッドシートに関する質問【GAS】複数条件がある場合のステータス集計（データの集計）を自動化しようとしているのですが、分からないためどなたか教えてください。

編集履歴

回答編集履歴

3

質問文での仕様変更に伴う修正

2022/01/03 15:10

投稿

退会済みユーザー
answer CHANGED
@@ -4,147 +4,102 @@
4
4
 
5
5
  詳細は下記のコードのmain()を参照いただきたいのですが、下記のように、
6
6
  ・１行読みとる
7
- ・A列に空白でないものが現れた場合は、その値を項目１として保存
7
+ ・A列に空白でないものが現れた場合は、その値を項目１として保存。ブロックの開始行をstartという変数に保存
8
- ・配列（下記コードでは「block」という変数）に、読み取った行データを蓄積していく
9
8
  ・次にA列に空白でないものが現れたとき
10
- ->項目１の値と一緒に蓄積したblockを別関数に渡し、その別関数の中でステータス最大値を計算し、結果を返す
9
+ ->項目１の値と一緒にstartから現在行までのblockを別関数に渡し、その別関数の中でステータス最大値を計算し、結果を返す
11
- 　その後項目１を切り替えて、blockを初期化、ループ最初に戻って次の行を読み取る
10
+ 　その後項目１を切り替えて、start行を更新、ループ最初に戻って次の行を読み取る
12
11
  という流れになります。
13
12
 
14
- 下記を実行すると、
15
- 各項目１のグループごとに、条件に当てはまるステータス最大値が
16
- ```
17
- [ { currentKoumoku1: 'A', statusValue: 1 },
18
- { currentKoumoku1: 'B', statusValue: 1 },
19
- { currentKoumoku1: 'C', statusValue: 1 } ]
20
- ```
21
- のような形式でログに表示されます。
22
- （currentKoumoku1が各グループの項目１、statusValueがステータスの最大値）
23
- 　
24
13
  ※なお、下記のコードが正常に動く前提として
25
14
  ・各行の確認日はすべて処理当日以前の日付であること
26
- ・ステータスに設定される値の最小値は−1より大きいこと
15
+ ・各項目において、同じ担当者で同じ確認日のデータは存在しない
27
16
  とします。
28
17
 
29
- また、抽出条件は、質問文記載のものではなく、コメント後半での回答に記載されている文から読み取れる限りのものであることとします。
30
- すなわち
18
+ ```js
31
- 「②確認日＞連携日である
32
- ③確認日が当日に一番近い
33
- ①ステータスが同じ項目内で最大である
34
- ②->③->① の順番でふるいわけを行い、
19
+ // ステータス値を表すオブジェクト
20
+ const status = { 'a': 1, 'b': 1, 'c': 3, 'd': 4, 'e': 5, 'f': 6 }
35
- 最終的に複数の異なる担当者が残った場合は、任意の１人を選べばよく、
21
+ const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
36
- 最終的に答えとして出すのはステータスだけでよく、誰のいつのステータスかまでは不要」
22
+ // ステータスの最小値
23
+ const MIN_STATUS_VALUE = Object.values(status).sort((a, b) => a - b)[0];
37
- ということとしています。
24
+ // ステータスの最大値
25
+ const MAX_STATUS_VALUE = Object.values(status).sort((a, b) => a - b).slice(-1)[0];
38
26
 
27
+ function startFunc() {
28
+ // F列の最終行を取得
29
+ const lastRow = sheet.getRange(sheet.getMaxRows(), 6).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
30
+ // F列（連携日）のデータを取得
39
- ・statusValueに−1が返ってくるときは条件にあてはまる行がないという意味です。
31
+ const renkeibiList = sheet.getRange(2, 6, lastRow - 1, 4).getValues();
40
32
 
41
- ・質問者さんの理解のしやすさを優先して、mapやfilter、reduceはあえて使用していません。
33
+ // I列（項目１）をキー、Ｆ列（連携日）を値とする連想配列を作成。
34
+ const renkeibiTable = renkeibiList.reduce((acc, cur) => { acc[cur[3]] = cur[0]; return acc }, {})
42
35
 
43
- ```js
44
- // ステータス値を表すオブジェクト
36
+ // ステータス最大値を持つレコードを取得
45
- const status = { 'a': 1, 'b': 1, 'c': 3, 'd': 4, 'e': 5 }
37
+ const records = getMaxStatusRecords(renkeibiTable);
46
38
 
39
+ // 各項目１ごとのステータスの結果を書き込む。ステータス最大値の場合は、成約額を書き込む。
47
- function main() {
40
+ const writeValues = renkeibiList.map(e =>
41
+ [
42
+ records[e[3]]?.[2] ?? '', // J列
43
+ status[records[e[3]]?.[2]] === MAX_STATUS_VALUE ? records[e[3]][4] ?? '' : '' // K列
44
+ ]
45
+ );
48
- const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
46
+ sheet.getRange(2, 10, writeValues.length, writeValues[0].length).setValues(writeValues);
47
+ }
49
48
 
49
+ function getMaxStatusRecords(table) {
50
- // シートからデータ部分を取得する。
50
+ // 列Bの最終行を取得
51
- const data = sheet.getDataRange().getValues();
51
+ const lastRow = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
52
52
 
53
- // 連携日
53
+ // シートからデータ部分を取得する。（最後の行の判定のため１行余分に取得）
54
- const renkeibi = sheet.getRange("F2").getValue();
54
+ const data = sheet.getRange(2, 1, lastRow, 5).getValues()
55
55
 
56
- // ブロック（項目A,B,C...ごとに行データをまとめる箱）
57
- let block = [];
58
-
59
- //　処理中ブロックの「項目１」を保存する変数
56
+ //　処理中のブロックの「項目１」を保存する変数
60
57
  let currentKoumoku1 = '';
61
58
 
62
59
  // 結果を格納する配列
63
- const result = [];
60
+ const result = {};
61
+
64
- // row:行番号（2行目から読み取るので初期値は1）;
62
+ // 各ブロックの最初の行
63
+ let start = 0;
64
+
65
- for (let row = 1; row < data.length; row++) {
65
+ for (let row = 0; row < data.length; row++) {
66
- // 現在の行データを１行読み取る
66
+ // 現在行のA列（項目１）を読み取る
67
- // A列（項目１）
68
67
  const koumoku1 = data[row][0];
69
68
 
70
69
  // 取得した行の「項目１」が空欄かどうか判定
71
70
  if (koumoku1 !== '') {
72
71
  // 現在保存中の「項目１」が空欄かどうか判定
73
72
  if (currentKoumoku1 !== '') {
74
- // 取得した行の「項目１」が空欄ではない＆保存中の「項目１」が空欄ではない
73
+ /* 取得した行の「項目１」が空欄ではない＆保存中の「項目１」が空欄ではない
75
- // ＝最初の行ではない＆項目１が切り替わったタイミング　ということなので、
74
+ ＝最初の行ではない＆項目１が切り替わったタイミング　ということなので、
76
- // 蓄積したブロックを処理。
75
+ ブロックを処理。*/
77
- const statusValue = checkStatusValues(block, renkeibi)
76
+ const statusValue = checkStatusValues3(data.slice(start, row), table[currentKoumoku1])
78
- // 結果用配列に項目１とステータス値を格納
77
+ // 結果用連想配列に項目１をキーとしてステータス値を格納
79
- result.push({ currentKoumoku1, statusValue });
78
+ result[currentKoumoku1] = statusValue;
80
79
  }
81
- //　切替後の新しい「項目１」をcurrentKoumoku1に代入し、ブロックを初期化する。
80
+ //　切替後の新しい「項目１」をcurrentKoumoku1に代入し、ブロックのstartを現在行に更新する。
82
81
  currentKoumoku1 = koumoku1;
83
- block = [];
82
+ start = row;
84
83
  }
85
- // ブロックに、現在の行データを追加。
86
- block.push([currentKoumoku1, data[row][1], data[row][2], data[row][3]]);
87
84
  }
88
85
  //　ループを抜けたので残っているブロックを処理する
89
- const statusValue = checkStatusValues(block, renkeibi);
86
+ const statusValue = checkStatusValues3(data.slice(start, lastRow - 1), table[currentKoumoku1]);
90
- // 結果用配列に項目１とステータス値を格納
91
- result.push({ currentKoumoku1, statusValue });
92
87
 
93
- //　結果を表示
88
+ // 結果用連想配列に項目１をキーとしてステータス値を格納
89
+ result[currentKoumoku1] = statusValue;
90
+
94
- console.log(result)
91
+ return result;
95
92
  }
96
93
 
97
- /**
98
- * 2次元配列から、条件にあてはまる行のステータス最大値を取り出す関数
99
- * block : ２次元配列
100
- * renkeibi　: 連携日
101
- * 戻り値：
102
- * ステータスの最大値
103
- * 　 条件に当てはまるものがない場合：−1
104
- * **/
105
- function checkStatusValues(block, renkeibi) {
94
+ function checkStatusValues3(block, renkeibi) {
106
- /**** 第１段階：確認日が連携日より後のものを抽出 ****/
95
+ // 確認日降順に並び替える。
107
- const block1 = [];
108
- for (const row of block) {
96
+ return block.sort((a, b) => b[3].getTime() - a[3].getTime())
109
- // 取り出した行の確認日が連携日より後であれば追加。
97
+ // 確認日が連携日より後のものを抽出
110
- if (row[3] > renkeibi)
98
+ .filter(e => e[3] > renkeibi)
99
+ // 同じ担当者であれば確認日が処理当日に直近のものを抽出
100
+ .reduce((acc, cur) => acc.some(e => e[1] === cur[1]) ? acc : [...acc, cur], [])
111
- block1.push(row);
101
+ // ステータスが最大のものを抽出
102
+ .reduce((a, b) => status[a[2]] > status[b[2]] ? a : b, MIN_STATUS_VALUE - 1);
112
- }
103
+ }
113
104
 
114
- /****　第２段階：block1から、確認日が処理当日に一番近いものを抽出　****/
115
- let block2 = [];
116
- // 処理日当日
117
- const now = new Date();
118
-
119
- for (const row of block1) {
120
- // 最初の１個目は配列に追加するだけ
121
- if (block2.length === 0) {
122
- block2.push(row);
123
- continue;
124
- }
125
- // 取り出した要素の確認日と当日の差を、現時点の暫定最小値と比較する。
126
- // 取り出した要素の確認日と当日の差(=A)
127
- const a = now.getTime() - row[3].getTime();
128
-
129
- // 現在のblock2の確認日と当日の差（暫定最小値）(=B)
130
- const b = now.getTime() - block2[0][3].getTime();
131
-
132
- if (a === b) {
133
- // => A=Bであれば、block2に取り出した行を追加する
134
- block2.push(row);
135
- } else if (a < b) {
136
- // => A<Bであれば、取り出した行をblock2とする。
137
- block2 = [row];
138
- }
139
- // => A>Bであれば、何もしない。
140
- }
141
-
142
- /****　第３段階:block2 からステータスが最大のものを抽出　****/
143
- let maxStatus = -1;
144
- for (const row of block2) {
145
- if (maxStatus < status[row[2]]) maxStatus = status[row[2]];
146
- }
147
- // 結果（ステータス最大値）を返す
148
- return maxStatus;
149
- }
150
105
  ```

2

2022/01/03 15:10

投稿

退会済みユーザー
answer CHANGED
@@ -3,10 +3,12 @@
3
3
  >　項目1がAの場合のステータス比較と、項目1がBの場合のステータス比較は、それぞれ独立しているものとして扱いたいのですが、項目1がAから次のBにいくまでに、画像のA列が空白になっているので、区別して扱うにはどのようにすれば良いか
4
4
 
5
5
  詳細は下記のコードのmain()を参照いただきたいのですが、下記のように、
6
+ ・１行読みとる
6
- ・１行ずつ読みとり、A列に空白でないものが現れたときに、その値を項目１として保存
7
+ ・A列に空白でないものが現れた場合は、その値を項目１として保存
7
- ・A列が空白の間は、配列（下記では「block」）にデータを蓄積
8
+ ・配列（下記コードでは「block」という変数）に、読み取った行データを蓄積していく
8
- ・次にA列に空白でないものが現れたときに、項目１の値と一緒に蓄積したblockを別関数に渡す。
9
+ ・次にA列に空白でないものが現れたとき
9
- ・その別関数の中でステータス最大値を計算し、結果を返す
10
+ ->項目１の値と一緒に蓄積したblockを別関数に渡し、その別関数の中でステータス最大値を計算し、結果を返す
11
+ 　その後項目１を切り替えて、blockを初期化、ループ最初に戻って次の行を読み取る
10
12
  という流れになります。
11
13
 
12
14
  下記を実行すると、
@@ -100,7 +102,7 @@
100
102
  * ステータスの最大値
101
103
  * 　 条件に当てはまるものがない場合：−1
102
104
  * **/
103
- function checkStatusValues2(block, renkeibi) {
105
+ function checkStatusValues(block, renkeibi) {
104
106
  /**** 第１段階：確認日が連携日より後のものを抽出 ****/
105
107
  const block1 = [];
106
108
  for (const row of block) {

1

修正

2021/12/31 05:59

投稿

退会済みユーザー
answer CHANGED
@@ -1,32 +1,148 @@
1
+ (コメントより）
1
- ★の基本的な考え方の所ですが、下記のようにやれば効率的だと思います。
2
+ > 画像内の「項目1」のAとBをそれぞれ異なるものとして扱い集計をかけたい
3
+ >　項目1がAの場合のステータス比較と、項目1がBの場合のステータス比較は、それぞれ独立しているものとして扱いたいのですが、項目1がAから次のBにいくまでに、画像のA列が空白になっているので、区別して扱うにはどのようにすれば良いか
2
4
 
5
+ 詳細は下記のコードのmain()を参照いただきたいのですが、下記のように、
6
+ ・１行ずつ読みとり、A列に空白でないものが現れたときに、その値を項目１として保存
7
+ ・A列が空白の間は、配列（下記では「block」）にデータを蓄積
8
+ ・次にA列に空白でないものが現れたときに、項目１の値と一緒に蓄積したblockを別関数に渡す。
9
+ ・その別関数の中でステータス最大値を計算し、結果を返す
3
- 以下の2段階の操作を行います。
10
+ という流れになります。
4
11
 
12
+ 下記を実行すると、
5
- （※前提として、各項目ごとのデータについて条件に当てはまるものを求めるのですから、
13
+ 各項目１のグループごとに、条件に当てはまるステータス最大値が
6
- **各項目ごとのデータについて**、以下の操作を行う必要があることに注意してください。）
7
-
8
- ```text
9
- ０．　最初の１行を配列に格納する。これを以下「仮MAXの配列」と称する。
10
- １．　次の行があるか調べ、なければ終了。あれば次の１行を取り出す。
11
- ２．　取り出した行の確認日が連携日より後かどうか比較。
12
- ３．　２がyes（確認日＞連携日）であれば、４に行く。noであれば１に戻る。
13
- ４．　取り出した行のステータス値を現在の仮MAXのステータス値と比較する。
14
- 　　　　=>ステータス値が仮MAX値と同じであれば、仮MAXの配列に現在行を追加する
15
- 　　　　=>ステータス値が仮MAX値より大きければ、現在の行を仮MAXとする。
16
- 　　　　=>ステータス値が仮MAX値より小さければ、何もしない。
17
- ５.　１に戻る
18
14
  ```
15
+ [ { currentKoumoku1: 'A', statusValue: 1 },
16
+ { currentKoumoku1: 'B', statusValue: 1 },
17
+ { currentKoumoku1: 'C', statusValue: 1 } ]
18
+ ```
19
+ のような形式でログに表示されます。
20
+ （currentKoumoku1が各グループの項目１、statusValueがステータスの最大値）
19
21
  　
20
- これにより最終的に残った「仮MAXの配列」に対して、以下の操作を行います。
21
- 　
22
- ```
23
- ０．　「仮MAXの配列」の最初の要素（行）を配列に格納する。これを以下「仮MINの配列」と称する。
24
- １．　次の行があるか調べ、なければ終了。あれば次の１行を取り出す。
25
- ２．　取り出した要素の確認日と当日の差(=A)を、仮MINの確認日と当日の差(=B)と比較する。
26
- 　　　　=> A=Bであれば、仮MINの配列に現在行を追加する
27
- 　　　　=> A<Bであれば、現在の行を仮MINとする。
28
- 　　　　=> A>Bであれば、何もしない。
22
+ ※なお、下記のコードが正常に動く前提として
23
+ ・各行の確認日はすべて処理当日以前の日付であること
24
+ ・ステータスに設定される値の最小値は−1より大きいこと
29
- ３.　１に戻る
25
+ とします。
30
- ```
31
26
 
27
+ また、抽出条件は、質問文記載のものではなく、コメント後半での回答に記載されている文から読み取れる限りのものであることとします。
28
+ すなわち
29
+ 「②確認日＞連携日である
30
+ ③確認日が当日に一番近い
31
+ ①ステータスが同じ項目内で最大である
32
+ ②->③->① の順番でふるいわけを行い、
33
+ 最終的に複数の異なる担当者が残った場合は、任意の１人を選べばよく、
34
+ 最終的に答えとして出すのはステータスだけでよく、誰のいつのステータスかまでは不要」
35
+ ということとしています。
36
+
37
+ ・statusValueに−1が返ってくるときは条件にあてはまる行がないという意味です。
38
+
39
+ ・質問者さんの理解のしやすさを優先して、mapやfilter、reduceはあえて使用していません。
40
+
41
+ ```js
42
+ // ステータス値を表すオブジェクト
43
+ const status = { 'a': 1, 'b': 1, 'c': 3, 'd': 4, 'e': 5 }
44
+
45
+ function main() {
46
+ const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
47
+
48
+ // シートからデータ部分を取得する。
49
+ const data = sheet.getDataRange().getValues();
50
+
51
+ // 連携日
52
+ const renkeibi = sheet.getRange("F2").getValue();
53
+
54
+ // ブロック（項目A,B,C...ごとに行データをまとめる箱）
55
+ let block = [];
56
+
57
+ //　処理中ブロックの「項目１」を保存する変数
58
+ let currentKoumoku1 = '';
59
+
60
+ // 結果を格納する配列
61
+ const result = [];
62
+ // row:行番号（2行目から読み取るので初期値は1）;
63
+ for (let row = 1; row < data.length; row++) {
64
+ // 現在の行データを１行読み取る
65
+ // A列（項目１）
66
+ const koumoku1 = data[row][0];
67
+
68
+ // 取得した行の「項目１」が空欄かどうか判定
69
+ if (koumoku1 !== '') {
70
+ // 現在保存中の「項目１」が空欄かどうか判定
71
+ if (currentKoumoku1 !== '') {
72
+ // 取得した行の「項目１」が空欄ではない＆保存中の「項目１」が空欄ではない
73
+ // ＝最初の行ではない＆項目１が切り替わったタイミング　ということなので、
74
+ // 蓄積したブロックを処理。
75
+ const statusValue = checkStatusValues(block, renkeibi)
76
+ // 結果用配列に項目１とステータス値を格納
77
+ result.push({ currentKoumoku1, statusValue });
78
+ }
79
+ //　切替後の新しい「項目１」をcurrentKoumoku1に代入し、ブロックを初期化する。
80
+ currentKoumoku1 = koumoku1;
81
+ block = [];
82
+ }
83
+ // ブロックに、現在の行データを追加。
84
+ block.push([currentKoumoku1, data[row][1], data[row][2], data[row][3]]);
85
+ }
86
+ //　ループを抜けたので残っているブロックを処理する
87
+ const statusValue = checkStatusValues(block, renkeibi);
88
+ // 結果用配列に項目１とステータス値を格納
89
+ result.push({ currentKoumoku1, statusValue });
90
+
91
+ //　結果を表示
92
+ console.log(result)
93
+ }
94
+
95
+ /**
96
+ * 2次元配列から、条件にあてはまる行のステータス最大値を取り出す関数
97
+ * block : ２次元配列
98
+ * renkeibi　: 連携日
99
+ * 戻り値：
100
+ * ステータスの最大値
101
+ * 　 条件に当てはまるものがない場合：−1
102
+ * **/
103
+ function checkStatusValues2(block, renkeibi) {
104
+ /**** 第１段階：確認日が連携日より後のものを抽出 ****/
105
+ const block1 = [];
106
+ for (const row of block) {
32
- これにより最終的に残った「仮MINの配列」が求める答えになります。
107
+ // 取り出した行の確認日が連携日より後であれば追加。
108
+ if (row[3] > renkeibi)
109
+ block1.push(row);
110
+ }
111
+
112
+ /****　第２段階：block1から、確認日が処理当日に一番近いものを抽出　****/
113
+ let block2 = [];
114
+ // 処理日当日
115
+ const now = new Date();
116
+
117
+ for (const row of block1) {
118
+ // 最初の１個目は配列に追加するだけ
119
+ if (block2.length === 0) {
120
+ block2.push(row);
121
+ continue;
122
+ }
123
+ // 取り出した要素の確認日と当日の差を、現時点の暫定最小値と比較する。
124
+ // 取り出した要素の確認日と当日の差(=A)
125
+ const a = now.getTime() - row[3].getTime();
126
+
127
+ // 現在のblock2の確認日と当日の差（暫定最小値）(=B)
128
+ const b = now.getTime() - block2[0][3].getTime();
129
+
130
+ if (a === b) {
131
+ // => A=Bであれば、block2に取り出した行を追加する
132
+ block2.push(row);
133
+ } else if (a < b) {
134
+ // => A<Bであれば、取り出した行をblock2とする。
135
+ block2 = [row];
136
+ }
137
+ // => A>Bであれば、何もしない。
138
+ }
139
+
140
+ /****　第３段階:block2 からステータスが最大のものを抽出　****/
141
+ let maxStatus = -1;
142
+ for (const row of block2) {
143
+ if (maxStatus < status[row[2]]) maxStatus = status[row[2]];
144
+ }
145
+ // 結果（ステータス最大値）を返す
146
+ return maxStatus;
147
+ }
148
+ ```