Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
Level
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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?

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

ThinkTilt is joining the Atlassian Family!

This morning, Atlassian announced the acquisition of ThinkTilt , the maker of ProForma, a no-code/low code form builder with 700+ customers worldwide. ThinkTilt helps IT empower any team in their or...

284 views 16 17
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