My team at work keeps many small branches off of master for our feature development. Our goal is to ship small features as frequently as possible, and so we often branch new feature branches off of other feature branches that are currently in development. This leads to having multiple branch chains off of master, which can be very time-consuming to keep track of and understand. We use Github and their milestone feature to group our team’s pull requests together, and we want a way to visually see what pull requests we have open, what needs to be worked on, and what branches are highest priority.

We used to use Github’s own milestone view, but it has drawbacks:

The main problem is that I can’t see which branches are off of master, and which branches are off of each other. This makes it difficult to easily identify the high-priority branches (the ones closest to master). So, my team started using a Google Drawing to manually keep track of our PR tree.

Every time we merged or created a branch, we had to update the spreadsheet with the branch name, title, and link. It took so much time to maintain. I wanted to automate the process. I knew Github’s GraphQL API and corresponding explorer were powerful and flexible, and I knew we could write a script to render stuff into a Google Spreadsheet. I didn’t want to create yet another document I’d have to keep track of (I already have too many pinned tabs), so I wanted to target a spreadsheet that the team already used frequently. We pair program, so our daily pairing schedule was a prime candidate.

Another engineer on the team was also excited to automate the process, so we spent a Friday morning building it. It was fun and surprisingly easy to get it working. If you want to jump straight to the code, the repo is here. The explanation is below (note: the code is modified from the final version, so not all variables are defined):

Enter Google Apps Scripts! I had never worked with them before. Google Apps Scripts is an environment where you can write Javascript files to interact with other Google products (like Sheets, Docs, etc.). Some methods are different than what you’re used to in a normal browser: making network calls is synchronous and you have to use special Google-provided methods for lots of things that browsers give you by default. (For more information, read the docs.) A script bound to a Google Spreadsheet can modify the cells in the sheet using Javascript. We planned to render our Pull Request tree to a tab in our pairing schedule.

Next up was the Github API — I wasn’t sure it would expose everything we needed, but it did! The GraphQL query below fetches all of the information necessary to get all of our pull requests:

query {
  organization(login: orgName) {
    repository(name: repoName) {
      milestone(number: milestoneNumber) {
        pullRequests(last: 100, states:[OPEN]) {
          nodes {
            title
            url
            baseRefName
            headRefName
          }
        }
      }
    }
  }
}

This grabs the last 100 open PRs on our milestone, and their titles, urls, branch names (headRefName), and parent PR’s branch names (baseRefName). Assuming all of the PRs in our tree are on our milestone, that’s all we need to construct our tree. Because Github’s API returns an array of PRs, we will need to reconstruct the tree out of parent branch references, so if any of our milestone’s PR’s parents are not also in the milestone, our tree construction will break.

We fetch the PRs and iterate through them to build a tree with the root node being the master branch. Here’s the node class we made:

function PullRequest(branch) {
  this.title = branch.title
  this.pointingTo = branch.baseRefName
  this.branchName = branch.headRefName
  this.url = branch.url
  this.children = []
}

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

function convertPRsToTree(PRs, options) {
  // PRs is an array of objects returned by the Github API

  var master = new PullRequest({
    title: 'master',
    pointingTo: null,
    headRefName: 'master',
    url: 'github.com/' + orgName.toLowerCase() + '/' + repoName
  })
  var 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];
}

Fun fact: Google Scripts doesn’t support all ES6 features yet (including class), so it was back to good old ES5 prototypal inheritance here.

In order to render our tree, we start at master and iterate recursively through the PRs, rendering each one to the spreadsheet with the Google Spreadsheets API. We figure out which cell to render saying “increment my column by one from my parent, and increment my row by (my older sibling’s row + the number of leaf nodes my older sibling has)”. Then, if a parent PR has multiple children, we merge it with the correct number of cells below so it spans across all of its descendants:

var alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')

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

function displayNode(sheet, row, col, node) {
  addNodeToCell(sheet, row, col, node)
  if (node.children) {
    if(node.numLeaves() > 1) {
      var address = getCellAddress(row, col) + ':' + getCellAddress((row + node.numLeaves() - 1), col)
      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(sheet, row + index + offset, col + 1, child)
    })
  }
}

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

We render the branch by calling displayNode(sheet, 0, 0, masterNode) where sheet is the Google Spreadsheet (accessed earlier, not shown), and masterNode is a PullRequest with references to all its children PRs.

That’s pretty sweet, and gets us a lot of the way to where we want to be: on the left is master. The next column to the right lists all of the branches that are pointing to master, and so on, so that each branch will be merged into the cell to its left.

We’re getting there, but this view hard to read and I want PRETTY COLORS in my spreadsheets, so let’s color code each PR’s cell based on its status. What is its status? Great question! This will vary based on your team’s processes, but we wanted our statuses to indicate when action was possible for any given PR. Thus:

• Green (ready to ship) if it’s been reviewed, QA’d and passed our CI
• Red (there’s a problem, needs attention) if we have failing specs, requested changes from a review, a rejected story from QA, or it doesn’t have the correct labels
• Yellow (it’s out of date, pull it through!) if it’s been open longer than 5 days — this was a choice we made to - encourage our team to pull through open PRs faster and focus on tight iteration cycles
• Pink if the PR has the Blocked label
• Purple if the PR has the WIP label
• Gray (no immediate action necessary) if none of the above apply

Calculating the status meant asking for more data for each PR — we needed to know its test status and its labels. Because we integrate Github with our CI, every time we push to Github we run our tests, and the result is associated with the last pushed commit. Thus, the test status of the PR as a whole is the status of the last commit on the PR. The final query looked like this:

query {
  organization(login: orgName) {
    repository(name: repoName) {
      milestone(number: milestoneNumber) {
        pullRequests(last: 100, states:[OPEN]) {
          nodes {
            title
            url
            labels (last: 100) {
              nodes {
                name
              }
            }
            createdAt
            baseRefName
            headRefName
            mergeable
            commits (last: 1) {
              nodes {
                commit {
                  status {
                    state
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Finally, we set the background color of the cell based on its status, add a lengend, and voila!

We can update the view at any time by clicking the Mavenlink menu item and selecting Branch Strategy:

At this point, everything was working, but was in a single giant file, which was difficult to navigate and understand, plus, nothing was testable! While Google Scripts does have some testing available, the tooling isn’t easy to use and is difficult for scripts that are bound to a single spreadsheet, as this one is.

Additionally, there’s no importing or module system in Google Scripts, so splitting up the code into testable/comprehensible chunks is tricky. Oh, CommonJS and Webpack, how I miss thee!

Finally, we knew other teams would want to use this visualization for their own milestones, but we didn’t have a great way of sharing this other than copying and pasting everything.

Enter Github and eval to the rescue! (yes, really). I pulled all the code out into a Github repo and extracted the milestone-specific information into variables that could be passed into generic functions. Then, all someone else has to do to use it is copy the following snippet into their own script attached to a Google Sheet and fill in the relevant variables:

// FILL THESE IN
// The name of the Google Sheets menu tab you want the script to go under
var tabName = YOUR_TAB_NAME
// The name of the menu item in the menu tab
var menuName = YOUR_MENU_NAME
// The name of the github organization your repo is nested under
var githubOrg = YOUR_GITHUB_ORGANIZATION_NAME
// The name of the repo that contains your milestone (may need to try lowercasing this)
var repoName = YOUR_REPO_NAME
// The milestone whose PRs you want to visualize
var milestoneNumber = YOUR_MILESTONE_NUMBER
// Generated Github token - you can get one in your Github settings
var token = YOUR_GITHUB_TOKEN


// YOU DON'T NEED TO TOUCH ANYTHING BELOW THIS LINE
var prFetcherURL = "https://raw.githubusercontent.com/naomiajacobs/googleSheetsGithubPRVisualization/master/pullRequestTree.js"
var rendererURL = "https://raw.githubusercontent.com/naomiajacobs/googleSheetsGithubPRVisualization/master/googleSheetsRenderer.js"
var githubPRs = eval(UrlFetchApp.fetch(prFetcherURL).getContentText())
var renderer = eval(UrlFetchApp.fetch(rendererURL).getContentText())

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

  this.spreadsheet.addMenu(menuName, entries)
}

function fetchBranchesAndRender() {
  var options = {
    orgName: githubOrg,
    repoName: repoName,
    milestoneNumber: milestoneNumber,
    token: token
  }
  var tree = githubPRs.pullRequestTree(options)
  renderer.renderTree(tree, tabName)
}

Now, we’re treating Github as a CDN hosting the data fetching and rendering code. We use eval to fetch the raw javascript file. Each file is wrapped in an IIFE (which is, not incidentally, also how Webpack compiles your bundles!), which we eval. Then, you fill in your variables, add the correct triggers, and you’re done! With the click of a button, you can refresh a tab in a spreadsheet and see all of your pull requests, their dependencies, and statuses.

Again, you can find all of the code here. Feel free to use this for your own teams or fork it and add your own conditions for PR state. Thanks for reading!