Athlete Profiles

Using Google Sheets as backend to rapidly generate athlete stats

Tablet showing Molly Rensaw’s stats, and a phone showing Tonia Couch’s Diving Results

I worked on this project at The ASA in October 2016.

By creating a new automated system, the laborious process of updating profiles was transformed into one that would enable quick and easy changes. The new system, backed by Google Sheets, enabled us create a larger amount of profiles, each one including more comprehensive stats. We also used the same backend to generate editorial ‘ones to watch’ widgets for a range of aquatic competitions.

Diver Tonia Couch’s profile page as it was in 2013 (full image, via archive.is). At the time, all stats had to be manually researched and updated in the Content Management System.

A better way of doing it

To curate statistics for profile pages, Chris, our Content Manager would browse handful of third party sites. After a competition he would have to go in to each and every profile page in the CMS and enter in any changes. The process was particularly tedious when it came to calculating the swimmer’s PBs, as the stats had to be cross-referenced.

To speed up the process we created an array of Google Sheets. They covered all swimming disciplines, many competitions, and had relevant stats to all the athletes we were covering. Updating the spreadsheets simply involved copy and pasting in new stats.

The profiles were then updated by running Gulp command.

gulp save-stats

This command would download the relevant spreadsheets and normalize the data. It would then proceed to generate a JSON document for each athlete profile, and upload it to our static file server using FTP. The Nodejs scripts made heavy use of lodash and async, with google-spreadsheet used to download the stats.

The main content of Tonia’s profile page (webarchive.org). The new profile stats widget, sits under the profile copy. An action shot, out of view in the screenshot, serves as the headline image.

Redesigning the profiles

The automated system allowed us to cover more athletes and show more stats. So it was worthwhile spending the time to make them really nice. By adding in the tabs, the profile page became cleaner and more coherent. The stats also became easier to browse. Where we had stats for athletes from multiple domestic or interantional competitions, we further divided the stats up by using a dropdown to switch between competitions.

To make the stats clear and accessible, I spent time optimising the typography. I switched the font to Source Sans Pro, used by the ASA in the marketing materials and event programmes, for its excellent legibility in small sizes and the performance of its lining figures. Next, I divided up the results into multiple smaller tables, making them easier to digest (it also allowed us to show the location of the venue in the title). Finally, I made sure that the table columns aligned accross results and tabs, so there wasn’t the appearance of the content shifting around as you browse.

Two phones, one showing past swimming results from the British Championships, and the other showing International Synchronised Swimming results. — Profile stats for swimming (left) and synchronised swimming (right). Using Bootstrap tabs, along with the accessibility plugin made the tabs keyboard-navigable and screen reader compatible.

Collaborating on new widgets

The spreadsheets of stats were also be used to create new ‘ones to watch’ widgets for aquatic competitions. Our Content Manager would write additional copy in a new sheet, as I worked on the design and implementation. (I later learned that The Verge used a very similar technique to build The Verge 50).

Tablet showing Molly Rensaw’s stats — Ones to watch widget for the ASA National Swimming Championships. Non-breaking spaces are used for the ‘events entered’, so that the line doesn’t break between distance and event.