OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

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?

http://spreadsheets.google.com/feeds/worksheets/key/visibility/projection

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…

http://spreadsheets.google.com/feeds/worksheets/reBYenfrJHIRd4voZfiSmuw/public/basic

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?

http://spreadsheets.google.com/feeds/worksheets/reBYenfrJHIRd4voZfiSmuw/public/basic?title=geo

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…:-(

Written by Tony Hirst

June 2, 2009 at 11:56 am

3 Responses

Subscribe to comments with RSS.

  1. No demo, but a useful insight into how you work. Seriously, a post on ‘methodology’ *is* useful and helps build confidence in people like me wanting to learn this stuff (rather than just clone your demos!)

    Joss Winn

    June 2, 2009 at 7:47 pm

  2. I couldn’t remember why I was following you on twitter – but now I remember. Something about spreadsheets mixed with sarcasm…
    So – due to some screwy api docs links (which we’re fixing) you might have missed the new “tables” and “records” feeds – which are worth looking at if the list feed seems to miss the target a bit. They’re at the bottom of this page:

    http://code.google.com/apis/spreadsheets/docs/3.0/developers_guide_protocol.html

    Also curious if you’ve played with importing data *into* spreadsheets… I’ve had some fun with the twitter api this way, with no code – just some kooky cell formulas.
    cheers, JR

    JR

    June 2, 2009 at 7:51 pm

  3. @JR sarcasm, what, me? No chance… Heh heh…

    “the new ‘tables’ and ‘records’ feeds – which are worth looking at if the list feed seems to miss the target a bit. They’re at the bottom of this page:
    http://code.google.com/apis/spreadsheets/docs/3.0/developers_guide_protocol.html

    AH, hmmm, tables look interesting, though not sure I fully understand them till I try to have a play. Thanks for the pointer:-)

    One thing I was looking for that I couldn’t obviously see in a public spradsheets feed were sheet gids that I could use in Viz API query call (ie a number n:0…N so I could append &gid=n to spreadhsheet when making a call; there are things that look like cribs of the the form &sheet=ocm but i can’t get them to work displaying a particular sheet (maybe I’m doing something wrong trying to construct th URI?)?

    Some of the requests in the new documentation also look to be POSTs – I tend to prototype using Yahoo pipes and simplistic JSON calls, so POSTs are are bit of a stumbling block in most of my recipes…

    “Also curious if you’ve played with importing data *into* spreadsheets…”

    Not yet, no time; and no data to hand other than private data. Seeing the tables, though, I’ll add adding data to my list of stuff to play with :-)

    Tony Hirst

    June 2, 2009 at 8:55 pm


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 843 other followers

%d bloggers like this: