ここで紹介する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)をエディタへコピペします。
- function diyReply(SheetNo) {
- // 引数SheetNo:設定用シートのタブの位置(一番左のシートが0、2番目が1、3番目が2…)
- if(!(SheetNo > 1)) SheetNo = 1;
- // アクティブフォーム
- const form = FormApp.getActiveForm();
- // 設定用シート(2枚目のシート)
- const mailSheet = SpreadsheetApp.openById(form.getDestinationId()).getSheets()[SheetNo];
- // 最新の回答を配列に取得
- const latestItems = form.getResponses().pop();
- const answerItems = latestItems.getItemResponses().map(item => item.getResponse());
- answerItems.unshift(latestItems.getTimestamp()); // タイムスタンプを追加
- // 回答を設定用シートにセット
- mailSheet.getRange(2, 2, 1, answerItems.length).setValues([answerItems]); SpreadsheetApp.flush() // sync;sync;sync
- // B12がONなら編集リンクを設定用シートにセット
- mailSheet.getRange('C12').setValue(mailSheet.getRange('B12').getValue() === 'ON' ? latestItems.getEditResponseUrl() : ""); SpreadsheetApp.flush() // sync;sync;sync
- // 設定用シートの項目(セル:B3-B11)を配列に取得
- const mailTbl = mailSheet.getRange('B3:B11').getDisplayValues().flat();
- // B11が空白ならアクティブユーザーのメールアドレスを使用
- if(!mailTbl[8]) mailTbl[8] = Session.getActiveUser().getEmail();
- // B3がOFF以外はメールを送信
- if (mailTbl[0] !== 'OFF')
- GmailApp.sendEmail(mailTbl[3], mailTbl[1], mailTbl[2], {from: mailTbl[8],cc: mailTbl[4],bcc: mailTbl[5],name: mailTbl[6],replyTo: mailTbl[7]});
- } // Ver2.2.0 for maintenance
↓ ↓ コピペ ↓ ↓ (保存を忘れずに！)
トリガー
トリガーを追加します。
イベントの種類の選択は、「フォーム送信時」を選びます。
※承認を求められたら、「Advanced」(あるいは、「詳細」)→「Go to ～～(Unsafe)」(あるいは、「～～に移動」)→「Allow」(あるいは、「許可」)
動作テスト
フォームを実行(プレビュー)して、回答、そして送信します。
結果、リンクしたシート(フォームの回答)
結果、シート1に次のように表示されています。
結果、宛先(TO:)へ送られてきた返信メールです。
成功しました。
このように、GAS(diyReply)はフォームからの回答をシート1にコピー、シート1の内容をメールするだけです。
つまり、思い通りのメールを送るには、スプレッドシートの関数を駆使して自分で作る必要があります。
そして、どのような返信メールも自由自在に作ることができます。
実践編
既存のフォームを修正変更し、改めて回答を募集する際は、過去のデータが混ざらないよう、リンクしたスプレッドシートとフォームの回答を全削除してください。(以下の①②の作業)
①回答シートの回答行を削除する。
②フォームの回答を削除する
応用例その8
フォームより入力された項目には価格があり、その合計金額をメールに表示したいとします。
注文商品(価格あり)の個数をプルダウンのメニューから選ぶフォームです。
返信メールのためのシート1を次のように作りました。
数式表示[Ctrl]+@しています。
テーブル(コピペ用)に表示します。
| 質問 | タイムスタンプ | 名前 | メールアドレス | 注文商品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:(省略可) |
"文字列"(ダブルクォーテーションで挟まれた文字列)とセルの数値(C2など)を、&(半角)でつなぎながら文章を書くのは慣れないと少し難しいかもしれません。
コツは最初に"を付けてから、一気に文章を書きあげて最後に"で閉じます。
この時、改行([Ctrl]+[Enter]キー)も気にせずに使います。
それから必要な箇所を"&※※&"に置き換えていきます。(※※は、A1,、B2など)
B5セルに望みの文章が表示出来たら、それがそのままメールの文章になります。
実行(プレビュー)します。
成功です。
合計金額が表示された返信メールが送られてきました。
シート1の名前(NAME:)で記した○○商店も表示されています。
この例を参考に、ご自分の思い通りな返信メールをお作り下さい。
既存のフォームを修正変更し、改めて回答を募集する際は、過去のデータが混ざらないよう、リンクしたスプレッドシートとフォームの回答を全削除してください。(以下の①②の作業)
①回答シートの回答行を削除する。
②フォームの回答を削除する
応用例その8.5(クレジット決済を追加する)
お支払い方法に銀行振込だけでなくクレジット決済も対応した応用例です。
ご参考までにどうぞ。
応用例その9
応用例その11(2つのGASを使って署名活動フォームを作る)
応用例の補足(フォームの選択肢に定員設定を追加する)
次のGAS(updateItems)を使って、フォームの選択肢に定員(人数制限)を付けられます。
今回のGAS(diyReply)と同時に使うことで、フォームの応用がさらに広がります。
複数のGASの同時使用の応用例が『応用例その11』です。
参考にしてください。
応用例の補足2(フォームの回答者数に定員設定を追加する)
次のmultiReplyは、今回のdiyReplyに"フォームの回答者数に定員設定"ができる機能を追加しました。
「質問の選択肢」毎の定員でなく、「回答者数」の定員でよいのなら、とても有用なスクリプトです。
おわりに
GASを組むことで、フォームやスプレッドシートから得られる質問項目と回答項目を、メール文書に加えるのは簡単です。
しかし、それらにない計算値などをメールに加えるには、GASを特別に組み直さなければいけません。
フォームに対して1対1のGASになってしまい、汎用性が失われてしまします。
また、GASで数式を組んでテストをするのは、なかなかに手間がかかります。
そこで、この負担をスプレッドシートの関数側に持っていくために、GASの機能を削りに削ってできたのが今回のdiyReplyです。
GASでは困難な複雑な数式も、スプレッドシートの関数ならトライ＆エラーが簡単に行えるため短時間で完成できます。
diyReplyと組み合わせることで、今回の例では合計金額ですが、関数に詳しい方はいくらでも難しい数式で得た値を、メールに表示できます。
ぜひ、自由自在な返信メールをご活用ください。
それでは、また次の記事でお会いしましょう。
追記(2024.9.25)：
このGASを公開して1年以上が経ちますが、今でも日に20～30人の方がご覧になられるようです。
それに伴いこれまで、いくつかの問題点をご指摘いただきました。
特に、フォームとスプレッドシートの回答数が一致しない場合、混乱が生じていたというご指摘を何度か受け、今回の大幅なバージョンアップを行い以下の点を改善いたしました。
- 従来はスプレッドシートから回答を取得していましたが、新しいバージョン(2.1.0)では、フォームに直接記録された回答を取得するように変更いたしました。
これにより、確実に最後の回答を取得できるようになり、フォームとスプレッドシートの回答数が一致しない場合でも確実に、フォームからの送信者(回答者)の回答を使ったメールが送れます。
次の機能は、より柔軟な対応を可能にするため、追加いたしました。
- 返信メールの送信者(From:)に、フォームのオーナーのメールアドレスのエイリアスを設定できるようにしました。
- 回答者が自身の回答を編集するためのURLを表示できるようになりました。
もし使ってみて何か問題点がありましたら、コメントにてお知らせいただけると幸いです。
コメント
スクリプトのメールを送る段階で権限で弾かれていますね。ログのメッセージからは権限が付与されていないと読めますね。
また確認ですが、スクリプトを保存するときとか、トリガーをセットするときに承認は求められませんでしたか？
※承認を求められたら、「Advanced」(あるいは、「詳細」)→「Go to ～～(Unsafe)」(あるいは、「～～に移動」)→「Allow」(あるいは、「許可」)
いいえ、シートの位置が左から２番目でしたら、そのままでよいです。SheetNoは、指定しないと1になります。
問題は別にありますね。
実行ログを見ると、
Exception: Gmail operation not allowed. : Mail service not enabled
diyReply @ コード.gs:20
と表示されます。他に設定すべき箇所があるのでしょうか・・・
ご返信ありがとうございます！
トリガーも掲載頂いている通りに設定しておりますが、今確認したところエラー100％になっていました・・
メールを設定しているシートも左から2番目で間違いありませんｍｍ
もしかしてですが、スクリプトの冒頭
function diyReply(SheetNo) {
↓
function diyReply(1) {
にしないといけないのでしょうか？
当ブログをご覧いただきありがとうございます。
う～ん、確認ですけど…トリガーは大丈夫ですよね？ あと、B3～B10を設定しているシートのタブの位置は左から2番目になっていますか？
このシートの位置を例えば、左から3番目にする時は、diyReply(2)と引数を入れてシートの位置の指示が必要です。
記事を参考にさせていただきました。
スクリプトをコピペし、スプシのシート２のB3〜B10に返信内容を納めているのですが、メールが飛びません。。
回答内容からVLOOKUPで検索した内容を返信メールに掲載したく、
VLOOKUPの検索ようにシートをもう２枚追加しております。
こちらが原因でしょうか。
ご教示いただけると幸いです。
なるほどなるほど、なさりたいことが理解できました。
本文のセルに次の様な形式で対応できると思います。
セルの中での改行は[Ctrl]+[Enter]、あるいは[Alt]+[Enter]をお忘れなく。
=IF(H2=””,””,”注文商品：”&H1&”×”&H2&”個
注文者：”&I2&”
“)&IF(J2=””,””,”注文商品：”&J1&”×”&J2&”個
注文者：”&K2)
ご教示ありがとうございます。
知識が無いもので、理解が追い付かず申し訳ございません。
例えば、H1に商品名とH2に注文個数が飛んできます。
下記の【現在の設定】だと、H2が空欄だったとしても注文確定メールには『商品× 個』で表示されています。
H2が空欄の商品は注文確定メール上で非表示にしたいです。
下記の【現在の設定】の場合、本文のどこに&IF(C2=0,””,”商品:”&C2&”個”)をいれればいいのでしょうか。
【現在の設定】
注文商品：
“&H1&”×”&H2&”個
注文者：”&I2&”
“&J1&”×”&J2&”個
注文者：”&K2&”
お手すきの際にご教示いただけますと幸いです。
当ブログをご覧いただきありがとうございます。
非表示にできるとお答えします。なぜならスプレッドシートの関数の問題なので大概のことはできるからです。
具体例ですと、C2にフォームからの回答の商品個数が入るとして、本文中に、&IF(C2=0,””,”商品:”&C2&”個”)などようにIFを使うなどすればよいのではないでしょうか？
記事拝見させていただき設定させていただきました。
例えば、個数選択されていない商品項目の項目名は自動メールでは非表示にすることはできるのでしょうか？