GAS 自動でメール送信 Googleフォームの回答内容と回答内容を参照したセルの値をメール本文に出力したい。

前提・実現したいこと

Googleフォームで回答がされたら自動で返信できるスクリプトを作成したいです。

Googleフォームの回答に対して、回答内容を参照して関数を組んでいるセルの中身を出力して、自動でメール送信をしたい。

発生している問題

url(回答内容を参照して同じスプレッドシート内で関数を組んでいるセル)がメール内に表示されない。
おそらくトリガーがフォーム送信時になっているのが問題かと思っていますが解決方法がわかりませんでした。
O列(URLが入る列)に事前に文字列を入れた場合にはメール文面に反映されました。

トリガー
イベントソースの選択：スプレッドシートから
イベントの種類を選択：フォームの送信時

試したこと

今書いてあるコードです。

function sendReply(e) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('回答内容とurl生成のセルがあるシート');

  const answeredName = e.values[3]         // 回答されたお名前
  const answeredEmail = e.values[4]        // 回答されたEmail
  const ownName = '自分の名前'                // こちらの名前
  const title = "タイトル件名"

  const row = sheet.getLastRow(); //最終行を取得
  const url = sheet.getRange(row, 15).getValue(); //定数urlにO列(URL生成の列)の最終行にあるセルの中身を取得

  const body = `
  ${answeredName} 様
  
  メール本文

  ${url}
  `
  GmailApp.sendEmail(answeredEmail, title, body)

その他

初学者で調べながら見様見真似でやってます。初歩的なミスもあるかもしれませんが、よろしくお願いいたします。

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

回答1件

ベストアンサー

トリガー
イベントソースの選択：スプレッドシートから
イベントの種類を選択：フォームの送信時
を設定したとき、フォームの内容は、namedValuesというオブジェクトで渡されます。

そして、複数チェックボックス以外のアンケート項目は
e.namedValues['質問項目'][0];
という書き方で、回答を取り出せます。

例えばフォームの質問項目（タイトル）が「名前」「Email」である場合、
名前とEmailは下記のようにして取得できます。

js
1function sendReply(e) {
2
3  const ss = SpreadsheetApp.getActiveSpreadsheet();
4  const sheet = ss.getSheetByName('回答内容とurl生成のセルがあるシート');
5
6  const answeredName = e.namedValues['名前'][0];         // 回答されたお名前
7  const answeredEmail = e.namedValues['Email'][0];       // 回答されたEmail
8
9以下略

#追記

フォームの解答以外のセルを出力するにはどうしたら良いかがわかりません。

フォーム送信して新しいデータが最下行に追加された時、O列に入っている式が計算される前（ARRAYFORMULAが走る前）にスクリプトが実行されて、空白セルとしてデータが読み取られてしまっているのが原因と思われます。

解決方法として、O列に入っている数式と同じものを、スクリプト上で組み立てるやり方が考えられます。

※下記のコード内の'XX列の質問項目' は、実際のフォームの質問項目に変えてください。
（3行目にnamedValuesの内容をログ出力するコードを追加したので、その出力を参考に）

js
1function sendReply(e) {
2  // 追加
3  console.log(JSON.stringify(e.namedValues, undefined, 1) // フォームの質問項目の内容を出力
4
5  const ss = SpreadsheetApp.getActiveSpreadsheet();
6  const sheet = ss.getSheetByName('回答内容とurl生成のセルがあるシート');
7
8  const answeredName = e.namedValues['名前'][0];         // 回答されたお名前
9  const answeredEmail = e.namedValues['Email'][0];       // 回答されたEmail
10  const ownName = '自分の名前'                // こちらの名前
11  const title = "タイトル件名"
12
13// -------追加-------
14  // 'XX列の質問項目' は、実際のフォームの各質問項目に変えてください。
15  const C = encodeURI(e.namedValues['C列の質問項目'][0]);
16  const D = encodeURI(e.namedValues['D列の質問項目'][0]);
17  const F = encodeURI(e.namedValues['F列の質問項目'][0]);
18  const G = encodeURI(e.namedValues['G列の質問項目'][0]);
19  const H = encodeURI(e.namedValues['H列の質問項目'][0]);
20  const I = encodeURI(e.namedValues['I列の質問項目'][0]);
21  const J = encodeURI(e.namedValues['J列の質問項目'][0]);
22  const K = encodeURI(e.namedValues['K列の質問項目'][0]);
23  const L = encodeURI(e.namedValues['L列の質問項目'][0]);
24  const M = encodeURI(e.namedValues['M列の質問項目'][0]);
25  const N = encodeURI(e.namedValues['N列の質問項目'][0]);
26
27  const url = "https://docs.google.com/forms/d/e/Googleフォームのキー/viewform?usp=pp_url&entry.2145272979="
28    + C + "&entry.918876992=" + D
29    + "&entry.965629543=" + F
30    + "&entry.789231669=" + G
31    + "&entry.55383597=" + H
32    + "&entry.343743215=" + I
33    + "&entry.1212281365=" + J
34    + "&entry.2139868976=" + K
35    + "&entry.36015981=" + L
36    + "&entry.2091679258=" + M
37    + "&entry.269090822=" + N
38
39// -------追加ここまで-------
40
41  const body = `
42  ${answeredName} 様
43
44  メール本文
45
46  ${url}
47  `
48  GmailApp.sendEmail(answeredEmail, title, body)
49
50（以下略）
51
52}

「'XX列の質問項目' は、実際のフォームの各質問項目に変えてください。」というのは下のような意味です。
たとえばフォームが図のような質問だと仮定します。

このとき、色の枠で囲った部分を、それぞれe.namedValues['○○']の「○○」に入れてください、という意味です。

  const C = encodeURI(e.namedValues['好きな果物は何ですか？'][0]);
  const D = encodeURI(e.namedValues['好きな動物は何ですか？'][0]);
  const F = encodeURI(e.namedValues['好きな野菜はなんですか？'][0]);
  const G = ...
  ...
  ..

投稿2021/12/10 16:18

編集2021/12/13 15:14

退会済みユーザー

総合スコア0

k_k

2021/12/10 23:27

ご回答ありがとうございます。フォームの解答以外のセルを出力するにはどうしたら良いかがわかりません。

退会済みユーザー

2021/12/10 23:58 編集

フォーム送信して新しいデータが最下行に追加された時、O列に入っている式が計算される前にスクリプトが実行されて、空白セルとしてデータが読み取られてしまっているのが原因と思われます。解決方法として、Ｏ列に入っている数式の計算を、スクリプト上で再現するやり方が考えられます。 O列には具体的にどのような数式が入っているのでしょうか？たとえば2行目のO2セルに入っている数式を、省略することなく記載してください。その式をスクリプトに変換したものを回答します。

k_k

2021/12/12 07:39

`=ARRAYFORMULA(IF(A2:A="","","https://docs.google.com/forms/d/e/Googleフォームのキー/viewform?usp=pp_url&entry.2145272979="&C2:C&"&entry.918876992="&D2:D&"&entry.965629543="&F2:F&"&entry.789231669="&G2:G&"&entry.55383597="&H2:H&"&entry.343743215="&I2:I&"&entry.1212281365="&J2:J&"&entry.2139868976="&K2:K&"&entry.36015981="&L2:L&"&entry.2091679258="&M2:M&"&entry.269090822="&N2:N))` 上記になります。内容としては、フォームAの回答が事前入力された、フォームBのURLを生成するセルです。

退会済みユーザー

2021/12/12 08:03

追記しました。

k_k

2021/12/13 13:17 編集

詳しくご回答ありがとうございます。勉強になります。 URLを実際のメール本文に出力することができました！！ちなみにURL内に文字列が入ってしまうのですが、変換することは可能なのでしょうか？ // 'XX列の質問項目' は、実際のフォームの各質問項目に変えてください。（3行目にnamedValuesの内容をログ出力するコードを追加したので、その出力を参考に） └こちらは質問項目をコピペするのではなく、3行目のような形で記載が必要でしょうか？今はやり方がよくわからず、質問内容をそのままコピペしています。 1から10まで聞いており申し訳ございませんが、ご回答いただけますと助かります。

退会済みユーザー

2021/12/13 15:15

追記しました。「ちなみにURL内に文字列が入ってしまうのですが、変換することは可能なのでしょうか？」についてですが、%のような文字に変換することを言っているのだと推測し、encodeURI関数で囲むように直しました。

k_k

2021/12/14 07:25

無事思っていた通りに実装ができました。すぐに回答いただいて本当に助かりました。ありがとうございました。

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