Import JIRA REST API results to Excel Web Power Query

Nigel Domaingue May 15, 2019

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.

 

3 answers

1 accepted

4 votes
Answer accepted
Nigel Domaingue May 24, 2019

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:

  1. In Jira, create my desired filter and save it ('Save As' button, next to the header)
  2. Select the saved filter: Issues menu > 'name of saved filter'.  Copy the URL, eg https://jira.XXXXXX.com/issues/?filter=18650
  3. In Excel (I'm using 2016 edition),
    • Data ribbon
    • Get Data > From Web
    • Provide URL
  4. 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.
  5. 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.

Parag Madpuwar July 20, 2020

What is the permission I have to provide?

Like # people like this
Melissa March 24, 2022

@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?

Like # people like this
Irina Kurakin
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 26, 2023

@melissa were you able to solve?  facing the same problem.  thanks!

Melissa C October 30, 2023

@Irina Kurakin 

I wasn't able to get all the columns and rows to show up.  Maybe it was my lack of knowledge looking to pick this up soon. In the mean time I'm working on creating this as a report in Project Automation.  That seems more promising. 

If you get it to work Please let us know!!

Melissa

1 vote
Christopher Lebruh August 15, 2022

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

Melissa October 26, 2022

@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 

Like Christophe Bernardin likes this
Christopher Lebruh October 27, 2022

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

Like # people like this
Oleksandr Chalyi November 14, 2022

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.

 

Capture.JPG

Like Bansod.Ajay likes this
Christopher Lebruh November 14, 2022

Good day @Oleksandr Chalyi , 

 

I can confirmed the above steps was tested with SSO. 

 

Assuming you are using o365 and you logged in, then it should automatically connect to the source. If you are not signed-in then you would need to follow step 9 and use your AD credentials. 

 

Based on the above screenshot. I would select load and then open Power Query, Go to data source settings and then update the credentials there. 

Like Christophe Bernardin likes this
Melissa November 16, 2022

@Christopher Lebruh  Thank you!!!! I appreciate you taking the time to answer my question!

By any chance do you know how to export the Epic Name, My company does use Better Excel but I would like to set up a excel Power Query that I can fetch a weekly report.  I'm able to export Epic Name using Better Excel but that is not the case using excel. 

sean_young
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
December 29, 2022

What exactly is your problem?  Is it simply exporting Epic Name in a JQL filter to be picked up by power query in a csv export from Jira, or?

Daria Pavlova May 10, 2023

@Nigel Domaingue @Christopher Lebruh is there any way to connect to google sheets? As I may know there is no Power Query in google sheets yet

Christopher Lebruh May 10, 2023

Hi @Daria Pavlova , I unfortunately don't use Google Sheets so I am not able to test it, but according to ChatGPT, there is a way to connect to the Jira API or the link provided above using Script editor in Google sheets

Like Daria Pavlova likes this
0 votes
Ian Brown
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
January 4, 2023

I also had a problem with providing credentials and I was getting the same result as Oleksandr. I got this working by

Going to Query, Edit (the query editor will open),

Select Edit Permissions (a new window opens)

Under Credentials, select Edit, I then selected Basic and entered my Jira username and password

Suggest an answer

Log in or Sign up to answer