Connect Jira To Google Sheets

Jira is a universal project management solution for teams of any size. If you’re reading this, you likely use this agile tool for your own project, be it software development, handling operations, finances, and so on. Our team also benefits from simple task management and reporting in Jira. Moreover, we export data from our projects to build custom reports in Google Sheets. It seems you’d love to learn how we do this. The recipe is a GSheets add-on, Coupler.io. Below, we’ll prove it is worth your attention.

What’s the point of moving data to spreadsheets?

Every team has its own reasons to export Jira to Google Sheets. Some need raw data to build a dashboard to monitor KPIs. Others build a report to estimate the launch date of a project. You might need Jira financial tickets to adjust the financial balance of employees. There are many other use cases that share the same feature – they require the power of a spreadsheet. 

For these purposes, there are Jira Rest APIs, which send JQL queries and receive the requested issues. This allows you to set up different types of integration with Jira. The drawback of this way is that coding skills are required. For less tech-savvy users, Coupler.io is a go.

How to quickly export data from Jira to Google Sheets

Coupler.io is a Google Sheets add-on that connects Jira to GSheets easily. You need to install the add-on and set up a dedicated importer, which will import the specified data.

Installation

Install Coupler.io from G Suite Marketplace via this link

Click Install, then choose your Google account and grant Coupler.io the rights required to enable data importing:

For more information about the add-on and available integrations, please visit the Coupler.io home page.

Setting up an importer

An importer is an integration that connects Google Sheets to a specific app. Since you’re going to import data from Jira, you need to choose a Jira importer. Open Coupler.io in the Add-ons tab:

On the right side of your spreadsheet, you’ll see the Coupler.io dashboard with the +Add Importer button readily noticeable. Click on it and set up the importer by filling in the required fields:

Title – The name of your data source. For example, Monitoring Incident from Jira, Jira Service Request, etc. 

Sheet Name – The name of a sheet where you will import your data. Coupler.io will generate a random name here, such as “jira-api k5ezlevr,” but you can specify any name you would like.  

Re-import automatically – Pick the schedule that you want to use for Coupler.io to update data in your spreadsheet. If you choose ‘Never,’ you’ll have to run the importer manually every time you want to update your data.

JQL – Enter a JQL query or an ID of the saved filter in Jira. If this field is empty, all issues will be imported by default. 

To get a JQL query for the required issues, you need to set up your search filters and then click on the Switch to JQL button. Here is an example:

project = JTGS AND issuetype = Task AND status = "In Progress" AND assignee in (EMPTY) order by created DESC

Check out more about Jira Query Language.

Additional fields and Settings

Additional fields include Import format (Jira CSV export or Detailed data) and Fields list. The Settings field allows you to choose a data import mode: replace or append, as well as specify a start cell where the data will be imported. For more on this refer to Coupler.io knowledge base

Once you’ve filled in all the required fields, click Connect and perform the requested manipulations to connect your Jira account (data source) to Coupler.io.

With all the required permissions granted and fields filled in, click on the Add Importer button. The last step is to run your Jira importer, which will move your data to Google Sheets. Yeah, it’s that simple!

Use cases of integration Jira with Google Sheets

Let’s explore some hands-on use cases of how you can analyze and visualize raw data in a spreadsheet. We’re going to build an interactive dashboard drilled across Jira issue levels. It may look as follows:

Our dashboard consists of four charts: Issues, Performance, Average resolution days by assignee, and Performance by priority. A useful feature of the dashboard is that you can sort metrics by year due to data validation. To make this available, as well as to implement other calculations, we created additional columns (Date, Year and Month) for both created and resolved issues. For this, we used the ARRAYFORMULA formula.

Another column titled Days per issue was set up for calculation of average resolution days. Here is how everything works:

We’re happy to share the dashboard so you can download and adjust it for your project. Enjoy!

To wrap up

Google Sheets is not limited to building dashboards and visualizing data. It is a multi-purpose tool where you can perform complex calculations, elaborate reporting, and many more. You may also use spreadsheets as a source to import data to your tool for project management, accounting, or anything else. So, when you need to integrate Jira with Google Sheets, Coupler.io will do the job. Many data-oriented specialists already leverage this tool. Join them and forget about manual data import. Save your time and enjoy your data!

Leave a Reply