Get PIMS data 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 getPimsData() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("PIMS");
  var aUrl = "http://misc.jisc.ac.uk/pims/api/dev/project";
  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.Projects; 
      var proj = projects.getElements("Project");
      var cutOffDate = new Date();
      cutOffDate.setYear(cutOffDate.getYear()-3);
      for (var i = 0; i < proj.length; i++) {
        var endDate = formatDate(proj[i].getElement("EndDate").getText());
        var id = proj[i].getElement("Id").getText();
        var url = proj[i].getElement("Website").getText().trim();
        if (endDate > cutOffDate && endDate < new Date()){
        var nextRow = sheet.getLastRow()+1;
          sheet.getRange(nextRow, 1).setValue(id);
          sheet.getRange(nextRow, 2).setValue(proj[i].getElement("Name").getText());
          sheet.getRange(nextRow, 3).setValue(endDate);
          sheet.getRange(nextRow, 4).setFormula('=HYPERLINK("https://pims.jisc.ac.uk/projects/view/'+id+'","Go")');
          sheet.getRange(nextRow, 5).setValue(url);
        }
      }
    }
  } catch(e) {
    Logger.log(e);
  }
}