🎥 🏉 AppSheet + AppScript for sports videography highlights tracking

During the summer I was asked to help with some video recording for a rugby tournament, but rather than spending hours rewatching footage to find highlights, I quickly threw together some tech to make things a lot easier.

Background

Sport videography is a hobby of mine (specifically rugby). It is however very tedious work rewatching full matches in order to find the highlights. That's why when I was asked to help a friend film an entire rugby tournament this summer I was hesitant. Rewatching a full day’s worth of rugby videos is not something I wanted either of us to have to do.

There are some cameras that have a “mark special moment” button that will let you set a marker of when something cool happens, but we were using multiple cameras that didn’t have that feature. We discussed a simple paper based solution where we just noted down the time of when cool things happened. This was the solution we settled on until the morning of the event which is when I discovered a technology that would make things a lot easier.

Solution

original spreadsheet

For the data capture/entry we used AppSheet, which is a no code app builder that integrates seamlessly into Google Sheets (as well as other things). As long as you have your spreadsheet set up with the right data formats for your columns, AppSheet can generate a clean and robust data entry UI that feeds data directly back into the spreadsheet.

I found out about AppSheet from this video by Carter Morgan 👇:

Thanks to Carter’s intro I was able to build the perfect data entry app on the same morning of the tournament that would fit my needs (and was even able to style the colour theme of it), and both my friend and I were able to access it from a native Android App on the field!

generated AppSheet app

Throughout the day, anytime something cool happened whilst the cameras were rolling I just put in a note using the app, and because of how I set it up; the app knew to use the current time as the default value.

After the tournament was over

When it came to finding the actual clips that contained the highlights after the tournament, this is where AppScript came into play (I know AppScript and AppSheet are annoying similar names). AppScript is a technology that lets you build automation within GSuit e.g Google Drive, Sheets, Docs, etc. Check out this video for a quick overview 👇

We first uploaded all of the video clips from the tournament into Google Drive. Then within the Google Sheet that contained all of our inputted highlight timestamps I wrote a AppScript that would loop through each row and:

1. extract out the time data for that specific highlight
2. scan through the file metadata of all the videos in a specified Google Drive folder to find the video that overlapped with that time
3. automatically paste in a link to that video file back into the spreadsheet

The AppScript that does this is attached to the Google Sheet so that I get a nice toolbar button to trigger the script. When the script is triggered it prompts me to specify the Google Drive folder url containing the tournament videos to search through. Watch it in action 👇

the automated clip finding process using my script

You can see all the AppScript code in my github:

gsuit-sports-videography-tool/script.gs at main · ZackAkil/gsuit-sports-videography-tool

and here’s a visual overview of the whole system:

high level diagram of the entire solution

Tricky challenges

Camera clocks

When I first ran the script to find the matching clips they were all wrong. Turns out the system clock of the camera was wrong 🤦‍♂️. So I added a quick and dirty bit of code to the script to offset the times.

‘File created’ time vs ‘File updated’ time

When I was writing the code to fetch the video metadata from Google Drive I thought that the file.getDateCreated() method from the AppScript Google Drive API would tell me when the video was captured. Turns out that method just returns when the file was created/uploaded “in Google Drive”. This had me panicked for a while thinking the project was dead as I could not extract the lower level file metadata, but turns out file.getLastUpdated() will tell you the last time the file was edited which in our case was the time the video was captured on the camera.

Future

Now that we have timestamps of highlights along with the specific video clips we can look into training machine learning models that could automatically detect highlights in videos. This could be done with a tool like AutoML Video Intelligence which can train powerful video models without us writing any machine learning code.

Takeaway

The time it takes to build an app is not correlated to how useful that app can be. Tools like AppSheet take minutes to use and when the right moment comes for them they can save you literally days of work. Combine that with scripting tools like AppScript and you have a powerful problem solving duo.

Follow and ask me questions on twitter ZackAkil!