PR

【GAS】Googleフォームから自動で返信メール～合計金額など好きな値を追加できる自由度の高い返信メール

Google Workspace
2023.06.19

ここで紹介するGAS(diyReplay)とスプレッドシートの関数を使って、Googleフォームの回答以外の値を、返信メールの文面に簡単に表示できます。

例えば、回答項目を使って計算した合計金額などを、返信メールの文章に加えることができます。

スポンサーリンク

準備

テストのフォームとスプレッドシートを作りながら、作成したGAS(diyReply)の機能を説明します。

フォーム

次のようなフォームを作ります。

①返信メールの件名
②返信メールの本文
③返信メールの宛先

スプレッドシート

スプレッドシートにリンクします。

シートを追加して、返信メールに必要なシート(ここでは、シート1)を作ります。

  • 1行目(C1～E1)には、質問項目の①②③を入れます。
  • 2行目(B2～E2)には、フォームからの回答が入ります。
  • B3：OFF(半角)を入れるとメールを送りません。(文章作成時のテスト時に使えます。)
  • B4：返信メールの件名を入れます。ここでは、=C2を入れます。
  • B5：返信メールの本文を入れます。ここでは、=D2を入れます。
  • B6：返信メールの宛先を入れます。ここでは、=E2を入れます。
  • B7：必要なら、CC:を入れます。複数の場合は、aaa@gmail.com, bbb@gmail.comのように,で区切ってください。
  • B8：必要なら、BCC:を入れます。複数の場合は、aaa@gmail.com, bbb@gmail.comのように,で区切ってください。
  • B9：必要なら、送信元(自分)の表示名を入れます。
  • B10：必要なら、返信先アドレスを入れます。
  • B11(New!)：必要なら、送信元アドレスを入れます。
    送信元アドレスには、Googleアカウント(Gmailアドレス)のエイリアスをご利用ください。
    (※権限のないメールアドレスでは、送信できませんのでご注意ください。)
  • B12(New!)：ON(半角)を入れるとC12に回答編集用のURLを表示します。

GAS

GAS(diyReply)を登録します。スクリプトエディタを開きます。

次のGAS(diyReplay)をエディタへコピペします。

  1. function diyReply(SheetNo) {
  2.   // 引数SheetNo:設定用シートのタブの位置(一番左のシートが0、2番目が1、3番目が2…)
  3.   if(!(SheetNo > 1)) SheetNo = 1;
  4.   // アクティブフォーム
  5.   const form = FormApp.getActiveForm();
  6.   // 設定用シート(2枚目のシート)
  7.   const mailSheet = SpreadsheetApp.openById(form.getDestinationId()).getSheets()[SheetNo];
  8.   // 最新の回答を配列に取得
  9.   const latestItems = form.getResponses().pop();
  10.   const answerItems = latestItems.getItemResponses().map(item => item.getResponse());
  11.   answerItems.unshift(latestItems.getTimestamp()); // タイムスタンプを追加
  12.   // 回答を設定用シートにセット
  13.   mailSheet.getRange(2, 2, 1, answerItems.length).setValues([answerItems]); SpreadsheetApp.flush() // sync;sync;sync
  14.   // B12がONなら編集リンクを設定用シートにセット
  15.   mailSheet.getRange('C12').setValue(mailSheet.getRange('B12').getValue() === 'ON' ? latestItems.getEditResponseUrl() : ""); SpreadsheetApp.flush() // sync;sync;sync
  16.   // 設定用シートの項目(セル:B3-B11)を配列に取得
  17.   const mailTbl = mailSheet.getRange('B3:B11').getDisplayValues().flat();
  18.   // B11が空白ならアクティブユーザーのメールアドレスを使用
  19.   if(!mailTbl[8]) mailTbl[8] = Session.getActiveUser().getEmail();
  20.   // B3がOFF以外はメールを送信
  21.   if (mailTbl[0] !== 'OFF')
  22.     GmailApp.sendEmail(mailTbl[3], mailTbl[1], mailTbl[2], {from: mailTbl[8],cc: mailTbl[4],bcc: mailTbl[5],name: mailTbl[6],replyTo: mailTbl[7]});
  23. } // Ver2.2.0 for maintenance

↓　↓　コピペ　↓　↓　(保存を忘れずに！)

トリガー

トリガーを追加します。

イベントの種類の選択は、「フォーム送信時」を選びます。

※承認を求められたら、「Advanced」(あるいは、「詳細」)→「Go to ～～(Unsafe)」(あるいは、「～～に移動」)→「Allow」(あるいは、「許可」)

動作テスト

フォームを実行(プレビュー)して、回答、そして送信します。

結果、リンクしたシート(フォームの回答)

結果、シート1に次のように表示されています。

結果、宛先(TO:)へ送られてきた返信メールです。

成功しました。

このように、GAS(diyReply)はフォームからの回答をシート1にコピー、シート1の内容をメールするだけです。

つまり、思い通りのメールを送るには、スプレッドシートの関数を駆使して自分で作る必要があります。

そして、どのような返信メールも自由自在に作ることができます。

実践編

既存のフォームを修正変更し、改めて回答を募集する際は、過去のデータが混ざらないよう、リンクしたスプレッドシートとフォームの回答を全削除してください。(以下の①②の作業)

①回答シートの回答行を削除する。

②フォームの回答を削除する

応用例その8

フォームより入力された項目には価格があり、その合計金額をメールに表示したいとします。

注文商品(価格あり)の個数をプルダウンのメニューから選ぶフォームです。

返信メールのためのシート1を次のように作りました。
数式表示[Ctrl]+@しています。

シート1

テーブル(コピペ用)に表示します。

質問タイムスタンプ名前メールアドレス注文商品1(1,500円/個)注文商品2 (2,500円/個)
最新の回答
OFF：
メールを送らない
件名ご注文承りました
本文=C2&" 様、
ご注文をありがとうございます。
-------------------------------
注文商品： "&E1&"×"&E2&"個 "&F1&"×"&F2&"個
合計金額："&TEXT((1500*E2+2500*F2),"#,##0")&"円
-------------------------------
お支払方法は、～～～

またのご利用をお待ちしております。"
TO:=D2
CC:(省略可)
BCC:(省略可)
NAME:(省略可)〇〇商店
REPLY TO:(省略可)
シート1
表計算が得意な人には何でもない関数ですが、セル内に文章を書くのは不慣れな人が多いのではないでしょうか。
"文字列"(ダブルクォーテーションで挟まれた文字列)とセルの数値(C2など)を、&(半角)でつなぎながら文章を書くのは慣れないと少し難しいかもしれません。
コツは最初に"を付けてから、一気に文章を書きあげて最後に"で閉じます。
この時、改行([Ctrl]+[Enter]キー)も気にせずに使います。
それから必要な箇所を"&※※&"に置き換えていきます。(※※は、A1,、B2など)

B5セルに望みの文章が表示出来たら、それがそのままメールの文章になります。

実行(プレビュー)します。

シート1

成功です。

合計金額が表示された返信メールが送られてきました。

シート1の名前(NAME:)で記した○○商店も表示されています。

この例を参考に、ご自分の思い通りな返信メールをお作り下さい。

既存のフォームを修正変更し、改めて回答を募集する際は、過去のデータが混ざらないよう、リンクしたスプレッドシートとフォームの回答を全削除してください。(以下の①②の作業)

①回答シートの回答行を削除する。

②フォームの回答を削除する

応用例その8.5(クレジット決済を追加する)

お支払い方法に銀行振込だけでなくクレジット決済も対応した応用例です。
ご参考までにどうぞ。

Googleフォームから自動で返信メール～自由度の高いメールでクレジット決済に対応
Googleフォームから商品をいくつか選んで、その合計金額をクレジットで決済する。そのような難しい処理も、ここで紹介するGAS(diyReplay)とスプレッドシートの関数、そして決済型メールリンクを使うと簡単にできます。次の記事からの続き...
terihat.com
2024.02.01
クレジット決済のためにGASとは関係のない次の「メールリンク型決済」サービスを使います。
Square(スクエア)でメールでのクレジット決済が簡単・無料！～アカウント作成から使い方まで完全ガイド
Squareのメールリンクを利用したオンライン決済をご存知ですか？メール内のリンクから、手軽にクレジットカードで決済できます。このサービスを使うと、メールだけでなくSMS(ショートメール)、LINE、X(旧Twitter)、Facebook...
terihat.com
2024.01.30

応用例その9

【GAS】Googleフォームの返信メールを自由自在に作る
googleフォームからの返信メールの冒頭に、百人一首からランダムに選んだ和歌を自動で記載するちょっと洒落ている、しかし使いどころにちょっと困る機能をGASとスプレッドシートの関数で実現しました。次のように文章の冒頭に百人一首が書かれた返信...
terihat.com
2023.06.21

応用例その11(2つのGASを使って署名活動フォームを作る)

【GAS】Googleフォームに掲示板と返信メールを追加して署名活動フォームを作る
googleフォームに掲示板と返信メールの機能をGASとスプレッドシートの関数を使って、次のような署名活動フォームを作ってみました。署名者数の合計や達成状況などをフォームに掲示します。また、署名に対しての返信メールに達成状況などを掲載します...
terihat.com
2023.07.02

応用例の補足(フォームの選択肢に定員設定を追加する)

次のGAS(updateItems)を使って、フォームの選択肢に定員(人数制限)を付けられます。
今回のGAS(diyReply)と同時に使うことで、フォームの応用がさらに広がります。

【GAS】Googleフォームでラジオボタン・プルダウン・チェックボックスの選択肢に定員(人数制限)を設定
Googleフォームで、ラジオボタン、プルダウン、チェックボックスといった選択肢に対して、定員(人数制限)を設定できる機能があると便利です。例えば、「松の会」「竹の会」「梅の会」のような選択肢があり、それぞれに定員数が設定されている場合、定...
terihat.com
2023.05.29

複数のGASの同時使用の応用例が『応用例その11』です。
参考にしてください。

応用例の補足2(フォームの回答者数に定員設定を追加する)

次のmultiReplyは、今回のdiyReplyに"フォームの回答者数に定員設定"ができる機能を追加しました。
「質問の選択肢」毎の定員でなく、「回答者数」の定員でよいのなら、とても有用なスクリプトです。

【GAS】Googleフォームに定員(人数制限)と返信メール、掲示板などマルチ機能を追加する
ここで紹介するGAS(multiReply)を使うことで、Googleフォームに次の3つの機能を追加できます。定員(人数制限)フォームの申し込み(回答)が、定員(人数制限)になると回答受付を終了します。返信メール回答後に送る自動返信メールを...
terihat.com
2024.03.02

おわりに

GASを組むことで、フォームやスプレッドシートから得られる質問項目と回答項目を、メール文書に加えるのは簡単です。

しかし、それらにない計算値などをメールに加えるには、GASを特別に組み直さなければいけません。

フォームに対して1対1のGASになってしまい、汎用性が失われてしまします。

また、GASで数式を組んでテストをするのは、なかなかに手間がかかります。

そこで、この負担をスプレッドシートの関数側に持っていくために、GASの機能を削りに削ってできたのが今回のdiyReplyです。

GASでは困難な複雑な数式も、スプレッドシートの関数ならトライ＆エラーが簡単に行えるため短時間で完成できます。

diyReplyと組み合わせることで、今回の例では合計金額ですが、関数に詳しい方はいくらでも難しい数式で得た値を、メールに表示できます。

ぜひ、自由自在な返信メールをご活用ください。

それでは、また次の記事でお会いしましょう。

追記(2024.9.25)：

このGASを公開して1年以上が経ちますが、今でも日に20～30人の方がご覧になられるようです。
それに伴いこれまで、いくつかの問題点をご指摘いただきました。
特に、フォームとスプレッドシートの回答数が一致しない場合、混乱が生じていたというご指摘を何度か受け、今回の大幅なバージョンアップを行い以下の点を改善いたしました。

  • 従来はスプレッドシートから回答を取得していましたが、新しいバージョン(2.1.0)では、フォームに直接記録された回答を取得するように変更いたしました。
    これにより、確実に最後の回答を取得できるようになり、フォームとスプレッドシートの回答数が一致しない場合でも確実に、フォームからの送信者(回答者)の回答を使ったメールが送れます。

次の機能は、より柔軟な対応を可能にするため、追加いたしました。

  • 返信メールの送信者(From:)に、フォームのオーナーのメールアドレスのエイリアスを設定できるようにしました。
  • 回答者が自身の回答を編集するためのURLを表示できるようになりました。

もし使ってみて何か問題点がありましたら、コメントにてお知らせいただけると幸いです。

コメント

  1. えみ より:
    2024年7月31日 9:32 PM

    なるほど！
    勉強になります^ ^
    ありがとうございます。
    やってみます！
    また聞きたいことあればコメントするので、その時はご教授くださいm(_ _)m

  2. Kasuga Kasuga より:
    2024年7月31日 8:54 PM

    フォームの回答が13でしたら、次の送信で回答が書かれる行が14行目(タイトル行があるため+1です)になるはずです。確か…
    ここら辺を抑えておけば、うまく扱えると思いますよ。
    あっ、それとシートの回答行と回答行の間に空白行を入れないでくださいね。これは、私の例題の関数COUNTIFが空白でない行をカウントしているので、お気付けください。

  3. えみ より:
    2024年7月31日 8:09 PM

    返信ありがとうございます！

    では、フォームの回答が13ならば、シートの回答も13であれば問題はないということですか？

    いろいろテストしたりして、フォームの回答も消したり、シートもテストのものが間に挟まったりして、そこを削除して行を詰めて同じ13に合わせたら大丈夫ですか？

  4. Kasuga Kasuga より:
    2024年7月31日 7:50 PM

    当ブログをご覧いただきありがとうございます。
    あ～、これは、フォームとシートの回答数がズレているからだと思います。
    今は手元にフォームを実行する環境がないので、メニュー項目などを詳しく説明できないのですが．．．。
    まず、フォームとシートの同期はフォームからの一方向だと言うことを覚えておいてください。
    例えば、シートの回答を削除しても、フォームの回答は削除されないということです。
    フォームの回答を見て「3件の回答」となっていたとき、シートの回答数も3件なら問題ないのですが、シートの回答を削除したり弄ってしまってもフォームとは同期されませんので、ここは手動で合わせなければなりません。
    一番良いのは、フォームの回答を「︙」から「すべての回答を削除」でクリアして、シートの回答の行を削除して、最初から始めることでしょう。

  5. えみ より:
    2024年7月31日 12:57 PM

    このまんまコピペで使用していますが、質問フォームから送信すると、たった今送信した人ではなく、前回の最終行の人がそのままシート1に反映されてメールが行っちゃいます。
    （BCCに私のアドレスを入れたから判明しました）

    コードを開いて、『実行』を押すと最終行の人のが新たにシート1に反映されて返信メールできるようになりますが、毎回それだと、1人2回ずつメールが送らさる感じになっています。

    ①最初にフォームから送信した時は前回の人（仮Aさん）にメールがいく

    ②コードから実行と押せば今送信した人（Bさん）にメールがいく

    ③次にフォームから送信した人にはメールがいかず、前回送信した人（Bさん）にメールが行く

    どうしてかわかりますか？

  6. E.N より:
    2024年7月18日 5:14 PM

    CHAR(10)で改行できました！ありがとうございます！
    とても参考になりましたｍｍ

    Nice to haveですが、回答修正のリンクも入れられるともっと便利になりそうですね！
    また参考にさせていただきます！

  7. Kasuga Kasuga より:
    2024年7月18日 3:52 PM

    おめでとうございます。
    セルの中での改行は、[Ctrl]+[Enter]、あるいは[Alt]+[Enter]で改行できます。
    文章の改行したい位置で[Ctrl]+[Enter]キーを入力して、そのまま文章を続けてください。
    私の例文の改行箇所には、[Ctrl]+[Enter]が入っているのですよ。
    ちなみに関数CHARを使うと、次のようになります。
    =”◯△◇様、”&CHAR(10)&”お世話になります。”

  8. E.N より:
    2024年7月18日 12:52 PM

    自分のアカウントで試したら飛びました！！ありがとうございます！！

    もう１点、分かればでよいのですが、
    現状メールの本文が改行されない状態で送られてしまっていまして、改行コードなどありましたらご教示いただけますと幸いですｍｍ

  9. Kasuga Kasuga より:
    2024年7月17日 8:03 PM

    どうでしょう？セキュリティの問題は、私の方で再現できないのでなんとも言えないのですね。
    その会社のアドレスは、多量のメールを送ったりしますか？そうであるなら何か制限がかかっている可能性もありますね。
    取り敢えず可能でしたら、ご自分のGoogleアカウントで同じフォームを作ってテストするのが良いかもしれません。

  10. E.N より:
    2024年7月17日 6:31 PM

    一番最初に承認を求められたので許可しています・・
    会社のアドレスでやっているので、なんらかのセキュリティシステムが働いてる可能性はありますか・・？

1 2 3 4 … 7
タイトルとURLをコピーしました