【Google App Script】列のセルに入力、および更新がされたら特定の人にメールで通知をしたい

Question

A列の名前の人からの問い合わせ内容を、B列の問い合わせ内容が更新されたら、D列の担当者にメールで通知したい。

###　A列の名前を入力すると、VLOOKUPでD列に担当者名が入るようになっています。B列の問い合わせ内容を入力したら、D列の担当者にメールで通知したいです。

|A|B|C|D|E|F|G|H|
|:--|:--:|--:|
名前|問い合わせ内容|日付|担当者|-|-|-|-|
赤坂|XXXXX|-|A|-|-|-|-|田中|C|
青山|XXXXX|-|B|-|-|-|-|赤坂|A|
赤坂|XXXXX|-|A|-|-|-|-|青山|B|

I	J	-
名前	担当者	-
赤坂	A	-
青山	B	-

現在のコード

function sendNotification() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("QA");
  const cell = ss.getActiveCell().getA1Notation();
  const columnNameInAlphabet = cell.replace(/\d+/,'');
  
  const recipients = { 
    '赤坂': '[e-mail]',
    '青山': '[e-mail]'
  };
  
//名前を取得
  const person = sheet.getRange('I'+ sheet.getActiveCell().getRowIndex()).getValue();

//メールの件名と本文
  const subject = '【test】'+ss.getName();
  const body = person + 'さんから問い合わせがありました。';

//更新された行が通知対象の場合メール送信
  if (person in recipients == true){
    MailApp.sendEmail(recipients[person], subject, body);
  }  
};

問題点　B列を入力または更新した際に通知を送りたいのですが、他のセルが変更されてもメールが送られてくる。また、赤坂さんが2回目の質問をした際(表の4行目)は通知が届かない。

行き詰ってしまったので、どこが原因かお分かりになる方、ご教示いただきたくお願いいたします。

Answer 1

　B列を入力または更新した際に通知を送りたいのですが、他のセルが変更されてもメールが送られてくる。

この原因は、編集された列を限定せずにsendNotification()関数の処理を全部実行してしまっているからです。
「B列以外が編集されたとき」は何もせずreturnする必要があります。

getActiveColumn()を使うことも可能ですが、入力直後にアクティブセルが移動されたような場合、不具合が生じる可能性があります。

トリガーで実行する関数（sendNotification()関数）に引数を設定することにより、編集されたセルに関するデータを取得できるので、これを利用した方がよいでしょう。

sendNotification(e) {
  const col = e.range.columnStart;  // 更新された列の取得
  // 編集された列がB列以外の場合はreturn。
  if (col != 2) return;

　

赤坂さんが2回目の質問をした際(表の4行目)は通知が届かない。

これは、

  const person = sheet.getRange('I'+ sheet.getActiveCell().getRowIndex()).getValue();

で「A列」ではなく「I列」の該当行からpersonを取得していることが原因です。

たとえば4行目（B4）セルが編集されたとき、getActiveCell().getRowIndex()は「4」となり、
personには「I4」セルの値が入ります。
しかし「I4」セルは空白のため、person in recipients　はfalseとなり、メールが送信されません。

ここは「A4」セルを取得するべきだと思われます。
（ここも前述と同様にgetActiveCell()ではなく、e.range.rowStartを使って、編集された行番号を取得しています）

//名前を取得
  const person = sheet.getRange('A'+ e.range.rowStart).getValue();

Comments

[Yuppon]

qnoir様

丁寧に教えてくださりありがとうございます。
分かりやすく、理解できました。

記載してくださった内容を反映してみたのですが、
TypeError: Cannot read property 'range' of undefined
というエラーが出てしまいます。

トリガーは、スプレッドシートから、変更時になっています。

いろいろ調べてるのですが、解決できていないのでもし対処法をご存じでしたらご教示いただければ幸いです。

[退会済みユーザー]

sendNotification関数を実行させるトリガーの設定は、「変更時」ではなく、「編集時」にしてください。
（「変更時」にしていると、うまく動きません）
あと、トリガー経由ではなく、エディタから直接sendNotification関数を実行するだけだとうまく動かず、上記のエラーが出ます。
スプレッドシートの、青山さんか赤坂さんの行のＢ列のセルを編集（入力）すると、トリガー経由で正常に実行されるはずです。
（もしテストのためメールを実際に送信したくない等であれば、 テスト時はMailApp.sendEmailの行はLogger.logに変えてください）