/
Dynamic and Powerful Jira Reporting Using Google Sheets

Dynamic and Powerful Jira Reporting Using Google Sheets

Owned by Haddon Fisher
Last updated: Jul 02, 2024
2 min read

A Pretty Foundational Problem

Jira is a fantastic way to keep track of any number of data points as they evolve through time. The tool scrupulously tracks the history of every change in the history log, and so you’d expect it to be fantastic at using that information as well. Oh my sweet summer child.

On the one-hand, Jira’s lack of anything close to decent reporting makes sense. It’s a very hard nut to crack; ask anyone whose tried to learn eazyBI. Everyone needs to see slightly different data, structured slightly differently, in a slightly different way.

On the other hand, all that data is just like…there, right? Dashboards and the canned reports it does have make it feel like it should be able to do this…right? And there are other tools that somehow manage to make this possible…right? RIGHT?

Google Sheets Has Entered the Chat

After a lot of trial and error, I’ve ended up using Sheets to create a lot of my reporting. You have to build everything yourself, automating the data load isn’t easy, and…well…sheets! All that is true…and…I have found it capable of delivering exactly what you need, when you need it, without an onerous amount of work.

This one neat trick…

The single biggest thing I’ve learned to make this as easy as possible is to make everything dynamic. Isolating the raw data and using formulas to generating the rest:

  • Is way easier to update and maintain

  • Keeps you entirely data-driven

Building the Report

Determine Your Dataset

Create one or more filters to isolate the Jira issues you want to report on, and have filter-specific columns set so that running your filter always returns the same data points.

When setting your columns, its usually better to pull in anything you think you might need. There’s little cost to not using something, but there are certain instances when can be a challenge to add columns to your raw tab down the road.

Set Up Your Raw Tab

Once you have your filter, you need to export the data and get it into sheets. To do this…

  1. Run your filter.

  2. In the “Export” drop-down, click on the option for “Export HTML report (filter fields)”.

  3. Open the HTML report and copy the content in the table. Be careful not to grab the rows above and below the table; Sheets will see these as merged cells and it will mess up the paste.

  4. Name that tab the ‘Raw’ tab.

Build Your Analysis

With the raw data in hand, you can then use functions like FILTER and QUERY to create complex analysis. Some tips:

  • Never hardcode anything! Making everything dynamic makes it incredibly easy to update or recycle these reports.

  • Use mapping tables to manage static\non-Jira data like team-of-teams memberships, sprint start\end dates.

  • Create a “problem children” tab to identify issues with missing or otherwise “bad” data, and add directions on how to fix each as a comment or note directly on the sheet.

Some Examples

Related content