The add-in you’ve been waiting for: Jira Cloud for Excel 🙌

[#1]

Introducing Jira Cloud for Excel

Here at the product integrations team at Atlassian, we are thrilled to announce the new Jira Cloud for Excel add-in! This add-in lets you export Jira data directly to Excel using existing filters or custom JQL.

It is available for free on the AppSource marketplace and can be installed by any Office 365 user (for both the Online and Desktop version, Excel 2016 and later.) The add-in works with all Jira Cloud products (Jira Software, Jira Core, Jira Service Desk).

Get the add-in free from Microsoft AppSource

------------------------------------------------------

***IMPORTANT***: If you are trying to install this add-in for Excel Desktop, you must have an Office 365 subscription, as well as be on either:

• version 1904 or later for Windows

• version 16.24 or later for Mac

To get the latest build, you will need to be on the Monthly Channel. If you are on the semi-annual update channel, you will only be able to use this add-in starting from January 2020.

This add-in is not supported on Office 2016, Office 2019 Pro Plus, or versions of Office 365 before 1904 on Windows or 16.24 on Mac.

Installing this add-in with an unsupported version will show an “Add-in is no longer supported” error.

------------------------------------------------------

Not using Excel? Check out the Jira Cloud for GSheets plugin.

What can I do with the add-in?

Jira’s standard reports are great for understanding your team's performance at a glance, but they often don't give you the flexibility you need to answer your specific questions.

With the Jira Cloud for Excel add-in, you get the power of Excel to slice and dice your Jira data and create the custom reports and charts you need to answer your organizations pressing questions such as:

• Create a stakeholder report in Excel to provide updates on progress towards project goals 

• Use pivot tables to analyze the velocity of a development team and figure out where your bottlenecks are

• Estimate the quality of shipped code by joining completed stories with bug re-open rates in Excel

• Gain a portfolio-level view of your teams work by pulling data from multiple projects

Three ways to export Jira data to Excel

1. Import from the Jira side panel and easily refresh existing data

Your saved filters in Jira will automatically appear in the side panel. Simply select a filter and the import will display the corresponding data (The filter must be starred in order for it to appear). You can also use JQL to import and select the exact fields to return. If you already have Jira data in your workbook you can refresh the data with a single click.

2. Use the JIRA() function

This option is the most powerful one as it allows you to write a custom JQL query and specify the list of Jira fields and number of rows you want the query to return. You can also use it to paginate through a large list of issues.

3. One-click export from Jira (coming soon)

The easiest way to export Jira data to Excel is by using the brand new `Open in Excel` option under the Export dropdown. With this, you can open any Jira search or filter directly in Excel without the need to download, save and convert CSV files. This functionality will be available in your Jira instance in the coming month.

Installation instructions

1. Install the add-in from AppSource

1. Download the add-on from AppSource

2. Follow the prompts to give the add-on access to your Office 365 account.

Note: If you have trouble installing the add-in, refer to this article for troubleshooting.

2. Connect your cloud site/instance

Once you’ve downloaded the add-on and followed the prompts to give the add-on access to your Office365 account, you’ll need to connect your Jira instance.

If you restrict access to your Jira Cloud instance, you will need to add the list of server IP addresses used by this add-on to your Jira Cloud’s allow list.

3. Get started

You should see a Jira icon appear in the upper right hand of the Home tab. Click on the Jira logo to begin using the Jira Cloud for Excel add-in.

[Andrew Makar]

This is an excellent and much needed add-on / feature!  Thanks!

[Deepanshu Natani]

Is there any plan to provide such addin for Word?

[Philipp Suchan]

@Deepanshu Natani - interesting idea, can you elaborate what use case you are looking for in Word? I would assume that Excel is a more natural place to interact with Jira data. 

[Mark Hostetler]

To elaborate on Deepanshu's comment; we have non-Jira users who like to get an overview of what exactly is going out in any given sprint. Release notes almost...

Tickets currently have an 'export to word' function. We'd kind like more control over what data gets exported, and definitely more control over the format of the end result. Critical custom fields are not brought into this export function. We also frequently insert data into the document that isn't captured in Jira because it only applies to this external group. Carving out locations for that additional text would be ideal. The current word export isn't the prettiest thing in the world, and of course, our non-Jira users that are viewing this have been used to getting certain things in a certain way that we'd like to continue to mirror. 

[Philipp Suchan]

thanks, @Mark Hostetler - I have relayed this to the product team to consider as they are planning new features in this space.

[Deepanshu Natani]

@Philipp Suchan, Most of my needs have been explained by @Mark Hostetler .

To add, many times we need to create reports (for sprints, releases/ versions) using Jira issues data in Microsoft Word (we have our fixed templates for these reports) and share these reports with our customers.

A word add-in will be really helpful in such cases since it will enable us to work with our existing templates and will enable us to embed Jira issue data and create reports in form of Word Documents.

[Stephan Grubbe Sølby]

I am still unable to get the parent-child relationship exported. I can do it directly with API, but the parent ref field is always empty in the exports.

What to do?

[Jack Brickey]

this is an awesome feature! thanks for adding.

[Russell D. Beyer]

Is there any current way to preserve formatting on import, so that the headers / fields can remain readable?

Using the JIRA() function to re-import does not clear out old data, causing incorrect reports. Ex) If a data pull retrieves 50 rows, and the next data pull retrieves 40 rows, the report shows 50 rows: The most recent 40, plus the last 10 of the previous pull. Perhaps it could clear rows based on the row bound parameter provided.

This could be a very useful extension, if it can update correctly and maintain formatting rather than blow selective rows away entirely, ignoring the extra rows within the bounds.

[Philipp Suchan]

@Russell D. Beyer - Hi Russel if you use the import via the side-panel the import (as well as the refresh functionality) will clear the sheet:

[Russell D. Beyer]

@Philipp Suchan 

Hi Philip. I'm not using the Import via the Side-Panel. Unfortunately using this tool not only destroys all formatting on the sheet, but all other related data on the sheet that I would very much like to keep. There is then no readability and the rest of my important contextual data is all blown away.

I am using the JIRA() function to insert this data midway down the sheet, which works fine with the exception of the two points I discussed above. Is there any way to retain cell formatting? Is there any way for the old cells to be cleared to display a correct report, without the overflow issue of prior data?

[Philipp Suchan]

@Russell D. Beyer  - I have relayed your feedback to the team. 

As a workaround have you considered using a pivot table in addition to the side-panel import? You could set up the pivot table on a separate tab and use a dynamic table range (example: JiraImport!$A:$K) to summarize the data and keep your formatting.

[Dmitry Astapkovich _Colined_]

@Stephan Grubbe Sølby ,
Pivot Report preserves Issue Tree / Structure after export to Excel. I do understand you need an out-of-the-box solution, but this may be a temporal workaround. You can check the demo here: https://bit.ly/2X8ZX8d
We have support for default trees like Epic > Task/Story > Sub-task, custom Portfolio hierarchy levels and custom hierarchies in Pivot Report. 

[Tanguy Crusson]

@Russell D. Beyer Tanguy here from the product team. We're working on a fix so the table is cleared before each refresh. I've created this Jira ticket for you to follow the progress of the resolution: https://jira.atlassian.com/browse/API-199

Thanks for reporting this!

Note that this only addresses your question re: clearing old data when re-running an import using the Jira() function. We are not sure what to do re: preserving formatting yet, as use cases vary. 

[Russell D. Beyer]

@Tanguy Crusson We appreciate it, that's certainly the more problematic issue of the two! I can paste the formatting overtop as needed for now, but ensuring the dataset will be enough for us to use this method.

[Rob Ling]

@Philipp Suchan Will this function for Google Sheets as well?

[Adam McKinty]

Great to see this.   I'd really love a JIRA function that returns the  number of records in a JQL filter rather than getting a list of issues in the filter and COUNT the rows...really helpful for forecasting calculations. 

[Rob Ling]

Strange my question disappeared so re-adding it:
@Philipp Suchan  Will this function with Google Sheets as well?

[Philipp Suchan]

@Rob Ling - thought you deleted it :P

yes, the functionality is available for GSheets. You can install it from here: https://gsuite.google.com/marketplace/app/jira_cloud_for_sheets/1065669263016

[Rob Ling]

Fantastic, thanks!

[Jose Vicente]

Awesome feature! When will be in server available?

[Aron Gombas _Midori_]

@Deepanshu Natani @Mark Hostetler You can already export custom documents from Jira using the Better PDF Exporter app. The PDF files are generated from templates, to which you can add custom pieces of texts, the output is rather professional (samples), you can control what fields to export and in what format, etc.

You may want to try if it fits your use case.

[Deepanshu Natani]

Hi @Aron Gombas _Midori_

Thanks for the suggestion.

I am aware of the addon suggested by you. However, we need to create Word documents and this is a standard practice with many of our clients which cant be changed. So a PDF file wont work in our case.

[Aron Gombas _Midori_]

@Deepanshu Natani I don't want to de-rail the original topic, but you can import PDF files to Word. It is a technique some of our customers use in their daily practice.

[Catherine Chang]

Hi Stephan Grubbe Sølby -Alpha Solutions A-S-,

I'm the product manager for this integration and I'd like to understand more about the parent-child relationship that you're referring to. Are you trying to export subtasks, issuelinks, parent link, or parent? Are you using Jira Portfolio?