nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Fetch data from JIRA to excel

Sarvesh Mehra

Hi. I am trying to count number of bugs which are reported every week in specific projects with JQL (I am not sure how to get count only). and then I want to get this number in excel, so that I can see how many bugs have been reported, in comparison to last week, last month, last year. And I am drawing excels graphs using that data. Excel is storing my data and drawing graphs for me but I need to have data from JIRA automatically based on filters, I apply.

So I need counts of bugs in projects (not bugs), historic data and how to fetch them directly from excel so that I don't need to go to JIRA. I trying to automate my reports. We have JIRA version 6.4.9

I hope I am able to describe my question and now waiting for response.

Thank you.

Accepted answers

Philipp Suchan

See here for the official Jira Cloud to Excel export: https://marketplace.atlassian.com/apps/1221301/jira-cloud-for-excel-official

This plugin supports JQL from within Excel via the new Jira() function.

Petter Gonçalves

Hello Everyone,

Several teams have interacted In this thread for the past few years, so here is an updated answer for everyone interested in this matter:

Atlassian has deployed some solutions to manage Jira Cloud issues directly from Excel or Google sheets and provide an easier way to build reports integrating both applications. You can check below the two free apps that provide this functionality:

Jira Cloud for Excel official

Jira Cloud for Google Sheets official

With the end of sale for Jira server, we are not planning to develop any official integration option that provides this integration, however, there are some third-party vendors that provided some paid solutions to achieve the same goal. You can check our Atlassian marketplace for some available options:

https://marketplace.atlassian.com/

Thanks!

All comments

Timothy

You'll probably be able to find some reporting add ons in the marketplace:

https://marketplace.atlassian.com/search?q=reporting

For example:

https://marketplace.atlassian.com/plugins/com.valiantys.jira.plugins.reporting

Bob Swift

getIssueList from JIRA Command Line Interface (CLI) produces a CSV file that can be imported into Excel.

Contact Kintosoft

Timothy, A new integration of Birt has been released recently: https://marketplace.atlassian.com/plugins/com.kintosoft.jira.birt_viewer

Contact Kintosoft

SQL for JIRA is a standard SQL API for reporting on JIRA with support for SQL aggregation functions like COUNT, SUM, etc. It also supports JIRA JQL, therefore you can execute:

SELECT COUNT(*) FROM ISSUES WHERE JQL = 'project=MYPROJECTID and type = Bug'

The database SQL query above will execute the JIRA JQL query and count the number of issues returned.

You could write a small Java program to save the results in a file with CVS format, for instance.

SQL for JIRA is really powerful add-on and it would be able to provide all the data sequence at once (all the bugs for a project grouped weekly for a period of time).

SQL for JIRA can be integrated seamlessly with Birt Reports for JIRA if you want to create a nice chart graph and display it on a JIRA Dashboard.

In this way, you would be able to see the graph up to date on JIRA itself at any time.

Midori

Use the Better Excel Plugin that makes creating this report easy:

Write all issues to include in the report to a worksheet (make sure you export the project and creation date for each issue)
Calculate the creation year, month, week from the creation date using Excel functions
Create a pivot chart from this worksheet, using the project field on the horizontal axis, and the creation year, month, week on the other
Draw a pivot chart from the pivot table

If you have the working template, you can refresh this any time using the latest issue data.

Dave Ahlers

You can easily and automatically import Jira Cloud data directly into Excel by following these steps:

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path. Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog. Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table. Click Load to load it into the current worksheet.

From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

Michael Rothstein

This is exactly what I needed. I had been downloading the CSV, putting it into a folder and connecting to it that way. It never occurred to me that I could directly link Power Query to JIRA through a Web data connection. Brilliant! This saves me a huge amount of time.

If you enter you credentials wrong or need to change them, it's not easy to find as you're not prompted again after entering them. Here's how to fix it. I'm using Excel 2016 (Office 365):

Right click your Power Query entry in "Queries & Connections" select Edit
Click "Data source settings". A list of data sources is displayed.
Find the data source you want to modify and select "Edit Permissions"
Under "Credentials" you can change your login type, from using Windows Credentials, to Basic (to enter your own username/password) etc.
For Privacy Level, I used "Private" in my setting.

Hope that helps and thanks for the info!

Peter Schulze

The path does not seem to work with JIRA v6.4.13. My filter is in the format: https://mycompany.atlassian.net/jira/issues/?filter=39787

would someone know the corrsponding CSV path?

I tried various choices like https://mycompany.atlassian.net/jira/sr/jira.issueviews:searchrequest-csv-current-fields/39787/SearchRequest-39787.html but no luck.

searchrequest-printable works but it creates 2 rows if I include the labels column so wanted to try CSV.

Dave Ahlers

The URL's have not changed for me. Not sure why your path is different. The way I determined the CSV path was by looking at the network traffic in Fiddler when I clicked the link to download a CSV of the report. You can use the same technique.

Ken Parry

Answer from Dave Ahlers above worked for me after I used Fiddler to get the exact query "https://MyCompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-all-fields/12345/SearchRequest-12345.csv?" _ I was then able to use the query editor to remove the columns that I did not want... Thanks so much!

Sujit Gangadharan

I am a novice on using JIRA and have only basic understanding of Fiddler. Could you help me with a couple of hints on how you used fiddler to capture that URL, please?

Appreciate the help

Sujit

Ken Parry

You could just enable develop console and view the console to see what the sent code is.. for instance in Chrome it's Ctrl+Shift+I to enable - look for the Console Window

Adrian Galarza_hl0vjs

When I put the given path into a browser, I get the CSV file: https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

When i set up the web query in Excel, I get HTML code in the columns and only a single row of data.

My hypothesis is that it's caused by my company's Single Sign-On. When I log in to jira, I put in my work email and it then takes me to a SSO page to re-enter my log in info. I believe that my credentials are not making it to that second sign on page or are not getting accepted. Any ideas?

brendanwalker

Adrian, I reckon I've just bumped into the same problem. We just converted to SSO via Cloud and I now can't access these URLs from Excel anymore.

+1 on any ideas?

Müller Daniel

Unfortunately, I run into the same problems as Adrian Galarza. When connecting through query in Excel, I get HTML code (e.g. <!DOCTYPE.html> etc. and further down meta-names and javascript-code. In addition, no real data from JIRA is imported. Does anybody know how to configure the query correctly?

Sujit Gangadharan

Thank you, Ken.

I had got distracted :-) when I could not make progress with this. Will try again.

Brendan Walker_zn28du

Hi all,

A lot of comments bout using CSV. I've actually followed very similar step but am using the APIs instead. MUCCCHHHH better. The data is much richer and you don't run into troubles when importing fields that are multi-select. In CSV, multi-select field come up in separate columns which you might not be able to predict so you transform logic become fragile.

Here's what I did... very similiar to the CSV version.

Importing into Excel Using Jira APIs

Create a filter, or JQL you want to use to establish your data set, and make a note of it. Example: "project=DIG and issuetype=Bug" -> https://mycompany.atlassian.net/issues/?jql=project%3DDIG%20and%20issuetype%3DBug
You then want to use the search API to retrieve all the issues and their meta data. I also advise you limit the query to the columns you want, not everything to limit the hit on the database, noting that the Cloud APIs will only return 100 records at a time - don't worry though you can appen them all in Power Query. Example API call: https://mycompany.atlassian.net/rest/api/3/search?jql=project%3DDIG%20and%20issuetype%3DBug&fields=issueType,summary,assignee,status,created&maxResults=100&startAt=0)
Start Excel and the Power Query editor. This is different in different versions of Excel, but you need Excel 2016 or above I believe.
You need to create a function in Power Query to load the first 100 rows and then call this function multiple times in order to get all your data. I haven't bothered figuring out how to set this up without hard coding, but I'm sure a techie could get onto that - I'm just a dumb project manager! You'll need to add these using the Advanced Query editor initially. You can follow the wizards but this blog would be too long if I tried to explain that.
Here's a copy of the function I set up: Query: LoadJiraIssuesFn

let LoadJiraIssuesFn = (startAt as number) =>
    let
        Source = Json.Document(Web.Contents("https://mycompany.atlassian.net/rest/api/3/search?jql=project=DIG and issuetype=Bug&amp;fields=issueType,summary,assignee,status,created&amp;maxResults=100&amp;"&Text.From(startAt))),
        issues = Source[issues],
        #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"key", "fields"})
    in
        #"Expanded Column1"
in LoadJiraIssuesFn

And here's a copy of the query to get all the data out: Query: LoadAllJiraIssues

let
// load all the records (this is will only load 200 records unless I make the call a few more times)
    Source = Table.Combine({#"LoadJiraIssues"(0),#"LoadJiraIssues"(100)}),
    #"Filtered Rows" = Table.SelectRows(Source, each true),
// Expand out the results to get the data
    #"Expanded fields1" = Table.ExpandRecordColumn(#"Filtered Rows", "fields", {"summary", "assignee", "created", "status"}, {"fields.summary", "fields.assignee", "fields.created", "fields.status"}),
// Expand out some of the fields to get display names
    #"Expanded fields.assignee" = Table.ExpandRecordColumn(#"Expanded fields1", "fields.assignee", {"displayName"}, {"fields.assignee.displayName"}),
    #"Expanded fields.status" = Table.ExpandRecordColumn(#"Expanded fields.assignee", "fields.status", {"name"}, {"fields.status.name"}),
// Rename the column because I'm OCD
    #"Renamed Columns" = Table.RenameColumns(#"Expanded fields.status",{{"fields.summary", "Summary"}, {"fields.assignee.displayName", "Assignee"}, {"fields.created", "Created"}, {"fields.status.name", "Status"}, {"key", "Jira Id"}})
in
    #"Renamed Columns"

When you run the query, if you haven't already, you'll be prompted with a permissions diaglogue (much like the CSV version shown in other people's responses). Select Basic authentication, enter your Jira username and password, and click Connect.
After Excel connects, you will see a dialog with a preview of the table. Click Load to load it into the current worksheet. From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

(Thanks Dave Ahlers for the CSV tips above - I unashamedly based this on yours! Thanks mate - this really helped!)

Some other things to note:

I've done this with the Cloud APIs, so am just assuming it works the same for Server version. I'm not a Jira guru... yet.
If you've just switch over to Atlassian Access for SSO, then you'll need to realise that the APIs (and CSV) imports don't authenticate against the SSO. Not sure why, and I'm no way an expert in this area - like I said, a dumb project manager - so best to talk to your security gurus about that kind of thing. To authenticate, you'll need to use old passwords, as stored on the Atlassian servers. If the people using your spreadsheet can't remember the password then an admin will have to reset it for them.

Brendan Walker_zn28du

With this method I've been able to produce reports like this in Excel really quickly that also update with the press of a button. They look much nicer than what I can do in Confluence and no plug-ins required.

unknown

Thank you for sharing, @Brendan Walker

sim xing

I'm using Excel 2013. I couldn't find "Data menu, New query, From Other Sources, From Web". So, I use "Data menu->From Web" directly, which pops up a window called "New Web Query". I pasted the cvs file path

https://jira.mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/30167/SearchRequest-30167.csv

into the address field. There is no place to choose "Basic authentication". Only button "GO" and "Import", which pops up a window asking me to either open or save.

Is that something related to permission, excel version, or I don't have something needed on my machine? Thanks!

Astidis

Excel Jira Error.PNG Need some help on getting the link to work. Modifying the

"//mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv"

Link and posting it into a web browser opens the data in Excel as desired.

When I use the same link in Excel "Get Data" wizard as described in the post, the wizard preview show lots of blank rows with a few XLM objects (<!DocType html>. <head>, <meta charset=...>.

Any suggestions on how to resolve?

Using Excel 2010 and
Atlassian JIRA Project Management Software (v7.8.2#78002)

Jocelyn Menard

Astidis. had the same problem. I manage to make it work by changing the permission.

From where you are on your screen shot, click on edit..

In the Power Query editor window, do the following:

Click "Data source settings". A list of data sources is displayed.
Find the data source you want to modify and select "Edit Permissions"
Under "Credentials" you can change your login type, to Basic (to enter your own username/password) etc.
For Privacy Level, I used "Private" in my setting.
Click on OK then Close
Do a refresh all under refresh.

unknown

Hey, are you able to have the data actualized every time or you need to regenerate a new excel report every day? Thanks !

Jocelyn Menard

I actually have a VBA script that refresh the data at a click of a button.

Tee Emm

Created an account just to share this comment.

Found this discussion to be very rich, diverse, informative, and polite.

Thank you all for your contributions.

I implemented the solution using PowerQuery with the URL containing the CSV link and basic authentication. While it works well for me, ideally I'd like to authenticate using the credentials of the user opening the Excel workbook. But, that's a task for later.

Zheng Li

this document is useful. However, I met "forbidden" error when export result over 1000. Is there any solution to export result over 1000?

Nic Brough -Adaptavist-

Please see the discussions above.

Philipp Suchan

Stephan Grubbe_7sqs2x

My initials tests with the new plugin works. Sort of.

I am still unable to get the parent reference for any subtasks no matter what I do. The data is definately there because I get the data when I query the API directly.

Any one else has this experience?

Balu All

Is there a way to achieve fetching data from JIRA JQL into spreadsheet by using VBA? I have Excel 2013 that does not have Power Query. I am afraid it is a workstation and therefore I am unable to download the dll from M$oft.

Samuel Ballé

I cannot find the documentation about the JIRA.JQL() function. I saw yesterday some examples but I closed the page and cannot find it anymore.

It should be linked to the support tab of the addon.

Mohamed Patel

If you've got your own JIRA server and an older JIRA version you can try

"https://<my jira server>/sr/jira.issueviews:searchrequest-excel-current-fields/11940/SearchRequest-11940.xls"

Adam Lee_z1xn20

Firstly, thank you all for the information above. It was exactly what i was looking for. I am using the power query to download the JIRA data and then using pivot tables to create a dashboard, i have also added a macro refresh button.

My next hurdle is to set up the source data download so that anyone who opens the dashboard in the company can refresh. Is it possible to change the credentials from basic to anonymous by adding the API key to the source URL code? I have done this in the past by adding "&key=XXXXXX" with another ticketing system and it worked well.

Thanks,
A

Jeff Abbott

Cheap and dirty method here.

You can embed the URL that downloads the csv as a hyperlink an Excel cell. Then use VBA to execute that link. You would need to have already logged into JIRA through your web browser.

Range.Hyperlinks.item(1).Follow

Put in a do while loop that then waits for the file to open in Excel. The ActiveWorkbook.Name will match the csv name you provided in the url. I recommend you include a wait time threshold and prompt the user to keep waiting if there is no response in 10 seconds or so. Once the file is opened, use code to automate what ever task you are interested in completing in Excel.

Pete Rooney

As Jeff suggested above:

Sub FollowJIRAQuery()

Dim QueryString As String

QueryString = "https://jira/sr/jira.issueviews:searchrequest-csv-current-fields/99999/SearchRequest-99999.csv"

ActiveWorkbook.FollowHyperlink QueryString

End Sub

Will open the results of query 99999 directly into Excel. Again, you need to be logged in first.

Dan Zietlow

Has anyone successfully been able to fetch data from Jira to Excel in the current 2020 version of Jira Software Cloud with either the CSV file or Cloud API method not using any add-ons?

TIA,
Dan

Ian Bartley_yuibyb

Try https://atlassianintegrationscom.sharepoint.com/:x:/s/Atlassianintegrations/EW1H0Is-WgZJhHvAb7u262IBKrhfiT9NbVUjVnu5vSU3ng?rtime=WMUDe9zZ2Eg

Found that from the "Find Examples" link here:

Petter Gonçalves

Hello Everyone,

Several teams have interacted In this thread for the past few years, so here is an updated answer for everyone interested in this matter:

Jira Cloud for Excel official

Jira Cloud for Google Sheets official

https://marketplace.atlassian.com/

Thanks!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira