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.

[Nigel Domaingue · Accepted solution]

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]

What is the permission I have to provide?

[Melissa]

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

[Irina Kurakin]

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

[Melissa C]

@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

[Caitlin Armer]

@Nigel Domaingue - Thank you for your original post. I am also digging it back up. When I get into the Navigator, my only options are HTML Code and Displayed Text. Any idea on how to get to the actual data? Any help is appreciated! 

[Christopher Lebruh]

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

[Melissa]

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

[Christopher Lebruh]

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. 

[Melissa]

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

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]

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

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

[Ian Brown]

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