OUseful.Info, the blog…

On Thursday, I get to go down to the JISC offices to pitch our “vision” for the Digipedia website based on a submission earlier this year to the Strategic Content Alliance: Digipedia from Prototype to Pilot Service invitation to tender.

The pitching will be competitive, so there’s all the more reason to post the application we made to see how well it sits with members of the community who might be expected to use it…

Outline Project Description

The Digipedia pilot will be a web resource containing authoritative information on the digital content lifecycle that will be the focus of a community of practice for policy makers and practitioners in the field.

We propose an architecture for Digipedia that combines lightly-coupled authoring, publishing and community features within an integrated, editorially controlled platform. We will structure content so that it is portable and republishable using RSS/Atom, ensuring that content is widely visible and laying a sustainable foundation for the future. We will demonstrate how the content can be reaggregated into a wide variety of thematic views and incorporate innovative tools to improve browsing and searching of information. Audience surgeries will be used to ensure that developments meet the needs of project sponsors.

We will use experience gained elsewhere (Open University projects, WriteToReply, JISCPress) to design a web resource that encourages user-generated contributions. We will engage with SCA sponsors and other stakeholders to foster the community, and provide branded opportunities for organisational contributions that will pave the way for sustainable content.

We will conclude the project by investigating avenues for the long-term sustainability of Digipedia.

The Open University, as a major publisher of digital content in the not-for-profit sector, is well placed to contribute to the Digipedia community of practice. The project team believes that we have the requisite experience with consultation and community platforms and with the development of online communities to develop a successful pilot service for Digipedia.

Proposed work
The Digipedia pilot will be a web resource containing authoritative information on the digital content lifecycle that will be the focus of a community of practice for policy makers and practitioners in the field. The Open University, as a major publisher and licensee of digital content in the not-for-profit sector, has a wealth of institutional knowledge that the investigators can draw on and is well placed to contribute to this community.

We propose an architecture that combines lightly-coupled authoring, publishing and community features within an integrated, editorially controlled platform. Loose coupling means that content authored in one location can be published in a variety of other, independent locations, and complemented with additional community functions. Tight integration means that the user is presented with an apparently seamless user experience even though the user interface may be drawing on content from multiple independent sources.

During an initial project phase, we will convert prototype Digipedia content to the Atom/RSS syndication format. Using our own networks, those of our sponsors, partner members of the Strategic Content Alliance (SCA) and the existing Digipedia contributors and users, as well as consultants, we will map out the anticipated user community. For example, we are keen to use UKOLN whose cross-sectoral expertise, which spans the HE/FE and cultural heritage communities in particular, could be used to identify key stakeholders in the community engagement.

At a preliminary workshop we will use the original Digipedia content to demonstrate to partners how syndicated content may be republished in environments as diverse as Ning, Netvibes and WordPress, as well as on custom websites, and how it can be reaggregated into a wide variety of thematic views. Participants will include representatives from Strategic Content Alliance sponsors, other experts and users from at least the sectors identified in the call: cultural heritage, education and research, health and public sector broadcasting. We will also involve experts from the OU Library and OU Learning & Teaching Solutions who can bring with them our institutional experience of managing digital content at scale. The user surgeries will also be used to explore authoring environments and the local publishing requirements of individual SCA members insofar as they relate to the generation of content relevant to Digipedia. These large surgeries may be supplemented by more focused smaller expert group meetings and site visits.

Based on preliminary workshop feedback, expert consultant advice and on our prior experience of consultation and community platforms such as JISCPress and WriteToReply , we will then customise an instance of the openly licensed WordPress Multi User (WPMU) appropriate for the Digipedia pilot service using openly licensed plug-ins and where necessary developing new open source ones.

The platform will be able to publish content ingested via syndication feeds, provide a discovery service and preview facility for relevant content hosted on partner and other third party sites as well as delivering content authored on the platform itself. WPMU’s support for custom themes supports the creation of separate areas of the site or partner hosted WordPress installations to be branded as required. In this way, Digipedia can provide a centrally branded presence capable of drawing on locally hosted content or content from federated partner sites.

Editorially controlled subject categories and tags will be used to create aggregations of content that can be browsed in a faceted manner. Facets might include sector based classification, national provenance and asset types. User tags and ratings, and potentially semantic tags, will also be used to structure content views. Pathway browsing of sequential or otherwise ordered resources will also be supported.

To encourage the widest possible reuse of Digipedia material, content will also be made available via standards based Atom syndication feeds. We will incorporate innovative tools to improve browsing and searching of information, particularly opening up the possibility of user-generated navigation through the information resources.

Drawing on experience gained from developing the WriteToReply and JISCPress consultation platforms (both of which use WPMU), Open University projects such as iSpot, and the design and support of online resource based courses, we will design a web resource that encourages user-generated contributions and fosters a sense of online community. We will engage with SCA sponsors and other stakeholders to foster this community, and provide branded opportunities for organisational contributions that will pave the way for sustainable content.

Whilst the final set of community features will evolve through consultation with stakeholders, we anticipate the platform will:
• support topic based forums and news feeds
• distinguish between moderated and unmoderated content
• offer a user tagging system
• offer branded content from content partner organisations
• support user-generated navigation, for example by concept-mapping.

Whenever possible we will seek to incorporate existing tools to achieve good functionality for limited development cost. For example, these might include concept mapping tools such as OU Compendium or Cohere which will allow users to contribute their own sense making in topic areas, or simple user list making to create sets of, or pathways through, Digipedia resources.

Traditional dissemination models are often based on attendance at particular conferences or workshops, and the publication of a final project report. As part of our dissemination plan, we aim to deliver a workshop on using Digipedia at a relevant digital preservation event, and a presentation about the technical architecture of the site. It is possible that we will use consultants retained for other parts of the project who are known to and trusted by the corresponding communities to deliver such presentations on our behalf. We will also engage with relevant conference backchannels on realtime social networks such as Twitter to promote Digipedia, as well as raising its profile through commenting, where appropriate, on news and blog articles identified through web alerts. We also fully expect to talk about our work on Digipedia in other contexts, such as at JISC Developer events. We will tag our social media communications so we can track our dissemination efforts.

It is our intention that as many aspects as possible of the project will take place in public channels including a project blog and, where appropriate, through public, commentable consultation documents hosted on JISCPress or WriteToReply, allowing SCA partners as well as the wider community watch the project evolve and even participate. Stakeholders such as JISC Programme Managers and SCA partners will be actively encouraged to offer feedback and participate in iterative testing of the project as well as full participation in face-to-face surgeries. A private channel will also be made available between the project team and SCA partners to allow for a full and frank exchange of views on any matters deemed too sensitive for public airing.

General feedback will be solicited throughout the project via the project blog and Twitter. User stories will be gathered early in the project and refined throughout based on feedback and testing.

Growing Digipedia
Developing a sustainable and thriving community of practice around a web resource is fraught with challenges but far from insurmountable.

A prime requirement is seeding with high-quality authoritative resources that are widely discoverable and able to draw traffic to the site from search engines and by linking from partner organisations. Digipedia starts from a good position with established content and sponsor support from major players in the area.
One strategy for moving from passive consumption to active engagement is to encourage contributions by techniques such as rewarding contribution by visible reputation. In the case of Digipedia, contributors are likely to often be acting both as an individual and as a member of an organisation., Branding or badging contributions as coming from a named member of named organisation provides a mechanism for individuals to enter the community with established reputation; organisations can asked to vouch for expertise of their staff who can then participate in Digipedia at high reputation levels without requiring high levels of participation.

We intend to draw on expertise gained through working on iSpot , a social network that encourages members of the public to upload their observations of wildlife to public website where other community members can comment and help with identification. iSpot has separate reputation ratings for ‘sociability’ and for expertise. Expertise is earned by identifying a plant or animal, as well as having identifications confirmed by other users and by recognised experts. As well as individual reputation, expert users can be badged as members of a natural history society for reciprocal benefit; experts who contribute to the community generate exposure for their organisation. In the case of Digipedia, users might similarly gain two sorts of reputation – social and expertise. Experts could be seeded. Experts could operate on behalf of sponsors by badging, returning an incentive to institutions and their staff to participate.

Providing an ongoing service
During the project duration we will maintain availability to some form of Digipedia service to ensure that it remains a known destination for the user community. Content originally sourced from the Digipedia prototype will be migrated to the new environment as required. The pilot service will be deployed as a platform configuration using WordPress Multi-User (WPMU). WPMU is available as an open source codebase and is used widely in government as well as on other JISC projects (e.g. JISCPress, ArchivePress). By adopting such a platform, service maintainers will be able to draw on support from system administrators and developers from across the JISC and public sector community and contribute expertise back to it.

Editorial Control
We believe that for long-term sustainability of Digipedia there should be a shift towards a hybrid resource which contains both assured high-quality resources that are the end product of an editorial or moderated process, and post-moderated user-generated contributions. Where content is being republished from partners sites through syndication feeds, the selection of those feeds and maintenance of any filters applied to them will be the responsibility of a Digipedia editor. The post-moderation route might simply take the form of allowing any contributions with a mechanism for an expert to later approve them, and using distinctive colour clues so that users can distinguish between moderated and unmoderated contributions. [Examples Citizendium , Encyclopedia of Life ]

One model for long term sustainability would be for SCA partners maintain their own content for their own purposes, and Digipedia to aggregate relevant content from those partners using syndication feeds or custom search queries; community ranking of resources would also provide an element of crowdsourced editorial control.

A technical requirement is that any host system will need to support different permission levels for users to distinguish users with editorial permissions from other users; Digipedia is likely to require greater flexibility than the traditional administrator and user levels.

We have budgeted for some paid moderation and editorial oversight for Digipedia. However, we will need early consultation with project sponsors to establish the best way of delivering moderation and/or editorial input for Digipedia during the pilot year and further consultation on achievable long-term activities. Reputation enabled permissions may also have a part to play.

Developing a sustainability plan
Providing for the long term sustainability of Digipedia is undoubtedly a major challenge. There are three aspects to this: technical, social and financial.

The web is a fast-moving environment, with capabilities and expectations changing rapidly. There is a tension between providing innovative features and rolling out a robust service. We will seek to develop technical work using widely available standards and technologies. Further we will develop an architecture that cleanly separates content into reusable forms. This provides two levels of sustainability: the Digipedia pilot will be maintainable over the short to medium term, and a fall-back is always available of export and reincorporation into another site.

The social aspects of sustainability involve the creation of a dynamic community of practice; this is required to ensure that material remains current and pertinent. We discuss what above our approach to this will be during the project. The experienced gained and the views of project sponsors and other stakeholders will be used to inform our forward plan for community building.

The financial requirements for sustainability must also be addressed. The range of possibilities for income is large but also uncertain. A viable plan also depends strongly on the cost basis which in turn depends on the level of input required to build and sustain a community. We will seek to minimise cost; for example our feed-based approach can provide currency for minimal cost. However, there are no easy answers to sustainability, and the project team will approach this with an open mind, taking note of other experiences in this area, particularly the case studies captured by the JISC/Ithaka sustainability reports , and seeking the views of sponsors and possible future partners.

Any plan will include a minimal cost archive solution that will ensure that Digipedia content is preserved even if the community fails to achieve long-term sustainability.

Deliverables
The main deliverable will be the pilot Digipedia web resource, including content from the prototype and additional contributed resources. This will include the functional features requested:
• an efficient browse mechanism
• good search capabilities
• user ranking of entries
• item sharing via RSS or other mechanisms
• an FAQ system
• easy to use methods for adding new material of different types ranging from short comment to substantial case studies
• rich media entries to include audio, video and animation.

We will also include additional features to encourage and support a community of practice; the set of features included will evolve through consultation with stakeholders.

We will provide top-level documentation of the technical infrastructure, including a breakdown of tools used and indicating alternative provision where known. This will be complemented by the support documentation below.

We will provide clear concise support documentation sufficient to allow the service to be handed over as a running concern.

We will conduct audience surgeries to engage with users of Digipedia and other stakeholders (discussed above). We have budgeted for three such surgeries, but it may be preferable to hold two large scale events and several smaller consultations with experts. We will take advice from the JISC project manager and joint working group.

We will produce a sustainability plan that reviews options for medium to long term sustainability of Digipedia

We will produce a dissemination plan to indicate how we will engage with SCA sponsors and the wider Digipedia audience to stimulate the growth of the user community.

Mandated project deliverables such as the formal project report will be published at the end of the project along with interim progress reports as and when appropriate.

We will maintain a project blog throughout the project to document project activities, and where appropriate post open consultation documents to solicit feedback from the community.

Any comments, please feel free to post them below – or email me direct…

Another passing observation from conference related Twitter activity and hashtag discovery.

Earlier today this tweet passed me by:

Due to an oversight in coding of of my hashtag community pipe, the community isn’t actually constructed around hashtag use – it’s based around a free text search…

…which means I can look for @jobsworth’s “hashtag” community around the terms “chris messina” OR chrismessina:

What this means is we have a really simple way of seeing who’s inside (and outside) you community for particular search terms.

On a related note, I’ve posted previously about a couple of related pipes that discover:
- folk who are using a particular hashtag around a particular search term (“hashtags around search term(s)” pipe)
- hashtags the people you follow are using (“hashtags in my network” pipe).

See Searching for Twitter Hashtags and Finding Hashtag Communities for details about the pipework…

PS see also Demonstrating Twitter in Conference Presentations for details about how to splash a map showing where (ish!) folks who’ve responded to a Twitter shout out from you are located.

Every so often I see tweets go by along the lines of “demoing twitter – please say hi”, and I typically respond with a link to a Twittermap pipe I created some time ago that takes a URL for a set of Twitter search results and then tries to plot the location of each Twitterer based on their location setting in their Twitter profile:

Having to find the URL a) of an appropriate search, and b) and the feed of that search is a bit of a pain though, so here’s a tweak:

Enter your username and conference hashtag (because these shout outs usually happen at hashtagged events, right?), some sort of hint as to how recently you want the tweets from (you can also enter a date) and the pipework should do its stuff.

The URL for the pipe is of the form:

http://pipes.yahoo.com/ouseful/youtweetedme?u=USERNAME&h=HASHTAG

so for example:
http://pipes.yahoo.com/ouseful/youtweetedme?u=joedale&h=pls10

If you want a Google Maps version, use a URL of the form:

http://maps.google.com/maps
 ?q=http:%2F%2Fpipes.yahoo.com%2Fouseful%2Fyoutweetedme%3F_render%3Dkml
 %26h%3DHASHTAG
 %26t%3Dtoday
 %26u%3DUSERNAME

For practical use, it probably makes sense to bookmark the pipe and/or the Google map with the settings you require (in the case of the Google map, this might include setting the zoom level and central point of the map, and then grabbing the Google generated link for that map configuration).

So how does the pipe work? Lazily, that’s how – we just grab the required parameters and construct the URL that my original Tweetmap pipe required…

There’s an additional hack in the form of the Date Builder block which is used to generate a by-the-second time stamp that is passed as an additional made up parameter to the Twitter search API in order to get round any cacheing issues in Yahoo pipes; (the normal cacheing means that if you’re running the pipe several times in a session, you may not see any new results… Note that the Google Maps views might become stale because of cacheing at the Google end…)

Exciting news: Google Apps script is now available for all Google spreadsheet users…, so it seems I was timely in starting to get to grips with playing with this stuff…;-)

So what can we do with Google Apps Script? I’ve been posting a few ideas already, but here’s something I was working on last night – a script that automates table screenscraping using the Google spreadsheet =importHTML() formula.

Here’s the setting: Lichfield council lists details of the last round of council elections on a summary results page:

and a set of individual pages detailing comprehensive results from each ward (example).

What I wanted was a big table listing all the results by ward.

It’s easy enough to pull these results into a Google Spreadsheet using the =importHTML(“URL”,”table”,N) formula (where N is the number of the table in the page), but pulling results in for all 26 wards and then processing them separately would be laborious, so here’s what I did…

First of all, pull in the summary table to get a list of all the wards:

The results by ward page all live on a commonly structured URL (e.g. http://www.lichfielddc.gov.uk/site/custom_scripts/electiondetail.php?ward=2&category=1) with the ward parameter identifying the ward. This URL isn’t picked up by the table scraper, so I had to do a bit of hand finishing, adding an ID column to which I added the appropriate ward number of each ward as used in the ward results URLs.

So, having got a sheet that listed the wards, and a (hand added) identifier for each ward, I could write a script that would create a separate sheet for each ward and add an appropriately customised =importHTML() formula to it:

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var sheet = ss.getSheetByName("Wards");
  var wardNameID=[];
  var wardIDName=[];
  if (sheet != null) {
    var col_ward=getColNumber(sheet,"Ward");
    var col_ID=getColNumber(sheet,"ID");
    var startRow = 2;  // First row of data to process
    var numRows = getMaxRows(sheet);
    var maxcols= getMaxColumns(sheet);
    
    var dataRange = sheet.getRange(startRow, 1, numRows, maxcols);
    var data = dataRange.getValues();
    for (i in data) {
     var row = data[i];
     var wardName=row[col_ward];
     var wardID=row[col_ID];
     //Browser.msgBox(wardName+" "+wardID);
     wardNameID[wardName]=wardID;
     wardIDName[wardID]=wardName;
    }
  }
  for (var i=2;i<numRows;i++){//28
    var sname=wardIDName[i];
    ss.insertSheet(sname, 1);
    sheet = ss.getSheetByName(sname);
    addTableImporter(sheet,i)
  }
}

This function uses a couple of utility functions – getMaxRows() and getMacColumns() which I superstitiously added to get the number of populated rows/columns from a sheet:

function getMaxRows(sheet){
  var maxrows = sheet.getMaxRows();var max =maxrows;
  for (var j=1;j<maxrows+1;j++){
    var header= sheet.getRange(j, 1, 1, 1).getValue();
    if (header==""){
       max=j; j=maxrows+1;
    }
  }
  return max;
}
      
function getMaxColumns(sheet){
  var maxcols=sheet.getMaxColumns(); var max=maxcols;
  for (var j=1;j<maxcols+1;j++){
    var header= sheet.getRange(1, j, 1, 1).getValue();
    if (header==""){
       max=j; j=maxcols+1;
    }
  }
  return max;
}

and the more generally useful getColNumber(), which gets the number of a column in the sheet given its header:

function getColNumber(sheet,colName){
  var colNum=-1;
  var maxcols=sheet.getMaxColumns();
  for (j=1;j<maxcols+1;j++){
    var header= sheet.getRange(1, j, 1, 1).getValue();
    if (header==colName) colNum=j-1;
  }
  return colNum;
}

The addTableImporter() function is the one that adds the importTable() formula to each sheet as required:

function addTableImporter(ss,n) {
 var sc = ss.getActiveCell();

 var arr = [];
 var c = [];
 c[0]='=importHTML("http://www.lichfielddc.gov.uk/site/custom_scripts/electiondetail.php?ward='+n+'&category=1","table",1)';;
 arr[0]=c;

 var destinationRange = ss.getRange(1 , 1, 1,1);

 destinationRange.setValues(arr);
}

Running the addSheets() function creates one sheet per ward, imports the appropriate table, and names the sheet as the name of the Ward.

We’re now in a position to pull together a monolithic table that aggregates data from all the wards:

function aggregator(){
  // for each ward spreadsheet, load in data by column
  // dataRow=[wardName, candidate, votes]
  // if votes=="n/a", votes =100;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var table=[];
  table.push(["Ward","Candidate","Party","Vote"]);
  var writeRow=[];
  var bigsheet = ss.getSheetByName("Aggregate");
  var wards=getWardNames();
  for (var i=0;i<wards.length;i++){
    //get sheet
    var sheet = ss.getSheetByName(wards[i]);
    if (sheet!=null){
      var maxrows=getMaxRows(sheet);
      for (var j=2;j<maxrows+1;j++){
        var candidate=sheet.getRange(j, 1, 1, 1).getValue();
        var party=sheet.getRange(j, 2, 1, 1).getValue();
        var vote = sheet.getRange(j, 3, 1, 1).getValue(); if (typeof vote != 'number') vote=100;
        if (candidate!=""){
          writeRow=[wards[i],candidate,party,vote];
          table.push(writeRow);
        }
      }
    }
  }
  var destinationRange = bigsheet.getRange(1, 1, table.length, 4);
  destinationRange.setValues(table);
}

Again, I make use of a utility function, this time to grab the names of the wards from the ward spreadsheet:

function getWardNames(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
     
   var sheet = ss.getSheetByName("Wards");
   var wardNames=[];
   if (sheet != null) {
       var col_ward=getColNumber(sheet,"Ward");
       var startRow = 2;  // First row of data to process
       var numRows = getMaxRows(sheet);
       var dataRange = sheet.getRange(startRow, 1, numRows, 1);
       var data = dataRange.getValues();
       for (i in data) {
        var row = data[i];
        wardNames[wardNames.length]=row[col_ward];
       }
   }
  return wardNames;
}

Running the aggregator function pulls all the data into one sheet:

Finally, for completeness, I added a routine that will delete the separate ward sheets:

function deleteWardSheets(){
     var ss= SpreadsheetApp.getActiveSpreadsheet();
     var wardNames=getWardNames();
     for (var i=0;i<wardNames.length;i++){
       ss.setActiveSheet(ss.getSheetByName(wardNames[i]));
       ss.deleteActiveSheet();
     }
  }

Supposedly, it’s possible to share scripts by submitting them to an Apps script gallery:

On submitting a script, it appears to get passed into an approvals process, so I’m not sure what happens next…?

Anyway – the code is all provided above if you want to try it out. Remember, what it’s doing is looking at a list of Ward names and identifiers, creating a sheet for each Lichfield local council ward, importing the election results table for each ward from the Lichfield site into the appropriate sheet, then providing another function that generates an aggregated datatable containing all the results, annotated with the name of the appropriate ward.

I was wondering whether this could all be done with from single function call, but I got twitchy that if I called a sheet before the imported data was loaded everything would break.

The take home from me about this is that with a little bit of creativity you can mix and match spreadsheet formula and Javascript functions. (I don’t know if spreadsheet formula can be accessed directly from the apps script?) That is, you can run calculations either in Javascript/Apps script, or if it’s more convenient to use a spreadsheet formula, you can…

Just a quick post to log another Google Apps script how to – this time how to grab a 2D array of CSV data from a URL and then paste it into a spreadsheet. For the trivial case, we could just as easily do this with an =importData() formula, but I’m still working out what bits of glue might be useful on the Apps script front…;-)

So – the demo function will take a blank spreadsheet and do this to it:

That is, shove some data from a query run over a data.gov.uk SPARQL endpoint into it.

Here’s the script – it shows how to HTTP POST a query from the Apps script to the SPARQLproxy endpoint, grab the results back as CSV, then convert them crom the CSV array to a range that can be posted into the spreadsheet.

function dgtest(){

  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getActiveSheet();
  var sc = ss.getActiveCell();

  //I really need to tweak this so we can enter a SPARQL query and then generate the args string from it...
  var args="query=prefix+sch-ont%3A+%3Chttp%3A%2F%2Feducation.data.gov.uk%2Fdef%2Fschool%2F%3E%0D%0ASELECT+%3Fschool+%3Fname+%3Fdate+%3Fdistrict+WHERE+{%0D%0A%3Fschool+a+sch-ont%3ASchool%3B%0D%0Asch-ont%3AestablishmentName+%3Fname%3B%0D%0Asch-ont%3AopenDate+%3Fdate%3B%0D%0Asch-ont%3AdistrictAdministrative+%3Fdistrict.%0D%0A}+ORDER+BY+DESC%28%3Fdate%29+LIMIT+15&output=csv&callback=&tqx=&service-uri=http%3A%2F%2Fservices.data.gov.uk%2Feducation%2Fsparql";

  var x=UrlFetchApp.fetch('http://data-gov.tw.rpi.edu/ws/sparqlproxy.php',{method: 'post', payload: args});
  var ret=x.getContentText();
  ret = CSVToArray( ret, "," );

  var arr = [];
  var c = [];
  for (var i=0;i < ret.length-1;i++) {
    c=[];
    for (var j=0; j< ret[0].length;j++){
      c.push(ret[i][j]);
    }
    arr.push(c);
  }

  var destinationRange = ss.getRange(1, 1, i, j);
  destinationRange.setValues(arr);
}

The CSV2Array function is one I found on Stack Overflow…

I was at a meeting yesterday looking at rebooting the OU’s Facebook strategy. With a bit of luck, this means that we’ll be doing another push on the OU Facebook apps that were developed several years ago now and which I still believe provide a sound basis for a range of community building and social learning support services (Course Profiles – A Facebook Application for Open University Students and Alumni).

The apps were largely developed out of time and in stolen time, and it seems that things are likely to continue in this way (which is both a plus – freeing us from constraints of interminable committees wanting to plan strategies rather than jfdi, and a minus – @liamgh is the only person we trust with the code which means any maintenance falls to him ;-)

For those who don’t remember the apps we developed, there were two: Course Profiles, which allowed students to declare the courses that had taken were taking and intended to take, and then provided a range of services around that information (find friends on a course, find a study buddy, link to course information or course related OpenLearn resources, get course recommendations); and My OU Story, where students could maintain a “status diary” about their progress on a course, along with a mood indicator so they could track their mood over a course, and other app users could add supportive comments. (I’d be surprised if anyone in the Student Services retention project has even heard about this project, but looking at some of the peer support that has gone on within the context that app, I’d argue it might be contributing to retention…)

Course Profiles quickly attracted several thousand users following the initial push just after it was first launched, so it evidently served a need then that presumably still exists today, i.e. a badging mechansims for celebrating course achievements and declaring future study intentions. One thing that might be worth looking at is the rate at which early adopters of Course Profiles have continued to update it, and report on the extent to which their original “future study” intentions converted to actual course registrations.

There’s also going to be a push on growing the number of fans on the official OU profile page. I’m not sure what plan @stuartbrown has for growing the numbers (for the task appears to have fallen to him…;-) but with a bit of luck the apps as well as the fan page will get highlighted through some of the official communication channels.

We also had a bit of discussion around other potential apps. Something I’d quite like to see would be a gallery app pulling images from the various flickr groups that have popped up around the T189 Digital Photography short course. Alumni of that group are already pretty active, and have just launched their first online exhibition, so if we could provide a channel that increases the audience for their show, and if they’re happy for us to amplify it via an OU Facebook app, that might be quite a fun thing to try as a community building app… (For more about the background to the exhibition, see Inspiring Learners; also see the T189 Graduates’ Exhibition).

(I also wonder if a similar gallery style app might work to showcase some of the games that students on T151 Digital worlds manage to create, all with their permission of course…)

Someone (I forget who) also suggested a “Share on Facebook” button within the gallery environment students use to build their portfolio whilst they take T189 (limited so that sharing was limited to photographs that a student had uploaded themselves, of course). This would amplify a student’s work and progress on a course to their Facebook friends, and provide their friends with a glimpse of what sorts of activities are involved in this particular OU course.

One thing I never even half managed to convince anybody that it was important was the data that was collected by the Course Profiles app in particular, though I did have a go at a few quick’n'dirty takes on this, such as OU Course Profiles Facebook App – Treemaps, Hierarchical Course Clusters from Course Profiles App and Tinkering with Google Charts (which started to consider what a course team dashboard view might look like). I was mulling this over again last night, and the following uses came to mind if we started to reconcile Course Profiles with institutional data (something we were always wary of, but anyway – here’s the thinking…;-)

- predictors and conversion rates: I’m not sure if Liam is logging when/how users change their status updates, but it’d be useful to know what percentage of users are updating their Course Profiles (e.g. from ‘currently taking’ to ‘taken’ courses, or more interestingly ‘intend to take’ to taking) and whether an “intend to take” course declaration is a good predictor of whether students do actually take a course. There’s an obvious quick win here for a possibly intrusive marketing campaign chasing folk who’ve declared an ‘intend to take’ course but don’t appear to have followed up on it;

- predicting course sizes: with several thousand users, does the sample of users on Course Profiles predict future course enrollment numbers? As far as I know, no-one in planning ever came to us asking to peak at our data to explore this. Nor did any more than a couple of Course Chairs ever seem to think it was interesting that we had stated intentions about course pathways, and that for new courses in particular we might be able to spot whether students were signing up for a course based on a pathway the course team was hoping for?

- retention: is the retention rate of students on a course who are on Facebook with Course Profiles and/or My OU Story different to the retention rate across the course as a whole? Does the fact that students who have declared ‘intend to take’ courses on the Course Profile correlate with their likelihood of completing an award?

- course planning and recommendation: on the one hand, courses appear to have natural numbers; on the other, working out what courses to take in what order for a particular degree given various factors (such as courses already taken, course exclusions etc) can be a confusing affair. At the moment, I believe a rule based support tool is being explored to help with course recommendations, but how well do those suggestions compare with a simple clustering based on Course Profiles data?

PS Just in passing, it’s worth noting that as with other groups who’ve used Facebook to mount campaigns against unpopular corporate decisions, OU students are no different… Open University curbs Tesco ‘clubcard degree’ scheme .

In the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google Apps Script. After a quick chat with the person who was compiling a spreadsheet they wanted to use to populate a set of calendars, I revisited the script to make a few tweaks and hopefully increase its usability.

So here’s a glimpse of the spreadsheet they’re using to list dates for various campaigns and channels where related activity might occur. Firstly, we have some columns relating to the event or activity, and the dates on which they occur. The first column (added to calendar) is a control switch that identifies that the calendar details have been updated for that event:

Within the spreadsheet, I set the two date columns to have the Date type (from the Tools menu, set the Data Validation option to Date). I’m not sure how the spreadsheet is (correctly) identifying the US date format (MM-DD-YY) – maybe from a US timezone as a global setting for the spreadsheet?

As well as various other admin columns, there are columns relating to whether or not a channel will be used to support a particular event:

From what I could ascertain, the way the spreadsheet is supposed to work goes along the lines of: someone adds details of an event and the associated channels for the event to the spreadsheet. “Add” in a channel column says that event is to be added to that channel calendar. When the updating script is run, for each event it checks the control column A to see that an event hasn’t been added to the various channel calendars, and if it hasn’t checks the channel columns; if a channel column is set to “Add” the event details are added to that event calendar.

So – how do I need to modify the original script? Firstly, the original script use the default calendar. In this case, we need a separate calendar for each channel, so in Google Apps I created one calender per channel:

We can grab a calendar by name from a spreadsheet apps script using a call of the form:

 var cal_broadcast=CalendarApp.openByName("broadcastDemo");

When the script runs, we need to grab the appropriate range of cells from the spreadsheet to see which calendars to update. For testing purposes, I only grabbed a few rows…

var startRow = 2;  // First row of data to process
var numRows = 4   // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 26);
var data = dataRange.getValues();

for (i in data) {
  var row = data[i];
  var title = row[1];
  var desc=row[15];
  var added = row[col_added];  //Check to see if details for this event have been added to the calendar(s)
  var tstart = row[2]; //start time - I have defined the column in the spreadsheet as a Date type
  var tstop = row[3]; //start time - I have defined the column in the spreadsheet as a Date type
  var broadcast=row[col_broadcast]; // is this event one to "Add" to the broadcast calendar?
  var itunes=row[col_itunes]; // is this event one to "Add" to the itunes calendar? etc
  var youtube=row[col_youtube];
  if (added!="Added") { //the calendar(s) have not been updated for this event
    if (broadcast=="Add") {
      cal_broadcast.createEvent(title, tstart,tstop, {description:desc}); //add the event to the "broadcast" calendar
    }
    if (itunes=="Add"){
      cal_itunes.createEvent(title, tstart,tstop, {description:desc});
    }
    // etc for each channel
    var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
    sheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
  }
}

In order to identify which columns to use to identify the broadcast, itunes, etc channels, I went defensive (the following bit of code comes before the previous snippet; what is does is to look at each column heading, and then set the column number for each channel appropriately based on its name; I should probably use a similar technique to identify the start/stop dates. What this approach does is accommodate changes to the spreadsheet in terms of the insertion of additional columns or the reordering of columns, for example, at a later date):

var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); //need a routine to set active sheet by name?
//go defensive
var col_broadcast,col_itunes, col_youtube=1;
var maxcols=sheet.getMaxColumns();
for (var j=1;j<=maxcols;j++){
  var header= sheet.getRange(1, j, 1, 1).getValue();
  switch(header){
   case "Added to Google (Y/N/Hold)":col_added=j-1;
   case "Broadcast":col_broadcast=j-1; break;
   case "iTunes":col_itunes=j-1; break;
   case "YouTube": col_youtube=j-1; break;
   default:
  }
}

Running the combined function thus searches the spreadsheet for the appropriate channel columns and control column, checks the control column for each event entry to ensure that the event hasn’t been added to the selected calendars, and then adds the event to the appropriate channel calendars if required.

Playing with the script, it seemed a little bit clunky, so I tweaked it to update the channel cells with the word “Added” if it had been set to Add, and the calendar had been updated:

if (broadcast=="Add") {
  cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
  dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}

It also struck me that if the settings of a channel was updated to “Add” after that event was updated, that channel’s calendar would never get updated. So I created a variant of the updating function that would just run on a per column basis and update a calendar entry for an event if it was set to “Add”, rather than checking the control column:

function caltestAddtoCal_broadcast(){ caltestAddtoCal("broadcast"); }
function caltestAddtoCal(addCal){
  //...
  if (addCal!="") {
    if ((addCal=="broadcast")&&(broadcast=="Add")) {
      cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
      dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); //+1 is offset for sheet numbering
    }
  // ...
  }
}

What this means is is that a channel controller can update entries in their calendar by running the script just for that channel and adding “Add” to any event they want adding to the calendar, the list of “Added” entries showing which events have already been added to that calendar:

Having doodled a script that sort of works, it’s now time to hack it around it so it looks a little more elegant. Which means refactoring.. sigh… and another reprise in a day or two, I guess…?!

Got another query via Twitter today for a Yahoo Pipe that is oft requested – something that will aggregate a number of feeds and prefix the title of each with a slug identifying the appropriate source blog.

So here’s one possible way of doing that.

Firstly, I’m going to create a helper pipe that will truncate the feed from a specified pipe to include a particular number of items from the feed and then annotate the title with a slug of text that identifies the blog: (Advisory: Truncate and Prefix).

The next step is to build a “control panel”, a place where we list the feeds we want to aggregate, the number of items we want to truncate, and the slug text. I’m going to use a Google spreadsheet.

We can now create a second pipe (Advisory: Spreadsheet fed feed aggregator that will pull in the list of feeds as a CSV file from the spreadsheet, for each feed grab the feed contents, then truncate them and badge them as required using the helper pipe:

To keep things tidy, we can sort the posts so they appear in the traditional reverse chronological order.

PS Hmmm… it might be more useful to be able to limit the feed items by another criteria, such as all posts in the last two weeks? If so, this sort of helper pipe would do the trick (Advisory: Recent Posts and Prefix):

HTH:-)

Chatting just now to @paulafeery, I learned about something that completely passed me by at the time – the OU COP15 Press Room.

Built on WordPress (yay!-) using the Studiopress Lifestyle theme, the site provided a single point of access to content and several OU academics with relevant expertise in the area in order to “support” journalists writing around issues raised over the course of the COP15 Climate Talks last year.

The site makes good use of categories to partition content into several areas (each, of course, with its own feed:-) So for example, there are categories for News, Research and Opinion, the latest items from which are also highlighted on the front page:

The site also syndicated a feed from an OU Audioboo site where OU academics were posting audio commentaries on related matters:

I don’t think there was a COP15 channel on the OU Boxee TV channel though, although there was an OU COP15 Youtube playlist:

(It strikes me that it might have been good to put a playlist player in an obvious or obviously linked to place on the COP15 press room front page? I also wonder how we might best guarantee OU exposure from any video material we publish and what sort of form it needs to be in, and under what sort of licensing conditions, in order for news outlets to run with it? e.g. How the Ian Tomlinson G20 video spread The Guardian brand across the media, Video Journalism and Interactive Documentaries and to a lesser extent The OU on iPlayer (Err? Sort of not…).)

Anyway, this thematic press room seems like a great idea to me – though I’d have also liked to see a place for 200-500 word CC (attribution) licensed explanatory posts of the sort that could be used to populate breakout factual explanation boxes (with attribution) in feature articles, for example.

Compared to the traditional press release site (which apparently serves as much as an OU timeline/corporate memory device as anything, something that hadn’t occurred to me before…) this topical press room offers another perspective on the whole “social media press release” thang (e.g. Social Media Releases and the University Press Office).

If you want to look back over the COP15 Press Room, you can find it here: OU COP15 Press Room

PS If I was as diligent as Martin Belam at this sort of critique, I’d have probably have done a comparison of the OU Press Room site and example output as appearing on the Guardian COP15 topic page:

or the BBC COP15 topic page:

in order to see what sorts of content fit there might be going from copy on the OU Press Room to the material that is typically published on news media sites. If the content doesn’t fit, no-one will re-use it, right?

Maybe next time?!;-) (If you know of such a comparative critique, please post a link back to here or add a comment below;-)

[See also: UK Nordic Baltic Summit 2011 discussion site.]

A comment to Updating Google Calendars from a Google Spreadsheet, where I showed how to get events described in a Google spreadsheet into a Google Calendar wondered if the other way around is possible? … It would be great for logging hours for projects or declaring working hours ;-)

Yes:-) Twenty seconds ago, this spreadsheet was empty:

A quick run of a script and I populated it from my default calendar using Google Apps script. Once again, all I did was peek through the docs and pull out the fragments I needed: Here’s the script:

function caltest3(){
  //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents
  // The code below will retrieve events between 2 dates for the user's default calendar and
  // display the events the current spreadsheet
  var cal = CalendarApp.getDefaultCalendar();
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var events = cal.getEvents(new Date("March 8, 2010"), new Date("March 14, 2010"));
  for (var i=0;i<events.length;i++) {
    //http://www.google.com/google-d-s/scripts/class_calendarevent.html
    var details=[[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime()]];
    var row=i+1;
    var range=sheet.getRange(row,1,1,4);
    range.setValues(details);
  }
}

So now not only can we use a spreadsheet as a database we can also use a calendar in a similar way, and if necessary, link them all together?