A Glimpse of Work In Progress

Prompted by a couple of comments from @Josswinn to be more transparent (?!;-), here’s a glimpse into how I set about learning how to do something.

It potentially won’t make a lot of sense, but it’s typical of the sort of process I go through when I hack something together…

So, there’s usually an initiator:

Then there’s a look to see if there’s anything to play with that is on my current list of things I thing I’d like to, (or need to;-), know more about:

Hmm, ok… Google spreadsheets. I’ve just learned about how to write queries against Google spreadsheets using the visualisation API query language, so can I push that another step forward…? What don’t I know how to do that could be useful, and that I could try to demo in an app using this resource?

How about this: a web page that lets me pull the result out for a searched for by name university.

Hmm, what would that mean? A demo around a single subject, maybe, or should the user be able to specify the subject? The subject areas are listed, but how do get those in to a form? Copy the list of tab names from the spreadsheet? Hmm… Or how about entering the name of a single university and displaying the results for that HEI in each of the categories. That would also require me to find out how many sheets there were in the spreadsheet, and then interrogate each one…

Okay, so it’d be nice to be able to search for the results of a given university in a given subject area, or maybe even compare the results of two universities in a given subject area?

So to do that, do I need to learn how to do something new? If not, there’s no point, it’s just makework.

Well, I don’t know how to grab a list of worksheet names from a Google spreadsheet, so that’d be something I could learn… So how to do that?

Well, the query language only seems to work within a sheet, but there is a Google spreadsheets API I think? Let’s have a look: Google Spreadsheets APIs and Tools: Reference Guide. F**k it, why haven’t I looked at this before…?!

[Go there now – go on… have a look…]

Blah, blah, blah – ah: Spreadsheets query parameters reference. Quick scan… hmm, nothing obvious there about getting a list of worksheets. How about further up the page…?

Ah: “The worksheets metafeed lists all the worksheets within the spreadsheet identified by the specified key, along with the URIs for list and cells feeds for each worksheet”

I have no idea what the “list and cells feeds” means, but I’m not interested in that; “lists all the worksheets within the spreadsheet identified by the specified key” is what I want. Okay, so where’s a URL pattern I can crib?


Scan down a bit looking for keywords “visibility” and “projection” (I’m guessing key is the spreadsheet key…). Okay, visibility public and projection basic just to check it works…


Okay, that works… No obvious way of getting the gid of the worksheet number though, unless maybe I count the items and number each one…? The order of worksheets in the feed looks to be the sheet order, so I just need to count them 0,1,2 etc from the top of the list to gd the worksheet gid. Ah, there could be an opportunity here to try out the YQL Execute in a pipe trick? After all, the demo for that was an indexer for feed items, and because the API is chucking out RSS I need to use something like a pipe anyway to get a JSON version I can pull into my web page.

Hmmm, what else is there on the docs page? “alt, start-index, max-results Supported in all feed types. ” I wonder? Does alt stand for alternative formats maybe? Let’s try adding ?&alt=json to the URL – it may work, or it may relate to something completely other…. [Success] heh heh :-) Okay, so that means I don’t need the pipe?

What else – anything that could be useful in the future? Hmm, seems like the Spreadsheets API actually supports queries too? So e.g. I can run a query to see if there is a sheet that contains “geo” maybe?


Okay – lots of other query stuff there; remember that for another day…

So: to recap, the above process took maybe 10-15 mins and went from:

– initiator: see a tweet;
– follow-up: look at the resource;
– question: is there something I could do with that data that I don’t know how to do?
– question refined: how about I pull out a list of the worksheets from the spreadsheet, and use that in e.g. a drop down box so students can choose a subject area from a list, then search for one, or compare two, HEIs in that subject area. I don’t know how to get the list, and I’m not sure about the best way of comparing two items, so I’ll probably learn something useful.
– solution finding: check out the Google spreadsheets API documentation; (If that had failed, I’d have done a blogsearch along the lines of ‘feed list worksheets google spreadsheet’
– plan: err, okay, the plan is a form that pulls a list of worksheets from the HEI spreadsheet via JSON, indexes each one to give me the worksheet gid number (this is a possibly flakey step? Could I index the spreadsheet by name?) then builds a query on that worksheet using an input from one or more text boxes containing the name of HEIs (or maybe a single text box with comma separated HEI names?)

Normally I’d have then spent up to an hour messing around with this (it is (working) lunchtime i.e. playtime after all), but instead I spent forty five mins writing this blog post… which means there is no demo…:-(