This Google Scripts code passes parameters from a Google Spreadsheet to the Hypothes.is API. It then parses the API's json into a new sheet. On a third sheet it builds a table of users and URLs and runs countifs formulas to count annotations by user by url. Here's a link to a blog post about the project: www.johnastewart.org/tools/hypothes-is-co…

CollectAnnotations.gs

function collectAnnotations() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange(7, 5, 4).getValues(); // create array of parameters set by the user by generating an array from row 7, column 5 for 3 rows
  
  // parsing the array of parameters set by the user
  var user = values[0];
  var url = values[1];  
  var tag = values[2];
  var anyField = values[3];

  var group = sheet.getRange(14, 5).getValues(); // get the user entered value for the group 

  var authorization = sheet.getRange(20, 5).getValues(); // get the user entered value for the authorization token
  
  var limit = sheet.getRange(21, 5).getValues(); // get the user entered value for the authorization token
  if (limit < 1)
    limit = 200;

  // build the API request URL with a base and whatever parameters were entered
  var request = 'https://hypothes.is/api/search?limit=' + limit; //pull the account name from cell A1 in the spreadsheet
  
  if ( tag != '' )
      request += '&tags=' + tag;
  if ( user != '' )
      request += '&user=' + user;
  if ( group != '' )
      request += '&group=' + group;
  if ( url != '' )
      request += '&url=' + url;
  if ( anyField != '' )
      request += '&any=' + anyField;
  
  // If there is an authorization token we have to set up headers and pass those to options for the UrlFetchApp.fetch
  if ( authorization != '' )
  {
    var headers = 
        {
          "Authorization" : "Bearer " + authorization
        };
  
    var options =
        {
          "headers" : headers
        };
  
  var response = UrlFetchApp.fetch(request, options); // get feed
  } else 
  {
    var response = UrlFetchApp.fetch(request)
    };  


  // Get the active sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // The code below will deletes an old Archive to make space for the new
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  while(sheets.length > 1)
  {
    ss.deleteSheet(sheets[1]);
    sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  }
    
  ss.insertSheet('Annotation Archive', 1);
  
  // a set of variables for keeping track of student annotations
  var students = [];
  var readings = [];

  // a set of variables for cleaning up the output for the spreadsheet
  var userAccount = "";
  var modifiedUser = "";
  var time = "";
  var modifiedTime = "";

  // parse the json
  var data = JSON.parse(response);
  var counter = data.total;

  if(limit < counter){
    counter = limit;} 

  Logger.log(counter);

  var columnHeaders = ["User", "URL", "Text", "Time", "Annotation Link", "Tag(s)"];
  SpreadsheetApp.getActiveSpreadsheet().appendRow(columnHeaders);

  // output the json into the rows of a spreadsheet
  for (var i = 0; i< counter; i++) {
    
  var stats = [];
  
 userAccount = data.rows[i].user;
    modifiedUser = userAccount.replace("acct:", "");

    time = data.rows[i].updated;
    modifiedTime = time.replace("T", " ");
    modifiedTime = modifiedTime.substring(0, 16); //trim off the fractional time of annotation
    
    stats.push(modifiedUser); //output the user to the A column
    stats.push(data.rows[i].uri);//output the uri to the B column
    stats.push(data.rows[i].text);//output the text of the annotation to the C column
    stats.push(modifiedTime);//output the time of the annotation to the D column
    stats.push(data.rows[i].links.html);//output the HTML link for the annotation to the E column

    //for(var g in data.rows[i].tags){
      //stats.push(data.rows[i].tags[g]);} 

    if(data.rows[i].tags.length > 0){
      var tagString = data.rows[i].tags.toString();
      stats.push(tagString); //output the Tags for the annotation to the F column and as many more columns as needed
    }
    
    students.push(modifiedUser);
    readings.push(data.rows[i].uri);
   
  SpreadsheetApp.getActiveSpreadsheet().appendRow(stats);
  }

  // Create a new sheet with a table of annotators in rows and urls as column headers
  ss.insertSheet('Annotation Count', 2);
  
  // Create uniqueReadings by eliminating duplicate entries from readings array  
  var uniqueReadings = [];
  readings.sort();
  uniqueReadings.push(readings[0]);
  for(var n in readings){
    Logger.log(uniqueReadings[uniqueReadings.length-1]+'  =  '+readings[n]+' ?');
    if(uniqueReadings[uniqueReadings.length-1]!=readings[n]){
      uniqueReadings.push(readings[n]);  
    }
  }
  
  // Add the readings as column headers
  sheet = SpreadsheetApp.getActiveSheet();
  var abc = 2;
  for(var n in uniqueReadings){
    sheet.getRange(1, abc).setFormula('=HYPERLINK("http://via.hypothes.is/' + uniqueReadings[n] + '", "' + uniqueReadings[n] + '")');
    abc++;
  }

  // Create courseRoster by eliminating duplicate entries from students array  
  var courseRoster = [];
  students.sort();
  courseRoster.push(students[0]);
  for(var n in students){
    Logger.log(courseRoster[courseRoster.length-1]+'  =  '+students[n]+' ?');
    if(courseRoster[courseRoster.length-1]!=students[n]){
      courseRoster.push(students[n]);
    }
  }

  // Add the users as row headers
  var x = 2; 
  for(var n in courseRoster){
    sheet.getRange(x, 1).setValue(courseRoster[n]);
    x++;
  }
  
  // Add a countifs formula to all cells to count number of annotations by user per reading
  var calculationArea = sheet.getRange(2,2,courseRoster.length,uniqueReadings.length);
  calculationArea.setFormula("=countifs('Annotation Archive'!$A:$A,R[0]C1,'Annotation Archive'!$B:$B,R1C[0])");
  
  // Color cells to show where the number of annotations is more than 0

  var x = 2;

  for(var n = 0; n < courseRoster.length; n++){ 

    var y = 2;
    
    for(var p = 0; p < uniqueReadings.length; p++){
      
      var calculations = sheet.getRange(x, y).getValue();
      
      if(calculations > 0)
        sheet.getRange(x,y).setBackground("#d9ead3");
      
      y++; // increase y to move across columns within a row
    }
    
    x++; // increase x to move down a row
  }
}