USA's Foluke Akinradewo, 2011

A part-time writing assignment that I took on a few years back was reporting on international volleyball matches here in Japan. Because I love sports, this is one of the most enjoyable gigs I've ever done. But there were some friction points that arose during my court-side writing process. I'd frequently have to refer to the roster sheet to ensure that I wasn't misspelling some of the more complex names, without having to look away from the action for too long.

Pre-prepared text shortcuts was the obvious solution. I wanted to conjure the entire player list with a keystroke, and ideally, I wanted to call a single player name by referencing his/her roster number (i.e. "u16"" for USA's number 16, Foluke Akinradewo, pictured above, a name that I've always had trouble spelling).

In my first time attacking this problem a few years back, I managed to prepare this vast library of snippets in TextMate, but it took significant effort for the initial input to prepare all the shortcuts.

Some of the pain in the process was removed.

But not all of it.

Deeper down the rabbit hole

Every year before an event I still have the tedious task of updating my snippets -- roster's change after all -- and I didn't like that. Someone smarter than myself would create a system that referenced the current roster on the official website, and automatically converted that to a snippet somehow.

So how could I do that?

Step 1: Get scrapey

The first part was relatively easy. I considered something Beautiful Soupy, but decided to keep within my capabilities and use Google Spreadsheet's little known import HTML function. This could scrape any HTML table or list on the web and bring it into a Google spreadsheet. Here the task was relatively simple and, for me, within reach. Import the player names into Google Spreadsheets and somehow massage them into a snippet.

The import function for team USA, for example, goes as follows 1:

=ImportHtml("http://www.fivb.org/EN/Volleyball/Competitions/GrandChampionCup/2013/Men/Teams.asp?Team=USA"; "table";4)

In action, that function performs thusly:

ImportHTML function

I still wasn't quite sure how I could make them into a TextMate snippet. But what about a Text Expander snippet?

Step 2: Get snippety

The pop-up snippet format would work nicely, although I decided to abandon figuring out a way to automate the creation of uniform number snippets. I confess, I still sometimes create these manually for any player name that is especially problematic (or anytime team Poland plays).

Text Expander meant I could also have mobile support as well using the Text Expander Touch app. Not that I needed it, but it's a nice side effect. I thought it might be useful to use Text Expander's pop-up snippets here, which for the USA men's team, would need to be written in the following syntax to create the snippet:

%fillpopup:name=USA:default=Matthew Anderson:Sean Rooney:Kyle Caldwell:David Lee:Richard Lambourne:Paul Lotman:Kawika Shoji:William Priddy:Murphy Troy:Antonio Ciarelli:Micah Christenson:Russell Holmes:Vaafuti Tavana:Jeffrey Menzel:Carson Clark:Ryan Ammerman:Maxwell Holt:Garrett Muagututia:Erik Shoji:David Smith%

Rock the Concat

Massaging the spreadsheet output into that form would take a little effort, but not much. And once I'd done it once, I could re-use it for all the teams.

It just required some creative concatenating with Google spreadsheet's concatenate function.

First I had to join the first and last names in a new column. Easy enough 2.

=CONCATENATE(F2," ",G2)

And then I need to join all those names (listed in the "O" column), each separated by a colon:

=CONCATENATE(O2,":",O3,":",O4,":",O5,":",O6,":",O7,":",O8,":",O9,":",O10,":",O11,":",O12,":",O13,":",O14,":",O15")

Finally I need to add the popup name and some other required Text Expander cruft. The previous string is passed through as cell P2.

=CONCATENATE("%fillpopup:name=USA:default=", P2, "%")

From there I could simply copy and paste the snippet into Text Expander for reference during matches.

So to summarize: Team roster link goes in, and a Text Expander snippet comes out.

Fun stuff, right?

Of course, there aren't many people who are going to ever need shortcuts for international volleyball reporting. But the process can be applied to a number of other situations:

  • Can't remember all the provinces in China or their names in Chinese? Scrape this Wikipedia table into a snippet.
  • Can't remember stock symbols for renewable energy companies? Scrape this table.
  • Want an emoji snippet? Scrape this list (use 'list' instead of 'table' in the import function)

I'm really not sure if this helps any actual people out there, but it's fun to play around and see what can be done. Generally when I write posts on this site, I try to picture my audience as myself, but just a year younger.

Younger Me could have definitely made use of this crude little hack. And I hope some of you find it useful too.

The end result


  1. Note that the "4" in the function is referencing that the desired table is the fourth tabled from the top on that page. I don't always check or count the tables before importing, but rather I try 4, and adjust up or down based on whatever table I've hit.  

  2. I don't really need to separate these steps, but for clarity's sake, I will.