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. But it’s not necessary to be a programmer to do that. In this blog post, we’ll explain how you can connect Jira to Google Sheets without a single line of code and automate export of Jira issues on a schedule.
How to quickly export data from Jira to Google Sheets
We’ll be using the Jira importer by Coupler.io, as a ready-to-use integration between Jira and Google Sheets. Coupler.io is a solution for importing data into Google Sheets from different sources, such as Jira, WordPress, Xero, and many more.
You have two options:
- Coupler.io web app – you can configure the export of Jira issues without entering Google Sheets.
- Coupler.io add-on – you can configure the export of Jira issues from your Google Sheets document.
Jira to Google Sheets integration with the web app
The web flow is quite clear and intuitive – just sign in to Coupler.io, click “Add importer” and name it. Then go step by step to set up the Source, Destination, and Schedule settings. Here is what the flow looks like:
Export Jira data to Google Sheets with the add-on
With Coupler.io add-on, you’ll need first to install it from the Google Workspace Marketplace.
After that, go to Add-ons => Coupler.io => Open dashboard. Click “+Add Importer” and choose Jira.
Make the following steps to set up the Jira – Google Sheets integration:
1. Set up Source – connect Jira to Google Sheets
- Add the name of your importer in the “Title” field.
- Click “Connect” and pick the Jira Account you want to connect to. Sign in to the chosen Jira Account and click “Allow” to grant access to Coupler.io to view your Jira data.
- Enter a JQL query or an ID of the saved filter in Jira. Leave empty to import all issues. Learn how to get a JQL query.
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.
2. Set up Destination
- Click “Connect” to connect a Google account you want to export Jira data to.
- Add the name of the sheet that will be receiving data in the “Sheet name” field.
Click “Show advanced” to specify the first cell where the data range will be imported.
3. Configure Settings
- You can automate export of Jira issues to Google Sheets on a custom schedule. To do this, enable the Automatic data refresh and select Interval, Days of week, Time range and Time Zone. 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 the Jira importer, 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!
Pull Jira data filtered by status and other filter criteria into Google Sheets
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
Other Jira Google Sheets integrations for data export
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.
Another option is to export project-level data from Jira, such as project names, keys, categories, etc. For this, you’ll need to set up a custom integration using the JSON Client. Here is the guide on how to pull project-level data from Jira to Google Sheets.
Use case: Build a dashboard using the issues exported from Jira to 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!
Automatically pull Jira filtered data to Google Sheets
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. Many data-oriented specialists already leverage this tool. Join them and forget about manual data import. Save your time and good luck with your data!Back to Blog