Googleフォームから得た回答で、メッセージありのものについてのみ、メール通知をするという仕組みを作りたい。

前提・実現したいこと

はじめまして。
Googleフォームとスプレッドシートの連携に関する質問です。

Googleフォームから得た回答で、メッセージありのものについてのみ、
メール通知をするという仕組みを作りたいのです。
つまり、スプレッドシートの特定の列に変更があった場合、
その行のデータをメール通知するということです。

具体的に書いたほうがわかりやすいかもしれないので書くと、
GoogleフォームをWEB拍手みたいに使いたいと思っています。

試したこと

フォームから届いた新しいデータは、スプレッドシートの一番下に記載されていきますが、
新しいデータが上に来たほうが勝手がよいと思い、
タイムスタンプを利用してデータを並び替えるスクリプトはうまくできました。
これは、スプレッドシートのスクリプトエディタからの操作です。

そこまではよかったのですが、メール通知のスクリプトの際、
１行目には項目名が入っているので、その行の下の行のデータをメール通知したいのに、
１行目のヘッダ情報？しか取ることができません。
ちなみにこちらは、Googleフォームのスクリプトエディタからの操作です。（スクリプト１）

（試したところ、スプレッドシートとGoogleフォームでは、
同じ「フォームの送信」というトリガーを使った場合、
スプレッドシートのスクリプトが優先して動くみたいです。）

該当のソースコード（スクリプト１）

function sendNotification() {
  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var url = "＊＊＊";
  var sheet = SpreadsheetApp.openByUrl(url);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = sheet.getActiveCell().getA1Notation();
  var cellD = ''
  var val = sheet.getRange('B'+ sheet.getActiveCell().getRowIndex()).getValue();
  var val2 = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue();
  var val3 = sheet.getRange('A'+ sheet.getActiveCell().getRowIndex()).getValue();
  if(cell.indexOf('D')!=-1){ // = if you edit data in col D
cellD = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
// add more condition if necessary and/or send your mail (cellD contains the value in column D of the active row
}
  var dAddress = "＊＊＊";
  var subject = "＊＊＊";
  var content = "名前："+val+"\n";
  content += Utilities.formatDate(val3,'JST','yyyy年M月d日 H時m分')+"\n";
  content += "**********************\n"+val2;
MailApp.sendEmail(dAddress,subject,content);
}

仕方ないので、先に並び替えをすることを中止して、
最新のデータが一番下に来る状態で、その一番下のデータをメール通知する、
ということには成功しました。（スクリプト２）

該当のソースコード（スクリプト２）

function sendNotification() {
  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var url = "＊＊＊";
  var sheet = SpreadsheetApp.openByUrl(url);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = sheet.getActiveCell().getA1Notation();
  var cellD = ''
  var val = sheet.getRange('B'+ sheet.getLastRow()).getValue();
  var val2 = sheet.getRange('D'+ sheet.getLastRow()).getValue();
  var val3 = sheet.getRange('A'+ sheet.getLastRow()).getValue();
// もし列Dの値に異動があれば
  if(cell.indexOf('D')!=-1){ // = if you edit data in col D
cellD = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
// add more condition if necessary and/or send your mail (cellG contains the value in column G of the active row
}
  var dAddress = "＊＊＊";
  var subject = "＊＊＊";
  var content = "名前："+val+"\n";
  content += Utilities.formatDate(val3,'JST','yyyy年M月d日 H時m分')+"\n";
  content += "**********************\n"+val2;
MailApp.sendEmail(dAddress,subject,content);
}

なので、データの並び替えは、メール通知の終了後、
スプレッドシートの起動をトリガーにしてもいいかなとも思いましたが
（この方法についても成功しました）、

でも正しい方法さえわかれば、「フォームの送信」というトリガーひとつで、
並び替えからのメール通知も可能なのではないかと思うのです。
私が素人だからそう思うのかはわかりませんが、みなさまのお知恵をお貸しいただけると、幸いです。

当方、先にも述べましたが、ずぶの素人なので、的外れなことを書いていたらすみません。
多少、大目に見ていただけると、ありがたいです。

追記

関連の質問を投稿しました。もしよかったら見てください。
https://teratail.com/questions/116569

#～解決後のまとめ～
stshishoさんの回答を参考に解決しましたので、まとめておきます。

完成形です。
あ、でもなにか思うところがあれば、コメントしていただけるとうれしいです。

javaScript
1function sendNotification() {
2  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
3  var url = "＊＊＊";
4  var sheet = SpreadsheetApp.openByUrl(url);
5  var ss = SpreadsheetApp.getActiveSpreadsheet();
6  var val = sheet.getRange('B'+ sheet.getLastRow()).getValue();
7  var val2 = sheet.getRange('D'+ sheet.getLastRow()).getValue();
8  var val3 = sheet.getRange('A'+ sheet.getLastRow()).getValue();
9  // もし列Dの値に異動があれば
10  if(val2!=''){ // = if you edit data in col D
11  var dAddress = "＊＊＊";
12  var subject = "＊＊＊";
13  var content = "名前："+val+"\n";
14  content += Utilities.formatDate(val3,'JST','yyyy年M月d日 H時m分')+"\n";
15  content += "**********************\n"+val2;
16MailApp.sendEmail(dAddress,subject,content);
17   }
18
19  var target_col = 1;
20  var asc = false;
21  var start_row = 2;
22  var start_col = 1;
23  
24  var sh = SpreadsheetApp.getActiveSheet();
25  var last_col = sh.getLastColumn();
26  var last_row = sh.getLastRow();
27  var num_rows = last_row - (start_row - 1);
28  var num_cols = last_col - (start_col - 1);
29  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
30  range.sort([{
31    column: target_col,
32    ascending: asc
33  }]);
34}

参考記事（2018/03/27 追記）

・https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10173170780
こちらのベストアンサーの方の書かれたスクリプトをベースにしました。
・https://tonari-it.com/gas-moment-js-format/
日付と時刻の書式フォーマットに関するスクリプトをお借りしました。
・http://www.pre-practice.net/2016/06/blog-post_22.html
データのソートに関するスクリプトをお借りしました。
あと、「getLastRow()」で一番下の行を取得できることを知りました。
・http://emplos.jp/news/3336.html
「\n」と書くと文章が改行されることを知りました。
変数内で「+=」と書くと、文章を追加できることを知りました。これを使うと見やすくなる。
・https://teratail.com/questions/116487
当記事です。特定のセルの値を走査する方法を教えていただきました。

本題から若干それますが、
・https://code.i-harness.com/ja/q/ea05cb
ここから「function sendNotification()」という関数名をいただいたみたいです。
・https://qiita.com/nurburg/items/744ec53477f4ae328555
URLを指定して別のスプレッドシートを開く方法はこちらの記事からいただいたみたいですが、
そんなことしなくても、「getActiveSheet()」でいいと、いまとなってはわかるのです…。

以上、おそらくこのあたりの記事を参考にしたのだと思われます。

行動規範の内容に同意します

回答2件

ベストアンサー

送信直後でソート完了後、特定の列（D列？）に値があるかどうか、ということでしたらもっとシンプルに考えれば良いと思います。
つまり、２行目のデータについて、セルD2の値を走査すれば事足りるのではないでしょうか？
以下、上記前提の修正です。

javascript
1function sendNotification() {
2  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
3  var url = "＊＊＊";
4  var ss = SpreadsheetApp.openByUrl(url);
5  var sheet = SpreadsheetApp.getActiveSheet();
6  var val = sheet.getRange('A2:D2').getValues()[0];//２行目のA～D列の値
7  // もし列Dの値に異動があれば
8  if(val[3]!=''){
9    var dAddress = "＊＊＊";
10    var subject = "＊＊＊";
11    var content = "名前："+val[1]+"\n";
12    content += Utilities.formatDate(val[0],'JST','yyyy年M月d日 H時m分')+"\n";
13    content += "**********************\n"+val[3];
14    MailApp.sendEmail(dAddress,subject,content);
15  }
16}

動作確認はしておりませんので、参考までに。

投稿2018/03/07 14:44

退会済みユーザー

総合スコア0

koromo_t

2018/03/07 17:57 編集

お返事、ありがとうございます！！「特定の列に値があるかどうか」、まさにこれが、私がやりたかったことでした！「!=’’」と書けばよかったのですね！これはおそらく、基礎中の基礎の知識なのではないかと思います、が、私は本気でつまづいていたので、教えていただいて、本当に助かりました。やりたいと思ったことができて、感動しております…！そして、少々話はそれるかもしれませんが、この質問を投稿したあと、ふと閃きまして、「メール通知」と「並び替え」のスクリプトを、２つに独立して書いているから、「今回はこっちが先に発動したけど、今度はこっちが先に発動したわ」みたいな、不安定さがあるのではないか、２つのスクリプトを、１つのスクリプトにまとめれば、読み込まれた順に発動させることが可能なのではないか、と。フォームから最下部に追加されたデータにメッセージがあればメール通知、なければ何もしない、それが終われば降順にソート、というスクリプトを、回答でいただいたスクリプトを参考にしつつ、書いてみました。このコメント欄ではマークダウン記法が利用できないみたいなので、自己解決の場合のみ記入してくださいと書いてありますが、そちらにスクリプトを書いておきます。これでうまくできました。

koromo_t

2018/03/07 17:59

派生的な質問をもうひとつ投稿したので、もしよかったら見ていただけるとうれしいです。 https://teratail.com/questions/116569 （こういう誘導はよくないのでしょうか…？）

行動規範の内容に同意します

#～解決後のまとめ～
stshishoさんの回答を参考に解決しましたので、まとめておきます。

完成形です。
あ、でもなにか思うところがあれば、コメントしていただけるとうれしいです。

javaScript
1function sendNotification() {
2  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
3  var url = "＊＊＊";
4  var sheet = SpreadsheetApp.openByUrl(url);
5  var ss = SpreadsheetApp.getActiveSpreadsheet();
6  var val = sheet.getRange('B'+ sheet.getLastRow()).getValue();
7  var val2 = sheet.getRange('D'+ sheet.getLastRow()).getValue();
8  var val3 = sheet.getRange('A'+ sheet.getLastRow()).getValue();
9  // もし列Dの値に異動があれば
10  if(val2!=''){ // = if you edit data in col D
11  var dAddress = "＊＊＊";
12  var subject = "＊＊＊";
13  var content = "名前："+val+"\n";
14  content += Utilities.formatDate(val3,'JST','yyyy年M月d日 H時m分')+"\n";
15  content += "**********************\n"+val2;
16MailApp.sendEmail(dAddress,subject,content);
17   }
18
19  var target_col = 1;
20  var asc = false;
21  var start_row = 2;
22  var start_col = 1;
23  
24  var sh = SpreadsheetApp.getActiveSheet();
25  var last_col = sh.getLastColumn();
26  var last_row = sh.getLastRow();
27  var num_rows = last_row - (start_row - 1);
28  var num_cols = last_col - (start_col - 1);
29  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
30  range.sort([{
31    column: target_col,
32    ascending: asc
33  }]);
34}