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.

Making an Index Using Javascript

Working with a faculty member we had a rather long page that was originally written in Google Docs. It had many sections that were (mostly) designated by H tags of various denominations. The goal was to and put it on a website quickly build an index of anchor links. I did not wish to do the index portion by hand.

With javascript things like this are relatively pleasant. You can see the whole thing in this codepen but I’ll break it down a bit below.

First we can get all the H tags with querySelectorAll.

let headers = document.querySelectorAll("h2, h3, h4, h5, h6")

I can console.log(headers) and I’ll see a NodeList of all the headers it found. I tend to work console.log all my variables as I go just to make sure it’s really happening the way I think it is.

My next move is to add an id to each of these headers so that we can navigate to them via anchor links. with this forEach loop each header will get an id of header-whatever number we’re on in the loop.

let i = 1;
headers.forEach(function(header) {
  header.id = "header-" + i;
  ++i;
});

Now that I have headers that I can link to as anchor links, I need to build the index and put it somewhere.

In this case it was easy for me to add a div to the source manually so I did. That will be where my index ends up.

    I’ll get that div as a destination.

    let indexHolder = document.getElementById('index');
    

    I’ll assign a variable (indexHtml) to hold a string of HTML which will be all the li tags with the links and titles.

    We’ll go back to our forEach (cleaned of other stuff for clarity) loop and use it to build out that HTML.

    let i = 1;
    let indexHtml = '';
    headers.forEach(function(header) {
      indexHtml = indexHtml + '<li><a href="#header-' + i + '">'+header.innerHTML+'</a></li>' ;
      ++i;
    });//
    indexHolder.innerHTML = indexHtml;
    

    Now that we have the string, we can assign it as the innerHTML of the index div we created earlier.

    indexHolder.innerHTML = indexHtml;
    

    The whole thing looks like this.

    let headers = document.querySelectorAll("h2, h3, h4, h5, h6")
    let i = 1;
    let indexHtml = '';
    let indexHolder = document.getElementById('index');
    headers.forEach(function(header) {
      header.id = "header-" + i;
      indexHtml = indexHtml + '<li><a href="#header-' + i + '">'+header.innerHTML+'</a></li>' ;
      ++i;
    });//
    indexHolder.innerHTML = indexHtml;
    
    

    Photography with Faculty

    IMG_1239.jpg

    I had the opportunity to work with Ryan Smith again recently. He’s been putting in serious work on on his website (Richmond Cemeteries) and is now turning a portion of that work into a book (Death and Rebirth in a Southern City: Richmond’s Historic Cemeteries). Ryan came by to talk a bit about pictures for the book which led to a field trip (Hebrew Cemetery and Shockoe Street Cemetery) and I think some useful reflections on how the balance between technology, technical proficiency, and art works together to make something interesting. It’s a bit of rambling tour of a series of issues that are specific to this task (getting high quality images of grave markers for a book) but are also illustrative of larger things.

    Basic Considerations

    Light

    Light matters quite a bit. When we looked through Ryan’s initial photos many of them were taken in very bright light. That’s good in some scenarios but leads to really hard shadows. In any photo, thinking hard about where the light is and how it falls will be key in creating the image you want. Usually you want the light behind you. Usually you want it to be soft.

    I showed up a little before sunrise but I didn’t have a shot list and I’d never visited the site before. That led to some mistakes or at least a poor use of time. I knew I wanted to get the “golden hour” light. That worked out ok but I didn’t really take into account the fact that the cemetery is on a hill. That led to the sun hitting one side very quickly. In the future, I’d take elevation into better account and work from East to West. I’d also visit the site once to figure out how I was going to work through the shots that were needed. You can see the better light in the first picture in this post and then how things become more harsh in the image directly below. The shadows from the markers in the foreground could be something artistic but they tend to distract from more functional/documentary images.
    IMG_1369.jpg
    Depending on what settings you have for the camera, you’re also dealing with how the light meter works. The camera manipulates aperture, ISO, and shutter speed to try to keep all the bright parts from being too bright and all the dark parts from being too dark. The larger the difference between the darkest portion and brightest portion, the harder it is to get them both within the spectrum captured. That how you end up with blown out skies (so bright it turns to white with no details) or with shadows that have no detail (just featureless black).

    You can manipulate where the light meter in the camera samples from to end up with more control. You can also take more control over all the settings. You can also take control over which things the camera can change. I suggest doing this gradually by using aperture priority and other camera settings so that you can focus on one variable at a time. This will help you build up room in your head to hold all the variables at once (while also worrying about angles, light, background, not falling off a cliff etc.).

    Also consider that you can manipulate light to a degree. You can use physical reflectors. That can be a piece of poster board or windshield shades. That’ll let you bounce light back to help light up the shadowed areas. Off-camera flash is another option but it’s far more expensive and opens up another world for consideration. That’s great if you want that but most people want easier and less choice. The Strobist would be my first step down that path if you were interested.

    Background

    Background awareness is a skill. It’s easy to get tunnel vision. You see something awesome and you take the picture. All your attention focuses on the object in the foreground. You end up missing the weird things in the background that distract other people. Step one is being aware of the background and then you start to integrate it with intention. There are a variety of ways to do that.

    Changing angles is one way to manipulate the background. People tend to take photos from their normal standing height. Take the gate below. I moved inside to shoot it against the less buy path. I did that by elevating the camera. It did some good things but also led to some things I didn’t like.
    IMG_1383.jpg

    I could also drop the lens really low and try to shoot against the sky. That works in some scenarios. It doesn’t work so well in this case. It ends up being a bit too dramatic and the dark magnolia trees end up obscuring things. That plus the loss of detail in the fence makes this kind of fun but not usable.
    IMG_1386.jpg

    Given our scenario, I opted to retry creating some more separation using the path and keeping in mind that these images would be black and white in the book. Many cameras will let you preview the images in black and white. If you’ve got that option it’s well worth changing the settings if that’s how your images are going to end up. You may be able to “see” your color images in black and white but I find it very difficult.

    This image is a fairly normal perspective and I did more post-processing work to get the background toned down. You can also see that I tightened up the crop. Another random tip, shoot wider than you need as you can also go back and trim it down but the reverse is not true.
    IMG_1384.jpg

    Depth of Field

    The other thing you can do to help create separation in images is to manipulate the depth of field. The shallower the depth of field the more the background will blur/bokeh. This is going to be mechanically limited by the lens. If a lens says it’s f2.8, that’s the widest aperture you have and will give you the shallowest depth of field. It also lets in the most light. You can now do more stuff with software and there are some additional fringe hardware options in the future but for most people it’s easier to do it initially. You can see that in many of these pictures.

    Hardware

    Taking pictures is not about the camera or lens except when it is. You can have really nice equipment and still fail to take good pictures but there are times when you cannot take the picture you want without the right equipment. Figuring out the kind of pictures you want to take and investing in a decent lens oriented towards that kind of image is a good idea. People smarter than me tend to prioritize lenses over camera bodies. You can certainly dwell too much on the hardware but I dislike just how easily some people say that “the hardware doesn’t matter.”

    There is an endless amount of gear you can pursue- bodies, lenses, off camera flash . . . it all makes sense in different scenarios with different budgets.

    Other Hardware

    But there are also other pieces of hardware that people don’t really think of- ladders for bird’s eye shots, poster board/car sunshade for reflecting light, maybe a selfie stick etc. That stuff is far cheaper than high quality lenses.

    Software/Post Processing

    It’s not clear to me what the lines are here. It’s interesting to look at these National Geographic rules to take note of all the things that they consider. There’s a lot there but they don’t mention things like correcting lens aberrations or correcting perspective. Does this make the image more real or is it digital fraud? I don’t know. Lots of stuff to think about and in an environment that will only get more complex.

    Stuff I Need to do Better

    Backwards Design

    I’m not used to having a focused academic goal with my photos. I usually shoot for my own amusement or towards some rough idea of being visually interesting. That’s not the same as shooting for a book. We’re not leading with an artistic idea, we’re leading with a point that Ryan is trying to illustrate. That idea of backwards design for photos was not something I approached in enough detail for this round. When we finished, we came back and looked at the original photos vs the new photos and talked through what was working and not working. That’s when the details came out that I should have figured bout better ahead of time.

    The angel series below makes for a good example. In the image below I captured the statue with the sun at my back. I framed it such that I cut off a portion of the pedestal and you’ll note that the side I captured has the broken off hand. So good for the light but bad for the statue. We did figure that out in the field so I took some shots from the other side.
    IMG_1342.jpg

    It turns out that the size of statue was also important. This shot deals with the missing hand but the position makes it hard to tell that the statue is large. It’s also moving towards artistic drama rather than a photo that represents what a visitor would see. It also came out that capturing the androgynous nature of the angel and some additional details were important. That’s all stuff I should have figured out ahead of time. I ended up with a variety of shots but none that really did a good job on all those elements.

    Untitled
    We came up with this after we came back. It is something I will think about for the future. It makes for a nice way to do planning document if pictures already exist. I think doing some phone scouting would also work pretty well. It’d give you a chance to go over the location and have something real to talk and work with.

    It may also be that we can’t do all those things at once. We may have to decide on the top goals. It’ll be difficult to get the detail of the face and the entire piece in the same shot. That aspect of having to choose only one image is much more of an issue for books than for the web. Digital would open up a range of possibilities and interaction options but that’s for another day . . .
    IMG_1351.jpg

    You can see a similar struggle with documenting the first marker in the cemetery. One goal was to make it legible but we also wanted to show the whole thing (including that it not flat). That was complicated by the fact that the left side was pretty tightly crowded with other markers. You can see in the original photo below that you’ve got a hard shadow from fairly harsh sun. You can also see the other markers intruding a bit.
    Untitled

    You can an attempt at the overhead view using the step ladder in this image. We saw on-site that it wouldn’t work because while it’s better for legibility it makes it two dimensional.
    IMG_1394.jpg

    Here we try framing the marker more tightly. We get the lower script and the rocks but we lose the writing in the upper portion. It gets the three dimensional aspect but we have the brick walk creeping into the corner. I’m not thrilled with the contrast in general. This is one I’ll probably reshoot.
    IMG_1423.jpg

    I feel like I learned a lot in this attempt. It has also further helped me hone what I share with faculty and how I try to scaffold their photography skill acquisition as they consider taking on these skills. This is extra work for them in most cases and work they take on in an entirely practical way. “What is the least they need to know?” is the question I must keep asking myself. “What is the easiest path?”

    Photography with Faculty

    IMG_1239.jpg

    I had the opportunity to work with Ryan Smith again recently. He’s been putting in serious work on on his website (Richmond Cemeteries) and is now turning a portion of that work into a book (Death and Rebirth in a Southern City: Richmond’s Historic Cemeteries). Ryan came by to talk a bit about pictures for the book which led to a field trip (Hebrew Cemetery and Shockoe Street Cemetery) and I think some useful reflections on how the balance between technology, technical proficiency, and art works together to make something interesting. It’s a bit of rambling tour of a series of issues that are specific to this task (getting high quality images of grave markers for a book) but are also illustrative of larger things.

    Basic Considerations

    Light

    Light matters quite a bit. When we looked through Ryan’s initial photos many of them were taken in very bright light. That’s good in some scenarios but leads to really hard shadows. In any photo, thinking hard about where the light is and how it falls will be key in creating the image you want. Usually you want the light behind you. Usually you want it to be soft.

    I showed up a little before sunrise but I didn’t have a shot list and I’d never visited the site before. That led to some mistakes or at least a poor use of time. I knew I wanted to get the “golden hour” light. That worked out ok but I didn’t really take into account the fact that the cemetery is on a hill. That led to the sun hitting one side very quickly. In the future, I’d take elevation into better account and work from East to West. I’d also visit the site once to figure out how I was going to work through the shots that were needed. You can see the better light in the first picture in this post and then how things become more harsh in the image directly below. The shadows from the markers in the foreground could be something artistic but they tend to distract from more functional/documentary images.
    IMG_1369.jpg
    Depending on what settings you have for the camera, you’re also dealing with how the light meter works. The camera manipulates aperture, ISO, and shutter speed to try to keep all the bright parts from being too bright and all the dark parts from being too dark. The larger the difference between the darkest portion and brightest portion, the harder it is to get them both within the spectrum captured. That how you end up with blown out skies (so bright it turns to white with no details) or with shadows that have no detail (just featureless black).

    You can manipulate where the light meter in the camera samples from to end up with more control. You can also take more control over all the settings. You can also take control over which things the camera can change. I suggest doing this gradually by using aperture priority and other camera settings so that you can focus on one variable at a time. This will help you build up room in your head to hold all the variables at once (while also worrying about angles, light, background, not falling off a cliff etc.).

    Also consider that you can manipulate light to a degree. You can use physical reflectors. That can be a piece of poster board or windshield shades. That’ll let you bounce light back to help light up the shadowed areas. Off-camera flash is another option but it’s far more expensive and opens up another world for consideration. That’s great if you want that but most people want easier and less choice. The Strobist would be my first step down that path if you were interested.

    Background

    Background awareness is a skill. It’s easy to get tunnel vision. You see something awesome and you take the picture. All your attention focuses on the object in the foreground. You end up missing the weird things in the background that distract other people. Step one is being aware of the background and then you start to integrate it with intention. There are a variety of ways to do that.

    Changing angles is one way to manipulate the background. People tend to take photos from their normal standing height. Take the gate below. I moved inside to shoot it against the less buy path. I did that by elevating the camera. It did some good things but also led to some things I didn’t like.
    IMG_1383.jpg

    I could also drop the lens really low and try to shoot against the sky. That works in some scenarios. It doesn’t work so well in this case. It ends up being a bit too dramatic and the dark magnolia trees end up obscuring things. That plus the loss of detail in the fence makes this kind of fun but not usable.
    IMG_1386.jpg

    Given our scenario, I opted to retry creating some more separation using the path and keeping in mind that these images would be black and white in the book. Many cameras will let you preview the images in black and white. If you’ve got that option it’s well worth changing the settings if that’s how your images are going to end up. You may be able to “see” your color images in black and white but I find it very difficult.

    This image is a fairly normal perspective and I did more post-processing work to get the background toned down. You can also see that I tightened up the crop. Another random tip, shoot wider than you need as you can also go back and trim it down but the reverse is not true.
    IMG_1384.jpg

    Depth of Field

    The other thing you can do to help create separation in images is to manipulate the depth of field. The shallower the depth of field the more the background will blur/bokeh. This is going to be mechanically limited by the lens. If a lens says it’s f2.8, that’s the widest aperture you have and will give you the shallowest depth of field. It also lets in the most light. You can now do more stuff with software and there are some additional fringe hardware options in the future but for most people it’s easier to do it initially. You can see that in many of these pictures.

    Hardware

    Taking pictures is not about the camera or lens except when it is. You can have really nice equipment and still fail to take good pictures but there are times when you cannot take the picture you want without the right equipment. Figuring out the kind of pictures you want to take and investing in a decent lens oriented towards that kind of image is a good idea. People smarter than me tend to prioritize lenses over camera bodies. You can certainly dwell too much on the hardware but I dislike just how easily some people say that “the hardware doesn’t matter.”

    There is an endless amount of gear you can pursue- bodies, lenses, off camera flash . . . it all makes sense in different scenarios with different budgets.

    Other Hardware

    But there are also other pieces of hardware that people don’t really think of- ladders for bird’s eye shots, poster board/car sunshade for reflecting light, maybe a selfie stick etc. That stuff is far cheaper than high quality lenses.

    Software/Post Processing

    It’s not clear to me what the lines are here. It’s interesting to look at these National Geographic rules to take note of all the things that they consider. There’s a lot there but they don’t mention things like correcting lens aberrations or correcting perspective. Does this make the image more real or is it digital fraud? I don’t know. Lots of stuff to think about and in an environment that will only get more complex.

    Stuff I Need to do Better

    Backwards Design

    I’m not used to having a focused academic goal with my photos. I usually shoot for my own amusement or towards some rough idea of being visually interesting. That’s not the same as shooting for a book. We’re not leading with an artistic idea, we’re leading with a point that Ryan is trying to illustrate. That idea of backwards design for photos was not something I approached in enough detail for this round. When we finished, we came back and looked at the original photos vs the new photos and talked through what was working and not working. That’s when the details came out that I should have figured bout better ahead of time.

    The angel series below makes for a good example. In the image below I captured the statue with the sun at my back. I framed it such that I cut off a portion of the pedestal and you’ll note that the side I captured has the broken off hand. So good for the light but bad for the statue. We did figure that out in the field so I took some shots from the other side.
    IMG_1342.jpg

    It turns out that the size of statue was also important. This shot deals with the missing hand but the position makes it hard to tell that the statue is large. It’s also moving towards artistic drama rather than a photo that represents what a visitor would see. It also came out that capturing the androgynous nature of the angel and some additional details were important. That’s all stuff I should have figured out ahead of time. I ended up with a variety of shots but none that really did a good job on all those elements.

    Untitled
    We came up with this after we came back. It is something I will think about for the future. It makes for a nice way to do planning document if pictures already exist. I think doing some phone scouting would also work pretty well. It’d give you a chance to go over the location and have something real to talk and work with.

    It may also be that we can’t do all those things at once. We may have to decide on the top goals. It’ll be difficult to get the detail of the face and the entire piece in the same shot. That aspect of having to choose only one image is much more of an issue for books than for the web. Digital would open up a range of possibilities and interaction options but that’s for another day . . .
    IMG_1351.jpg

    You can see a similar struggle with documenting the first marker in the cemetery. One goal was to make it legible but we also wanted to show the whole thing (including that it not flat). That was complicated by the fact that the left side was pretty tightly crowded with other markers. You can see in the original photo below that you’ve got a hard shadow from fairly harsh sun. You can also see the other markers intruding a bit.
    Untitled

    You can an attempt at the overhead view using the step ladder in this image. We saw on-site that it wouldn’t work because while it’s better for legibility it makes it two dimensional.
    IMG_1394.jpg

    Here we try framing the marker more tightly. We get the lower script and the rocks but we lose the writing in the upper portion. It gets the three dimensional aspect but we have the brick walk creeping into the corner. I’m not thrilled with the contrast in general. This is one I’ll probably reshoot.
    IMG_1423.jpg

    I feel like I learned a lot in this attempt. It has also further helped me hone what I share with faculty and how I try to scaffold their photography skill acquisition as they consider taking on these skills. This is extra work for them in most cases and work they take on in an entirely practical way. “What is the least they need to know?” is the question I must keep asking myself. “What is the easiest path?”