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

Question

前提・実現したいこと

Google Spreadsheetで、GASを用いて下記のようなスクリプトを作成したいと考えています。
書いてある言語は読めるものの、基本的にコピペの連続で付け焼刃的に対処してきたため、
細かい部分もご教示いただけると非常にありがたいです。
また、同様の質問が本サイト内にあまり存在しなかったため、ぜひご協力いただきたいです。

######やりたいこと

• 同一ファイル内に、検索用シートと蓄積シート×2の計3つが存在している（それぞれモデルを添付）
• 蓄積シートB1セルの担当Aor担当Bを、検索用シートのA列でそれぞれ検索する
• 検索にヒットした行のA~E列をコピー
• コピーした値を蓄積シートの最終行・A列~E列にペースト
• 上記を検索用シートのデータが更新されることをトリガーにして処理し続ける

######条件が複雑になりそうな部分

• コピー&ペーストする値は、すでに蓄積シートに存在している値を除く（B列の重複を許さない）
• 検索用シートA列の担当Bが数週間後に担当Aに変更された場合でもコピー&ペーストが可能
• 上記、担当がB→Aになった場合であっても、ペースト先は蓄積シートの最終行に限る
• よって、毎処理ごとに蓄積シートのデータが上書きされる・すでにペースト済の行が上下に移動する、といった事象はありえない、とする

######これによって何がしたいのか？

• 担当者ごとの顧客データを、顧客の追加や担当変更によって常に最終行に蓄積していきたい
• 担当が増えるごとに蓄積シートを複製し、B1セルのプルダウンによって検索キーワードを変えたい
• 蓄積シートF列以降は手動で情報を記載するため、処理ごとにA-E列の情報がずれることを避けたい

######【モデル①】検索用シート
||A列|B列|C列|D列|E列|
|--|--|
|1行|担当者|顧客名|フリガナ|性別|顧客登録日|
|2行|担当A|田中|タナカ|男|09/21
|3行|担当B|鈴木|スズキ|女|10/01
|4行|担当A|佐藤|サトウ|女|10/04
|5行|担当B|山田|ヤマダ|男|11/12

######【モデル①】蓄積シート
検索用シートのA列が蓄積シートのB1セルのものを抽出している

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

▼蓄積シートB：担当B用の蓄積シート
||A列|B列|C列|D列|E列|
|--|--|
|1行|担当者名：|担当B|||▼顧客登録日|
|2行|担当B|鈴木|スズキ|女|10/01
|3行|担当B|山田|ヤマダ|男|11/12

######補足

• 検索用シートのデータを、担当がB→Aに変更になった場合、現在のような顧客登録順ではなく、担当変更実施順に並べ替えることは可能
• そのため、例えば最初の処理以降は「処理実行の1時間以内の変更のみ一気にペースト」等は可能
• 上記補足に基づいたモデルを下記に記載

######【モデル②】検索用シート（担当変更ver）
もともと担当Bだった「鈴木」について、11/17に担当変更処理を実施し、担当変更順に並べ替えた
||A列|B列|C列|D列|E列|
|--|--|
|1行|担当者|顧客名|フリガナ|性別|担当変更日|
|2行|担当A|田中|タナカ|男|09/21
|3行|担当A|佐藤|サトウ|女|10/04
|4行|担当B|山田|ヤマダ|男|11/12
|5行|担当A|鈴木|スズキ|女|11/17

######【モデル②】蓄積シート（担当変更ver）

▼蓄積シートA：担当A用の蓄積シート
||A列|B列|C列|D列|E列|
|--|--|
|1行|担当者名：|担当A|||▼担当変更日|
|2行|担当A|田中|タナカ|男|09/21
|3行|担当A|佐藤|サトウ|女|10/04
|4行|担当A|鈴木|スズキ|女|11/17

▼蓄積シートB：担当B用の蓄積シート
||A列|B列|C列|D列|E列|
|--|--|
|1行|担当者名：|担当B|||▼担当変更日|
|2行|担当B|山田|ヤマダ|男|11/12

該当のソースコード

決して丸投げをしたいわけではないのですが、本当にネット上の記載が数少なく、
私の言語能力とリサーチ能力では思考しきれませんでした。
申し訳ないのですが、お心優しい方がいらっしゃいますと非常に嬉しいです。

Comments

[退会済みユーザー]

モデル①とモデル②の関係がよく分かりません。
モデル②では、担当A用蓄積シートのA5セルとE5セルを変更した後、担当A用の蓄積シートの並び順が変わっており
「毎処理ごとに蓄積シートのデータが上書きされる・すでにペースト済の行が上下に移動する、といった事象はありえない、とする」の後半のルール（ペースト済の行が上下に移動しない）に違反していることになります。

①は理想形で、②は①を実現できない場合の次善の形ということでしょうか？

①の場合、担当変更の変更元シートの行は空白になる、ということでしょうか？

Answer 1

モデル 1 で考えました。

• q370876_onEdit を編集時トリガーに指定するか、onEdit に変更してください。
• starter は検索用シートにいくつか入力がある状態で、まとめて担当のシートを作る目的です。
• checker は蓄積シートをさわってしまったりして、データが連動していない状態を対応する目的です（特に、複数セルをコピペして担当が変更されたとき、前の担当者の情報が消し切れないので）。

質問と違って、担当を変更したときに、前の担当の蓄積シートでは背景を灰色で塗り潰しています。これは F 列から右には手入力がなされるとしており、その部分を暗黙に消すのは、担当者によっては好ましく感じないと考えたからです。
かといって、A-E 列のみを消してしまうと、F 列以降との整合性がとれなくなるので、目的に合いません（この点から私は query では対応できないと考えました）。

このため前の担当のシートでは、空白にするか、背景色をつけるか、といった対応が妥当に思いました。
ただ、蓄積シートの最終行が担当変更した場合に、空白だとその行を詰めることになると思うんですが、他と動作が異なることが気になるのと（実際には空白にした場合、つめられるかどうかは F 列以降に記入があるかにも影響を受けると想定します）、”前の担当”という情報は業務だと求められがちなので、背景色にしときました。

現在のコードだと、複数セルのコピペをしたときに、うまく動きません。
特に google apps script の仕様から複数セルの編集では編集前の状態が分からなくなるので、複数セルをコピペして担当が変更になる場合については原理的に対応できないです。
まとめてコピペして担当を変える、というのは、例えば担当者になる人の増員欠員の場合に起こりがちなので、使い勝手を考えるともっと具体的な業務分析が必要になりそうです。

const inputSheetName = `検索用シート`;
const requiredIndexes = `AB`.toUpperCase().split(``).map(e=>e.charCodeAt(0)-`A`.charCodeAt(0));;
const inChargeSheetName = (name) => `${name}用の蓄積シート`;
const invalidColour = `#444444`;

const q370876_onEdit = (e) => {
    const range = e.range;
    const sheet = range.getSheet();
    if(sheet.getSheetName() !== inputSheetName) return;
    const r = range.getRow();
    if(r === 1) return;
    const c = range.getColumn();
    if(c > 4) return;
    let editingRow = sheet.getRange(r,1,1,5).getValues()[0];
    if(c === 1) {
        const today = q370876_cascadeInputDate(sheet,r);
        editingRow[4] = today;
    }
    if(!q370876_isRequiredFulfilled(editingRow)) return;
    q370876_upsertInChargeSheet(editingRow);
    const previous = e.oldValue;
    if(c !==1 || previous === undefined) return;
    q370876_cascadeDeleteInChargeSheet([previous,editingRow[1]]);
}
// 蓄積シートがないときの初期設定用
const q370876_starter = () => {
    const values = SpreadsheetApp.getActive().getSheetByName(inputSheetName)?.getDataRange().getValues().slice(1);
    if(!values) return;
    const personInCharges = [...new Set(values.map(([e])=>e)).values()];
    personInCharges.forEach(name=>{
        const sheet = q370876_getInChargeSheet(name, true);
        const inCharge = values.filter(([e])=> e === name);
        sheet.getRange(2,1,inCharge.length, inCharge[0].length).setValues(inCharge);
    })
}
// 蓄積シートの整合性をチェックするとき用
const q370876_checker = () => {
    const inChargeSheets = SpreadsheetApp.getActive().getSheets();
    const data = new Map(SpreadsheetApp.getActive().getSheetByName(inputSheetName).getDataRange().getValues().map(e=>[`${e[0]}\t${e[1]}`,e]));
    inChargeSheets.forEach(s=> {
        if(!s.getSheetName().endsWith(inChargeSheetName(``))) return;
        const r = s.getLastRow();
        const range = s.getRange(1,1,r,5);
        range.setBackground(null);
        const values = range.getValues();
        values.forEach((e,i) => {
            if(i===0) return;
            if(!data.has(`${e[0]}\t${e[1]}`)) { s.getRange(i+1,1,1,5).setBackground(invalidColour);}
        });
        const renewed = values.map(e=>data.has(`${e[0]}\t${e[1]}`)?data.get(`${e[0]}\t${e[1]}`):e);
        range.setValues(renewed);
    });
}

const q370876_cascadeInputDate = (sheet,rowNumber) => {
    const dateColumn = 5;
    const now = new Date();
    const today = new Date(now.getFullYear(),now.getMonth(),now.getDate(),0,0,0,0);
    sheet.getRange(rowNumber,dateColumn).setValue(today);
    return today;
}
const q370876_cascadeDeleteInChargeSheet = (row) => {
    const sheet = q370876_getInChargeSheet(row[0], false);
    const indexes = sheet.getDataRange().getValues().reduce((a,c,i)=> q370876_isMatchRows(c,row) ? [...a,i] : a,[]).map(e=>e+1);
    if(indexes.length < 1) {
        console.log("unexpected code path");
        return;
    }
    indexes.forEach(r=>sheet.getRange(r,1,1,5).setBackground(invalidColour));
}
const q370876_upsertInChargeSheet = (row) => {
    const sheet = q370876_getInChargeSheet(row[0], false);
    const indexes = sheet.getDataRange().getValues().reduce((a,c,i)=> q370876_isMatchRows(c,row) ? [...a,i] : a,[]).map(e=>e+1);
    if(indexes.length < 1) {
        sheet.appendRow(row);
        return;
    }
    indexes.forEach(r=> {
        const range = sheet.getRange(r,1,1,row.length);
        range.setValues([row]);
        range.setBackground(null);
    });
}
const q370876_isMatchRows = (a,b) => requiredIndexes.every(i=>a[i]===b[i]);
const q370876_getInChargeSheet = (name, reset = true) => {
    const sheetName = inChargeSheetName(name)
    const existingSheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    if((!reset) && existingSheet) return existingSheet;
    const renewSheet = existingSheet?.clear() ?? SpreadsheetApp.getActive().insertSheet(sheetName);
    renewSheet.appendRow(`担当者名：\t担当A\t\t\t▼顧客登録日`.split(`\t`));
    return renewSheet;
}
const q370876_isRequiredFulfilled = (value) => requiredIndexes.every(e=>value[e] !== ``);
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);}});
}

Comments

[astroryyy]

詳細なコメントを頂き、誠にありがとうございます！
この方法で今試しているのですが、スムーズにいきそうです。

2点ほど確認したく、もしよろしければご回答をお願いいたします...！

①今回の「検索用シート」を、別のCRMからimportrangeしたデータを活用する形で運用したく考えております。（つまり、直接「検索用シート」を編集するのではなく、その参照元を編集します。）トリガーでOnEditなどを選択すると、下記のように参照先そのものは変わらないため上手く編集機能が働きません。このような場合の対処方法はありますでしょうか？
https://qiita.com/sakaimo/items/cc2056d2c2ef108c05c9

補足：同様の質問があったものの、上手く反映させることができませんでした
https://teratail.com/questions/127009

②s.getLastRow();の部分について、事前にF列以降に情報が記載されている場合があります。A:E列のみにおける最終行を選択することは可能でしょうか？

お手数おかけしますが、ご回答よろしくお願いいたします。

[astroryyy]

【追記】
上記の質問に対し、papinianus様からダイレクトにコメントを頂きました。
下記のご質問を頂いたため、今後の知識の共有のために私の回答も含めて記載いたします。

=====
＜papinianus様からのご質問＞
2 点確認させていただきたいです。
- CRM からとありますが IMPORTRANGE はシートからの認識です。IMPORTXML など別の関数なのか、一旦別のスプレッドシートに CSV 取り込み済と考えて良いのかをご共有いただけますか。また後者の場合取り込みのときにしか更新は起こらないと考えられますが日次など取り込みのタイミングが予め予想できるか、随時行われるかご共有いただけますでしょうか。
- F 列から右の書き込みは E 列までの最下端より下にある、とのことですが、検索シート（もしくはその転写元）に新たな顧客の追加があって、蓄積シートに転記をするとき、その転記は、F 列以降の最下端の次に挿入するのでしょうか、もしくは E 列までの最下端の次に入れるのでしょうかご教示ください。さらに後者の場合、いわゆる行挿入をして A-E を書き込むのか、単に A-E にだけ書き込むのかいずれか、ご教示ください。（こちらは、仮に E5 が E の末尾で F6 に書き込みがあるときに、行挿入して F6 は F7 にずらして、A6:E6 に入れるのか、F6 をずらさずに、A6:E6 に貼り付けを行い F6 のメモが今回挿入する顧客のメモであるかのような見かけになるようにするのか、という意味です）

=====
＜私からの回答＞
わざわざご返信いただきありがとうございます！
①CRMについて
こちらは、CRMから随時出力したシート（これが担当者変更を行うシートになります）をquery+importrangeで引っ張ってきたものが「検索用シート」になる、という認識です。よって更新は日時が決まっているものではなく、随時行われる認識です。
②lasteditrowについて
転記の際はA-E列の最終行に入力します。しかし、担当者が多い環境下において、稀に「E列が空白なのにF列以降に誤って記載してしまう・メモ書きなどを残してしまう」可能性があり、未然に防ぎたいという意図です。
>E5 が E の末尾で F6 に書き込みがあるとき
こちらは、実装可能でしたら「F6をF7にずらす」という形式を取りたいですが、これによってスクリプトが重くなるなどデメリットが大きいようでしたら、行挿入しない方法で問題ありません。
リクエストが多く申し訳ありませんが、ご確認おねがいいたします。

[papinianus]

続きは Zenn で。
https://zenn.dev/ulpianus/articles/2378c89b68fab0

Answer 2

注）下記はあくまで、質問文の「補足」以降に記載されているシート上のデータの動作を実現する手順です。

---

検索用シートのシート名が「検索用」という名前であると仮定します。

各蓄積シートについて、2行目以降（A2～E1000）のセルはあらかじめデータをクリアしておきます。
次に、各蓄積シートのB1セルに、対応する担当者名（例：「担当A」）を入力しておきます。
そして、各蓄積シートのA2セルに、以下の式を入力します。

=QUERY('検索用'!A:E,"WHERE A='" & B1 & "' ORDER BY E ASC",0)

これにより、各蓄積シートには、
検索用シートのB1セルに指定した担当者名が担当する者のデータが、担当変更日の昇順に表示されます。

検索用シートの担当者および担当変更日を編集する都度、自動的に抽出及び並び替えられて表示されます。