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 Cloud. 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 tailor reports 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 Cloud. 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 Cloud to GSheets easily. You need to install the add-on and set up a dedicated importer, which will import the specified data.
Install Coupler.io from the G Suite Marketplace. Here is a link.
For more information about the add-on and available integrations, please visit the Coupler.io home page.
Set up a Jira importer
An importer is an integration that connects Google Sheets to a specific app. Since you’re going to import data from Jira Cloud, you need to choose a Jira importer. For this, open Coupler.io in the Add-ons tab, click on the +Add Importer button and choose Jira. After that fill in the required fields:
Title – add the name of your importer.
Sheet Name – add the name of the sheet which will be receiving data.
JQL – enter a JQL query or an ID of the saved filter in Jira. Leave empty to import all issues.
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.
Automatic data refresh – choose the frequency of automatic resync of data. If you don’t need this feature, disable it by clicking on the toggle.
Additional fields and Settings
Additional fields include:
- Import format – Jira CSV export or Detailed data.
- Fields – the list of fields to include in the response.
For more about Additional fields, as well as the Settings section, refer to the Coupler.io knowledge base.
Once you’ve filled in all the required fields, click Connect to connect your Jira Cloud account (data source) to Coupler.io. With all the required fields filled in, click on the Add Importer button and run the importer afterwards. Yeah, it’s that simple!
The Jira importer exports data from Jira Cloud only. If you need to pull issues from Jira Server, you’ll need to set up a CSV importer. Read our dedicated blog post, How to Import Issues from Jira Server to Google Sheets, to learn the details.
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
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 Cloud with Google Sheets, Coupler.io will do the job. It can also help you pull project-level data from Jira. Many data-oriented specialists already leverage this tool. Join them and forget about manual data import. Save your time and enjoy your data!Back to Blog