Gravity Forms Notification to Google Spreadsheet

The idea that data can flow to different places for different purposes is one of the key concepts I want people to believe in. Different technologies and different interfaces have different affordances depending on what you’re trying to do.

In this case, we’ve built some online training for students. As part of that training they need to sign off indicating they read various rules and safety advice. We’re using Gravity Forms to collect that information. We’re going to set a special notification email that’s easier to parse in addition to the regular email that gets sent out (that one is oriented towards student confirmation and alerting the individual faculty).

Gravity Forms Notification

We’re just going to put the student email and faculty email in the subject line with a space between them. I did some fancier stuff early but went back to this when I realized what we were doing just wasn’t complex enough to justify extra drama. I set the from name to Health Hub Logger so it’d be easier to write the filter in GMail.

Notifications in Gravity Forms are pretty straight forward but you can find out more on their site.

Screenshot of the Gravity Forms notification interface indicating that the students and faculty email are in the subject line of the email via variables.

GMail Filter

Screenshot of gmail filter interface showing that I want these emails marked as read and labeled as "HealthHub".
I then setup a filter in GMail so that I could be confident that the Google Script could find these emails and that I would not really see them. I search for anything from the Health Hub Logger name and make it as read and label it with the “HealthHub” tag.

Google Script

Now comes the Google Script. I open a spreadsheet. Go to Tools>Script Editor and put in the following. After that, I set the trigger in the Google Script editor interface to run every 15 minutes.

function healthHubLogger() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
 var today = new Date();
 var dd = today.getDate()-1;
 var mm = today.getMonth()+1; //January is 0 DO NOT FORGET THIS
 var yyyy = today.getFullYear();
 var yesterday = yyyy + '/' + mm + '/' + dd;
  
  var query = "after:" + yesterday  + " label:HealthHub";// not necessary to restrict date really but I figure it's faster -- note the HealthHub label
  
  var threads = GmailApp.search(query);
  
  var allSubjects = sheet.getRange("C:C" + sheet.getLastRow()).getValues();
  var flatSubjects = allSubjects.map(function(row) {return row[0]});
  
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    //Logger.log(messages);    
    for (var m = 0; m < messages.length; m++) {
       var healthLog = [];
     
      var from = messages[m].getFrom();
      var to = messages[m].getTo();
      var time = messages[m].getDate();
      var subject = messages[m].getSubject();
      var student = subjectSplitter(subject,0);
      var faculty = subjectSplitter(subject,1);
      var mId = messages[m].getId();
      
      var mYear = time.getFullYear();
      var mMonth = time.getMonth()+1;
      var mDay = time.getDate();
      var messageDate = mYear + '/' + mMonth + '/' + mDay;
      if(flatSubjects.indexOf(subject) < 0 ) { 
        healthLog.push(from);
        healthLog.push(time);
        healthLog.push(subject);
        healthLog.push(student);
        healthLog.push(faculty);
        healthLog.push('https://mail.google.com/mail/u/0/#inbox/'+mId);
        sheet.appendRow(healthLog);      
     }
    }

  }          

}


//split subject line
function subjectSplitter(subject,num){
  var emails = subject.split(" ");
  return emails[num];
}

Now all that I need to do is share the spreadsheet with the program administrators and they have an easy way to see what’s what without having to go into WordPress or get any additional accounts.