Google Sheets for Sprint Reporting
In this very practical post, I want to share some capabilities of Google Sheets that can make it suitable for basic Scrum sprint progress reporting. My experimentation with this started out of frustration with Jira’s limited capability on story point reporting within standard dashboard widgets. If you find yourself in a situation where budget restrictions or access rights don’t allow for more advanced reporting features, Google Sheets can offer a lightweight alternative. Most of the Google Sheets features I used in this example, are built-in capabilities (generic functionality), others require the use of custom Google Scripts (script files that can be attached to a Google document, allowing for custom functionality).
Sprint report example
I created an example of a Sprint report that can be found here. The example contains three sections: basic sprint info, some metrics and the sprint backlog items. The Scrum Sprint reflected in this example is a 2-week sprint with a backlog of 5 items (3 feature stories and 2 bugs). The total story points for the sprint is 20. It’s the third day of the sprint and 4 story points have been finished.
Basic Sprint info
The first part of the sprint progress report contains some basic information about the sprint like start date and an end date. Once we know these dates, we can calculate the duration of the sprint using the build-in NETWORKDAYS function:
=NETWORKDAYS(C5,C6)The formula above returns the number of net working days between two provided days (in our case, start date is located in cell C5, and end date in C6). To make things more understandable, there’s the option to add additional text behind any returned value of a formula like this:
=NETWORKDAYS(C5,C6)&" working days"Next, we want to display the number of days left in the sprint (while the sprint is active), we can do this using the formula below.
In human language, this formula reads: if either the start date hasn’t commenced or the end date has passed, display a “zero” character, in all other cases show how many days are left between the current date and the end date.
=if(OR(NOW()<C5,NOW()>C6+1),"0",NETWORKDAYS(NOW(),C6))The next thing we want to display is the total workload in the sprint by simply calculating the items in the third section, the sprint backlog. We can count the total amount of items in the sprint backlog like this:
=COUNTA(A17:A99)&" things"and the total amount of story points in the sprint backlog like this:
=SUM(C17:C99)&" points"Sprint Metrics and Sprint Backlog
The next part of the sprint report contains some basic metrics of the sprint, showing the progression of “done” things and story points. I didn’t manage to create a proper burn-down graph (although I’m sure that could also be done with some custom scripting).
Below the metrics section, I created a Sprint Backlog section, I chose to indicate the “done” items in the sprint backlog section by marking them with a green background-colour. Using these indicators in the metrics section requires a custom script that can count the number of cells that have a specific background colour. Fortunately, somebody already wrote a script like this:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-count-of-colored.html
A similar script is available that sums up the values within cells that have a specific background colour:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html
This video explains how to implement these scripts into your Google Sheet:
To reflect the remaining work in the sprint, I created two additional scripts that can calculate the amount of items and story points for cells in sprint backlog that don’t have this background colour.
All four scripts used in the example sprint progress report can be found here:
- countMatchingColoredCells.gs
- countNonMatchingColoredCells.gs
- sumMatchingColoredCells.gs
- sumNonMatchingColoredCells.gs
The scripts are fairly easy to use once you’ve added them to your document. for example, to display the story points that were done:
=sumMatchingColoredCells(C17:C99,A12)In the formula above, A12 represents the reference cell that has the background colour that should be searched for within the indicated range in the sprint backlog (C17:C99).
Small downside in real-life use is that at least one value in range of referenced cells needs to be changed for scripts to be triggered and re-calculate the metrics (only changing background-colour doesn’t trigger the script).
Additionally, I chose to visually represent the story point progression in a 3D pie chart (other charts can be suitable as well). Whenever a new sprint starts, the current sheet can be copied to start a new sprint report, changing basic info section and re-populating Sprint Backlog. Over time, a separate sheet can be created that reflects averages over past sprints and calculated velocity.
There we have it, a simple sprint progress report in Google Sheets.
