Sending Confirmation Emails from Google Apps Forms

For a recent project, I had to create a form which allows invited respondents to register for a survey. Since we already use Google Apps, this seemed like an ideal opportunity to make use of Google Forms - and Google Sites, to provide a wrapper for the form, with supporting text and an easy-to-type URL.

And so it proved; creating a simple form was easy and within five minutes I was capturing test registrations into a Google Sheets spreadsheet. Another five minutes and it was embedded into a Google Site, and I could turn it over to the intended user.

But I can never resist fixing things that aren't broken, and it seemed like a good idea to add automatic sending of a confirmation email. Sure enough, there are plenty of recipes for doing this on various blogs, and after a few minutes more, that was working, too.

But wait a minute! What if the respondent gets the email and realises they've entered the wrong data (excluding their email address, of course - if that was entered wrongly, they won't even get the confirmation email). Can I add a link that will allow the respondent to edit their own form submission?

A quick bit of Googling later, and it appears that this is a problem that has stumped a lot of people and given rise to some ugly hacks. When that happens to me, it usually means that I've missed the point and am trying a totally wrong approach. So I set to reading the Google Apps Script documentation and looking for the classes and functions that might do what I needed. And, to cut a long story short, I found them.

Here's the resultant code:

function setup() {

  /* First, delete all previous triggers */
  var triggers = ScriptApp.getProjectTriggers();

  for (var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  /* Then add a trigger to send an email on form submit */
  ScriptApp.newTrigger("sendConfirmationEmail")
  .forForm(FormApp.getActiveForm())
  .onFormSubmit()
  .create();
}

function sendConfirmationEmail(e) {
  // e is a Form Event object - see https://developers.google.com/apps-script/guides/triggers/events#google_forms_events

  // Edit this to set the subject line for the sent email
  var subject = "Registration Successful";

  // This will show up as the sender's name
  var sendername = "Your Name Goes Here";
     
  // This is the body of the registration confirmation message
  var message = "Thank you for registering.<br>We will be in touch.<br><br>";
  message += "Your form responses were:<br><br>";

  // response is a FormResponse - see https://developers.google.com/apps-script/reference/forms/form-response
  var response = e.response;

  var textbody, sendTo, bcc;

  // Get the script owner's email address, in order to bcc: them
  bcc = Session.getActiveUser().getEmail();

  // Now loop around, getting the item responses and writing them into the email message
  var itemResponses = response.getItemResponses();
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "<br>";
    // If this field is the email address, then use it to fill in the sendTo variable
    // Check that your form item is named "Email Address" or edit to match
    if (itemResponse.getItem().getTitle() == "Email Address") {
      sendTo = itemResponse.getResponse();
    }
  }

  message += "<br>If you wish to edit your response, please click on <a href=\"" + response.getEditResponseUrl() + "\">this link</a>.";
  message += "<br><br>";
  textbody = message.replace("<br>", "\n");
     
  GmailApp.sendEmail(sendTo, subject, textbody,
                       {bcc: bcc, name: sendername, htmlBody: message});
}

To use it, set up your form, making sure it has an item named "Email Address", and then choose "Tools" -> "Script Editor...". Copy and paste the code above into it, edit the variables at the top of the sendConfirmationEmail() function and then save it, naming the project as you do so. Then choose "Run" -> "setup", which installs a trigger on the form; as it does this, it will ask you for various permissions, such as to access your Gmail and to run when you're not present.

That's it. Now, if you enter some data into the form, the sender will receive an email which lists their form responses, and also provides a link to edit them if they wish.

How It Works

For those who want to know what's under the covers, here's how it works. First of all, you need to understand how Google Forms works, under the covers.

When someone fills in a form and clicks on the "Submit" button, their answers are recorded in an automatically-created Google Sheets spreadsheet - usually with the same name as the form plus "(Responses)" tacked on the end. It's easy to assume that the form just directly stuffs the responses into the spreadsheet - but that's not what it does.

(And it was this assumption that has led a lot of programmers astray - they try to write a script using the Sheets Script Editor. That has access to the spreadsheet content, but not the form response info, and so they tie themselves into knots trying to get the URL which will edit user's response. Wrong approach!).

The form actually records the responses in a database behind the form - if you look at the Form editor menu, you'll see a menu item for "Responses (n)" where n is the number of responses recorded. And this probably matches the number of response rows in the backing spreadsheet - but not always. For example, you can delete rows in the spreadsheet, but the number of responses shown in the Form Editor won't change.

And more impressively, you can choose "Responses" -> "Change response destination", choose a new spreadsheet, and when you open it, it won't be empty - it will contain all the responses previously captured! This shows that the responses are being stored elsewhere. It also implies that if you edit the contents of the spreadsheet, your edits could be over-written by data from the response storage - and this will happen if the respondent chooses to edit their responses.

It therefore must be the case that the Edit URL edits the data in this database (and then copy the change to the spreadsheet). All of this logic belongs to the Forms app, and not the spreadsheet.

So, the correct place to deal with emailing the respondent is when the completed form is submitted - not in the spreadsheet. Google Apps triggers allow a function to be called when various events occur (see https://developers.google.com/apps-script/guides/triggers/), and this script mostly consists of an "Installable Trigger", plus the setup() function which installs it. When sendConfirmationEmail() is called, it is passed a FormEvent object, which contains an AuthMode object, a FormResponse object and a Form object representing the form itself. Only the FormResponse is of interest.

The script just sets up some strings for the email subject, sender name, and the beginning of the body text, then extracts the FormResponse variable (just for mnemonic simplicity). It then calls Session.getActiveUser().getEmail() to get the user's email address.

It then calls getItemResponses(), which returns an array of the form item responses, and loops through the array, getting the item title and the response, writing them to the email message. Along the way, when it finds the item titled "Email Address", it gets the corresponding reponse and uses it as the "Send To:" address. It then calls the FormResponse's getEditResponseUrl() method to get a URL which will edit this response, and wraps it up in an href element.

Finally, it calls the GmailApp sendEmail() method to send the email.

That's it; pretty clean and straightforward, once you know how Google Forms works.