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. Kasuga Kasuga より:
    2024年9月3日 10:16 PM

    う～んこれは……関数に全角文字が渡されたのかも……？
    セルに表示されるエラーは、#VALUE!ですか？
    セルの右上角の赤三角にカーソルを合わすとエラーの説明が表示されると思いますが、何と書いてありますか？

  2. YOSHIZAWAです。 より:
    2024年9月3日 9:58 PM

    有難うございます

    はい！外しました！
    まだ変わりなくエラーです。

  3. Kasuga Kasuga より:
    2024年9月3日 6:19 PM

    むむむ、なかなかに手強いですね。
    でもご安心ください。この関数は、私の方でテストしていますので、問題はありません。

    そちらのキー配置が私のと違う可能性があります。まず、セルに関数をコピペしてください。
    次の図のように数式のチェックを外してください。これで試してみましょう。

  4. YOSHIZAWAです。 より:
    2024年9月3日 2:29 PM

    沢山、ご教授を有難うございます。

    そうですね、前回のサンプルで”が全角になっていましたね。そういう事でしたか！難しいですね…。

    さて、また問題なのですが、何度もすみません。

    ①目で見て=IF(A1～～～なので、[Ctrl]+@をしてからコピペをしたのですが、切り替わらずエラーがでます。

    ②CHAR(10)で作って下さってるのに、改行にはならず…。

    [Ctrl]+@をするタイミングが良くないですか？

    （G,H,~と好きなだけ追加）はとても助かります！感謝です。

  5. Kasuga Kasuga より:
    2024年9月2日 9:38 PM

    ちょっと説明不足でした。半角””で囲まれていたら全角のどの様な文字でも問題ありません。ですから””内で全角の：を使うのは問題ありません。
    私もうっかりしていたのですが、このコメントに半角の””など特殊記号を使うと全角に自動変換されてしまいます。これは、Wordpress(ブログのシステム)の仕様なのですが、前回のコピペをお願いした関数も全角に変換されていたかもしれません。確認して全角でしたら半角””に直してくださいね。
    さて、関数表記のままメールが送られてきたそうですが、スクリプト(GAS)がセル(A1やB1のマス)に表示されているそのままを送るためです。
    目で見て=IF(A1～～～などのように表示されていたらそのままの表示が送られます。
    [Ctrl]+@キーで関数表示と結果に切り替わるので気をつけてください。関数がそのまま表示されていたらもう一度[Ctrl]+@で切り替えてください。
    ご希望の関数ですが、次のような感じでよいのではないでしょうか。今回は半角が全角に変換されないようにしましたのでそのままコピペできると思います。

    ="【注文商品】"&CHAR(10)&IF(D2="","",D1&"×"&D2&"個="&TEXT((1000*D2),"#,##0")&"円"&CHAR(10))&IF(E2="","",E1&"×"&E2&"個="&TEXT((2000*E2),"#,##0")&"円"&CHAR(10))&IF(F2="","",F1&"×"&F2&"個="&TEXT((3000*F2),"#,##0")&"円"&CHAR(10))

    この関数では、見やすくするために、改行にCHAR(10)を使っています。[Ctrl]+[Enter]でもよいのですが、関数のサンプルではこの方が分かりやすいかもしれませんね。覚えておいて良いと思います。
    このサンプルでは、D,E,Fの3つですが、G,H,~と好きなだけ追加してください。

    シートの関数のよいところは、テストが簡単にできることです。フォームからわざわざ回答を送信しなくても、D1やD2などを自分で直接に値を変えてテストしてくださいね。満足な表示ができたらフォームから回答を送信してテストするとよいですよ。念のため。

    今度はうまくいくとよいですね。

  6. YOSHIZAWAです。 より:
    2024年9月2日 1:02 PM

    早々にお返事を頂き、ありがとうございます。

    「””」については半角にしていましたが「：」が全角で、できていませんでした。
    ご指摘ありがとうございます。

    修正してくだったものをコピペしましたが、

    =IF(D2=””,””,”注文商品：”&D1&”×”&D2&”個
    “)&IF(E2=””,””,”注文商品：”&E1&”×”&E2&”個”)

    このままで送られて来ました。

    フォームの質問には
    ・〇〇商品（@1000)とし
    プルダウンで個数を選ぶようにしています。
    プルダウンが問題とかありますでしょうか？

    それと併せて教えて頂きたいことがあるのですが、
    （）内の数式に「”注文商品:」があると下記のようになりますでしょうか？

    商品数が多いので、例えば10種類の商品中2種類のみを注文の場合
    【注文商品】
    ・〇〇商品（@1000)×個数=金額
    ・〇〇商品（@2000)×個数=金額

    というようにしたいです。
    どうぞ宜しくお願い致します。

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

    当ブログをご覧いただきありがとうございます。

    数式を拝見しますと、気になるのが””(全角のダブルクォーテーション)を使われていないかです。関数記号は全て半角英数字ですので、再度確認をして確実に半角の””をお使いください。←初心者の方に多いミスです。
    これも記載ミスかもしれませんが、最後に”)が抜けています。
    (修正前)
    =IF(D2=””,””,”注文商品：”&D1&”×”&D2&”個
    “)&IF(E2=””,””,”注文商品：”&E1&”×”&E2&”個
    (修正後)
    =IF(D2=””,””,”注文商品：”&D1&”×”&D2&”個
    “)&IF(E2=””,””,”注文商品：”&E1&”×”&E2&”個”)

    ※改行は[Ctrl]+[Enter]、或いは、[Alt]+[Enter]
    この式をコピペして試してください。うまくいかないようでしたらお知らせくださいね。

  8. YOSHIZAWA より:
    2024年8月31日 6:05 PM

    はじめまして！
    自分がやりたかったことが載っているサイトに来れて、有難く設定させていただきました。

    私の知っている関数はSUM計算のみと言う超初心者です。

    質問ですが、他の方も書かれていたことで重複し申し訳ありません、
    何度やっても上手くできませんのでご教授頂きたいです。

    やりたい事は注文がなかった商品は
    上がってこない様にしたいです。

    ①【注文商品】
    “&D1&”×”&D2&”個
    “&E1&”×”&E2&”個
    　　　↓
    ②【注文商品】
    =IF(D2=””,””,”注文商品：”&D1&”×”&D2&”個
    “)&IF(E2=””,””,”注文商品：”&E1&”×”&E2&”個

    ①を②の様に訂正しましたら、エラーがでています。

    お忙しいところすみませんが教えていただければ幸いです。
    どうぞよろしくお願いします。

  9. Kasuga Kasuga より:
    2024年8月1日 11:15 AM

    おはようございます。
    そうですか～、実はフォームとシートの回答数の数合わせで検証したことがないもので、不確実な情報ですみません。
    フォームの回答数とシートの回答数が同じだと問題がないと思っていたのですが……いつか私も試してみますね。
    貴重な情報をありがとうございました。

  10. えみ より:
    2024年8月1日 10:13 AM

    おはようございます。

    数を合わせてもやっぱり新しい回答が飛んでくると、前の人にメールが行っちゃうようなので、とりあえず最初に言われたとおり、全てクリアして行も全部削除してやってみます。

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