Googleフォームから特定の回答があった場合のみ指定のアドレスに通知したい

Question

前提

Googleフォームでアンケートの回答をお願いしており、
回答があった際は連携するスプレッドシートに回答内容が自動記載されるような仕組みを取っております。
＊スプレッドシートにはシートが３つあり
①Googleフォームの回答が自動記載されるシート
②①のシートから「参加者」のみを抽出したシート
③①のシートから「参加者以外」を抽出したシート

となっております。

実現したいこと

Googleフォームの回答が自動記載されるシート①のＣ列に「参加者」と記載があったら、自動で指定したアドレスにメールで通知できるようにしたい。（シートも①を指定したいが、うまく行きませんでした）

Google Apps Scriptでの記述方法を教えていただけないでしょうか。

発生している問題・エラーメッセージ

スプレッドシートのシート①に回答が自動記載された場合、メールを飛ばすことができない。
（直接手動で入力した際にはメールが送信される）

function notification(){

const recipient = '[e-mail]'; //通知を飛ばす先のgメールアドレス
const subject = '【Googleフォーム新規回答あり】';//通知タイトル
const body = 'https://docs.google.com/spreadsheets/d/sample' ;//確認するシートのURL

var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
var myCell = mySheet.getActiveCell(); //アクティブセルを取得

if(myCell.getColumn()==3 && myCell.getValue()=="参加者"){ //C列に参加者と入力があったか判定
GmailApp.sendEmail(recipient, subject, body);//通知メールを送信

}
}

上記コードのＣ列に参加者と入力があったか判定するコードに、
特定のシート（シート①）を指定しようとしましたが、（ReferenceError: ss is not defined）というエラーが表示されてしまいました。

トリガーの設定は
実行する関数を選択：notification
イベントのソースを選択：スプレッドシートから
イベントの種類を選択：フォーム送信時
今すぐ通知を受け取る

にしております。

試したこと

Googleフォームに回答があった場合、スプレッドシートを通さずに通知メールを送信するスクリプトの設定も行ってみましたが、（TypeError: Cannot read property 'namedValues' of undefined）というエラーが表示され、うまく行きませんでした。

今回初めてGoogle Apps Scriptを利用しており、超初心者のため、Googleフォームとスプレッドシート、どちらでどのような設定を行えば良いのか分かりません。
どちらの方法でも構いませんので、「参加者」からの回答があった場合に指定のメールアドレス宛に通知をする方法をご教授いただけないでしょうか。
どうぞよろしくお願い致します。

Answer 1

元のコードの
「(myCell.getColumn()==3　～～」
から察するに、アクティブセルが3列目に該当することを期待しているようですが、
（シートに手入力するのではなく）フォーム回答時に自動入力される場合、
この方法は使えません。

もし似たような考え方で実装したいなら
・ getLastRow で入力のあった行を取得し、その3列目が「参加者」ならばメール送信
とすればよいでしょう。(下記）

function notification() {
  const recipient = '[e-mail]'; //通知を飛ばす先のgメールアドレス
  const subject = '【Googleフォーム新規回答あり】';//通知タイトル
  const body = 'https://docs.google.com/spreadsheets/d/sample';//確認するシートのURL

  var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得

  var lastRow = mySheet.getLastRow(); // 入力のあった行を取得
  var myCell = mySheet.getRange(lastRow, 3).getValue(); //C列（3列目）の値を取得
  if (myCell === "参加者") { //C列に参加者と入力があったか判定
    GmailApp.sendEmail(recipient, subject, body);//通知メールを送信
 }
}

---

・別解として、フォームから流れてくるデータそのものを捕まえるやり方もあります。

トリガーに設定した関数に引数をつけると、その引数から回答データにアクセスできます。

たとえば、3列目に該当するフォームの質問が「種類を入力してください」という質問である場合

e.namedValues['種類を入力してください'][0]

とすることで、その質問に対する回答を取得することができます。

// 関数の括弧の中に引数 「e」を追加すること
function notification(e) { 
  const recipient = '[e-mail]'; //通知を飛ばす先のgメールアドレス
  const subject = '【Googleフォーム新規回答あり】';//通知タイトル
  const body = 'https://docs.google.com/spreadsheets/d/sample';//確認するシートのURL

  //「種類を入力してください」という質問に対する回答が「参加者」であるかどうかを判定
  if (e.namedValues['種類を入力してください'][0] === "参加者") {
    GmailApp.sendEmail(recipient, subject, body);//通知メールを送信
 }
}

Comments

[Toyo32]

qnoir　様

ご教授いただきまして、ありがとうございます。
１つ目に教えていただいたソースコードに修正したところ、試運転用のスプレッドシートでメールが飛ぶことを確認することができました。

また、２つ目のソースコードを試してみましたが

TypeError: Cannot read property 'namedValues' of undefined

上記エラーが表示されてしまいました。
もし、お手数でなければ後学のためにこちらのエラーについてもご教授いただけないでしょうか？

[退会済みユーザー]

2番目のコードでそのエラーが発生する可能性はいくつかあります。
・notification()のカッコ内に引数「e」は書いていますか？
・notification関数を、エディタから直接実行していませんか？2番目のコードをエディタから直接実行しても正常に動作しません。
トリガーを適切に設定したうえでフォームから回答しないと正常に動作しません。

・「notification」という同じ名前の関数が2つ以上プロジェクトエディタ内に存在してしまっていませんか？たとえコード.gsと無題.gsというように異なるgsファイルに書き分けていたとしても、また引数が異なっていたとしても、同じ名前の関数が存在すればエラーになることがあります。
正常に動作させるためには、プロジェクト内で同じ名前の関数は1つしか許容されません。

・そのnotification 関数に対して、フォーム回答時に実行されるよう、正しくトリガーが設定されていますか？
もし不安ならば、念のため一旦今設定されているトリガーを削除してからもう一度同じようにトリガー関数を設定することを推奨します。

[Toyo32]

qnoir　様

ご指摘の通り、エディタから直接実行しておりました。
トリガーの設定をし直し、フォームから回答をしてみたところメールが飛ぶことを確認できました。
ありがとうございました。

また１つ質問なのですが、例えば回答が「参加者」の場合はＡのアドレス、「未参加者」の場合はＢのアドレスと別々のアドレスに通知が行くように指定したい場合は、どのように設定したらいいのでしょうか？
同じソースコードで異なるgsファイルにかき分けようかと思っていたのですが、同じ関数で何個も設定してはいけないようなので、別の良い方法があればご教授いただけないでしょうか？

[退会済みユーザー]

回答内容によって、if文で処理を分け、メール通知先を変えます。

let addressA = '[e-mail]';
let addressB = '[e-mail]';
if (e.namedValues['種類を入力してください'][0] === "参加者") {
GmailApp.sendEmail(addressA, subject, body);//A宛に通知メールを送信
} else if (e.namedValues['種類を入力してください'][0] === "未参加者") {
GmailApp.sendEmail(addressB, subject, body);//B宛に通知メールを送信
}

[Toyo32]

qnoir　様

ご親切に教えていただき、ありがとうございます。

本当に基本的な質問で申し訳ないのですが、その場合最初のnotification(e) で通知を飛ばすGmailアドレスを指定してしまっていますが、そこの部分は書き換えなくても問題ないのでしょうか？

また、メールの送信先が増えた場合（参加者＝Ａ、未参加者＝Ｂ、閲覧のみ＝Ｃ…）は続けてelse ifで宛先を追加しても大丈夫でしょうか？

[退会済みユーザー]

>　その場合最初のnotification(e) で通知を飛ばすGmailアドレスを指定してしまっていますが、そこの部分は書き換えなくても問題ないのでしょうか
→問題無いと思います。

>　また、メールの送信先が増えた場合（参加者＝Ａ、未参加者＝Ｂ、閲覧のみ＝Ｃ…）は続けてelse ifで宛先を追加しても大丈夫でしょうか

→大丈夫だと思います。

なお、いずれも具体的なソースコードやフォームの質問内容やスプレッドシートの内容がわからない状態ですので、確定的にOKと申し上げることはできません。プログラムというものはちょっと変えただけでうまく動かなくなるということは多々あることです。
同じコードで試せないと確定的に大丈夫ということはできません。
もしうまく行かないようであれば、実行したコード・フォームの画面キャプチャ、スプレッドシートの画面キャプチャなど全部の情報をそのまま開示したうえで、改めて質問してください。

[Toyo32]

qnoir　様

毎回ご親切にご回答いただいきまして、ありがとうございます。
前提条件の「参加者」「未参加者」ではなく、参加者の所属学部によってメールの送信先を変更したく、
qnoir様からの返信をいただく前にスプレッドシートからのスクリプト設定の方を採用し、ソースコードを書き直してみたのですが…
function sendEmail (){
var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
var lastRow = mySheet.getLastRow(); // 入力のあった行を取得
var myCell = mySheet.getRange(lastRow, 3).getValue(); //C列（3列目）の値を取得

let addressA = '[e-mail]'; //経済学部宛に通知メールを送信
let addressB = '[e-mail]'; //経営学部宛に通知メールを送信
let addressC = '[e-mail]'; //法学部宛に通知メールを送信
let addressD = '[e-mail]'; //人文学部宛に通知メールを送信
let addressE = '[e-mail]'; //工学部宛に通知メールを送信

if (myCell['所属学部'] === "経営学部") {
GmailApp.sendEmail(addressA, subject, body);//A宛に通知メールを送信
} else if (myCell['所属学部'] === "経済学部") {
　　 GmailApp.sendEmail(addressB, subject, body);//B宛に通知メールを送信
} else if (myCell['所属学部'] === "法学部") {
　　 GmailApp.sendEmail(addressC, subject, body);//C宛に通知メールを送信
} else if (myCell['所属学部'] === "人文学部") {
　　 GmailApp.sendEmail(addressD, subject, body);//D宛に通知メールを送信
} else if (myCell['所属学部'] === "工学部") {
　　 GmailApp.sendEmail(addressE, subject, body);//E宛に通知メールを送信
}
}

上記設定ですと、『スクリプト関数が見つかりません: notification』というエラーが送信されてきました。

notificationのトリガーはスプレッドシートからフォーム送信時にと設定しました。

本来は、きちんと勉強した上でうまく行かない個所を伺うべきですが、フォーム回答開始が今月中旬と差し迫っており、qnoir様に全面的にお助けいただいている状況で申し訳ありません。
どうぞよろしくお願い致します。

[退会済みユーザー]

関数名が変わってしまっているのでは？
実際にコードを試せていないのでまだ不具合あるかもしれませんが、取り急ぎ
先頭の
function sendEmail
を　
function notification
に変えてみてください。
あと「myCell[所属学部]===」　の部分はカッコ部分を全部なくして、「myCell ===」に修正してください。

[Toyo32]

qnoir　様

notification関数はうまく動いているので（１か所にメールを送ることは成功しています）、そこに別のコードを上書きしない方が良いかと思い（もし違ったらすみません）同じような関数を探して設定してみました。

関数をfunction notificationに書き換え、以下のソースコードに変更しました。

function notification() {
var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
var lastRow = mySheet.getLastRow(); // 入力のあった行を取得
var myCell = mySheet.getRange(lastRow, 3).getValue(); //C列（3列目）の値を取得

let addressA = '[e-mail]'; //経済学部宛に通知メールを送信
let addressB = '[e-mail]'; //経営学部宛に通知メールを送信
let addressC = '[e-mail]'; //法学部宛に通知メールを送信
let addressD = '[e-mail]'; //人文学部宛に通知メールを送信
let addressE = '[e-mail]'; //工学部宛に通知メールを送信

if (myCell === "経済学部") {
GmailApp.sendEmail(addressA, subject, body);//A宛に通知メールを送信
} else if (myCell === "経営学部") {
　　 GmailApp.sendEmail(addressB, subject, body);//B宛に通知メールを送信
} else if (myCell === "法学部") {
　　 GmailApp.sendEmail(addressC, subject, body);//C宛に通知メールを送信
} else if (myCell === "人文学部") {
　　 GmailApp.sendEmail(addressD, subject, body);//D宛に通知メールを送信
} else if (myCell === "工学部") {
　　 GmailApp.sendEmail(addressE, subject, body);//E宛に通知メールを送信
}
}

経済学部のアドレスを自分のアドレスに変更し、フォームの回答をしてみましたが、
「スクリプト 無題のプロジェクト を正常に完了できませんでした。失敗のまとめを下記に示します。」というメッセージが送信されてきました。
エラーの内容は…
関数：notification
エラーメッセージ：ReferenceError: subject is not defined
トリガー：formSubmit

また、設定しているトリガーは
関数：notification
イベントのソース：スプレッドシートから
イベントの種類：フォーム送信時

に設定しております。

[退会済みユーザー]

最初の方に
const subject = '【Googleフォーム新規回答あり】';//通知タイトル
const body = 'https://docs.google.com/spreadsheets/d/sample';//確認するシートのURL

の二行を追加して下さい。
（元々の質問のコードには記載されていたはずです）

[Toyo32]

qnoir　様

すみません、きちんとソースコードをコピーできておりませんでした。
2行のコードを入力しましたところ、エラーの表示もなくメールを送信することが出来ました。

qnoir様にはこの度は大変お世話になり、感謝しております。
プログラミングは全くの初心者の私に懇切丁寧に教えてくださり、本当にありがとうございました。
この後は、きちんと勉強してもっといろんなことをできるようになりたいと思います。

[退会済みユーザー]

解決してよかったです。