Google spreadsheet script that lists open pull requests on specified repos https://docs.google.com/spreadsheets/d/1sPyN9z9wZMpTNwqCfa6R9QSPZkIW4iQd-H4gZC7ILLk/edit?usp=sharing

googlescript.js

// This is the main script function
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  
  // target repositories
  var openedPullRequestsSpreadsheet = ss.getSheetByName('OPEN_PULL_REQUESTS');
  
  // spreadsheet that contains a list of Github repositories
  var coreRepositoriesSpreadsheet = ss.getSheetByName('CORE_REPOSITORIES');
  
  // spreadsheet that contains a list of ember-learn Github repositories
  var learningRepositoriesSpreadsheet = ss.getSheetByName('LEARNING_TEAM_REPOSITORIES');
  
  // get list of Github repositories from spreadsheet
  var learningRepositoriesRange = learningRepositoriesSpreadsheet.getDataRange().getValues();
  var learningRepositories = learningRepositoriesRange.reduce(function(acc, repo) {
    return acc.concat(repo);
  }, []);
  
  var coreRepositoriesRange = coreRepositoriesSpreadsheet.getDataRange().getValues();
  var coreRepositories = coreRepositoriesRange.reduce(function(acc, repo) {
    return acc.concat(repo);
  }, []);
 
  // clear the target spreadsheet
  openedPullRequestsSpreadsheet.clear({ contentsOnly: true });
  
  coreRepositories.forEach(function(repo){
    var url = 'https://api.github.com/repos/'+repo+'/pulls';
    var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());
    
    response.forEach(function(pr){
      if (/wip/i.test(pr.title)) { return; }
      if (!/doc/i.test(pr.title)) { return; }
      appendPullRequest(repo, pr, openedPullRequestsSpreadsheet)     
    });
  });
  
  
  learningRepositories.forEach(function(repo){
    var url = 'https://api.github.com/repos/'+repo+'/pulls';
    var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());
    
    response.forEach(function(pr){
      if (/dependabot/.test(pr.user.login)) { return; }
      if (/wip/i.test(pr.title)) { return; }
      appendPullRequest(repo, pr, openedPullRequestsSpreadsheet)
    });
  });

}

function appendPullRequest(repo, pr, spreadsheet) {
  var row = []; 
  
  var commentsUrl = pr.comments_url;
  // fetch the Pull Request comments
  var commentsData = JSON.parse(UrlFetchApp.fetch(commentsUrl).getContentText());
  
  // find date of latest activity on the Pull Request
  var lastUpdate = new Date(pr.updated_at);
  if (commentsData.length > 0) {
    var timeSortedCommentsData = commentsData.sort(function(a, b){
      return new Date(b.updated_at) - new Date(a.updated_at);
    });
    lastUpdate = new Date(timeSortedCommentsData[0].updated_at);
  }
  
  row.push(repo);
  row.push(pr.title);
  row.push('=HYPERLINK("'+ pr.user.html_url +'","'+ pr.user.login +'")');
  row.push(lastUpdate);
  row.push(pr.html_url);
  
  spreadsheet.appendRow(row);
}

// This updates the graph data
function trackInactivePullRequests() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var inactivePullRequestsSpreadsheet = ss.getSheetByName('OPENED PULL REQUESTS INACTIVE >= 1 WEEK');
  var range = inactivePullRequestsSpreadsheet.getRange("A3:A3");
  var cell = range.getCell(1, 1);
  var row = [];
  
  row.push(new Date());
  row.push(cell.getValue());
  
  inactivePullRequestsSpreadsheet.appendRow(row);
}