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 to import data from different sources, such as Jira, WordPress, Xero, and many more. To create your custom Jira Google Sheets integration, you need to install the add-on from the G Suite Marketplace and set up a Jira importer.
Set up a Jira importer
An importer is an integration that connects Google Sheets to a specific app. Open Coupler.io in the Add-ons tab, click +Add Importer and choose Jira.
Make the following steps to set up the importer:
1. Fill out the “Title” field
Add the name of your importer.
2. Set up your data source
2.1. Add a Connected Account
- Click Connect.
- Pick the Jira Account you want to connect to.
- Sign in to the chosen Jira Account.
- Coupler.io will need access to view your Jira data. Click Allow to grant access.
2.2. Fill out the “JQL” field
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.
Click Show advanced to set up optional parameters for your data source:
- Import format: Jira CSV export or Detailed data.
- Fields: Specify the names of the columns to import from Jira.
3. Set up your data destination
3.1. Fill out the “Sheet name” field
Add the name of the sheet that will be receiving data.
Click Show advanced to specify the first cell where the data range will be imported.
4. Configure importer’s settings
4.1. Enable the Automatic data refresh
Customize the import schedule:
- Select Interval
- Select Days of week
- Specify Time range
Check out more about Automatic data refresh.
Click Show advanced to set up optional settings for your importer:
- Import Mode: replace or append
- Last Update column
For more about Additional fields, as well as the Settings section, refer to the Coupler.io knowledge base.
Click Save to save the parameters or Save & Run to save the parameters and run the initial import right away. 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