Automatic email notifications for Google Docs Forms

Internet
Author

Vinh Nguyen

Published

October 9, 2011

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:

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.