List GitHub Issues on Google SpreadSheets

code.gs

const ui = SpreadsheetApp.getUi();
const userProperties = PropertiesService.getUserProperties();

const ACCESS_TOKEN = 'personal access token';
const ALPHABETS = [...Array(26)].map((_, y) => String.fromCharCode(y + 65)); // https://gist.github.com/mreigen/fdeafcc08a9e44d976bd6a8db468c496

function onOpen(){
  ui.createMenu('GitHub')
  .addItem('List GitHub Issues', 'listGitHubIssues')
  .addItem('Set Personal Access Token', 'setToken')
  .addItem('Delete Personal Access Token', 'deleteToken')
  .addToUi();
}

function setToken(){
  const scriptValue = ui.prompt('Personal Access Token', ui.ButtonSet.OK);
  userProperties.setProperty(ACCESS_TOKEN, scriptValue.getResponseText());
}

function deleteToken(){
  userProperties.deleteProperty(ACCESS_TOKEN);
}

function listGitHubIssues() {

  const ORG_NAME = 'Organization Name'; // Organization Name
  const REPOSITORIES = ['Repository Name']; // Repositories
  const PERSONAL_ACCESS_TOKEN = userProperties.getProperty(ACCESS_TOKEN); // Personal Access Token

  // fetch json format issues data from API response
  const fetchIssues = function(repository){

    let page = 1;
    let all_issues = [];
    while(true) {
      const url = 'https://api.github.com/repos/' + ORG_NAME + '/' + repository + '/issues?state=all&sort=created&direction=asc&per_page=100' + '&page=' + page;
      const options = {
        'headers': {
          'Authorization': 'token ' + PERSONAL_ACCESS_TOKEN
        }
      }
      const response = UrlFetchApp.fetch(url, options);
      const json = response.getContentText();
      const pr_and_issues = JSON.parse(json);
      if (pr_and_issues.length == 0) {
        break;
      }

      // remove pull requests
      const issues = pr_and_issues.filter(pr_and_issue => pr_and_issue.pull_request == null);

      all_issues = all_issues.concat(issues);
      page++;
    }
    return all_issues;
  }

  // Extract items from json data
  const extractAttributesOfIssue = function(issue){
    const title = issue["title"];
    const url = issue["html_url"];
    const state = issue["state"];
    const opend_at = issue["created_at"] ? issue["created_at"].substring(0, 10) : "";
    const closed_at = issue["closed_at"] ? issue["closed_at"].substring(0, 10) : "";
    const assignee = issue["assignee"] ? issue["assignee"]["login"] : "";
    const milestone = issue["milestone"] ? issue["milestone"]["title"] : "";
    const labels = issue["labels"] ? issue["labels"].map(label => label["name"]).sort().join(',') : "";

    return [
      title,
      url,
      state,
      opend_at,
      closed_at,
      assignee,
      milestone,
      labels,
    ]
  }

  const sortByValueOfIndex = function(ary, index){
    return ary.sort(function(a,b){
      if( a[index] < b[index] ) return -1;
      if( a[index] > b[index] ) return 1;
      return 0;
    });
  }

  // Get active spreadsheet
  const ss = SpreadsheetApp.getActive();

  // Write issue's info on the sheet
  REPOSITORIES.forEach(repository => {
    let issueItems = fetchIssues(repository).map(function(issue){
      return extractAttributesOfIssue(issue);
    });

    // sort by due on
    issueItems = sortByValueOfIndex(issueItems, 3);
    const titles = ["Title", "Issue URL", "State", "opened_at", "closed_at", "Assignee", "Milestone", "Labels"];
    issueItems.unshift(titles);

    const user = Session.getActiveUser();
    const user_id = user.getUserLoginId();
    const sheet_name = repository + "_" + user_id;

    let sheet = ss.getSheetByName(sheet_name);
    if(sheet == null) {
      ss.insertSheet(sheet_name);
      sheet = ss.getSheetByName(sheet_name);
    }
    for (let i = 0; i < issueItems.length; i++) {
      sheet.getRange("A" + (i + 1) + ":" + ALPHABETS[issueItems[i].length - 1] + (i + 1)).setValues([issueItems[i]]);
    }
  });
}