Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets Github PR Visualization
/*
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 {
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
}
}
}
}
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment