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; } } }

Import JIRA REST API results to Excel Web Power Query

Nigel Domaingue

I am trying to create a Power Query feed into an Excel file that presents all the Issues listed under a given Epic. I have already verified that the REST API URL delivers the content that I am looking for by testing it in the Restless browser plugin, and here is the query:

https://jira.XXXXXX.com/rest/api/latest/search?jql=cf[10182]=XXX-845

In Excel, I start creating a new Get Data: From Web import. In the Advanced view, I provide my validated URL.

Without any additional config information, I get an error 400, which I assume is because our Jira server requires authentication. No problem, maybe it needs the same Basic authentication that the Restless plugin needed.

So, I add an HTTP header of type 'Authorization', and provide the details that the Restless client used successfully.

Next error is this: "The 'Authorization' header is only supported when connecting anonymously. [...]"

Has anyone else managed to successfully import REST API data from a Jira Server into Excel and can offer solutions for my problem, so that I can import my data feed into my Excel dashboard?

Regards,

Nigel

Jira Server 7.12

Excel is latest edition, part of a corporate 365 subscription.

Accepted answers

Nigel Domaingue

OK, looks like I have managed to solve this myself, but it was tricky. I had previously found this article, https://community.atlassian.com/t5/Jira-questions/Fetch-data-from-JIRA-to-excel/qaq-p/198786 , which initially didn't look like what I needed.

Studying the comments in more detail there was one from Brendan Walker, dated 15 Nov 2018. Here is my variation on his process, which looks to be working for me:

In Jira, create my desired filter and save it ('Save As' button, next to the header)
Select the saved filter: Issues menu > 'name of saved filter'. Copy the URL, eg https://jira.XXXXXX.com/issues/?filter=18650
In Excel (I'm using 2016 edition),

Data ribbon
Get Data > From Web
Provide URL

I'm then presented with a 'Navigator' panel with two elements to select from, 'Document' and 'Table 0'. The information I am interested in is in 'Table 0', so I select that.I always prefer to preview and edit my Power Query imports, so I always click on 'Transform Data' next. (You can click on 'Load' or 'Load To', but its a bit more arduous to get back to the Power Query editor if you don't like what Excel gives you initially)

I make any further edits to the data that I see fit
Then in the 'Home' ribbon, click 'Close and Load'. This creates a new tab, with the data.

Thank you Brendan.

My main gripe though, and this was consistent with the download of a CSV file and import from an offline file, is that the field to describe what type of Issue each row is (Task, Story, Bug, etc) is a non-printable field, so just comes up as an empty column with title "T". I could perhaps fudge this with a range of aggregated filter queries per each type that I am interested in, but that seems a bit over the top.

All comments

Nigel Domaingue

Studying the comments in more detail there was one from Brendan Walker, dated 15 Nov 2018. Here is my variation on his process, which looks to be working for me:

In Jira, create my desired filter and save it ('Save As' button, next to the header)
Select the saved filter: Issues menu > 'name of saved filter'. Copy the URL, eg https://jira.XXXXXX.com/issues/?filter=18650
In Excel (I'm using 2016 edition),

Data ribbon
Get Data > From Web
Provide URL

I make any further edits to the data that I see fit
Then in the 'Home' ribbon, click 'Close and Load'. This creates a new tab, with the data.

Thank you Brendan.

Parag Madpuwar_hli9p3

What is the permission I have to provide?

Melissa_oqnbbr

@Nigel Domaingue Sorry to dig up this old thread!!! But its exactly what I need.

I get to Step 4, but in my I don't see "Table 0" When I preview I don't see anything but Document. Any suggestions?

Christopher Lebruh

Good day - Any suggestion on how to actually capture your credentials? Can anyone provide an example?

Melissa_oqnbbr

@Christopher Lebruh When you go to Data ribbon-->Get Data-->From Web, you should get a screen "From Web" with Basic and Advanced choices, I select basic paste the URL and click OK, an then should get another screen that says Anonymous, Basic etc. I choose Basic and entered my credentials

I'm stuck on Step 4

Christopher Lebruh

Good day @Melissa ,

The below steps will allow you to achieve the following: It will allow you to export your Jira data (rows and fields of your choice) to Power BI or Excel using Power Query. It will create a live connection to Jira which can be refreshed from Excel or Power BI. There is no row limits

Steps

1. Go to Jira and create a filter which shows the columns and filters that meets your requirements. You should know see the data you would like to export

2. In the top right corner you will see an "Export" button. Left click on it.

3. You should be presented with a list of formats to export to

4. Hover over the one that says "CSV (Current Fields)" and right click on it

5. Left click on "Copy Link"

6. Open Excel or Power BI and Navigate to data or add new data

7. Add from Web

8. Select basic and paste the link. The link should look like this

https://Replace with Jira link/sr/jira.issueviews:searchrequest-csv-current-fields/Replace with filter number/SearchRequest-Replace with filter number.csv

9. Log in with your normal Jira Credentials

10. It should ask you to load the data or Transform the data.

All done. Hope it helps.

This was tested using Edge web browser

Oleksandr Chalyi

Hi @Christopher Lebruh ,

I'm following steps you described, I'm stuck on (9).

You mentioned it's necessary to login with "normal jira credentials", but we are using SSO authentication. Please advise if that's possible to setup export to excel when SSO involved.