Creating short links to customized forms and an auto-updating dynamic data dashboard

At Vermont Afterschool contracted trainers provide up to a total of 80 or more workshops throughout the state in a given year. There is a standard evaluation form for all workshops, and up until a few years ago they were all collected via paper forms. Paper forms made aggregating the data was extremely time consuming and prone to errors. This needed to change. I wanted training participants to be able to submit online evaluation forms AND have the data automatically populate a data dashboard AND have the dashboard be dynamic - ie, our staff could filter the data based on various criteria. For example, want to see the evaluation results for the workshops given by a particular trainer in FY17? Done.

Here's an image of the end result:

And here is an explanation of the many steps I used to make this all happen:

Step 1: Creation of online form in Cognito Forms

Perhaps the most straightforward step was to create the online form. In addition to the evaluation questions in the form, there is basic information that the respondents need to complete, like the name of the workshop, the name of the trainer, the location, and the date. And these pieces of information are crucial for the data dashboard at the end, especially since I want users to be able to filter the results by any of these categories. Since the list of workshops and trainers is ever-changing, drop-down lists would not have been sufficient. Also, free response items would not ensure that data would have been entered accurately. Even slight spelling variations would have prohibited the filtering features to function properly on the dashboard.

I needed a way for each form to look different, depending on the workshop. I needed the workshop, trainer, location and date fields to be pre-filled. Enter Step 2.

Step 2: Google Scripts and Firebase links to create links for surveys with specific pre-filled fields

In Cognito Forms, it is possible to encode URLs to create pre-filled forms. I set up a Google Sheet so that specific Cognito links could be created for upcoming workshops. The resulting URL would look something like this:

https://www.cognitoforms.com/VermontAfterschool/VermontAfterschoolTrainingEvaluation?entry={"Training":"Youth Leadership Development with UVM 4-H","Location":"Chittenden","Instructor":"Example Schwab","Date":"2018-5-21","Training2":"Youth Leadership Development with UVM 4-H","Location2":"Chittenden","Instructor2":"Example Schwab","Date2":"2019-5-21"}

A URL such as this one will effectively pre-fill the Cognito Form, but is terribly long. I wanted a short URL for workshop instructors to put in a PowerPoint slide at the end of a presentation and have participants complete on their phones right at the completion of the workshop. That's where Google Firebase links and a script came into play. Rather than have to enter each long URL into a URL shortener and then copy it and save it somewhere, I entered a script in Google Sheets so that a function could easily shorten the long auto-generated link.

Back in the spreadsheet, basic information could be entered about a workshop could be entered (through drop-down menus that could be updated), then the long Cognito link would be auto-generated, and finally a function run by the script would call out to Firebase and generate a short link in the format of the one below. Beautiful.

https://vermontafterschool.page.link/ + four randomly-generated characters

In Cognito, a customized link would lead to a Cognito Form with pre-filled fields. And I set up the Cognito Forms so those pre-filled fields would be hidden and ultimately populate "content fields" so that they could not be changed by the participant completing the evaluation form.

Step 3: Creation of a Zap in Zapier to send form entries over to a new Google Sheet

Zapier is an awesome tool for integrating web applications. "Zaps" are the workflows that start with a trigger; in this case, the trigger was a new form submission. I set up a Zap so that whenever an evaluation form would get submitted, the data would be automatically sent over to a Google Sheet. This particular Sheet (a different one from the one that generated the shortened links) would become the location of the dashboard.

Step 4: The QUERY function and set up of the dashboard in Google Sheets

With all of the evaluation data now nicely flowing straight into a Google Sheet I could work to set up the dynamic dashboard. I don't ever really need to worry about cleaning the data since I know that workshop titles, trainers' names, dates and locations of the workshops all will have been entered into each evaluation form through the pre-filled link as specified by us. So the data in the sheet is ever-ready to auto-populate a dashboard.

First, I set up the look of the dashboard. The main aesthetic choices I made to make it look more dashboard-like than spreadsheet-like were to remove the grid lines and set up doughnut charts with logical color gradients. I also created images of the text blocks for inside the charts so that way I could center them right inside the doughnut charts (which otherwise would not have been possible).

For the user interface, I created a few drop-down menus at the top to serve as filters. I used the data validation feature to turn ordinary cells into drop-down menus. The data validation feature allows you to create a drop-down list from a range of data somewhere else in the sheet.

So I could have simply pointed to the column of say, trainers, from the raw data that had been sent to the Sheet via Zapier. But that would have given me an un-alphabetized list of the trainers with likely duplicates listed. And I wanted an alphabetized list of trainers each listed once. Enter the QUERY function. I referred a lot to this blog post written by Ben Collins to familiarize myself with the QUERY function and how to use SQL statements to filter data. With the function, you can specify a range of data, select particular columns, sort it and use WHERE statements to act as filters. It's really super powerful. I had two main uses for the query function for this project:

1. To populate drop-down menus for filters for the user interface of the dashboard (as described above); I wrote a series of QUERY statements to summarize and alphabetize the data as it came in from Zapier. I was even able to get fancy enough and make drop-down options change to only relevant fields based on other selected filters. So or example if a workshop titled "Basic Specialized Care" is selected, the locations available to select from would be filtered so that only those towns where that workshop was given would be listed.
2. To create a table (in its own tab of the Google Sheet) that the doughnut charts would be based on. The WHERE statements would point to the user-specified fields at the top of the dashboard (ie. the filters) to filter the data by the desired parameters. Pretty neat!