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

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

ただいまの
回答率

90.11%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,395

koromo_t

score 58

 前提・実現したいこと

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

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

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

 試したこと

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

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

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

 該当のソースコード(スクリプト1)

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);
}

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

 該当のソースコード(スクリプト2)

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さんの回答を参考に解決しましたので、まとめておきます。


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

function sendNotification() {
  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var url = "***";
  var sheet = SpreadsheetApp.openByUrl(url);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  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(val2!=''){ // = if you edit data in col D
  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);
   }

  var target_col = 1;
  var asc = false;
  var start_row = 2;
  var start_col = 1;

  var sh = SpreadsheetApp.getActiveSheet();
  var last_col = sh.getLastColumn();
  var last_row = sh.getLastRow();
  var num_rows = last_row - (start_row - 1);
  var num_cols = last_col - (start_col - 1);
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  range.sort([{
    column: target_col,
    ascending: asc
  }]);
}

 参考記事(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()」でいいと、いまとなってはわかるのです…。

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

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

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


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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/03/08 02:50 編集

    お返事、ありがとうございます!!
    「特定の列に値があるかどうか」、まさにこれが、私がやりたかったことでした!
    「!=’’」と書けばよかったのですね! これはおそらく、基礎中の基礎の知識なのではないかと思います、
    が、私は本気でつまづいていたので、教えていただいて、本当に助かりました。
    やりたいと思ったことができて、感動しております…!

    そして、少々話はそれるかもしれませんが、この質問を投稿したあと、ふと閃きまして、
    「メール通知」と「並び替え」のスクリプトを、2つに独立して書いているから、
    「今回はこっちが先に発動したけど、今度はこっちが先に発動したわ」みたいな、
    不安定さがあるのではないか、
    2つのスクリプトを、1つのスクリプトにまとめれば、
    読み込まれた順に発動させることが可能なのではないか、と。

    フォームから最下部に追加されたデータにメッセージがあればメール通知、なければ何もしない、
    それが終われば降順にソート、というスクリプトを、
    回答でいただいたスクリプトを参考にしつつ、書いてみました。
    このコメント欄ではマークダウン記法が利用できないみたいなので、
    自己解決の場合のみ記入してくださいと書いてありますが、そちらにスクリプトを書いておきます。
    これでうまくできました。

    キャンセル

  • 2018/03/08 02:59

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

    キャンセル

0

~解決後のまとめ~

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


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

function sendNotification() {
  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var url = "***";
  var sheet = SpreadsheetApp.openByUrl(url);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  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(val2!=''){ // = if you edit data in col D
  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);
   }

  var target_col = 1;
  var asc = false;
  var start_row = 2;
  var start_col = 1;

  var sh = SpreadsheetApp.getActiveSheet();
  var last_col = sh.getLastColumn();
  var last_row = sh.getLastRow();
  var num_rows = last_row - (start_row - 1);
  var num_cols = last_col - (start_col - 1);
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  range.sort([{
    column: target_col,
    ascending: asc
  }]);
}

 参考記事(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()」でいいと、いまとなってはわかるのです…。

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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