Well not really but I’ll explain it anyway.
Gravity to Google
There are a number of plugins that tie Gravity Forms to Google Sheets but most (all?) of them rely on the ability to create an application to get authentication tokens. Many institutional accounts turn off that capability. VCU has turned that off.
As a result, I ended up going the more manual route with this Google Script integration. Note the Martin Hawksey mention! I won’t re-write their documentation here but it does work and it’s relatively easy to set up.
Counting Unique Things
Now that we had our data showing up, I wrote some stuff to summarize it. Nothing too fancy, but I do find myself using =sort(UNIQUE(Sheet1!E2:E)) which gets the unique values in column E and sorts them and then doing a =COUNTIF(Sheet1!E:E,”=”&A2) to count how many times those unique values show up.
That all works pretty well for a summary look but there was also a desire for monthly data. That’s complicated by the data being added live on a continuous basis and the date field coming in with a full date/time stamp. I considered, and attempted, a variety of paths and ended up using =query. It works well and I thought some of the details were kind of neat. I also knew that I would forget them if I didn’t write it down.
Getting just this month’s data
=query(Sheet1!A1:H,”select * WHERE H>=date'”&to_text($A$2)&”‘ AND H<=date'"&to_text($B$2)&"'")
This is the query that gets me the current month’s data no matter what month it is. As cleaner version of the query looks like this select * WHERE H>=date’2020-2-1′ AND H<=date'2020-2-29'"). It’s going to get all (that’s the * symbol) rows where column H’s date is equal to or between Feb. 1 and Feb. 29.1 To get the first and last day of the month and year without having to change it, I used the following functions. Start of the month =EOMONTH(today(),-1)+1 and the end of the month is =eomonth(today(),0). Note that when I use them as variables in the upper example, I’m using to_text to keep them from being treated as Google Sheets date variable.
This saves the manager of one of our programs a chunk of time every month. It frees her up to do more important work and, probably more importantly, doing it let her know someone wanted to make her life easier. Digital workflow creation can be an act of caring.
1 You can chant some bizarre thing about 30-days-hath-something-or-another at me all you want. I will never know nor care what months have what number of days.