Getting ONCourse Data Flowing into Google Spreadsheets

One of the challenges that needs to be addressed when developing data driven applications is how to get data from a data base to the point of use. Another relates to time-relevance. For example, in one document you might want to look at figures or data relating to a specific, fixed date or period (March 2012, for example, or the academic years 2012-13). At other times, you might want current data: values of some indicator over the last 6 weeks for example. Where live data feeds are available, you might want them to be displayed on a dashboard. In other circumstances, you might want a more traditional printed report or Powerpoint presentation, but one that contains absolutely up to the minute information. Ideally, the report or presentation would be “self-updating” so that each time you printed it off, it contained the latest data values.

One common user environment for data related activities is a spreadsheet. When developing an API that produces data that might be usefully manipulated within a spreadsheet context, it can be useful to provide a set of connectors that allow data to be pulled directly from the API and inserted into the spreadsheet.

Here’s a quick example of how we might start to pull data into a Google Spreadsheets context from the University of Lincoln course data API. The example calls broadly reproduce those described in Getting ONCourse With Course Data.

Although the new script management tools in the Google Apps environment confuse the process of defining spreadsheet specific scripts, at its heart defining custom spreadsheet functions for Google Spreadsheets is a trivial exercise: create a custom function (for example, myFunction(val)), pass in a value (such as a cell value) and return a value or array. You can can call the function using a formula, =myFunction(A3), for example. If a single value is returned, this will be returned into the calling cell. If a one-dimensional list is returned, it will add alues to the current row. If a two dimensional array is returned, it will populate rows and columns.

Here are a few helper functions for calling the ONCourse data into a Google Spreadsheet:

function signed(url){
  //Use this to add the API key, for example:
  //return url+'?key='+KEYVAL 
  return url
}

function gurl(url){
  //Fetch the JSON data and parse it as such
  raw=UrlFetchApp.fetch(signed(url))
  json=Utilities.jsonParse(raw.getContentText())

  return json
}
 
function jgrabber(u){
  root='https://n2.online.lincoln.ac.uk'
  return gurl(root+u)
}

function grabModulesId(id){
  path= '/modules/id/'+id
  json= jgrabber(path )
  return json['result']
}

function grabProgrammesId(id){
  path= '/programmes/id/'+id
  json= jgrabber(path )
  return json['result']
}

function assessmentByModuleID(moduleID){
  d=grabModulesId(moduleID)
  return d['assessments']
}

Let’s now see if we can pull data into the spreadsheet, and try to get a feel for whether this sort of approach looks as if it may be useful…

First up, how about getting a list of programmes associated with a module?

function printProgsFromModuleID(moduleID){
  d=grabModulesId(moduleID)
  var arr = [];
  c=[]
  c.push(d['title'])
  c.push(d['module_code']['code'])
  c.push(d['level']['description'])
  c.push(d['credit_rating'] + ' points')
  arr.push(c)
  arr.push([])
  c=['Programme', 'Course', 'Course Code']
  arr.push(c)
  for (var i=0;i< d['module_links'].length;i++){
   r=d['module_links'][i]['programme']
   c=[ r['programme_title'], r['course_title'], r['course_code']['code'] ]
   arr.push(c)
  }
  return arr
}

Here’s the result:

How about learning outcomes per module (via the assessments associated with each module)?

function learningOutcomesByModuleID(moduleID){
  d=assessmentByModuleID(moduleID)
  arr=[]
  for (i=0; i<d.length; i++){
    r=d[i]
    assessment=gurl(r['nucleus_url'])['result']

    c=[ assessment['module']['title'], assessment['module']['module_code']['code'], assessment['assessment_method'] ] 
    arr.push(c)

    learningOutcomes=assessment['learning_outcomes']
    for (j=0; j< learningOutcomes.length; j++)
      arr.push([ '','', learningOutcomes[j]['description'] ])
  }
  return arr
}

Which gives us:

And how about programme outcomes for a particular programme, broken out into outcome types:

function programmeOutcomesByProgrammeID(id){
  d=grabProgrammesId(id)
  arr=[]
  var types= new Object();
  for (i=0;i<d['programme_outcomes'].length; i++){
    r=d['programme_outcomes'][i]
    if (types[ r['category']['title'] ] == undefined ){
      types[r['category']['title']]=[]
    }
    types[r['category']['title']].push(r['description'])
  }

  for ( j in types )
    arr.push([j,types[j] ])
  return arr
}

The result? A handy table:

Okay, so that all seems easy enough. I think that Google docs are also scriptable, so it should be possible to populate a templated “word” document using data pulled from the API (for example, using a snippet like this one of Martin Hawksey’s: Google Apps Script to fill in a Document template with Spreadsheet data).

One thing I realised about the API from playing with this recipe was that it is defined very much as a little-l, little-d “linked data” API that works well for browsing the data. The well defined URIset and use of internal unique identifiers make it easy to traverse the data space once you are in it. However, it’s not immediately obvious to me how I could either search my way into the data, or access it via natural identifiers such as programme codes or module codes.

[Seems I missed a trick on the API front… As described in APMS -> Nucleus -> APIs. How? Why?, it seems that you could get details of all assessments that are the final assessment for the module, that contain group work at: https://n2.online.lincoln.ac.uk/assessments?access_token=ACCESSTOKEN&group_work=1&final_assessment=1]

For example, to print the programmes associated with a module, I might use the above formula =printProgsFromModuleID(97), but the identifier I need to pass in (97) is an internal, machine generated ID. This is all well and good if you are working within the machine-ID space, but this is not the human user space. For that, it would be slightly more natural to make use of a module code, and call something like printProgsFromModuleCode('FRS2002M'). There are issues with this approach of course, with module codes being carried over from presentation to presentation, and breaking a simple bijective (one-one onto) relationship between internal module IDs and module codes. As a result, we might need to further qualify these calls with a presentation year (or by default assume the presentation from the current academic year), or whatever other arguments are required to recapture the bijectivity.

PS in passing, here’s another view over modules by programme, broken down into core and option modules.

There are a few problems with this view – for example, the levels need ordering properly; where there are no core or no optional modules the display is not as good as it could bel the label sizing is a bit small; there is no information relating to pre-requisites for optional modules – but it’s a start, and it’s reasonably clean to look at.

It should also be easy enough to tweak the data generator script to allow us to use the same display script to show assessment types for each module in a programme, as demonstrated using enclosure charts in VIsually Revealing Gaps in ONCourse Data , and maybe even learning outcomes too.

If I get a chance, I’ll look through the sorts of thing requested in the ONCourse Focus Group – 14th March 2012 and try to pull out some views that match some of the requested ones. What would also be interesting would be to have a list of use cases from people who work with the data, too…