OUseful.Info, the blog…

I got a request today along the lines of:

We’re in the process of creating a master calendar of events spreadsheet relevant to [various things]. These [various things] will all then have their own Google calendar so they can be looked at individually, embedded etc and everyone could of course have access to all and view them all via their personal Google calendar, turn different calendars on or off, sync with Outlook etc. etc.

X said “wouldn’t it be great if we made the master spreadsheet with Google docs and it could somehow automate and complete the calendars”.

Sigh…;-) So – is it possible?

I’ve only had a quick play so far with Google Apps script, but yes, it seems to be possible…

Take one spreadsheet, liberally sprinkled with event name, description, start and end times, an optional location, and maybe a even a tag or too (not shown):

The time related columns I specified as a date type using the “Data Validation…” form from the Tools menu:

Now take one Google apps script:

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[0];  // First column
    var desc = row[1];       // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
 }
}

(This was largely a copy and paste from Sending emails from a Spreadsheet Tutorial, which I’d half skimmed a week or two ago and seemed to remember contained a howto for bulk mailing from a spreadsheet, and the Apps script Calendar class documentation.)

And here’s the result of running the function:

The email tutorial adds a bit of gloss that allows a further column to contain state information about whether an email has already been set; we could do something similar to specify whether or not an event has been automatically added to the calendar, and if not, add it when the function is run.

Because it can be a pain having to go into the script editor to run the function, it’s easier to just create a menu option for it:

Here’s how:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Shove stuff in calendar", functionName: "caltest2"} ];
  ss.addMenu("OUseful", menuEntries);
}

I had a little play to see if I could trivially get an RSS feed into the spreadsheet using an =importFeed() formula, and use the details from that to populate the calendar, but for some reason the feed importer function didn’t appear to be working?:-( When I tried using CSV data from a Yahoo RSS2CSV proxy pipe via a =importData() formula, the test function I’d written didn’t appear to recognise the date format…

PS Arghh… the test formula assumes a Date type is being passed to it… Doh!

Hack round importFeed still not working by grabbing a CSV version of the feed into the spreadsheet:
=importdata(“http://pipes.yahoo.com/ouseful/proxy?_render=csv&url=http%3A%2F%2Fopen2.net%2Ffeeds%2Frss_schedule.xml”)

Tweak the calendar event creation formula:

cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});

Run the function:

Heh heh :-)

PS it’s also possible to move content from a Google Calendar to a Google spreadsheet, as Grabbing Google Calendar Event Details into a Spreadsheet shows…

PPS it strikes me that the spreadsheets2calendar route provides one way of generating an iCal feed from a list of event times held in a spreadsheet, by popping the events into a Calendar and then making the most of its output formats? A bit like using Yahoo pipes as a quick’n'easy KML generator?

[UPDATE: related, via @mhawksey - Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts)]

In a series of recent posts, (The Yahoo Pipes Documentation Project – Initial Thoughts, Grabbing JSON Data from One Web Page and Displaying it in Another, . Starting to Think About a Yahoo Pipes Code Generator) I’ve started exploring some of the various ingredients that might be involved in documenting the structure of a Yahoo Pipe and potentially generating some programme code that will then implement a particular pipe.

One problem I’d come across was how to actually obtain the abstract description of a pipe. I’d found an appropriate Javascript object within an open Pipes editor, but getting that data out was a little laborious…

…and then came a comment on one of the posts from Paul Daniel/@hapdaniel, pointing me to a pipe that included a little trick he was aware of. A trick for grabbing the description of a pipe from a pipe’s pipe.info feed (e.g. http://pipes.yahoo.com/pipes/pipe.info?_out=json&_id=eed5e097836289dfb4e8586220b18e0e.

Paul used something akin to this YPDP pipe’s internals pipe to grab the data from the info feed of a specified pipe (the URL of which has the form http://pipes.yahoo.com/pipes/pipe.info?_id=PIPE_ID using YQL:

http://query.yahooapis.com/v1/public/yql?url=http%3A%2F%2Fpipes.yahoo.com%2Fpipes%2Fpipe.info%3F_out%3Djson%26_id%3D44d4492a582d616bffda237d461c5ef4&q=select+PIPE.working+from+json+where+url%3D%40url&format=json

It’s just as easy to grab the JSON feed from YQL, e.g. using a query of the form:
select PIPE.working from json where url=”http://pipes.yahoo.com/pipes/pipe.info?_out=json&_id=44d4492a582d616bffda237d461c5ef4″. The pipe id is the id of the pipe you want the description of.

If you have a Yahoo account, you can try this for yourself in the YQL developer console:

We can then grab the JSON feed either from YQL or the YPDP pipe’s internals pipe into a web page and run whatever we want from it.

So for example, the demo service I have set up at http://ouseful.open.ac.uk/ypdp/pipefed.php will take an id argument containing the id of a pipe, and display a crude textual description of it. Like this:

So what’s next on the “to do” list? Firstly, I want to tidy up – and further unpack – the “documentation” that the above routine produces. Secondly, there’s the longer term goal of producing the code generator. If anyone fancies attacking that problem, you can get hold of the JSON description of a pipe from its ID using either the YPDP internals pipe or the YQL query that are shown above.

If you’ve been to any confrences lately where Linked Data has been on the agenda, you’ll probably have seen the four principles of Linked Data (I grabbed the following from Wikipedia…)

1. Use URIs to identify things.
2. Use HTTP URIs so that these things can be referred to and looked up (“dereference”) by people and user agents.
3. Provide useful information (i.e., a structured description — metadata) about the thing when its URI is dereferenced.
4. Include links to other, related URIs in the exposed data to improve discovery of other related information on the Web.

Wot, no RDF? ;-) (For the original statement of the four rules, see TIm Berners Lee’s Design Issues: Linked Data personal note, which does mention RDF.)

Anyway – here’s my take on what we have… building on my Parliamentary Committees Treemap, I thought I’d do something similar for the US 111st Congress Committees to produce something like this map for the House:

I reused an algorithm I’d used to produce the UK Parliamentary committee maps:

- grab the list of committees;
- for each committee, grab the membership list for that committee.

That is, I want to annotate one dataset with richer information from another one; I want to link different bits of data together…

The “endpoint” I used to make the queries for the Congress committee map was the New York Time Congress API.

The quickest way (for me) to get the data was to use a couple of Yahoo Pipes. Firstly, here’s one that will get a list of committee members from a 111st Congress House committee given its committee code (it’s left as an exercise for the reader to generalise this pipe to also accept a chamber and congress number arguments ;-)

I get the data using a URL. Here’s what one looks like:
http://api.nytimes.com/svc/politics/v3/us/legislative/congress/111/house/committees/HSAG.xml?api-key=MY_KEY

So given a committee code, can get a list of members. Here’s what a single member’s record looks like:

rank_in_party: 5
name: Neil Abercrombie
begin_date: 2009-01-07
id: A000014
party: D

If I wanted to annotate these details further, there is also a list of House members that return records of the form:

id: A000014
api_uri: http://api.nytimes.com/svc/politics/v3/us/legislative/congress/members/A000014.json
first_name: Neil
middle_name: null
last_name: Abercrombie
party: D
seniority: 22
state: HI
district: 1
missed_votes_pct: 12.81
votes_with_party_pct: 98.27

I can grab a single member record using a URL of the form:
http://api.nytimes.com/svc/politics/{version}/us/legislative/congress/members/{member-id}[.response-format]?api-key=MY_KEY

Now, where can I get a list of committees?

From a URL like this one
http://api.nytimes.com/svc/politics/v3/us/legislative/congress/111/house/committees.xml?api-key=MY_KEY

The data returned has the form:

chair: P000258
url: http://agriculture.house.gov/
name: Committee on Agriculture
id: HSAG

Here’s how I grab the committee listing and then augment each committee with its members:

Although I don’t directly have a identifier in the form of a URL for the membership list of a committee, I know how to generate one given a pattern that will create the URL for a committee ID given a committee ID, and a committee ID. The pattern generalises around the chamber (House or Senate) and Congress number as well:
http://api.nytimes.com/svc/politics/{version}/us/legislative/congress/{congress-number}/{chamber}/committees[/committee-id][.response-format]?api-key=MY_KEY

So I think this counts as linkable data, and we might even call it linked data. If I work within a closed system, like the pipes environment, then using “local” identifiers, such as committee ID, chamber and congress number, I can generate a URL style identifier that works as a web address.

But can we call the above approach a Linked Data™ approach?

1. Use URIs to identify things.
This works for the committee membership lists, the list of committees and individual members, if required.

2. Use HTTP URIs so that these things can be referred to and looked up (“dereference”) by people and user agents.
Almost – at the moment the views are XML or JSON (no human readable HTML), but at least in the committee list there’s a link to a human audience web page.

3. Provide useful information (i.e., a structured description — metadata) about the thing when its URI is dereferenced.
The members’ records are useful, and the committee records do describe the name of the committee, along with it’s identifier. But info that make committee records uniquely identifiable exist “above” the individual committee record (e.g. the congress number and the chamber). In a closed pipes environment, such as the one described above, if we can propagate the context (committee id, chamber, congress number), we can uniquely identify resourceses using dereferencable HTTP URIs (i.e. things that work as web addresses) using a URI pattern and local context.

4. Include links to other, related URIs in the exposed data to improve discovery of other related information on the Web.
Yes, we have some of that…

So, the starter for ten: do we have an example of Linked Data™ here? Note there is no RDF and no SPARQL endpoint exposed to me as a user. But I’ve had to use connective tissue to annotate one HTTP URI identified resource (the committee list) with results from a family of other HTTP URI idnetified resources (the membership lists). I could have gone further and annotated each member record with data from the “member’s info” family of HTTP URIs.

The “top level” pipe is a “linking query”. IF I had constructed it slightly differently, I could have passed in a chamber and congress number and it would have:
- constructed an HTTP URI to look up a list of committees for that chamber in that Congress; (this was a given in the pipe shown above);
- grabbed the list of committees;
- annotated with them with membership lists.

As it is, the pipe contains “assumed” context (the congress number and chamber), as well as the elephant in the room assumption – that I’m making queries on the NYT Congress API.

On reflection, this is perhaps bad practice. The congress number and chamber are hidden assumptions within the pipe. The URL pattern that the NYT Congress API defines explicitly identifies mutable elements/parameters:

http://api.nytimes.com/svc/politics/{version}/us/legislative/congress/{congress-number}/{chamber}/committees[/committee-id][.response-format]?api-key={your-API-key}

Which suggests that maybe best practice would be to pass local context data via user parameters throughout the pipework to guarantee a shared local context within child pipes?

So where am I coming from with all this?

I’m happy to admit that I can see how it’s really handy having universal, unique URIs that resolve to web pages or other web content. But I also think that local identifiers can fulfil the same role if you can guarantee the context as in a Yahoo Pipe or a spreadsheet (e.g. Using Data From Linked Data Datastores the Easy Way (i.e. in a spreadsheet, via a formula)).

So for example, in the OU we have course codes which can play a very powerful role in linking resources together (e.g. OU Course Codes – A Web 2.OU Crown Jewel). I’ve tended to use the phrase “pivot point” to describe the sorts of linking I do around tags, or course codes, or the committee identifiers described in this post and then show how we can use these local or partial identifiers to access resources on other websites that use similar pivot points (or “keys”). (ISBNs are a great one for this, as ISBN Playground shows.)

If Linked Data™ zealots continue to talk about Linked Data solely in terms of RDF and SPARQL, I think they will put a lot of folk who are really excited about the idea of trying to build services across distrubuted (linkable) datasets off… IMVHO, of course…

My name’s Tony Hirst, I like linking things together, but RDF and SPARQL just don’t cut it for me…

PS this is relevant too: Does ‘Linked Data’ need human readable URIs?

PPS Have you taken my poll yet? Getting Started with data.gov.uk… or not…

Go to any of the data.gov.uk SPARQL endpoints (that’s geeky techie scary speak for places where you can run geeky techie datastore query language queries and get back what looks to the eye like a whole jumble of confusing Radical Dance Faction lyrics [in joke;-0]) and you see a search box, of sorts… Like this one on the front of the finance datastore:

So, pop pickers:

One thing that I think would make the SPARQL page easier to use would be to have a list of links that would launch one of the last 10 or queries that had run in a reasonable time, returned more than no results, displayed down the left hand side – so n00bs like me could at least have a chance at seeing what a successful query looked like. Appreciating that some folk might want to keep their query secret (more on this another day…;-), there should probably be a ‘tick this box to keep your query out of the demo queries listing’ option when folk submit a query.

(A more adventurous solution, but one that I’m not suggesting at the moment, might allow folk who have run a query from the SPARQL page on the data.gov.uk site “share this query” to a database of (shared) queries. Or if you’ve logged in to the site, there may be an option of saving it as a private query.)

That is all…

PS if you have some interesting SPARQL queries, please feel free to share them below or e.g. via the link on here: Bookmarking and Sharing Open Data Queries.

PPS from @iand “shouldnt that post link to the similar http://tw.rpi.edu/weblog/2009/10/23/probing-the-sparql-endpoint-of-datagovuk/“; and here’s one from @gothwin: /location /location /location – exploring Ordnance Survey Linked Data.

PPPS for anyone who took the middle way in the vote, then if there are any example queries in the comments to this post, do they help you get started at all? If you voted “what are you talking about?” please add a comment below about what you think data.gov.uk, Linked Data and SPARQL might be, and what you’d like to be able to with them…

Can’t sleep, can’t work do “proper work”, so I’ve taken the day off as holiday to play out some more of the thoughts and momentum that cropped up during Dev8D… Like this treemap of parliamentary committee membership (as of 1/2/10).

Here’s the tale…;-)

Whilst resisting playing with the new Guardian Politics API (and absolutely dreading the consequences any other topic specific APIs they open up might have on my time!) I did have a quick peek at at it to see whether or not it had any details about Parliamentary committees. It didn’t, but just looking upped my desire enough to pop over to the TheyWorkForYou API to see if they had any appropriate calls: what I wanted was a list of committees, and the membership thereof… (if you’ve read Council Committee Treemaps From OpenlyLocal you’ll maybe guess why!;-)

And it seems they do – getCommittee will “[f]etch the members of the committee that match [a specified] name – if more than one committee matches, return their names” and for a specified committee “[r]eturn the members of the committee as they were on [a specified] date”.

What I wanted was to be able to “select all” committees, which there’s no explicit switch for; but a search on the letter ‘e’ did turn up quite a comprehensive list, so as long as they’re using a free text search, I guess this has the same effect!

So I can get a list of committees, and now I want the members of each one. Pipework, I think:-)

First up, fetch a list of the committees from the TheyWorkForYou API. Then, for each committee, generate the URL that will pull pack the committee membership on a particular date.

For each committee, we now annotate the committee item with the membership of the committee and do a little tidying.

(In terms of API calls, this pipe makes N+1 calls to the TheyWorkForYou API, where the first call returns N committees.)

This pipe gives a JSON feed containing a list of committees annotated with the names and ID of committee members. The structure is not totally dissimilar to the structure I used for the Openlylocal committees, so I should be able to reuse that code if I can make the representations match.

So how do they differ? In no particular order, the differences are:
- the Openlylocal committee representation elaborates each member with a .party attribute which does not exist in the TWFY member lists, as well as using .first_name and last_name rather than .name to name the committee member;
- the TheyWorkForYou list does not have an .id attribute for each committee, which the Openlylocal committee list does have, and which is required for the treemap tooltips to work.

First task, then, is to annotate the committee membership list with each member’s party. A call to another TheyWorkForYou API call (http://www.theyworkforyou.com/api/docs/getMPsInfo returns a list of MPs and their party.

It’s easy enough to elaborate the committee list with this information – create a party lookup table and then annotate as required… While we’re at it, we can do a fudge with the name and id…;-)

var partyLookup=[];
for (var i=0; i< mpDetails.length; i++){
 partyLookup[mpDetails[i].person_id]=mpDetails[i].party;
}

for (i=0; i<mysocMPcommittees.value.items.length; i++){
  var currComm=mysocMPcommittees.value.items[i];
  currComm.id="cid"+i;
  
  for (var j=0;j<currComm.members.length; j++){
     currComm.members[j].party=partyLookup[currComm.members[j].person_id];
    
    currComm.members[j].first_name="";
    currComm.members[j].last_name=currComm.members[j].name;
  }
}

It was then easy enough to just plug this into the code I’d used to display the Openlylocal treemap. You can see it here.

Looking over the TheyWorkForYou API, I guess it would be possible to do a similar sort of thing to map the parties of people who spoke in debates? (I’m not sure if there’s data on votes/divisions with a party breakdown? If so, that would be good to plot too, with hierarchy based on : division, ayes/nays, party, so we could see defections, as well as relative sizes of the votes?

As a great fan of treemaps, I’ve posted a couple of times about the various ways in which they may be used to provide glanceable views over the makeup of local council committees (e.g. Glanceable Committee Memberships with Treemaps, Treemapping Council Committees Using OpenlyLocal Data.

Prompted by Dev8D hackery last week, I spent a little bit of time getting a version of the JIT treemap working with data feeds from the OpenlyLocal website, as hinted at in Some of My Dev8D Tinkerings – Yahoo Pipes Quick Start Guide, Cross-Domain JSON with JQuery and Council Committee Treemaps from OpenlyLocal.

If you want to grab a treemap for a particular council, you need to find it’s Openlylocal ID number. This appears in the various URLs for council pages – can you guess the ID number for the following council?

- http://openlylocal.com/councils/298-Isle-of-Wight-Council
- http://openlylocal.com/committees?council_id=298

Plugging this number into the following test URL should raise the treemap:
http://ouseful.open.ac.uk/councils/demo-embed.php?id=ID

For example: http://ouseful.open.ac.uk/councils/demo-embed.php?id=298

(I haven’t got colours set for every party type yet… Chris – any chance of a list of all party types across all councils??? ;-)

You should be able to embed the treemap in your own page within an iframe:

<iframe src="http://ouseful.open.ac.uk/councils/demo-embed.php?id=298" width="800" height="600" scrolling="no"></iframe>

To get the JSON data into the page from Openlylocal, I run it through a Yahoo Pipe so that I can then grab a JSON-P feed from the pipe. (Currently, Openlylocal just exposes a JSON object. To load this directly into a web page, we need to wrap it in a callback function – that’s all the pipe does – provide a callback…)

Here’s how I get the council committee data into the page with a JQuery call:

function get_ol(id){
 var url="http://openlylocal.com/committees.json?council_id="+id;
  url="http://pipes.yahoo.com/ouseful/jsonproxy?url="+encodeURIComponent(url)+"&_render=json&_callback=?";
 $.getJSON(
   url,
   function(data) {ol_cttees(data.value.items[0]); }
 )
}

The pipe simply grabs the JSON data and then re-exposes it:

To find out how the Openlylocal data is transformed, you can try and dig through:
http://ouseful.open.ac.uk/councils/ccl-treemap_files/example2-dyn.js
The function that kicks everything off is init2(id), with id set to the Openly local council id number. (Reader beware: the js file may contain unused and/or broken legacy code as well as the operational stuff…;-)

Note: thanks to Chris/@countculture for annotating the Openlylocal JSON feeds for council committees with council name data over the weekend so I could create these visualisations more easily :-)

PS for an alternative take on how to use the same data, but in XML form, from the Openlylocal site, see@kitwallace’s The party make-up of council committees – doing it the XML way. Very nice :-)

One of the issues I keep coming across when trying to get folk interested in using Yahoo Pipes is the question of what happens if Yahoo Pipes Dies? I tend to post a lot of screenshots from some of the pipes I build, so I’d stand some chance of recreating them elsewhere, but this is not generally the case for most pipes users.

If we could compile a version of pipe into code that could be run on our own servers, then we could use Yahoo Pipes as a rapid prototyping tool and code generator, rather than as a runtime platfrom. This might then make the environment more palatable to developers working in a conservative regime (e.g. Starting to Think About a Yahoo Pipes Code Generator).

As a stop gap, I thought it would be worth starting up a little Yahoo Pipes Documentation Project to produce some sort of documentation around Yahoo pipes. Grabbing JSON Data from One Web Page and Displaying it in Another contains a couple of bookmarks that demonstrate a simple way of grabbing the content of a pipe.

Here’s a first attempt reporter, done as a bookmarklet that can be applied when editing a pipe. The result is dsiplayed in the browser console:

javascript:(function(){var p=editor.pipe.working;var d="----- ";var c=console.log;c(d+d);var q,r,w,i,m,k,K,ok,oK,x,X;var D=false;var ins=[];var outs=[];var b=[];var o=[];if(D)c("CONNECTIONS");m=p.wires;for (i=0;i<m.length;i++){if(D)c(d);q=m[i].src;r=m[i].tgt;K=r.moduleid;k=q.moduleid;if (!(b[K])){b[K]={};b[K].conns=[]};if (!(b[k])){b[k]={};b[k].conns=[]};ok={};oK={};x=b[k].conns.length;X=b[K].conns.length;b[k].conns[x]={};b[k].conns[x].o=q.id;b[k].conns[x].typ="out";b[k].conns[x].t=K;b[k].conns[x].T=r.id;b[K].conns[X]={};b[K].conns[X].typ="in";b[K].conns[X].f=k;b[K].conns[X].F=q.id;b[K].conns[X].i=r.id;if(D)c("FROM "+k+" ("+q.id+") TO "+K+" ("+r.id+")");}if(D)c(d+d); m=p.modules;c('MODULES');for (i=0;i<m.length;i++){q=m[i];if (!(b[q.id]))b[q.id]=[];b[q.id].n=q.type};for (i=0;i < m.length;i++){c(d);q=m[i];k=q.id;c("MODULE: "+k+" ("+q.type+")");c("-- PROPERTIES:");for (w in q)c(d+w+" "+q[w].toSource()+" ");c("-- CONNECTIONS:");if (b[k])if(b[k].conns)for (j=0;j<b[k].conns.length;j++){K=b[k].conns[j];if(K.typ=="in"){w=d+'<INPUT> [ '+K.i+' ]';if (K.f)w+=' from '+K.f+' [ '+b[K.f].n+" : "+K.F+' ]';c(w)};if(K.typ=="out")c(d+'<OUTPUT> [ '+K.o+' ] connects to '+K.t+' [ '+b[K.t].n+' : '+K.T+' ]')}};c(d+d)})()

Here’s a snippet of the sort of display it gives:

PS @hapdaniel just alerted me to a way I can get hold of a description of a pipe and make it available via a JSON feed. I’ll get a post up about how to do that when I get a chance… Note that this approach deprecates the method above and the approach alluded to in Grabbing JSON data post. (In the meantime, here’s a taster…)