Get PROD into Google Spreadsheet. Used in http://mashe.hawksey.info/2011/08/open-data-equals-open-scrutiny-but-doesnt-always-equal-all-of-the-answer/

gistfile1.js

function getProdData() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("PROD");
  var aUrl = "http://bit.ly/nfL6Fx"; //shortened http://api.talis.com/stores/jisc-prod-dev1/services/sparql?query=PREFIX+prod%3A+%3Chttp%3A%2F%2Fprod.cetis.ac.uk%2Fvocab%2F%3E%0D%0APREFIX+doap%3A+%3Chttp%3A%2F%2Fusefulinc.com%2Fns%2Fdoap%23%3E%0D%0APREFIX+mu%3A+++%3Chttp%3A%2F%2Fwww.jiscmu.ac.uk%2Fschema%2Fmuweb%2F%3E%0D%0APREFIX+jisc%3A+%3Chttp%3A%2F%2Fwww.rkbexplorer.com%2Fontologies%2Fjisc%23%3E%0D%0ASELECT+DISTINCT+*%0D%0AWHERE+{%0D%0A++++%3Fs+a+doap%3AProject+.%0D%0A%09%3Fs+doap%3Aname+%3Fproject+.%0D%0A%09%3Fs+jisc%3Aend-date+%3Fdate+.%0D%0A%09%3Fs+doap%3Ahomepage+%3Fhomepage+.%0D%0A}
  try {
    var options =
    {
      "method" : "get"
    };
    var response = UrlFetchApp.fetch(aUrl, options);
    if (response.getResponseCode() == 200) {
      var responseStr = response.getContentText();
      var XMLdoc = Xml.parse(responseStr); // parse xml
      var projects = XMLdoc.sparql.results; 
      var proj = projects.getElements("result");
      var cutOffDate = new Date();
      cutOffDate.setYear(cutOffDate.getYear()-3);
      for (var i = 0; i < proj.length; i++) {
        var id = proj[i].getElements()[0].getElements()[0].getText().replace("prod","prod.cetis.ac.uk");
        var projectName = proj[i].getElements()[1].getElements()[0].getText();
        var endDate = formatDate(proj[i].getElements()[2].getElements()[0].getText());
        var url = proj[i].getElements()[3].getElements()[0].getText();
        if (endDate > cutOffDate && endDate < new Date()){
          var nextRow = sheet.getLastRow()+1;
          //sheet.getRange(nextRow, 1).setValue(id);
          sheet.getRange(nextRow, 2).setValue(projectName);
          sheet.getRange(nextRow, 3).setValue(endDate);
          sheet.getRange(nextRow, 4).setFormula('=HYPERLINK("'+id+'","Go")');
          sheet.getRange(nextRow, 5).setValue(url);
        }
      }
    }
  } catch(e) {
    Logger.log(e + " "+i);
  }
}