setting up script to include google docs form data in email notification

Question

I've setup a form using googledocs. I just want to have the actual data entered into the form emailed to me, as opposed to the generic response advising that the form has been completed.

I have no skill or experience with code etc, but was sure i could get this sorted. I've spent hours+hours and haven't had any luck.

My form is really basic.it has 5 fields. 4 of which are just text responses, and one multiple choice.

I found this tute online (http://www.labnol.org/internet/google-docs-email-form/20884/) which i think sums up what i'm trying to do, but have not been able to get it to work.

from this site i entered the following code:

function sendFormByEmail(e) 
{    
  var email = "[email protected]"; 

  var subject = "Google Docs Form Submitted";  

  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); 
}

To this, i get the following response: ->

Your script, Contact Us Form Mailer, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

The script is used by the document 100% Club.

Details:

Start               Function        Error Message                                                       Trigger     End
12/3/12 11:06 PM    sendFormByEmail TypeError: Cannot call method "toString" of undefined. (line 12)    formSubmit  12/3/12 11:06 PM

Is anyone able to help shed some light on this for me? I'm guessing i'm not including some data neeeded, but i honestly have no clue.

Solution 1

Workaround http://www.labnol.org/internet/google-docs-email-form/20884/ You have to setup app script to forward the data as email.

Solution 2

I'll point to the comment above that solved it for me: https://stackoverflow.com/a/14576983/134335

I took that post a step further:

• I removed the normal notification. The app script makes that generic text redundant and useless now
• I modified the script to actually parse the results and build the response accordingly.

function sendFormByEmail(e)
{
  var toEmail = "changeme";
  var name = "";
  var email = "";

  // Optional but change the following variable
  // to have a custom subject for Google Docs emails
  var subject = "Google Docs Form Submitted";
  var message = "";

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

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

  // Credit to Henrique Abreu for fixing the sort order
  for(var i in headers) {
    if (headers[i] = "Name") {
      name = e.namedValues[headers[i]].toString();
    }
    if (headers[i] = "Email") {
      email = e.namedValues[headers[i]].toString();
    }
    if (headers[i] = "Subject") {
      subject = e.namedValues[headers[i]].toString();
    }
    if (headers[i] = "Message") {
      message = e.namedValues[headers[i]].toString();
    }
  }

  // See https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
  var mailOptions = {
    name: name,
    replyTo: email,
  };

  // This is the MailApp service of Google Apps Script
  // that sends the email. You can also use GmailApp here.
  MailApp.sendEmail(toEmail, subject, message, mailOptions);

  // Watch the following video for details
  // http://youtu.be/z6klwUxRwQI

  // By Amit Agarwal - www.labnol.org
}

The script utilized in the example is extremely generic but very resilient to change because the message is built as a key/value pair of the form fields submitted.

If you use my script you'll have to tweak the for loop if statements to match your fields verbatim. You'll also want to edit the toEmail variable.

Thanks again for the question and answers. I was about to ditch Google Forms as the generic response was never enough for what I was trying to do.

Lastly, in response to the actual problem above "toString of undefined" specifically means one of the form fields was submitted as blank. If I had to guess, I would say the author only used this for forms where all the fields were required or a quick undefined check would've been put in place.

Something like the following would work:

for(var i in headers) {
  var formValue = e.namedValues[headers[i]];
  var formValueText = "";
  if (typeof(formValue) != "undefined") {
    formValueText = formValue.toString();
  }
  message += headers[i] + ' = '+ formvalueText + "\n\n";
}

I haven't tested this precisely but it's a pretty standard way of making sure the object is defined before trying methods like toString() that clearly won't work.

This would also explain Jon Fila's answer. The script blindly assumes all of the header rows in the response are sent by the form. If any of the fields aren't required or the spreadsheet has fields that are no longer in the form, you'll get a lot of undefined objects.

The script could've been coded better but I won't fault the author as it was clearly meant to be a proof of concept only. The fact that they mention the replyTo correction but don't give any examples on implementing it made it perfectly clear.

Comments

[user1872449]

unfortunately it doesn't.. it sends a notification email, when someone completes a form etc, but the email just says its been updated, click here to check. Im wanting to have the data entered, included in the notification email.

[w0rd-driven]

Just wanted to share that this worked for me. I modified the script to parse the form and actually fill out the email. I'll post the answer to share the code but I wanted to comment that this is "my solved answer".