Q&A
前提・実現したいこと
スプレッドシートからデータを読み込んで、GASで条件にあうものを選び、出力するというプログラムを作成しております。
条件は、以下となります。
以下の現状できていることに対して、変更したいことがあるのですが、どのように変えれば良いのか分からないため、
教えていただけますでしょうか？
▼現状できていること（添付画像の内容を元に、記載しております）
前提：A列の項目1のカテゴリごとに、以下に合致するものを出力する
・G列の確認日が、F列の連携日以降のデータを抽出し、
・B列が同じ担当者の場合は、確認日が、今日（プログラムを動かす日）に1番近い日を選び、
・その中で、1番ステータスの大きいものを出力する（複数同じステータスが該当した場合は、どれか1つを出力する ※担当者が異なることは、ここでは考慮しない）
▼変更したいこと
①現状、出力結果を、（複数同じステータスが該当した場合は、どれか1つを出力する ※担当者が異なることは、ここでは考慮しない）
としているので、出力結果は、A列の項目1のカテゴリごとに１つの結果になるのですが、
・ステータスfの場合のみ、連携日以降で複数あれば、どれか1つを選んで出力するのではなく、条件に合う件数分を出力する
と、変更したいです。
②①の出力内容を、成約額と成約人数にしたいです（I1～L9の範囲に反映）
※現状、成約額しか出力項目にないです
※追加する場合は、I2～L3のように、1行ずつ追加して記載したいです
※成約人数は、参照するデータに項目がありませんが、ステータスがfになった場合、1を出力するようにしたいです
③現状、項目1のデータは、I列に元々記載されている前提でプログラムが書かれていますが、
書かれていないものとして（I列が空白であるとして）、項目1のデータもI2以降の列に出力したいです。
※基本的に、項目の上から順番に出力されるかと思いますが、順番は問わないです
該当のソースコード
// ステータス値を表すオブジェクト
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);
}
あなたの回答
tips
プレビュー
