Jira Time Tracking (Part 2)

Adding Visibility Through Scripting

Written by Ben Carle, CIO

In Part 1 of this series we retrieved raw work log data from Jira through the REST API using a script. In part 2 we will examine how those data can be processed in a spreadsheet to create visibility through tables and charts. Our aim is to accomplish the following goals:


  1. Visualize the number of hours logged by each developer on each day of a sprint.
  2. Visualize the deviation in hours logged from a target amount, for each day of a sprint.

Recall that our script from Part 1 copied the work logs to the clipboard. For the examples in this article we will use Google Sheets as our spreadsheet tool. Most of the examples in this article can be recreated in other popular tools as well.


This article will be organized into sections based on the tabs we will create in the Google Sheet. Each section will be a tab. The article will have three sections (tabs) as follows:


  1. Data
  2. Hours
  3. Deviation

Data


To begin, paste the contents of the clipboard into a clean tab to create a raw Data tab. You can apply an alternating color theme (Format > Alternating colors) to make the data more readable. Your data will look something like this:


Data View

You should not make any other changes to the data tab. Over time you will want to update this model by pasting in new data from the script. Any edits you make to this tab would have to be reproduced again each time, so keep this as simple as possible. We will add formulas in the other tabs.


Hours


The Hours tab will accomplish our first goal of providing visibility into the number of hours logged by each developer on each day of a sprint. Our example sprint will range over one week, from Monday to Friday. We will show the developer and the number of hours for each week.


Table Headers

Developers


The developer names are already present in the data, but we need the unique set of names, and we prefer if they are sorted. We need the following components:

  • Names: Data!A2:A
  • Unique: UNIQUE()
  • Sorted: SORT()

All together our formula for A2 is =SORT(UNIQUE(Data!A2:A),1,TRUE)


The second column (B) will be used to track the target number of hours for each developer. We will set our targets to 6 hours for most developers and 4 hours for a few developers who may have other responsibilities.


Target Table

Hours by Day


To collect the hours for each developer by each day, we need to sum over the data based on two conditions:


  • The day matches the date for the column (e.g B1, C1, etc.)
  • The developer matches the name for the row (e.g A2, A3, etc.)

The SUMIFS function solves this problem with the following signature:


SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])


Let’s examine the calculation for Monday (column C) for Amarah Luna (row 2). Criterion 1 will check the date and criterion 2 will check the developer name. We need the following arguments to SUMIFS:

  • sum_range: Data!$I$2:$I
  • criteria_range1: Data!$F$2:$F
  • criterion1: C$1
  • criteria_range2: Data!$A$2:$A
  • criterion2: $A2

The full formula for B2 is:


=SUMIFS(Data!$I$2:$I, Data!$F$2:$F, C$1, Data!$A$2:$A, A2)


Because of the column and row anchors in our formula we can now copy and paste this formula to all developers for all days, producing the desired table:


Developers Table

Deviation


The Deviation tab will track a target number of hours for each developer for each day and visualize the amount by which each person logs above or below that number each day.


The first two columns (A & B) will list the developers and targets and will be defined in the same way as in the Hours tab.


Columns C through G will represent the days of the sprint. (Monday through Friday in our example) The value in each cell in those columns will be defined as the difference between the hours logged, which comes from the same cell in the Hours tab, and the target.


For example, on Monday Amarah Luna logged 6 hours and had a target of 4, so her deviation is 2. On the same day Amina Matts logged 3 hours with a target of 4, so her deviation is -1.


The formula for cell C2 is =Hours!C2-$B2


Finally, in the last column (H) we will track the running balance over the sprint. This is useful because it shows the total deviation from target hours in the sprint. This is simply calculated as the sum of cells C through G in the row. E.g. for H2 we define =SUM($C2:$G2). We now have the following:


Developers Table

Highlighting


These data are useful, but it can be difficult to identify problem areas quickly. Especially if there are lots of team members or days in a sprint. To help with this we can add some conditional formatting rules to emphasize gross deviations.


To apply conditional formatting, select the range including all daily deviations and the balance, or {C2:H9} in our example. Click on the menu for Format > Conditional formatting. We will use five bands, defined as five conditional format rules. Add your extreme examples first (min, max) then add inner bands, as order of precedence will be important when the rules are evaluated.


For a min rule we specify to format cells if Less than or equal to with a value of -4. Set the formatting style to add a red background. Click on + Add another rule to repeatedly add the rest of the five rules. When complete, our rules look like the following:


Developers Table

Now our table of deviations looks as follows, and our attention is quickly drawn to areas of concern.


Conditional Formatting Options

Charting


In addition to emphasis via conditional formatting, charts can provide visibility into troublesome patterns in large datasets. Let’s add a chart to our Deviation tab to help in identifying developers who trend in one direction (high or low) throughout a sprint.


Select a range covering the entire table, excluding balance. ({A1:G9}) Click on the menu Insert > Chart. Change the Chart type from Line chart to Stacked column chart. We want to use the developer names, but ignore the targets and balances here, so we want a range that omits columns B and H. Set the Data range to A1:A9,C1:G9.


We want to stack across days for a given developer, so change the Combine ranges setting to Horizontally. Uncheck the option for Switch rows / columns so the developer names are assigned to each stack. You may need to uncheck and re-check the option for Use row 1 as headers as there seems to be a bug with the initial display.


Your chart will come with some default header and footer labels that can be removed, as they are not required. Finally, you should have something like the following:


Developers hours chart

We can now quickly identify the developers who are consistently trending above or below the target number of hours for each day.


Conclusion


This article shows only a few examples of how a powerful spreadsheet tool can provide visibility into Jira work logs. There is some cost to this approach in terms of setup time, but there is also a benefit to the ability to tailor the data and display to the unique needs of your team.


---
At FullStack Labs, we pride ourselves on our ability to push the capabilities of cutting-edge frameworks like React. Interested in learning more about speeding up development time on your next project? Contact us.

Let’s Talk!

We’d love to learn more about your project. Contact us below for a free consultation with our CEO.
Projects start at $50,000.

FullStack Labs
This field is required
This field is required
Type of project
Reason for contact:
How did you hear about us? This field is required