Google Docs Forms: Automatic Email Notifications

Forms with Google Docs

In June I wrote a tutorial detailing how I use Google Docs for my own Contact Me form. The form allows readers to submit questions to which I may reply privately in my own time. I think this adds to reader experience and I've answered several submitted questions already. I've even made posts as a result of one or two. The mentioned tutorial showed you how to be notified of a form submission by email, but this was just very basic stuff. I've since come up with a much better way that I'm ready to share.

Detailed Email Notifications 

Google Docs automatic notifications only include a very basic level of information simply telling you someone has submitted a form. You must then visit the document to see what information was sent and it's stored in a fairly unattractive spreadsheet. Although this works as a bare minimum measure it's hardly an ideal way to interact with readers. I actually had to build responses to include the submitted form manually. I knew there was a better way. So, I took some time to dig through Google Apps Script documentation and built a script to improve the process.

I've created a script which automatically processes form submissions into both text and HTML friendly versions, then sends it off to all Spreadsheet Collaborators email. Each form entry is labeled by column label, then placed on its own line. Everything a reader submits I automatically receive in a pleasingly formatted email on my Google account. This saves a great deal of time and makes writing a response much easier. Note: The following tutorial assumes you've already created a Form using Google Docs. If you have not please review the previous tutorial.

Adding the Script to your Form

 Begin by logging into your Google Docs account, then click on the Form you'd like to receive automatic updates for. Next choose the Tools->Script Editor option then click on Create a new project. You'll now have an empty script project with a function named myFunction. You should change myFunction to something meaningful, for example sendEmail. Now, copy and paste the code provided below into the function placing it between the { and } characters.

var formSheet = SpreadsheetApp.getActiveSheet();
var firstRow = 2; //Skip a row for column labels
var lastForm = formSheet.getLastRow();
var lastCol = formSheet.getLastColumn();
var flagCol = null;
var subject = formSheet.getName() + " Form";
var mailTo = "";
var collaborators = SpreadsheetApp.getActiveSpreadsheet().getCollaborators();
for(var i=0; i < collaborators.length; i++){
    mailTo += collaborators + ",";
  }
  var labels = formSheet.getRange(1, 1, 1, lastCol).getValues()[0]; //get column Labels

  for(var i=0; i < labels.length; i++){ //Find which column contains the Sent Flag
    if(labels[i] == "Email Notice Sent"){
      flagCol = i;
      break;
    }
  }  
  if(flagCol === null){//Sent Flag Col not found, create it
    formSheet.insertColumnsAfter(lastCol++, 1);
    formSheet.getRange(1, 1, 1, lastCol).getCell(1, lastCol).setValue("Email Notice Sent");
    SpreadsheetApp.flush();
    flagCol = lastCol;
  }

  for(var i = lastForm; i >= firstRow; i--) { //Loop through forms, last first
    var form = formSheet.getRange(i, 1, 1, lastCol);
    var formVals = form.getValues()[0];  
 
    var message = "";
    var html = "";
 
    if(formVals[0] != "" && formVals[flagCol] != true){ //Skip empty and email sent rows
      for(var col=0; col < lastCol; col++) { //Build the message
        if(col != flagCol){
          message += labels[col] + ": " + formVals[col] + "\n\n";
          html += "<div style='margin-bottom: 10px;'>" + labels[col] + ": ";
     
          htmlVal = formVals[col].toString().replace(/</g, "&lt;").replace(/>/g, "&gt;").replace(/[\r\n]/g, "<br>");
          if(typeof(formVals[col]) == "string" && formVals[col].length > 100){
            html += "<p>" + htmlVal + "</p></div>";
          }
          else {
            html += htmlVal + "</div>";
          }
        }
      }
 
      GmailApp.sendEmail(mailTo, subject + ": " + formVals[1], message, {"htmlBody": html});  
      form.getCell(1, flagCol+1).setValue(true);    
      SpreadsheetApp.flush();  // Make sure the cell is updated right away in case the script is interrupted    
    }
  }

Triggering the Script and Finishing up

Finally, choose the menu option Triggers->Current Project's Triggers then click on Add a new trigger. Choose the name of your function(sendEmail for example) in the first box, from spreadsheet in the second and finally On Form Submit in the third. Save your changes, then save your script and it's done. From now on each time a reader submits that form, the spreadsheet collaborators are automatically notified by email with a detailed copy of the submitted form.

Conclusion

Email form notifications are a wonderful tool to improve correspondence with interested visitors. It saves a great deal of time and effort, while structuring the forms in a meaningful useful format. You even gain redundant copies of each message sent this way. Once setup you'll quickly and comfortably interact with forms through your Gmail account all for free. No need for 3rd party form accounts, just use Google with Apps Script. Give it a try and let me know how it works for you. I'm very happy with my own results thus far.

Support JSBlogStop:

Donate with Dwolla