OER Visualisation Project: Processing a resource feed to find frequency using Google Spreadsheets [day 3] #ukoer #ooher

I’m really not intending on posting each  day … honest (although some people would like that ;), but here’s another quick hack for getting a feed post frequency using Google Spreadsheets.

Looking down Phil Barker’s UKOER pilot phase sources list (and getting over the shock of limited full repository feeds <sigh>) I noticed that Leeds Metropolitan University have a full feed(?) available via Yahoo Pipes <yummy>. The feed contains 341 items and I was interested in the frequency of item submission. My initial thought was to pull the feed into Google Spreadsheet using the ImportFeed function  (e.g. =importFeed("http://pipes.yahoo.com/pipes/pipe.run?_id=286bbb1d8d30f65b54173b3b752fa4d9&_render=rss"; "items"; true;100))  but it was limited to 20 results (looking at the raw feed I saw it had 100 items – I’m sure there must be a switch in Pipes to get more).

imageInstead I switched to getting the Pipe as JSON. JSON input powers a lot of my other Google Spreadsheet toys like TAGS and Google+ network generator so I’ve a growing bank of code snippets I can throw together.

Here’s my ‘Frankenstein’ code to read a JSON source and write to a sheet. Two little tricks in here are to recast the pubDate as a date so that it’s interpreted as such in the spreadsheet and writing the JSON objects to a sheet using normalised column headers to identify object elements (I borrowed this technique from the writing data Apps Script tutorial).

Once the data is in the spreadsheet there are some standard spreadsheety things you can do like using the Frequency function to count occurrences of a dates and generate charts like this one:

or something less conventional like using the data in a visual timeline gadget to produce:


View interactive version of Leeds Metropolitan University Unicycle feed (this link lets you file > make a copy of the spreadsheet for your own use)

And as this data was grabbed using Google Apps Script if I had a list of JSON sources for UKOER repositories I could automate the whole process …