Google Spreadsheets: Automatically fetch and populate a set of GitHub users and their total # of assigned PRs (across a set of specified repos).

pullrequest-counter.gs

var repos = [
  "constancecchen/object-fit-polyfill",
  "octocat/Hello-World",
];

var users = [
  "constancecchen",
  "octocat",
];

var githubAPI = "https://api.github.com/repos/";
var accessToken = YOUR_OAUTH_TOKEN; // Only needs private repo access. Not needed if only querying public repos.
var since = "2017-01-01T00:00:00Z"; // ISO 8601

function onOpen() {
  // Set column headings
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Dev")

  var date = since.substr(0,10).split("-");
  date = [date[1], date[2], date[0]].join("/")
  SpreadsheetApp.getActiveSheet().getRange("B1")
    .setValue("# of PRs assigned since " + date)
    .setComment("In repositories:\n\n" + repos.join("\n"));

  // Zero out PRs so viewers know that data is fetching
  SpreadsheetApp.getActiveSheet().getRange("A2:B6").setValue("");

  // Populate each row with a Github username and their total # of assigned PRs
  for (i = 0; i < users.length; i++) {
    var totalPRs = 0;
    var range = "A" + (i + 2);
    SpreadsheetApp.getActiveSheet().getRange(range).setValue(users[i]);

    for (j = 0; j < repos.length; j++) {
      var url = githubAPI + repos[j] + "/issues"
      + "?access_token=" + accessToken
      + "&since=" + since
      + "&assignee=" + users[i]
      + "&state=all";

      var response = UrlFetchApp.fetch(url);
      var json = JSON.parse(response.getContentText());
      var PRs = json.length || 0;
      
      totalPRs += json.length;
    }

    var range = "B" + (i + 2);
    SpreadsheetApp.getActiveSheet().getRange(range).setValue(totalPRs);
  }

}