1
votes

How can I set the var subject in google forms script editor to return the values of specified fields in the form/sheet its is connected to?

For example, if my column headers and corresponding form fields were, "Name" and "Date" respectively, how would I code the "var subject =" to pull that info from those columns, so that the subject of the confirmation email includes that data?

The code below pulls my Form Data from Google Docs into an Email confirmation Message, and displays the data entered. I want to customize the script to have custom email subject lines that match one or more of the form fields, instead of just a set "phrase" (i.e. "New Order Form") like it is now.

function sendFormByEmail(e) 
{    

  var email = "my email"; 

  var subject = "New Order Form";

  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
  var message = "";    

  for(var i in headers)
    message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n"; 


  MailApp.sendEmail(email, subject, message); 

}
2

2 Answers

1
votes

Below code snippet is from an already active Google Form email notification script. It is not the complete script, however, if you add it to your existing script, it will do what you asked for in your question.

What the code below is doing:

Step 1: Assigns two variables to two different constants you want to keep consistent in your email notifications, namely var subject1 and var subject2.

*Note: You can change the constants and the variables to suit your needs.

Step 2: Assign both variables you created in Step 1 plus the data you want inserted in your subject line from the user submitted responses.

If I submitted values "John" and "3/6/2014" on your Google form fields "Name" and "Date" respectively, you will receive an email with subject line that says "Submitted by John On 3/6/2014".

var subject1 = "Submitted by ";
var subject2 = "On ";

subject = subject1 + e.namedValues['Name'].toString() + subject2 + e.namedValues['Date'].toString();

Lastly, Remember that you can add to this subject string as many variables as you want to create the perfect subject for your email notifications.

Hope that helped,

Cheers T...

0
votes

Let's say your form has questions "Name" and "Date", which end up in column 2 (B) and 3 (C) respectively. As you are already accessing the event info that is passed to your function upon form submission, you can do the same to build the subject.

function sendFormByEmail(e) 
{    

  var email = "my email"; 

  var form = e.namedValues;
  var subject = form["Name"] + form["Date"];

 ...