質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

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

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 124

score 0

前提・実現したいこと

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

該当のソースコード

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • qnoir

    2021/11/25 20:49

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

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

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

    キャンセル

回答 2

+2

モデル 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);}});
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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


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

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

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


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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る