Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,144
Community Members
 
Community Events
165
Community Groups

Import JIRA REST API results to Excel Web Power Query

Edited

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.

 

1 answer

1 accepted

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.

What is the permission I have to provide?

Like # people like this

@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 Ophelie likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

155 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you