スプレッドシートでプルダウンで選択された人にメール通知をする

Question

実現したいこと

プルダウンメニューで項目を複数作成し、プルダウンメニュー内の選ばれた項目ごとに
それぞれの担当者にメールで通知される仕組みを作りたいです。

####イメージと仕組み

簡易的にAさん担当、Bさん担当と書いてますが
実際ここには、それぞれの人が担当する商品名が入ります。

商品の担当が違う人に変更になった際、
元の担当の人が、次に担当になる人をプルダウンメニューから選択します。
すると、次の担当にプルダウンから選ばれた人にメールで通知がいく仕組みです。

変更するたびに全員に通知メールがいくと不便なので
担当者にのみメールを送りたいです。

このような仕様にGASで対応することは可能なのでしょうか、、、？

#####現在のコード

参考にさせていただきましたが、そもそも仕組みがちがうので
応用できないのでしょうか。。
（参考：https://qiita.com/3mc/items/b78cbb4c2eebf06d26a0）

'use strict';
function sendNotification() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("シート1");
  
  const cell = ss.getActiveCell().getB1Notation();
  const columnNameInAlphabet = cell.replace(/\d+/,'');
  
  //通知対象者と対応するメール通知先を連想配列で格納
  const recipients = { 
    'A': '[e-mail]',
    'B': '[e-mail]',
    'C': '[e-mail]',
    'D': '[e-mail]'
  };
//更新行のC列の値を取得
  const person = sheet.getRange('C'+ sheet.getActiveCell().getRowIndex()).getValue();
  
  //メールの件名と本文を指定
  const subject = '【更新】'+ss.getName();
  const body = person + 'の行が更新されました。\n' + ss.getUrl() + '\n更新対象は' + columnNameInAlphabet +'列です。';
  //更新された行が通知対象の行を含む場合はメールを送る
  if (person in recipients == true){
    MailApp.sendEmail(recipients[person], subject, body);
  }  
};

Answer 1

まえおき

• 可能ですが、サンプルが悪いです。
• DをAにして、Aに通知したあと、AもしくはDがBに変更した場合、Bに通知するシナリオであってますか？

---

本題

イベントオブジェクトを正しく使えば容易です。
シート編集時のトリガーにはイベントオブジェクトが渡されその中に、oldValueとnewValueがありますので

function onEdit(e) {
  Logger.log(JSON.stringify(e);
  if(e.range.getSheet().getName() !== "") { return; } //特定のシート名でなければ中断
  SpreadsheetApp.getActiveSpreadsheet().toast('sheet');
  if(e.range.getColumn() !== 3 ) { return; } //C列でなければ中断
  SpreadsheetApp.getActiveSpreadsheet().toast('column');
  //通知対象者と対応するメール通知先を連想配列で格納
  const recipients = { 
    'A': '[e-mail]',
    'B': '[e-mail]',
    'C': '[e-mail]',
    'D': '[e-mail]'
  };
  //メールの件名と本文を指定
  const subject = '【更新】'+ e.source.getName();
  const body = e.oldValue + 'さんから' + e.value + 'さんに担当が変更されました。\n' + e.source.getUrl() + '\n更新対象は' + e.range.getRow() + '行C列です。';

  MailApp.sendEmail(recipients[e.value], subject, body);
}

Comments

[ran_net]

回答ありがとうございます。
サンプルが悪くてすみません。
・DをAにして、Aに通知したあと、AもしくはDがBに変更した場合、Bに通知するシナリオ
↑上記の解釈で間違いありません。

試してみます。ありがとうございます。

[papinianus]

サンプルというのは参考にしたサイトです。参考にする記事がみつかったと思ってもそこで止まらず、一応他にないかなーと探して比較してみてください

[papinianus]

結果として質問者様に失礼な言いかたとなってしまったことはお詫びします。

[ran_net]

そういうことでしたか。いえ、完全に私の解釈違いです、すみません。

上記のコードを試してみたのですが
if(e.range.getColumn() !=== 3 ) { return; } //C列でなければ中断
の部分が構文エラーが出てしまうのですがどうしてでしょうか・・・

[papinianus]

=が一つおおかったです。「!==」にしてください

[ran_net]

ありがとうございます！
その後、
TypeError: undefined からプロパティ「range」を読み取れません。（行 2、ファイル「コード」）
が出てしまいます・・・。
if(e.range.getSheet().getName() !== "") { return; } //特定のシート名でなければ中断
""にシート名を入れても関係ないですよね。。

[papinianus]

関数名変えてませんよね？
どのタイミングでエラーになりますか？

[ran_net]

関数名はそのままです。
他はメールアドレスの部分を自分のアドレスに変更しました。
スクリプトの画面で、関数onEditを選択し、実行ボタンを押すと
上記のエラーが出てしまいます。。。
また、トリガーはスプレッドシートから変更時に起動するように登録しました。

[papinianus]

スクリプトの画面では動きません。
イベントオブジェクトを使っているので、その名のとおり、イベントを起こす必要があります。つまり、シートのC列を変更する必要があります。
メールが送られたらやだなー、という場合にはBrowser.MsgboxやSpreadsheetApp.toastで通知するようにしてみてください。
"onEdit"ならトリガがあってもなくても動きます

[ran_net]

スプレッドシートシート上で、C列を変更することも試したのですがメールが一向に届きません・・。
メールを送るだけのスクリプトを試したらメールは受信されますので、メールアドレスには問題ないかと思います。

[papinianus]

途中途中でtoastするようにしました。
状況を見てみてください。
また、シート編集後に、スクリプトの画面に移動して、ログの下あたりにある「実行トランスクリプト」をみてください。どこで止まっているか分かります。

[ran_net]

実行トランスクリプトは以下のようになっていました。
[18-12-20 17:29:38:155 JST] Session.getActiveUser() [0 秒]
[18-12-20 17:29:38:155 JST] User.getEmail() [0 秒]
[18-12-20 17:29:38:156 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 17:29:38:165 JST] 実行を開始しています
[18-12-20 17:29:38:170 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 17:29:38:294 JST] Spreadsheet.toast([event fired]) [0.124 秒]
[18-12-20 17:29:38:301 JST] 実行に失敗: TypeError: undefined のメソッド「getSheet」を呼び出せません。（行 24、ファイル「コード」）（合計ランタイム 0.126 秒）

toastのシートの右下には「event fired」表示のみです。。。

[papinianus]

プルダウンをかえているのですよね？(コメントを削除したりしていませんよね、という意味です)
トリガを削除してみていただけませんか？同じエラーになりますでしょうか？

[papinianus]

newValueではなく、valueでしたが、こちらではプルダウンの変更でe.range.getSheet()が動作します(ただし、トリガがない状態です)
toastではなくログにかえてみたので、ログの中にrangeがあるか見ていただけますか(他にもauthmodeやvalueがあるかを見ていただきたいです)

[ran_net]

はい、プルダウンのみ変更しています。
トリガを削除して上記コードを実行させてみました。
実行トランスクリプトは以下です。
[18-12-20 21:38:31:704 JST] SpreadsheetApp.getActiveRange() [0 秒]
[18-12-20 21:38:31:704 JST] Range.getRow() [0 秒]
[18-12-20 21:38:31:704 JST] Range.getLastRow() [0 秒]
[18-12-20 21:38:31:705 JST] Range.getColumn() [0 秒]
[18-12-20 21:38:31:705 JST] Range.getLastColumn() [0 秒]
[18-12-20 21:38:31:705 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 21:38:31:707 JST] 実行を開始しています
[18-12-20 21:38:31:864 JST] Session.getActiveUser() [0.147 秒]
[18-12-20 21:38:31:864 JST] User.getEmail() [0 秒]
[18-12-20 21:38:31:866 JST] Logger.log([{"authMode":{},"range":{"columnStart":3,"rowStart":10,"rowEnd":10,"columnEnd":3},"source":{},"oldValue":"D","user":{"nickname":"メアド","email":"メアド@gmail.com"},"value":"A"}, []]) [0 秒]
[18-12-20 21:38:31:867 JST] Range.getSheet() [0 秒]
[18-12-20 21:38:31:924 JST] Sheet.getName() [0.057 秒]
[18-12-20 21:38:31:992 JST] 実行が無事終了しました（合計ランタイム 0.211 秒）
※↑はメールアドレス部分が表示されているので”メアド”表記に変更してます

実行は無事終了していますが、メールが届かないようです。。
ログの中には、range、authMode、valueは存在しております。

[papinianus]

何個か前の特定のシート名入れるやつやってますよね？
とりあえず中断の2行消してみてもらえませんか？

[ran_net]

すみません。下記が最新です。
中断の２行（if〜の行ですよね？）を消して、プルダウン部分のみを変更しました。
実行トランスクリプト
[18-12-20 22:27:32:941 JST] SpreadsheetApp.getActiveRange() [0 秒]
[18-12-20 22:27:32:941 JST] Range.getRow() [0 秒]
[18-12-20 22:27:32:941 JST] Range.getLastRow() [0 秒]
[18-12-20 22:27:32:942 JST] Range.getColumn() [0 秒]
[18-12-20 22:27:32:942 JST] Range.getLastColumn() [0 秒]
[18-12-20 22:27:32:942 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 22:27:32:943 JST] 実行を開始しています
[18-12-20 22:27:33:069 JST] Session.getActiveUser() [0.119 秒]
[18-12-20 22:27:33:069 JST] User.getEmail() [0 秒]
[18-12-20 22:27:33:070 JST] Logger.log([{"authMode":{},"range":{"columnStart":3,"rowStart":5,"rowEnd":5,"columnEnd":3},"source":{},"oldValue":"B","user":{"nickname":"メアド","email":"メアド@gmail.com"},"value":"A"}, []]) [0 秒]
[18-12-20 22:27:33:071 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 22:27:33:757 JST] Spreadsheet.toast([sheet]) [0.685 秒]
[18-12-20 22:27:33:758 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-12-20 22:27:34:222 JST] Spreadsheet.toast([column]) [0.463 秒]
[18-12-20 22:27:34:576 JST] Spreadsheet.getName() [0.354 秒]
[18-12-20 22:27:34:801 JST] Spreadsheet.getUrl() [0.223 秒]
[18-12-20 22:27:34:801 JST] Range.getRow() [0 秒]
[18-12-20 22:27:34:901 JST] 実行に失敗: MailApp.sendEmail を呼び出す権限がありません。必要な権限: https://www.googleapis.com/auth/script.send_mail（行 63、ファイル「コード」）（合計ランタイム 1.856 秒）

[papinianus]

そうでした。onEditはメール送れない罠がありました。これでメール送信までたどり着いている状態になったので、改めてトリガーを編集時でセットしてください。これで行けるはず

[ran_net]

ついに！！できました！！
凄すぎます。本当にありがとうございます。

GAS頑張ります。。

[papinianus]

ごめんなさい。仕様をうっかりしてました。onEditという名前はトリガ設定しなくても編集時に勝手に動くのですが、勝手に動くのでできることが制限されてて、メール送信ができないのでした。ふりまわしてすみません。

[ran_net]

なるほどです！onEditでも全てトリガなしで動く訳では無いのですね。
また勉強になりました。
理解力が乏しい私に付き合っていただき感謝しております。
ありがとうございました。

[ran_net]

何度もすみません。
昨晩に何回か試したところエラーもなくメールも届き問題なかったのですが、
正しく実行されたコードで、実際使用するスプレッドシートのスクリプト上で実行してみると
『undefined のメソッド「getRow」を呼び出せません。（行 63、ファイル「コード」）』
と実行トランスクリプトに出るようになりました。。

getRow()は行の番号の取得、、ということで
実際の使用する表では、B列やD列にもデータ値が埋まってる状態なので
それが原因かと思ったのですが、消しても変わらず・・

[papinianus]

トリガーは編集時ですね？
スクリプト上で実行とは？関数の実行でしょうか？

なぜランちゃんやめたのでしょうか？気もそぞろなんですが。

[ran_net]

あ！！すみません。。そうでした。
編集時と変更時を勘違いしていました！；；
お騒がせしてすみません。

自分の好きなキャラクターの画像を見つけたので変えてみました。
が、名前と画像が一致しないのでランちゃんに戻そうと思いますm(._.)m

[papinianus]

そうは言ったものの、らんちゃんはここのユーザ層とは合わないので、参考程度に。

あ、できてよかったです。