【GAS】複数条件がある場合のステータス集計（データの集計）を自動化しようとしているのですが、分からないためどなたか教えてください。

Question

前提・実現したいこと

以下の画像の集計をしております。
集計の方法としては、

①同じ項目内で、ステータスが1番大きいもの
②日付が連携日以降のもの（連携日は、F列にあり）
③同じ担当者がいる場合は、確認日が1番直近のステータスを選ぶ

→③の直近の日は、
データを集計する日（プログラムを動かす日）に対して直近の確認日になります。
※本日がデータ集計する日であれば、本日に対して直近の確認日となります。

→上記3つの条件を満たす中で、1番大きなステータスを選び出す
になります。

※こちらの画像の場合、回答は1行目の「b」になります。

＝＝＝

＜1.02追記内容＞
※上記の実現したいことから、内容が追加されているので、以下に記載します
※以下画像の赤字部分の項目が、追加したい内容に関わる部分となります

①F列に記載の連携日が、項目1の項目ごとに異なるので、それぞれの連携日を比較対象にして集計をしたい
→1.02の追記前は、F列には1つの連携日しかなかったので、項目1がどのような項目でも連携日を同じ日付で比較していましたが、項目1の内容ごとに、連携日が異なってきます

②ステータスを比較した結果（比較して解となるステータス）を、J列に出力したい
※項目1の項目ごとに順番は並ぶ前提となります

③比較した結果のステータスがfの場合（1番大きなステータス結果になった場合）E列に記載されている成約額を
K列に出力したい
※ステータスがfの場合は、必ず同じ行のE列に成約額が記載されている前提となります

＝＝＝

発生している問題・エラーメッセージ

上記の集計を実現したい場合、以下の内容をコード化すればよいのではないか？
と思っているのですが、どのようにプログラムを書けばよいか分からないため、
もし分かる方がいらっしゃいましたら教えてくださいm(_ _)m

＝＝＝

・ステータス比較をするために、数字でステータスを定義づける　→　今回であれば、a：1　b：1　c：2　d：3　e：4　f：5
※補足：上記、aとbのステータス名が違いますが、数字の重み付けとしては同じになるため、
同じ数字にしています。
どちらのステータスが回答になっても大丈夫です。

・項目Aのステータスの中で、1番大きなものを選ぶ

★
・もしそのステータスと同じ行の確認日が、連携日より前の日付であれば
・次に大きなステータスを選ぶ
・連携日より後の日付のステータスが見つかるまで繰り返す

・上記の条件に合致するステータスが見つかった場合

・そのステータスと同じ行の確認日以降で、1番最近の日付、かつ、担当者が同じステータスがあるか確認する

・ある場合（条件に合致するステータスが複数ある場合は、どちらかを選ぶ）
・そのステータスを優先する
・そのステータスより大きなものがないか探す（1度確認した担当者の場合は、検索対象から除く）
・★以下をを繰り返し
・連携日以降、1番ステータスの大きなもの（担当者が複数ある場合は1番直近の日付）を担当者ごとに比較し、1番大きなステータスが見つかるまで続ける

・ない場合
・そのステータスが回答

Comments

[退会済みユーザー]

③同じ担当者がいる場合は、確認日が1番直近のステータスを選ぶ
の「直近」とは、確認日がどの日に対して「直近」という意味なのでしょうか？

[tanaka_444]

ご確認ありがとうございます。
データを集計する日（プログラムを動かす日）に対して直近の確認日になります。
※本日がデータ集計する日であれば、本日に対して直近の確認日になります。
説明不足で申し訳ありません。

[退会済みユーザー]

→　今回であれば、a：1　b：1　c：2　d：3　e：4　f：5
とのことですが、aとbが同じ値なのは理由があるのでしょうか。（同じ値の場合もある、という例示でしょうか）その場合、どちらのステータスを選んでも構わないのでしょうか、

[tanaka_444]

理由があります。
ステータス名が違いますが、数字の重み付けとしては同じになるため、
同じ数字にしています。
どちらのステータスが回答になっても大丈夫です。
説明不足ですみません。

Answer 1

(コメントより）

画像内の「項目1」のAとBをそれぞれ異なるものとして扱い集計をかけたい
項目1がAの場合のステータス比較と、項目1がBの場合のステータス比較は、それぞれ独立しているものとして扱いたいのですが、項目1がAから次のBにいくまでに、画像のA列が空白になっているので、区別して扱うにはどのようにすれば良いか

詳細は下記のコードのmain()を参照いただきたいのですが、下記のように、
・１行読みとる
・A列に空白でないものが現れた場合は、その値を項目１として保存。ブロックの開始行をstartという変数に保存
・次にA列に空白でないものが現れたとき
->項目１の値と一緒にstartから現在行までのblockを別関数に渡し、その別関数の中でステータス最大値を計算し、結果を返す
その後項目１を切り替えて、start行を更新、ループ最初に戻って次の行を読み取る
という流れになります。

※なお、下記のコードが正常に動く前提として
・各行の確認日はすべて処理当日以前の日付であること
・各項目において、同じ担当者で同じ確認日のデータは存在しない
とします。

// ステータス値を表すオブジェクト
const status = { 'a': 1, 'b': 1, 'c': 3, 'd': 4, 'e': 5, 'f': 6 }
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// ステータスの最小値
const MIN_STATUS_VALUE = Object.values(status).sort((a, b) => a - b)[0];
// ステータスの最大値
const MAX_STATUS_VALUE = Object.values(status).sort((a, b) => a - b).slice(-1)[0];

function startFunc() {
  // F列の最終行を取得
  const lastRow = sheet.getRange(sheet.getMaxRows(), 6).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  // F列（連携日）のデータを取得
  const renkeibiList = sheet.getRange(2, 6, lastRow - 1, 4).getValues();

  // I列（項目１）をキー、Ｆ列（連携日）を値とする連想配列を作成。
  const renkeibiTable = renkeibiList.reduce((acc, cur) => { acc[cur[3]] = cur[0]; return acc }, {})

  // ステータス最大値を持つレコードを取得
  const records = getMaxStatusRecords(renkeibiTable);

  // 各項目１ごとのステータスの結果を書き込む。ステータス最大値の場合は、成約額を書き込む。
  const writeValues = renkeibiList.map(e =>
    [
      records[e[3]]?.[2] ?? '',   // J列
      status[records[e[3]]?.[2]] === MAX_STATUS_VALUE ? records[e[3]][4] ?? '' : ''  // K列
    ]
  );
  sheet.getRange(2, 10, writeValues.length, writeValues[0].length).setValues(writeValues);
}

function getMaxStatusRecords(table) {
  // 列Bの最終行を取得
  const lastRow = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

  // シートからデータ部分を取得する。（最後の行の判定のため１行余分に取得）
  const data = sheet.getRange(2, 1, lastRow, 5).getValues()

  //　処理中のブロックの「項目１」を保存する変数
  let currentKoumoku1 = '';

  // 結果を格納する配列
  const result = {};

  // 各ブロックの最初の行
  let start = 0;

  for (let row = 0; row < data.length; row++) {
    // 現在行のA列（項目１）を読み取る
    const koumoku1 = data[row][0];

    // 取得した行の「項目１」が空欄かどうか判定
    if (koumoku1 !== '') {
      // 現在保存中の「項目１」が空欄かどうか判定
      if (currentKoumoku1 !== '') {
        /* 取得した行の「項目１」が空欄ではない＆保存中の「項目１」が空欄ではない
         ＝最初の行ではない＆項目１が切り替わったタイミング　ということなので、
         ブロックを処理。*/
        const statusValue = checkStatusValues3(data.slice(start, row), table[currentKoumoku1])
        // 結果用連想配列に項目１をキーとしてステータス値を格納
        result[currentKoumoku1] = statusValue;
      }
      //　切替後の新しい「項目１」をcurrentKoumoku1に代入し、ブロックのstartを現在行に更新する。
      currentKoumoku1 = koumoku1;
      start = row;
    }
  }
  //　ループを抜けたので残っているブロックを処理する
  const statusValue = checkStatusValues3(data.slice(start, lastRow - 1), table[currentKoumoku1]);

  // 結果用連想配列に項目１をキーとしてステータス値を格納
  result[currentKoumoku1] = statusValue;

  return result;
}

function checkStatusValues3(block, renkeibi) {
  // 確認日降順に並び替える。
  return block.sort((a, b) => b[3].getTime() - a[3].getTime())
    // 確認日が連携日より後のものを抽出
    .filter(e => e[3] > renkeibi)
    // 同じ担当者であれば確認日が処理当日に直近のものを抽出
    .reduce((acc, cur) => acc.some(e => e[1] === cur[1]) ? acc : [...acc, cur], [])
    // ステータスが最大のものを抽出
    .reduce((a, b) => status[a[2]] > status[b[2]] ? a : b, MIN_STATUS_VALUE - 1);
}

Comments

[tanaka_444]

フィードバックありがとうございます。とても助かりますm(_ _)m

1点質問させていただきたいのですが、質問に記載している図の
項目AとB（それ以降も様々な異なる項目の種類がでてきます）
の識別をしたい場合、どのようにプログラムに組み込めばよろしいでしょうか？
項目Aの中で連携後の確認日かつステータスが1番大きいものの回答をもとめ、
項目Bの中で連携後の確認日かつステータスが1番大きいものの回答をもとめる、
というように、
項目ごとに解を分けて出したいです。

ご確認よろしくお願いいたしますm(_ _)m

[退会済みユーザー]

（１）
①ステータスが同じ項目内で最大であること
②確認日＜連携日であること
③（同じ担当者内で？）確認日が当日に1番近いこと
これらの優先順位が明確に書かれていません。
画像の例でbが選ばれるということは
②が最優先で、その次に③が優先で最後に①が優先ということでしょうか？
仮に②の次に①が優先されるならば、画像では2行目の山田b2021/06/01ではなく、6行目の山田d2021/01/20が選ばれるはずです。

（２）条件にあてはまるもの（項目の中でステータスが最も大きく、連携日以降で、当日にもっとも近いもの）を抽出したとき、
最終的に複数の異なる担当者が残った場合、任意の１人を選べばよい、という理解で良いでしょうか？
それとも複数の担当者の数だけ答え（最大ステータス？）を出さなければならないのでしょうか。

（３）最終的に答えとして出すのはステータスだけでよく、誰のいつのステータスかまでは不要なのでしょうか？

[tanaka_444]

ご返信ありがとうございます。確認が遅くなって、すみません。

質問の仕方が分かりづらくてすみません。
質問させていただきたかった内容は、
画像内の「項目1」のAとBをそれぞれ異なるものとして扱い集計をかけたい（今回質問をさせていただいているステータスの確認をしたい）のですが、
そのためには、どこにどのようなプログラム（考え方）を追記すれば良いか教えていただきたく質問しました。
現状、1行づつ読み込んでステータスの比較をしているかと思うのですが、
項目1のAとBの区別を特にしていないかと思います。
項目1がAの場合のステータス比較と、項目1がBの場合のステータス比較は、
それぞれ独立しているものとして扱いたいのですが、項目1がAから次のBにいくまでに、画像のA列が空白になっているので、
区別して扱うにはどのようにすれば良いか分からずに困っております。
質問の内容は、伝わっておりますでしょうか？
もし不明な点ありましたら、お手数ですが教えてください。

＝＝＝

ちなみに、
今回ご返信いただきました（1）～（3）については、以下に返信させていただきます。
分かりづらくて申し訳ないですが、ご確認お願いしますm(_ _)m

（１）
①ステータスが同じ項目内で最大であること
②確認日＜連携日であること
③（同じ担当者内で？）確認日が当日に1番近いこと
これらの優先順位が明確に書かれていません。
画像の例でbが選ばれるということは
②が最優先で、その次に③が優先で最後に①が優先ということでしょうか？
仮に②の次に①が優先されるならば、画像では2行目の山田b2021/06/01ではなく、6行目の山田d2021/01/20が選ばれるはずです。

→優先順位としては、
②確認日＞連携日　→　ご質問いただいた内容、等記号が逆のため訂正しております
③
①
の順番になります。

＝＝

（２）条件にあてはまるもの（項目の中でステータスが最も大きく、連携日以降で、当日にもっとも近いもの）を抽出したとき、
最終的に複数の異なる担当者が残った場合、任意の１人を選べばよい、という理解で良いでしょうか？
それとも複数の担当者の数だけ答え（最大ステータス？）を出さなければならないのでしょうか。

→こちらは、任意の1人を選ぶのみで大丈夫です。

＝＝

（３）最終的に答えとして出すのはステータスだけでよく、誰のいつのステータスかまでは不要なのでしょうか？

→はい、誰のいつのステータスかまでは不要になります。

[退会済みユーザー]

回答を修正しました

[tanaka_444]

ご確認ありがとうございます。
なるほど、こういったやり方があるのですね。
まだ大枠しか把握しておりませんので、プログラムの詳細確認して動かしてみます。
助かりますm(_ _)m

[tanaka_444]

すみません。
教えていただいたプログラムについて
確認させていただきたいのですが、
項目1の1つを処理するごとに（画像の例ですとAの項目）
プログラムとしては実行が一度終了する形になりますでしょうか？
こちらの画像ではAとBの2つの項目のみですが、
実際のデータでは、項目1の数が100個ほどあるので、全部で500行ほどになる可能性もあるため、
GASの実行時間の上限である6分を超えてしまうかもしれない、と思ったので確認させていただきたいです。
よろしくお願いしますm(_ _)m

[退会済みユーザー]

回答のコードは、全行処理するまで終了しなないようになっています。（項目1の数が100個ある場合は、その100個全部処理完了するまで終了しません）
6分を超えそうなばあいは、処理する行（項目）を区切って実行するなどの工夫が必要かもしれません。

[tanaka_444]

承知しました！
ご回答ありがとうございますm(_ _)m
やってみます。

[tanaka_444]

度々失礼します。
やりたい内容に追加があったため、質問内容の前提・実現したいことの中に＜1.02追記内容＞として
3つ画像付きで追加しております。
教えていただいたプログラム内に、どのように追加すれば良いか教えていただけますでしょうか？
すみませんが、よろしくお願いしますm(_ _)mm(_ _)m

[tanaka_444]

すみません。
あと、12/31に編集いただいたプログラムについて確認させてください。

/****　第２段階：block1から、確認日が処理当日に一番近いものを抽出　****/
のところですが、確認者がもし同じであれば、
上記の内容を実行する形になりますでしょうか？

確認者が同じ場合のみ、確認日が近い方のステータスを取得する設計にする必要があります（ややこしくてすみません・・）

ご確認お願いしますm(_ _)m

[退会済みユーザー]

質問文での仕様変更に合わせて修正しました。
> 確認者が同じ場合のみ、確認日が近い方のステータスを取得する設計にする必要があります
そのようにしています。

[tanaka_444]

度々ありがとうございます。
確認してみますm(_ _)m

[tanaka_444]

こんにちは。
先日お送りいただいたプログラムを理解しようと読み込んでいるのですが、
ついでに実行処理時間が6分以上になった時の対策のために、
プログラムを追加しようとしています。
以下のURLに記載のプログラムをそのまま組み込めば良いかと思ったのですが、理解あっているかご確認いただくことできますでしょうか？
https://kido0617.github.io/js/2017-02-13-gas-6-minutes/

また、組み込むのであれば、
教えていただいたプログラムのどこに入れるのが良いでしょうか？

お手数おかけしますが、ご確認お願いしたいですm(_ _)m

[tanaka_444]

すみません。
上記の件ですが、質問内容に追記があるため、まとめて後ほど記載いたします。
もし内容考えてくださっていましたら、ストップいただきたいです。
よろしくお願いしますm(_ _)m

[tanaka_444]

度々失礼しますm(_ _)m
昨日追加しました6分以上の対策については、他の方に質問してみます。
前回教えていただいたプログラムの内容から、一部変更したいことがあり、
本日新しく質問を投稿しておりますので、もし可能であればご確認いただけますでしょうか？
度々申し訳ありません。よろしくお願いいたします。