Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.