Making Google Docs Forms send useful email notifications

Forms created in Google Docs can serve as Contact Forms on your web site. You can enable notifications to receive an email when the form is submitted. However, by default the email just tells you that the form was submitted without providing any of the content. Often it would be more useful if the email provided the values submitted in the form. Especially if you are receiving the email on a mobile device where Google Docs is not that easy to navigate.

Making a Google Form send the content of the form is actually quite straightforward. These are the steps that we will follow

  1. Add an onFormSubmit script that sends the form values in an email
  2. Add an On Form Submit trigger to run this function
  3. Disable the default notification email
  4. Test

The following example assumes you've created a form with just two fields. The first field for an email address, and the second field for a name.


Create onFormSubmit function

Open the spreadsheet associated with the form. Click Tools > Script Editor. Paste in the following function


function onFormSubmit(e) {
  var email = e.values[0];
  var name = e.values[1];


  // change this address to be the address where you want the notification to go  
  var to = "xxxxxx@xxxxxx.com";
  var subject = "Form Notification";
  var message = "Full Name: " + name + "\n";
  message += "Email Address: " + email + "\n";
  
  MailApp.sendEmail(to, subject, message);
}

Add On Form Submit trigger


To make this run, you need to define a trigger. From the Script Editor click Triggers > Current script's trigger's. Add a new trigger to execute onFormSubmit, From Spreadsheet, On Form Submit. Save this and authorize the function when prompted.

Disable Default Notification

From the Google Spreadsheet click Tools > Notification Rules. Uncheck the box for notifying you when a user submits the form. While you are testing you may want to leave this box checked, just to make sure you don't miss any notifications.

Test

Now enter some dummy data in your form and submit. Check that the email comes through as you expected, and adjust the function to alter the subject or message as you desire.

The above approach will let you receive notification emails when your Google Forms are submitted. The notification emails will contain the actual content submitted in the form.

8 comments:

  1. Fantastic post. Here’s a tool that lets you build all types of web forms with email alerts fast and without coding. Just point and click http://www.caspio.com/online-database/features/web-forms-online.aspx

    ReplyDelete
  2. Exactly what I was looking for! I was raised on VBA, so suddenly having to learn a new language is a bit tricky - especially for someone who is the wrong side of 40! Keep the tips coming.

    ReplyDelete
  3. I know you're not in the tech support business, so feel free to ignore this question, but any idea why this doesn't work? I know the issue is in the if statement, but I cant see what I did wrong...

    function onFormSubmit(e) {
    var whentime = e.values[0];
    var email = e.values[1];
    var actnum = e.values[2];


    // change this address to be the address where you want the notification to go

    if (actnum.slice(0,3)="901")
    {
    var to = "1@gmail.com";
    }
    else
    {
    var to = "2@gmail.com";
    }

    var subject = "A new Territory Change Request has been submitted - " + whentime + "\n";
    var message = "Received: " + whentime + "\n";
    message += "Email Address: " + email + "\n";


    MailApp.sendEmail(to, subject, message);
    }

    ReplyDelete
  4. Well, it doesn't work for me. The data is added to the spreadsheet as usual, but no email is received.

    1. Does it matter where in the Code.gs file the new code is pasted? I just added it to the end.

    2. Does anything in the code besides the email address need to be edited?

    3. When I save the edited file, I am prompted for a new Project Name. I just used "FormSubmit Email" Is that OK? Does it matter?

    Sure hope you can help.

    ReplyDelete
    Replies
    1. 1. shouldn't matter where code appears
      2. no, just the to email address
      3. project name doesn't matter, that one is fine

      I wonder if you're running into something where it needs you to authorize sending the email. You could try adding a test function into your code.gs like

      function testEmail() {
      var email = "yyyyy@yyyyyyyyy.com";
      var name = "example name";
      var to = "xxxxxx@xxxxxx.com";
      var subject = "Form Notification";
      var message = "Full Name: " + name + "\n";
      message += "Email Address: " + email + "\n";

      MailApp.sendEmail(to, subject, message);
      }

      And then run that by saving your changes and clicking Run > testEmail on the menu (in the Script Editor). When that runs it might prompt you to authorize the email (should be a one time thing) but you should get the test email (as long as you change the email address appropriately). If you do, then go back and try the form again.

      Delete
  5. Does it matter if my column headers (the fields on the form) contains a space? (Like "Persons name")?

    ReplyDelete
    Replies
    1. No, it doesn't matter. In my example I had "Full Name" and "Email Address".

      Delete