Create a Dashboard in Google Sheets showing open pull request counts from Github

github_prs.gs

  //use this to get current counts of Pull Requests to put into a dashboard.
  // Things to update: YOUR_USER_NAME, YOUR_ACCESS_TOKEN, YOUR_REPO_PATH (see below)

// add custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom GitHub Menu')
      .addItem('Get User Repos','getUserRepos')
      .addItem('Get rate quota','getGitHubRateLimit')
      .addToUi();
}

//customise this based on your sheet...
function updateOpenPRs() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("open_prs").getRange('B2:B2').setValue(getOpenPRCount());
}

function getJSON(url) {
 //update with your personal access token and username: https://github.com/settings/tokens
  username = "YOUR_USER_NAME"
  password = "YOUR_GITHUB_ACCESS_TOKEN"
  

  var options = {};
  options.headers = {"Authorization": "Basic " + Utilities.base64Encode(username + ":" + password)};
  
  var response = UrlFetchApp.fetch(url, options);
  return(JSON.parse(response.getContentText()));

}

function getOpenPRCount(){
  return(getJSON("https://api.github.com/repos/YOUR_REPO_PATH").length);
}
 
/***************************************/
// Get User Repos
function getUserRepos() {
   var service = getGithubService_();
   
   if (service.hasAccess()) {
     Logger.log("App has access.");
     var api = "https://api.github.com/users/peterkappus";  // example
     
     var headers = {
       "Authorization": "Bearer " + getGithubService_().getAccessToken(),
       "Accept": "application/vnd.github.v3+json"
     };
     
     var options = {
       "headers": headers,
       "method" : "GET",
       "muteHttpExceptions": true
     };
     
     var response = UrlFetchApp.fetch(api, options);
     
     var json = JSON.parse(response.getContentText());
     
     Logger.log(json); // example
   }
   else {
     Logger.log("App has no access yet.");
     
     // open this url to gain authorization from github
     var authorizationUrl = service.getAuthorizationUrl();
     Logger.log("Open the following URL and re-run the script: %s",
         authorizationUrl);
   }
 }
 
/***************************************/
// Get Rate limit
function getGitHubRateLimit() {
  // set up the service
  var service = getGithubService_();
  
  if (service.hasAccess()) {
    Logger.log("App has access.");
    
    var api = "https://api.github.com/rate_limit";
    
    var headers = {
      "Authorization": "Bearer " + getGithubService_().getAccessToken(),
      "Accept": "application/vnd.github.v3+json"
    };
    
    var options = {
      "headers": headers,
      "method" : "GET",
      "muteHttpExceptions": true
    };
    
    var response = UrlFetchApp.fetch(api, options);
    
    var json = JSON.parse(response.getContentText());
    var responseCode = response.getResponseCode();
    
    Logger.log(responseCode);
    
    Logger.log("You have " + json.rate.remaining + " requests left this hour.");
    
  }
  else {
    Logger.log("App has no access yet.");
    
    // open this url to gain authorization from github
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log("Open the following URL and re-run the script: %s",
        authorizationUrl);
  }
}