Google Script send form values by email, error: cannot read property "namedValues"

Question

Project key: MyvPlY2KvwGODjsi4szfo389owhmw9jII

I am trying to run a script that sends the contents of my form by email each time the form is submitted. I was following the instructions from this link below exactly until I started getting errors and I received advice to change my script to open spreadsheets by id:

http://www.snipe.net/2013/04/email-contents-google-form/

When I complete the form, it is supposed to email the contents to my email.

The problem I am nowhaving is that the function which goes through the form values doesn't work. It's returning the error "TypeError: Cannot read property "namedValues" from undefined. (line 15, file "Code")" in regards to the piece of code below:

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

I am not too familiar with google scripts so I'm not sure how to work around this issue either. Is there anything you recommend? I have included the entire script below.

function sendFormByEmail(e) 
{    
  // Remember to replace this email address with your own email address
  var email = "[email protected]"; 

  var s = SpreadsheetApp.openById("1hOqnK0IVa2WT6-c-MY0jyGGSpIJIV2yzTXdQYX4UQQA").getSheets()[0];
  var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
  var message = "";
  var subject = "New User Form";

  // The variable e holds all the form values in an array.
  // Loop through the array and append values to the body.

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

  // Insert variables from the spreadsheet into the subject.
  // In this case, I wanted the new hire's name and start date as part of the
  // email subject. These are the 3rd and 16th columns in my form.
  // This creates an email subject like "New Hire: Jane Doe - starts 4/23/2013"
  subject += e.namedValues[headers[2]].toString() + " - starts " + e.namedValues[headers[15]].toString();

  // Send the email
  MailApp.sendEmail(email, subject, message); 
}

Solution 1

The function appears to be undefined as 'e' is not received as part of the function context. You'll need to set a trigger for the submission of the form, and send the information to the function. You can use the following code:

/* Send Confirmation Email with Google Forms */
function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  ScriptApp.newTrigger("SendConfirmationMail")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();
}


function SendConfirmationMail(e) {
  try {
    var ss, cc, sendername, subject, columns;
    var header, message, value, textbody, sender, itemID, url;

// This is your email address and you will be in the CC
cc = "[email protected]";

// This will show up as the sender's name
sendername = "name to be displayed as sender";

// Optional but change the following variable
// to have a custom subject for Google Docs emails
subject = "Choose an approppiate subject";

// This is the body of the auto-reply
message = "";

ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

// This is the submitter's email address
sender = e.namedValues["Username"].toString();

// Only include form values that are not blank
for ( var keys in columns ) {
  var key = columns[keys];
//Use this to look for a particular named key
  if ( e.namedValues[key] ) {
    if ( key == "Username" ) {
      header = "The user " + e.namedValues[key] + " has submitted the form, please review the following information.<br />";
    } else {
        message += key + ' ::<br /> '+ e.namedValues[key] + "<br />"; 
      }
    }
  }
}

textbody = header + message;
textbody = textbody.replace("<br>", "\n");

Logger.log("Sending email");
GmailApp.sendEmail(cc, subject, textbody, 
                   {cc: cc, name: sendername, htmlBody: textbody});



} catch (e) {
    Logger.log(e.toString());
  }
}

Solution 2

I have the same error and it took me some time to figure out how to solve it.

The problem is that you are writing your code in the form, and you should do it in your spreadsheet.

When I created the function inside the form and registered the event, it was called but the parameter didn't have the same structure (and didn't have the field namedValues, so the error "Cannot read property "namedValues" from undefined"). A better way to check this is to log the object as a JSON string:

Logger.log("e:  " + JSON.stringify(e));

So, the steps I have made to correct this issue:

1. Create a spreadsheet
2. Create the form through the spreadsheet (inside the spreadsheet select Tools->Create a form), and create your form
3. Go back to your spreadsheet, and create the script (Tools->Script Editor)
4. Write your function
5. Register the function (Edit->Current Project's Triggers): function sendFormByEmail, Event: From Spreadsheet -> On form submit

Hope this helps