Image for post
Image for post
Don’t worry, time doesn’t actually move that quickly

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.

Ali Babool
Nov 22, 2020 · 7 min read

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.

Volunteers at each checkpoint submit a form when an artist arrives
Volunteers at each checkpoint submit a form when an artist arrives
Volunteers at each checkpoint submit a form when an artist arrives

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.

Image for post
Image for post
Processed form responses
Image for post
Image for post
Credit to Nabiha Keshwani for making complex math easy to understand
Image for post
Image for post
Notice the column and row location of this first cell
Image for post
Image for post
Columns A, B, C are processed into Columns D and E
Image for post
Image for post
Conditional formatting is a blessing

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.

Image for post
Image for post
Missing values can be so unfulfilling
Image for post
Image for post
Imagine Find My iPhone, but without a map

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.

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.

The Startup

Medium's largest active publication, followed by +773K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store