Teratail/370876 (別シートから検索・コピーした値を蓄積し続けるGASを書きたい)

derived from

https://teratail.com/questions/370876

requirement

• CRM から Google SpreadSheet に出力が行なわれるデータを IMPORTRANGE で参照するシートをマスターとする
• マスターから 1 列目の担当ごとに顧客情報を分配する
• 参照元の変更に応じて随時更新する

sample

• マスターとなるシート

	A列	B列	C列	D列	E列
1行	担当者	顧客名	フリガナ	性別	顧客登録日
2行	担当A	田中	タナカ	男	09/21
3行	担当B	鈴木	スズキ	女	10/01
4行	担当A	佐藤	サトウ	女	10/04
5行	担当B	山田	ヤマダ	男	11/12

• 分配後のシート

	A列	B列	C列	D列	E列
1行	担当者名：	担当A			▼顧客登録日
2行	担当A	田中	タナカ	男	09/21
3行	担当A	佐藤	サトウ	女	10/04

hypothesis

• 部分的な入力はない(A-E 列のうち A 列だけが入力されることは起きない)
• 顧客名はユニーク
• 担当の変更がある
• 担当と顧客名以外が変更され得る
• マスターの記載順は変更されず、末尾に追記が起こる
  • ただし担当は変更され得る
• マスターに空行や表外の行・列への入力は存在しない
• 担当がいなくなったり、新たな担当が出現したりする
• 分配後のシートで A-E 列のデータの中間に空欄が存在し得る

code

const inputSheetName = `検索用シート`;
const invalidColour = `#444444`;
const resetColour = null;
const inChargeSheetName = (name) => `${name}用の蓄積シート`;
const isInChargeSheetName = (name) => name.endsWith(`用の蓄積シート`);
const whoInChargeFromSheetName = (name) => name.replace(`用の蓄積シート`,``);

const onChange = (e) => {
    if(e.changeType !== "OTHER") return;
    const spreadsheet = SpreadsheetApp.getActive();
    const mappedData = MapToWhoInCharge(spreadsheet.getSheetByName(inputSheetName).getDataRange().getValues().slice(1));
    const sheetNames = spreadsheet.getSheets().reduce((a,c)=>a.concat(fillInBySheet(c,mappedData)),[]).filter(e=> e !== undefined);
    [...mappedData.keys()].filter(e=>!sheetNames.includes(e)).forEach(e=>fillInByName(e,mappedData.get(e)));
}
const MapToWhoInCharge = (data) => new Map([...new Set(data.map(([e])=>e)).values()].reduce((a,c)=> [...a,[c,data.filter(([e])=>e===c)]],[]));
const fillInBySheet = (sheet, map) => {
    const [isProperSheet, person] = tryGetWhoInCharge(sheet,map);
    if(!isProperSheet) return;
    fillIn(sheet, map.get(person));
    return person;
}
const fillIn = (sheet, current) => {
    sheet.setTabColor(resetColour);
    const toRowNumberOffset = 2;
    const existing = sheet.getDataRange().getValues();
    const tail = existing.reduce((a,[c],i)=>c !== "" ? i+1 : a,existing.length);
    sheet.getRange(1,1,tail,5).setBackground(resetColour);
    const previous = existing.slice(1,tail).map(e=>e.slice(0,5));
    const previousClients = new Set(previous.filter(([e])=> e !== "").map(([,e])=>e));
    const currentClients = new Map([...new Set(current.filter(([e])=> e !== "").map(([,e])=>e)).values()].reduce((a,c)=> [...a,[c,current.filter(([,e])=>e===c)]],[]));
    previous.reduce((a,[,c],i)=>currentClients.has(c)?a:a.concat(i+toRowNumberOffset),[]).forEach(r=>sheet.getRange(r,1,1,5).setBackground(invalidColour));
    const appendRows = current.reduce((a,c)=>previousClients.has(c[1])?a:a.concat([c]),[]);
    if(appendRows.length > 0) {sheet.insertRowsBefore(tail+1,appendRows.length);} // insertRowsAfter だと最終行を invalidColour で塗り潰したとき前の行の色を引き継いでしまう。逆に下端外に色があると insertRowsBefore でも引き継ぐ。下端外に余分な入力があるパターン以外は考慮しないから、先に insertRowsAfter してから、塗り潰しをすべきかも。
    const willBe = previous.map(e=>currentClients.has(e[1])?currentClients.get(e[1]).flat():e).concat(appendRows);
    sheet.getRange(2,1,willBe.length,5).setValues(willBe);
}
const tryGetWhoInCharge = (sheet, map) => {
    const sheetName = sheet.getSheetName();
    if(!isInChargeSheetName(sheetName)) return [false,undefined];
    const person = whoInChargeFromSheetName(sheetName);
    if(!map.has(person)) {
        sheet.setTabColor(invalidColour);
        return [false, undefined];
    }
    return [true, person];
}
const fillInByName = (name, itsData) => {
    const sheet = prepareInChargeSheetByName(name);
    fillIn(sheet, itsData);
    return name;
}
const prepareInChargeSheetByName = (name) => {
    const sheetName = inChargeSheetName(name);
    const existingSheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    if(existingSheet !== null) {
        return existingSheet;
    }
    const newSheet = SpreadsheetApp.getActive().insertSheet(sheetName);
    newSheet.appendRow(`担当者名：\t${name}\t\t\t▼顧客登録日`.split(`\t`))
    return newSheet;
}
const setTrigger = () => {
    ScriptApp.newTrigger("onChange").forSpreadsheet(SpreadsheetApp.getActive()).onChange().create();
}
const initializer = () => {
    const dat = `担当者\t顧客名\tフリガナ\t性別\t顧客登録日
担当A\t田中\tタナカ\t男\t09/21
担当B\t鈴木\tスズキ\t女\t10/01
担当A\t佐藤\tサトウ\t女\t10/04
担当B\t山田\tヤマダ\t男\t11/12`;
    const inputSheet = SpreadsheetApp.getActive().getSheetByName(inputSheetName)?.clear() ?? SpreadsheetApp.getActive().insertSheet(inputSheetName);
    const value = dat.split(`\n`).map(e=>e.split(`\t`));
    inputSheet.getRange(1,1,value.length,value[0].length).setValues(value);
    SpreadsheetApp.getActive().getSheets().forEach(s=> {if(s.getSheetName() !== inputSheetName) {SpreadsheetApp.getActive().deleteSheet(s);}});
}

next action

• 業務の詳細な確認
  プログラム的な難点はない。
  仮定が適切であるか、業務に適応できるかの確認が必要
• そもそも CRM で解決できないか
  kintone などアプリが作れる CRM がある。単にメモ項目が用意されるだけでもよいのかもしれない。
  想定手法の妥当性。例えば CRM から SpreadSheet に連携されるなら、そもそも Google Apps Script でそのシートを読めば良いので、IMPORTRANGE するだけの "検索用シート” は不要。そのようなことをする合理的な理由があるのだとすれば未知の業務要件があるのかもしれない。
  Google Apps Script の限界として、IMPORTRANGE の変更は onChange でしかキャッチできない。これは編集行為のたびにスクリプトが起動することを意味する。動的に更新とのことだが、顧客がそんなに頻繁に追加されるのか、定期的な実行や別のシステムでは満たせない要件は何か。