Google Sheets Github PR Visualization

googleSheetsGithubPRVisualization.js

/*
To use this script:

1. Make a tab in your Google Sheet called "Branch Strategy".
2. Open the script editor for that Sheet by navigating to Tools > Script Editor.
3. Copy and paste this file into the editor there.
4. Replace YOUR_MILESTONE_NUMBER with the milestone your team uses to manage its PRs.
5. Replace YOUR_GITHUB_TOKEN with your Github token.
6. Add a trigger to run the `onOpen` function From spreadsheet onOpen.
7. Save the script.

Go back to your Sheet and refresh. You should see a new menu item called "Mavenlink". Click Mavenlink > Update Branches
to see your strategy!
*/

// In Google Scripts, we don't have ES6 or module support

var alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
var colors = {
  stale: '#f1c232',
  shippable: '#93c47d',
  normal: '#cccccc',
  needsAction: '#e06666'
}

function onOpen() {
  this.spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var entries = [{
    name : "Update Branches",
    functionName : "fetchBranchesAndRender"
  }]

  this.spreadsheet.addMenu("Github Branches", entries)
}

function PullRequest(branch) {
  this.title = branch.title
  this.pointingTo = branch.baseRefName
  this.branchName = branch.headRefName
  this.url = branch.url
  this.mergeable = branch.mergeable
  this.createdAt = branch.createdAt
  this.testStatus = branch.commits ? branch.commits.nodes[0].commit.status.state : ''
  this.labels = branch.labels ? branch.labels.nodes.map(function(label) { return label.name }) : []
  this.labelString = " (" + this.labels.join(' , ') + ")"
  this.setPRState()
  this.children = []
}

PullRequest.prototype.setPRState = function() {
  const context = this
  // save off label states so we only iterate through labels once
  this.labels.forEach(function(label) {
    switch (label) {
      case 'LGTM':
        context.LGTM = true; break
      case 'QA ✓':
      case 'Dev QA ✓':
        context.QAed = true; break
      case 'Requires QA':
      case 'Requires Dev QA':
        context.requiresQA = true; break
      case 'Blocked':
        context.blocked = true; break
      case 'WIP':
        context.WIP = true; break
      default:
        break
    }
  });

  this.shippable = this.mergeable && this.QAed && this.LGTM && !this.blocked && this.testStatus === 'SUCCESS'
  this.state = this.getState()
}

PullRequest.prototype.getState = function() {
  if (this.title === 'master') { return 'normal' }
  if (this.shippable) { return 'shippable' }
  if (this.needsAction()) { return 'needsAction' }
  if (this.stale()) { return 'stale' }
  return 'normal'
}

PullRequest.prototype.stale = function() {
  const createdAt = new Date(this.createdAt)
  const fiveDaysAgo = new Date()
  fiveDaysAgo.setDate(fiveDaysAgo.getDate() - 5)
  return createdAt < fiveDaysAgo
}

PullRequest.prototype.color = function() { return colors[this.state] }

PullRequest.prototype.numLeaves = function() {
  var count = 0
  if (this.children.length) {
    this.children.forEach(function(child) {
      count += child.numLeaves()
    })
  } else {
    count ++
  }
  return count
}

PullRequest.prototype.needsAction = function() {
  // if it is in a pull-through-able state
  if (!this.WIP && !this.blocked) {
    if (!this.requiresQA && !this.QAed) {
      // needs QA label of some sort
      return true;
    }

    if (this.testStatus === 'FAILURE' || this.testStatus === 'ERROR') {
      // branch is red or messed up
      return true
    }

    if (!this.mergeable) { return true; } // has conflicts
    // (to add later): if has reviewable label but no reviewer
    // return true
  }
}

function fetchBranchesAndRender() {
  const response = fetchBranches()
  const pullRequests = extractPullRequests(response)
  const tree = convertPRsToTree(pullRequests)
  renderTree(tree)
}

function fetchBranches() {
  const githubUrl = 'https://api.github.com/graphql'
  const query = "query {organization(login: \"Mavenlink\") {repository(name: \"mavenlink\") {milestone(number: YOUR_MILESTONE_NUMBER) {pullRequests(last: 100, states:[OPEN]) {nodes {title url labels (last: 100) {nodes {name}} createdAt baseRefName headRefName mergeable commits (last: 1) {nodes {commit {status {state}}}}}}}}}}"
  const options = {
    method: 'POST',
    payload: JSON.stringify({
      query: query
    }),
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'bearer ' + YOUR_GITHUB_TOKEN,
    }
  };
  return JSON.parse(UrlFetchApp.fetch(githubUrl, options));
}

function extractPullRequests(response) {
  return response.data.organization.repository.milestone.pullRequests.nodes.map(function(pull) { return new PullRequest(pull); });
}

function convertPRsToTree(PRs) {
  const master = new PullRequest({ title: 'master', pointingTo: null, headRefName: 'master', url: 'github.com/mavenlink/mavenlink' })
  const pullsWithMaster = PRs.slice()
  pullsWithMaster.push(master)
  PRs.forEach(function(pull) {
    findParentNode(pull, pullsWithMaster).children.push(pull)
  })
  return master
}

function findParentNode(target, pulls) {
  return pulls.filter(function(pull) { return pull.branchName === target.pointingTo; })[0];
}

function renderTree(tree) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  this.sheet = spreadsheet.getSheetByName('Branch Strategy');
  clearOldCells()
  renderLegend()
  displayNode(legendLabels.length + 2, 0, tree) // leave some space between legend and branches
}

var legendLabels = [
  { text: 'STALE, PULL ME THROUGH! (Open more than 5 days)', color: colors.stale },
  { text: "SHIP IT! (Green, no conflicts, QA'ed, LGTM)", color: colors.shippable },
  { text: "NEEDS ACTION!  (Doesn't have QA label, CI red, or has conflicts)", color: colors.needsAction }
]

function renderLegend() {
  var legendCells = this.sheet.getRange("B1:B" + legendLabels.length)
  legendCells.setFontColor('black');
  legendCells.setFontWeight('bold');
  legendCells.setVerticalAlignment('middle');
  legendLabels.forEach(function(label, index) {
    var cell = this.sheet.getRange("B" + (index + 1) + ":" + "B" + (index + 1));
    cell.setBackground(label.color);
    cell.setValue(label.text);
  })
}

function clearOldCells() {
  var range = this.sheet.getRange("A1:Z26");
  range.clear()
}

function displayNode(row, col, node) {
  addNodeToCell(row, col, node)
  if (node.children) {
    if(node.numLeaves() > 1) {
      const address = getCellAddress(row, col) + ':' + getCellAddress((row + node.numLeaves() - 1), col)
      this.sheet.getRange(address).mergeVertically();
    }

    // Keep track of total offset so far across children
    var offset = 0;
    node.children.forEach(function(child, index) {
      if (index > 0) {
        offset += node.children[index - 1].numLeaves() - 1
      }
      displayNode(row + index + offset, col + 1, child)
    })
  }
}

function addNodeToCell(row, col, node) {
  var cell = this.sheet.getRange(getCellAddress(row, col));
  cell.setFontColor('black');
  cell.setFormula("=hyperlink(\"" + node.url + "\";\"" + node.title+ "\")");
  cell.setBackground(node.color())
  cell.setVerticalAlignment("middle");
}

function getCellAddress(row, col) {
  return alphabet[col] + row
}

query.graphql

query {
  organization(login: YOUR_ORGANIZATION) {
    repository(name: YOUR_REPO) {
      milestone(number: YOUR_MILESTONE) {
        pullRequests(last: 100, states:[OPEN]) {
          nodes {
            title
            url
            labels(last: 100) {
              nodes {
                name
              }
            }
            createdAt
            baseRefName
            headRefName
            mergeable
            commits (last: 1) {
              nodes {
                commit {
                  status {
                    state
                  }
                }
              }
            }
            reviewRequests(first: 10) {
              totalCount
              nodes {
                requestedReviewer {
                  __typename
                }
              }
            }
          }
        }
      }
    }
  }
}