Thursday, October 24, 2013

Creating an Automatic Emailer from a Form Submission

I'm very happy that I could impress myself and solve a little problem at the same time: Raising two kids requires large amounts of communication and more time than is in the day.  I put together a little Google Apps Script (taken mostly from tutorials and that will save me time and allow some speedy communication of important kid-facts.
The problem was simple and solvable in several low-tech ways--some less clunky than others--however, this high-tech method is transferrable to education (hence my post on this blog).

I believe I could have used something like the autocrat script to get the same result.  But the way I did it allowed me to learn some javascript code.

Here's the original problem: I have two kids, so I look after one in the morning and my wife looks after the other.  She needs to know what I took care of in the morning and at night with that kid so nothing important gets missed each day.  This is pretty complex depending on how sick or fussy or tired each child (and each parent!) is that day.  Asking single questions for each topic took up too much time.

I solved this using a google form, reachable from my phone, and a script that emails both my wife and I when I submit the form.  The email displays a simple table with information on what I did with our child that day.

Below I've attached the code. If anyone knows how to pull the headers off of a Google Sheet and turn them into an array in javascript, that would be useful (in the "Properties" variable in line 5).

This can be used in education very simply if you have a tracking form that students fill out, it can email the whole team and/or mom and dad to inform how often a student is submitting the form.  For example, if they are coming in for after school tutoring and you need to track and document that.  I can see some ways to use this type of thing for back to school night with parent meeting requests as well.  Another functionality that Google allows is to trigger the script (i.e. send the email) on a time trigger (e.g. once a day) or on edit if the spreadsheet is edited.  This could be useful for collaborative writing projects in an English class or as a summary email for a project where you are tracking some kind of form-submitted data.  Basically, anything you used paper for 15 years ago you could collect frequency data on and get an email.  I could think of how to use this type of script in a small business as well.

Autocrat is, of course, a much more robust script that could be used more flexibly.  With this project, I was able to customize it for my uses and pick fancy colors for my email, etc.  Someone with more time than me (i.e. the website club at school) could expand this short code easily and add in some graphics to support the school. That would make a pretty neat looking response email.

Here's the code:
//Thanks to this code:
function OnSubmit(e) {
  var ownEmail = ""
  var NewSubmission = {}
  var Properties = ["DateTime","Time of Day","Data1","Data2","Data3","Data4","etc."];

  for(var i in Properties){
    NewSubmission[Properties[i]] = e.values[i];

  var table = "<table style=\"border:2px solid black;width:500\"><thead><tr><th colspan=\"2\">What did I do today?</th></tr></thead><tbody>";
  var j = 0;
  for(var Properties in NewSubmission){
    if (j % 2 == 0) {
      table += "<tr  style='background-color:orange'><td>" + Properties + "</td><td>" + NewSubmission[Properties] + "</td><tr>";
    } else {
      table += "<tr style='background-color:black'><td style='color:white'>" + Properties + "</td><td style='color:white'>" + NewSubmission[Properties] + "</td><tr>";
  table += "</tbody></table>"
  var emailText = "Oh hey there!" + "<br/>" + "Here's what we did this " + NewSubmission["Time of Day"] + ":" + "<br/>" + table + "<br/>" + "<br/>Love from FormBot"
  var advancedArgs = {htmlBody:emailText};
  MailApp.sendEmail(ownEmail, "Daily Digest",
  MailApp.sendEmail("", "Daily Digest",