Existing work

Firstly, after doing this much work, I have discovered someone already has done a similar investigation: How long is a Star Trek title? by Bruce Bennett, so check that out. One thing it does have that I don’t, is charts.

Improving workflow

The analysis so far has been done using a Google Docs spreadsheet. I then manually wrote the Markdown for the blog posts, which involved some tedious and error prone tables.

I was originally not going to include the giant list of every single-word episode title, since creating that table did not look like fun.

But… I also started working a little program to do more sophisticated processing, and realised I could also make the program generate the Markdown for me.

The program is a .NET 7 console application writen in C#, and writing it was probably quicker than the manipulation I had to do with the spreadsheet in the first place. It was definitely quicker than producing the Markdown tables in the first post manually.

The end result is I can generate tables of all sorts of random episode lists with different properties of varying levels of interest with very little effort.

Google continue to improve upon Google Docs today with the release of Google Docs motion which will “introduce a new way to collaborate - using your body”. Anything else I saw will be underwhelming compared to the article form Google, especially some of the pictures of some of the more advanced gestures it supports.

I’ve been vaguely using Google Docs (specifically Spreadsheets) since it came out but never to do anything actually important. Most of the time I just had a list I need sorting, or if I was feeling sophisticated I’d use it to decide on what was best value for money (how much £/GB a range of hard drives were for instance). Recently I started using it to plan lessons for the language learning app. The ability to use it from work (or any other computer I might be on - including viewing it on my Nokia 770) was useful, but in the end I was only really writing a list with it.

Until now. I now have a nifty little C# app that generates modules directly from a Google Spreadsheet which is definitely a Good Thing. I’ve been thinking of writing an app for module editing for a while since writing them by hand is tiresome and error prone. Google Spreadsheets does half the work for me by providing the user interface for generating a table and then provides access as simple XML. Which brings me to the matter of actually accessing the data. Google provide a client library in C# for accessing quite a lot of their API. I tried using it but found it a little confusing. Luckily since I was just wanting to query data, I discovered that raw access was actually easier. You simply make a GET request to http://spreadsheets.google.com/feeds/worksheets/_key_/public/values (where key is provided to you when you “publish” a spreadsheet - access to unpublished spreadsheets requires authorization which is more complicated). This gives you an Atom feed of URLs to the individual worksheets which them contain Atom feeds of either rows or columns (your choice). The query power of LINQ (along with XElement, XAttribute etc.) make transforming the feeds into modules really easy. In fact the code that does the hard work (takes a spreadsheet key and generates the XML) is only 102 lines long, and that’s including unnecessary spacing to make the LINQ more readable (the main LINQ query is 35 lines).