Automatic email notifications for Google Docs Forms

I use Google Docs to collect data using the Forms feature. People can just fill out the form and data will be automatically entered into a spreadsheet for me. It really makes data collection easy in simple cases. For example, creating a contact form using Google Docs is fairly straightforward. Google can even notify you when someone submits a form. However, the notification feature is weak in that it doesn’t provide any useful or customizable data; it just lets you know someone submitted a form. Luckily, Google has a scripts feature. This mail merge feature shows how you can email a list of people individualized emails. To get more useful notifications, you can write a script that will run whenever submits a script. This is a very useful example. I’ll reproduce it here:

  <pre class="example">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] + “nn”; html += “

” + labels[col] + “:”

htmlVal = formVals[col].toString().replace(//g, “>”).replace(/[rn]/g, ” “); if(typeof(formVals[col]) == “string” && formVals[col].length > 100){ html += “

” + htmlVal + “

“; } else { html += htmlVal + “

“; } } }

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

The syntax is fairly obvious if you ever programmed before. I successfully modified the script for my own use.

About Vinh Nguyen

Statistician

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>