【GAS/JavaScript】プログラム内に、条件を追加したいのですが、どこをどのように変更すれば良いか分からず困っております。
解決したいこと
以下のプログラムに条件を追加したいのでが、
どこに何を、どのように追加すれば良いか分からず困っています。
もし分かる方がいましたら教えてください。
現在の内容と、追加したい条件
プログラムで行っている内容を簡単に説明しますと、
上記の添付画像のH~I列に、AからG列のデータを集計して判定した結果を出力しています。
★現在の内容から、★追加したい内容を、追加する場合、
以下のプログラムの、どこに何を、どのように追加すれば良いか分からずに困っています。
★現在の内容
項目1ごとに、ステータスが最大値になるもの(ステータス)を、J列に出力し、
もしステータスがfの場合は、成約額をK列に出力する
※集計の結果、ステータスが最大値になるものが複数ある場合は、どれか1つを選択して出力する
(重複して同じ結果になるものは、区別せずに1つだけ出力する)
としております。
これを、以下のように条件を、2つ追加したいです。
★追加したい内容
①現在は、ステータスの最大値がfになるものが複数ある場合、どれか1つのみ結果を出力し、
J列にステータスを書き込み、K列に成約額を出力しているが、
ステータスの最大値がfになる場合のみ、複数ある場合はその数だけ成約額を出力する
→K列に、その数だけ成約額を足し合わせて、出力する
②現在は、ステータスの最大値がfになるものが複数ある場合、L列の成約人数には、何も出力していないが、
ステータスの最大値がfになる場合のみ、L列に成約人数を出力する
→成約人数=ステータスの最大値がfになる数 とする
→①と同じく、複数ある場合は、足し合わせてL列に結果を出力する
(例)画像内のM列~G列の黄色背景部分に記載のように、
項目1=Aでは、
集計の結果ステータスがfになるものが、2つあるので(C12とC13のセル)
E12とE13の成約額をそれぞれ足し合わせて、K2に出力する。
また、成約人数は2人(ステータスの最大値=fになるものが、2つあるので)
のため、成約人数にも足し合わせて2にする。
必ず変更しないといけないと思われる箇所
// 各項目1ごとのステータスの結果を書き込む。ステータス最大値の場合は、成約額を書き込む。
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);
}
→こちらが、//のコメント欄にも記載のあるように、
現状は、 各項目1ごとのステータスの結果を書き込む。ステータス最大値の場合は、成約額を書き込む。
というプログラムにしかなっておりません。
そのため、ここを上記の内容を追加するために変更しないといけないかと思います。
★追加したい内容(再掲)
①現在は、ステータスの最大値がfになるものが複数ある場合、どれか1つのみ結果を出力し、
J列にステータスを書き込み、K列に成約額を出力しているが、
ステータスの最大値がfになる場合のみ、複数ある場合はその数だけ成約額を出力する
→K列に、その数だけ成約額を足し合わせて、出力する
②現在は、ステータスの最大値がfになるものが複数ある場合、L列の成約人数には、何も出力していないが、
ステータスの最大値がfになる場合のみ、L列に成約人数を出力する
→成約人数=ステータスの最大値がfになる数 とする
→①と同じく、複数ある場合は、足し合わせてL列に結果を出力する
ここのプログラムは、最低変更しないといけないかと思いますが、
どのように変更すれば良いのか分かりません。
また、このプログラム内の「records」部分など、関わってくるプログラム部分も変更しないといけないかと思うのですが、
どこをどのように変更すれば良いのか分からないため、教えていただきたいです。
よろしくお願いします。
該当するソースコード
// ステータス値を表すオブジェクト
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列(項目1)をキー、F列(連携日)を値とする連想配列を作成。
const renkeibiTable = renkeibiList.reduce((acc, cur) => { acc[cur[3]] = cur[0]; return acc }, {})
// ステータス最大値を持つレコードを取得
const records = getMaxStatusRecords(renkeibiTable);
// 各項目1ごとのステータスの結果を書き込む。ステータス最大値の場合は、成約額を書き込む。
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();
// シートからデータ部分を取得する。(最後の行の判定のため1行余分に取得)
const data = sheet.getRange(2, 1, lastRow, 5).getValues()
// 処理中のブロックの「項目1」を保存する変数
let currentKoumoku1 = '';
// 結果を格納する配列
const result = {};
// 各ブロックの最初の行
let start = 0;
for (let row = 0; row < data.length; row++) {
// 現在行のA列(項目1)を読み取る
const koumoku1 = data[row][0];
// 取得した行の「項目1」が空欄かどうか判定
if (koumoku1 !== '') {
// 現在保存中の「項目1」が空欄かどうか判定
if (currentKoumoku1 !== '') {
/* 取得した行の「項目1」が空欄ではない&保存中の「項目1」が空欄ではない
=最初の行ではない&項目1が切り替わったタイミング ということなので、
ブロックを処理。*/
const statusValue = checkStatusValues3(data.slice(start, row), table[currentKoumoku1])
// 結果用連想配列に項目1をキーとしてステータス値を格納
result[currentKoumoku1] = statusValue;
}
// 切替後の新しい「項目1」をcurrentKoumoku1に代入し、ブロックのstartを現在行に更新する。
currentKoumoku1 = koumoku1;
start = row;
}
}
// ループを抜けたので残っているブロックを処理する
const statusValue = checkStatusValues3(data.slice(start, lastRow - 1), table[currentKoumoku1]);
// 結果用連想配列に項目1をキーとしてステータス値を格納
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);
}
0