Send an Email When a Google Form is Submitted

I'm building a new website to save my department from the terror that is Drupal.  I've used RapidWeaver, which I highly recommend for all you Mac users out there.

We need to have web forms that can be submitted for various things- being added to our mailing list, reserving a meeting space, inquiries about applications, etc.

Being married to a Google software engineer, I think to myself, "Easy!  I'll create Google Forms and embed them into my webpage.  No PHP, no database, cloud storage, easy to edit, it will be great!"

The creation of the forms and embedding them into the web page was easy.  An unforeseen issue, however, was the fact that each form on the website corresponded to a different staff member's job.  There's currently no easy way through the Forms interface to set up an automatic email to an outside email address when a user submits the form.

Today, though, I did manage to find a way to duct tape a solution together.

(Full Disclosure: I am NOT a software engineer and I did NOT write this code myself.)

First, I found a help page that described the code to insert in the script editor of the spreadsheet where the form's data gets stored:

Use Tools>Script Editor in the new Forms interface to pop up this screen.

Here is the code:


function emailAlert() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var SS = Spreadsheet.getSheets()[0];
  var submittedData = SS.getRange(SS.getLastRow(), 2, 1, SS.getLastColumn()-1).getValues();
  var headerData = SS.getRange(1, 2, 1, SS.getLastColumn()-1).getValues();
 
  var yourEmail = 'email';//Change it to yours
  var emailSubject = 'Form Submission';//Change as per your requirement
  var htmlBody ='<table><tbody>';
  for(var i=0; i < SS.getLastColumn()-1; i++){
    htmlBody += '<tr><td>'+headerData[0][i]+'</td><td>'+submittedData[0][i]+'</td></tr>';
  }
  htmlBody += '</tbody></table>';
  MailApp.sendEmail(yourEmail, emailSubject, 'body', {htmlBody : htmlBody});
}



I was super proud of myself that I even got the script to run at all.  But then I was like, "Wait, it needs to run automatically when the form is submitted!  Crap!"

So Scott pointed me to another help page that describes setting up triggers for a specific event.  You can have the script run automatically whenever the form is submitted:

Select "Current script's triggers..." to add a new trigger to this script.

The last pull-down menu allows you to select "On Form Submit."

On testing, this combo has worked beautifully!  I am certain that I'm not the only person in the world who needs this kind of functionality, and I find it interesting that Google doesn't have all of this information in one easy-to-find place!