How to export JQL filters to Google Sheets using Jira Cloud for Sheets for data analysis in Jira Service Management

Platform Notice: Cloud - This article applies to Atlassian products on the cloud platform.

Purpose

JQL is a fantastic tool to search and filter data across projects and workflows within Jira, and in some cases, it's necessary to pivot Jira data with internal business processes data to get new business insights and create new information.

Solution

An approach to correlate these data sets would be using Jira JQL Filters to load the filtered data into Google Sheets using Jira Cloud for Sheets. Note that it could be used a specific JQL on Jira Cloud for Sheets instead of the filter or using the =Jira() function as well, in this case, we'll use filters.

For example, let's say we have a service management project with a required user picker field, and we'd like to find the corresponding HR Business Partner in an internal business Google spreadsheet for each user picked on the tickets.

Step 1 - Filter the data required using JQL and save it as a filter;
For example:


Step 2 - Integrate Jira with Google Sheets using Jira Cloud for Sheets;

A detailed description of how to integrate Jira and Google Sheets can be found in the use Jira cloud for sheets doc.

Step 3 - Associate the data using the available Google Sheets functions;

After the Filter data is imported, it will look like the image below:


Our simple business data set example would look like this:

 


For example, in our case, we'll use the ARRAYFORMULA function to return the values originally imported from the filter to another sheet:

Then we can compare the Jira data with the internal Business Sheet (User - HRBP) data using the VLOOKUP function to find the correspondent HR Business Partner of the user:


Step 4 - Schedule data load from Jira to keep data updated;

Additionally, it is possible to schedule the data load from Jira in the Jira Cloud for Sheets extension to have data updated periodically:



Last modified on Aug 4, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.