Jira and Google Sheets Combine Well Together.

Now I work more than 10 years with Jira and gained some experience with it. I was first introduced to Atlassian Jira back in 2011, when I started my first job in the gaming industry.

The last couple of years I got much more familiar with the tool because as scrum master and product manager, you need to know all the details about the tool. You have to be able to manage the administration parts and customisations the system to the team’s needs.

I have to admit that Jira is not the best tool for everything and has many flaws. Nowadays, many extensions are available in the Atlassian marketplace to help overcome these shortcomings.

Why would you export your issues from Jira?

I faced multiple times the problem in Jira, that I was starting to lose the bigger picture about the backlog and its issues. In the teams I worked with, we planed certain epics and user stories weeks before we acutely implemented them. Specially in the planning and specification phase, it helps to have a different view on the items, instead of the usual list of ticket headline.

In my planning, I use either story points or man days to calculate rough release dates and roadmaps. If you want to analyse or filter some feature estimation in relation to department or individuals, it gets quickly fairly complicated.

This is also the case for resource planning, for example in one team we have frontend developers, backend developers and blockchain developers. You need to be able to coordinate the resources depending on the workload and the estimated release dates. If you have to visualise and analyse this kind of data, you would usually use a spreadsheet application.

The benefits of Google Sheets

I have tried and evaluated many apps in the Atlassian marketplace, but nothing did fit my requirements. I’m an Excel geek and I wanted a way to make my analysis, filtering and calculations in a spreadsheet. Jira offers the feature to export a list of issues to XLS, but this process is clunky and repetitive. After Excel, the next best spreadsheet app is Google sheets, and it has the advantage of being online and easily fetches data from the web.

Since a few years, Atlassian offers an official plugin for Jira, to connect the two applications. This way you can open a spreadsheet with the direct connection to your Jira tickets. With one button click, your spreadsheet is updated with the latest state of the backlog. From there you can then can create pivot tables, filters or other segmentation depending on the information you are looking for.

I use this integration now for some time and it helped me a lot with the planning and estimation of future releases or milestones.

How to integrate Jira Software and Google Sheets

I have created a new Jira project to demonstrate the integration with Google Sheets.

If you start in the Atlassian marketplace and look for “Jira Cloud for Google Sheets” from Atlassian, the installation will redirect you to the Google login page.

In the Google marketplace you have to install “Jira Cloud for Sheets”.

After allowing the installation to your Google account, the new extension is available in Google Sheets.

The first time you open the Google sheets, you will be prompted with a verification code.

In a separate browser tab/window you need to login into your Atlassian account and enter the verification code. If you have successfully connected the two accounts, you will see the following:

I like to use JQL to define the tickets I want to import.

1project = "PROJECT-CODE" AND status != Done and type != Epic ORDER BY created DESC

Next, you have to set up the fields you would like to have as table columns. I normally use the following, but you can choose whatever you need:

• Issue Type
• Key
• Summary
• Assignee
• Status
• Created
• Epic Link.Name
• Components
• Fix versions
• Linked Issues.linkType
• Linked Issues.issueKey
• Linked Issues.issueStatus
• Original estimate

Now you are able to fetch all the issues into the current spreadsheet. I also like to add conditional formatting for the issue’s status.