// ステータス値を表すオブジェクト
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);
}
