Live Tracking Dashboard Built With Google Forms and Sheets
Technology and art seldom go together, but when they do, it can be a sight to see.
Lisbon, 2018. A festival was bringing together performance artists from around the world for a multi-day showcase. With an expected audience of 20,000, the operations team brought in several volunteers and booked the largest arena in Portugal for the event. As the event got closer, and the showtime logistics became increasingly complicated, a lingering question was on everyone’s mind: how do we keep track of several people across a large venue all at the same time?
The team had brought in many people, but wasn’t inclined to spend money on tracking software. The steps to prepare an artist to be on stage couldn’t be trimmed down (makeup is hard to skip), the artists spoke a variety of languages, and the volunteers could only be briefed on-site. So how do we make a tool that’s easy to use, easy to customize, and can be built for free? Enter Google.
In and Out
Our problem was broken down into two segments: we needed accurate data about artists’ locations to come in quickly and we needed to present that data in an easy-to-understand way. Let’s begin with the first part.
Where Did They Go?
After sitting with the problem, the essential information presented itself: who is checking in where and at what time? Building a Google Form that asked the first two questions was straightforward, as the timestamp is already attached to each response. Each artist (or artist group, in some cases) had a given ID, and the steps each artist had to go through were pre-defined.
A volunteer at each step would submit the form when an artist arrived at that location. The next piece was presenting the resulting information as something prettier than a series of rows.
There They Are
Arguably the most important piece of the dashboard was the timestamps showing when each artist arrived at each location. We wanted to ensure that, as soon as a volunteer submitted the form for any artist, the dashboard would update quickly. After various iterations and Google searches, the Sheets magic came together.
The first step was pre-processing the form data. With CONCATENATE, two text fields were transformed into one that described the new status of the artist. The time response from Column A was extracted to Column E, which becomes helpful later in the process.
The steps each artist had to complete before going on stage was like a checklist, so we wanted to see a progress bar for each artist, from checking in to the venue to the moment they were offstage. With that, the dashboard’s design was born.
The second element was priming the dashboard to change when new form data arrives. Each cell represents a specific location and artist combination, so the formula in each cell are looking for that specific combination in the processed form data. As an example, check out the formula in top left cell (Bangladesh Dance — Initial Check In):
IF(ISNA(VLOOKUP(CONCATENATE($B6,” — “,F$5), Necessary!$D:$E, 2, 0)), ” “, VLOOKUP(CONCATENATE($B6, ” — “, F$5), Necessary!$D:$E, 2, 0))
Naturally, this looks like more Sheets noise than anything else, so let’s break it down bit by bit, starting with the CONCATENATE portion.
CONCATENATE($B6, ” — “, F$5)
This formula collects artist information from B6, location information from F5, and joins them with a dash, generating the phrase:
C1 - Bangladesh Dance - 1 - Initial Check In
Next, the VLOOKUP formula uses this new phrase in Necessary (where incoming form responses are processed):
VLOOKUP(CONCATENATE($B6,” — “,F$5), Necessary!$D:$E, 2, 0)
VLOOKUP finds the phrase in the processed form data and returns the time it was submitted (if it finds the phrase). In the case of the Bangladesh Dance group, a form submission resulted in the output below:
The VLOOKUP formula looked for the phrase of interest in Column D and, as soon as the phrase appeared, returned the time of submission from Column E to the dashboard, sparking a change on the dashboard:
Needless to say, the first time this worked, I was outrageously happy.
If the form response has not come in yet (meaning the artist hasn’t arrived at that location), VLOOKUP returns an invalid message (#N/A) and that’s not pretty. To avoid the jarring effect of an error message, the rest of the formula cleans up the output.
ISNA(VLOOKUP(CONCATENATE($B6, ” — “, F$5), Necessary!$D:$E, 2, 0))
ISNA capture the output of VLOOKUP and returns whether or not it is invalid; so when the return value is a real value, it returns FALSE.
IF(ISNA(VLOOKUP(CONCATENATE($B6, ” — “, F$5), Necessary!$D:$E, 2, 0)), ” “, VLOOKUP(CONCATENATE($B6, ” — “, F$5), Necessary!$D:$E, 2, 0))
Finally, the IF statement checks the result of the ISNA formula. If the ISNA reports TRUE (meaning VLOOKUP is returning an invalid value), the presented value is blank space (written as “ “). If ISNA reports FALSE (meaning there’s a timestamp to show), the resulting value is the output of the VLOOKUP.
Is this formula the prettiest or neatest way to achieve this task? Not at all. Did it work? You bet.
Upgrades
Every so often, new features were added due to the changing nature of logistics. One particular feature born halfway through the event was the “Current Location” column. Artists would not always follow the steps in the order we arranged; makeup would occasionally run late, so artists would be redirected to pick up their instruments and props instead. While this helped avoid delays, our progress bar was not exactly a complete bar anymore.
To determine where an artist was, regardless of rerouting or delays, we needed to know their current location. Before the rerouting, we focused on the question “Which preparation step is an artist on?” We then reframed the question into “Where did an artist most recently check in?” This required us to look at the timestamps of submissions, which, fortunately, were already presented on the dashboard. The formula below took advantage of this captured data:
=IF(ISNA(INDEX($F$5:$M$5,0,MATCH(MAX(F6:M6),F6:M6,0))), ”Inbound”, INDEX($F$5:$M$5,0,MATCH(MAX(F6:M6),F6:M6,0)))
Once again, this looks more terrifying than it really is. If the goal is to determine where an artist last checked in, then finding the MAX (hint, hint) value of the timestamps was the first step, as it returns the most recent timestamp.
MAX(F6:M6)
The MATCH formula returned the column this timestamp belonged to; if the most recent timestamp were at the third step in the process, it would return the number 3.
MATCH(MAX(F6:M6), F6:M6, 0)
If the number of the step is known, then all that’s left is the name of the step itself. INDEX acquires this once we specify where all the names are listed:
INDEX($F$5:$M$5, 0, MATCH(MAX(F6:M6), F6:M6, 0)))
Last, but not least, to avoid any invalid values or error messages from appearing, the IF and ISNA formulas are reutilized to ensure the output of the cell is always clean. If there are no timestamps listed in the artist’s row at all, then the formula returns the phrase “Inbound” until the artist arrives at the venue.
=IF(ISNA(INDEX($F$5:$M$5, 0, MATCH(MAX(F6:M6), F6:M6, 0))), ”Inbound”, INDEX($F$5:$M$5, 0, MATCH(MAX(F6:M6), F6:M6, 0)))
Again, this is probably not the cleanest possible version of the formulas, but it did achieve its purpose.
Pros and Cons
Like any other tool, the system had its vices. It purely depended on Wi-Fi and, if a volunteer’s device was malfunctioning, there were delays. Another issue was artists getting lost at the venue; given the arena’s size, lack of maps, and language barriers, finding an artist sometimes required taking a quick jog around the site.
However, the pros arguably outweighed the cons. The dashboard was easy to observe on any phone or tablet and updated in near real-time. Errors were also easy to fix; if a volunteer submitted an incorrect submission, simply deleting the new row corrected the dashboard. An additional plus was that, to reset the system, all submissions were deleted and we were ready for the next day. Last, but not least, making a high-quality tool for free felt good.
Just a Piece of the Puzzle
It should be noted that, amongst all the elements that made the event run smoothly, this technology was not even remotely the most important. Volunteers followed instructions to the letter, artists were incredibly flexible and understanding, and a few key leaders believed in the tool’s capacity; in fact, if it were not for a last-ditch attempt by Nabiha Keshwani, we’d be using paper slips.
In the grand scheme of an event that took months to plan, this tool was only born a week before showtime and, by lots of effort and a bit of luck, it helped us run a pretty stunning event.
Have feedback or other problems to solve? Leave a comment and we’ll check it out!
