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.

Google Sheets Data Flow

This is a pretty specific thing but the concepts ought to be broadly applicable and interesting for the 3 to 5 people who will end up reading this. It’s a fairly amusing blend of less standard Google Functions and a bit of Google Script to do something fairly decent that had been quite a bit of hassle to do previously.

We have Social Work students who are assigned to various supervisor/liaison people. There are a lot of students. We wanted students to be able to submit a form to Google Drive and we’d keep track of all this and show only the relevant data to the various supervisors.

Files from Form

Setting up a form that requires you to be logged in and accepts files is now very easy in Google Forms. It also remains easy to log that information to a spreadsheet associated with the form.

Merging the Data

The student email address became the unique ID that would allow us to tie the form submission to the list of students and their programs, liaisons etc. Now we needed a formula to link these two sheets via email. I started with =VLOOKUP but that would have required the student email to be the leftmost column in the data and that would be awkward for other things. After some banging around and general bad attitude on my part I found =MATCH. MATCH doesn’t care about the order and returns the row where the match occurs. That formula =MATCH(H2,’Form Responses 1′!D:D,0) let me build a few others to pull in what I wanted like this =INDIRECT(“‘Form Responses 1’!A”&I2). Note that to use other cell values as variables in other cell functions you need to use =INDIRECT. This ended up getting me the time of submission for the file and a link to the file.

Creating the Individual Views

Next up I needed to create views for 24 different people who were associated with various students. To get this I used the =UNIQUE function on the column holding their names. I didn’t want to do this by hand because I don’t like to do boring things. I also needed to figure out the function that would pull in the correct data from the combined sheet and prevent people from accidentally messing up that function.

The formula ended up being a combination of =IMPORTRANGE and =QUERY. The following function did the trick.

function makeSupervisorFormula(sup){
  var supervisor = "'"+sup+"'";
  var formula = '=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_FILE_ID_HERE/","student list!A:K"),"SELECT * WHERE Col1=' + supervisor +'")';
  return formula;
}

Now I needed to create a spreadsheet for each person, name it, set cell A1 to be that function, protect it, and then put all the spreadsheets in the same folder.

function makeSupervisorSheet(sup){
  var ssNew = SpreadsheetApp.create(sup);//make new ss
  var id = ssNew.getId();
  var sheet = ssNew.getSheets()[0];//get first and only sheet

  var cell = sheet.getRange("A1");//get cell A1
  var formula = makeSupervisorFormula(sup);
  cell.setFormula(formula);//set our formula via the function above
  var protection = cell.protect().setDescription('Sorry this links elsewhere.');//protect this with the emails for users defined below
  protection.addEditor('foo@vcu.edu');
  protection.addEditor('bar@vcu.edu');

  var file = DriveApp.getFileById(id);//put it all in the same folder
  var folder = DriveApp.getFolderById('YOUR_FOLDER_ID_HERE');
  folder.addFile(file);
}

Now we just needed to run all this through an array containing the names of the supervisor people.

function allSups(){
  var sups = ['foo','bar','buzz'];//your supervisors here . . .   
 sups.forEach(function(sup){
  makeSupervisorSheet(sup);//do that work
});
}

And presto, a whole bunch of files that do what we want. The one hassle is that it seems you have to approve the =IMPORTRANGE function by hand from the created sheet.