Extracting JIRA Issue Data using Power Query in Excel

[Singh_ Brajesh]

I am trying to extract JIRA issue data using power query in excel.  I have been able to do this in the past.  I would open a blank query and type the following using the correct domain name and API token.  However, it no longer works.  Does anyone know how I can extract data from Issue using Excel Power Query? 

= Json.Document(Web.Contents("https://mydomain.atlassian.net/rest/api/3/search?jql=filter=152345", [Headers=[Authorization="Bearer <API Token>"]]))

Thanks in advance.

[Paul Glantschnig _Appfire_]

Hi @Singh_ Brajesh ,

Arkadiusz Wroblewski already nailed the two most likely causes, so I'll just expand on those and add a couple of things.

Pagination The Jira REST API returns a maximum of 50 or 100 issues per page by default. If your filter returns more than that, you'll need to handle pagination by incrementing startAt. This has always been the case, but it's easy to overlook if your dataset has grown.

Check your API token If you recently changed your Atlassian password or if the token was revoked, you'll need to generate a new one at id.atlassian.com.

Separately, if the reason you're pulling data into Excel is to get a spreadsheet-like view of your Jira issues (sorting, filtering, pivoting, etc.), you might want to look at JXL for Jira. My team and I build it.

JXL gives you a full spreadsheet/table interface directly inside Jira, with inline editing, column filtering, grouping, and export to Excel/CSV. If your goal is "see my Jira data in a table I can work with," it might save you from maintaining an API integration altogether.

Of course, if you need the data in Power Query for further transformation or to feed into other Excel models, fixing the API call is the right path. But if it's mainly for viewing and light analysis, JXL could be a simpler option.

Hope this helps, Paul

Disclosure: I work for the team that builds JXL.

[Arkadiusz Wroblewski]

Hello @Singh_ Brajesh 

For Excel I was using Jira add-on for Excel to pull Data out of Jira. Have you ever tried it ?

[Singh_ Brajesh]

I believe I tried it once but I don't it worked because the add-on was not supported by my companies IT.  The ended up giving me that work around I described above and it worked great until it doesn't now.  :(  

[Arkadiusz Wroblewski]

@Singh_ Brajesh 

Most likely your old query stopped working because Atlassian has deprecated and is removing the older search endpoint you were calling.

Instead of:

/rest/api/3/search?...

you should now test with:

/rest/api/3/search/jql?...

Also, for Jira Cloud API tokens, make sure you are using Basic authentication with your Atlassian email address + API token, because that is the standard method Atlassian documents for this scenario.

So there are two things to check:

1. update the endpoint
2. verify the authentication method

Your Power Query should be closer to using the new search/jql endpoint rather than the old search one.

If you want to keep using a saved filter, your JQL can still reference the filter, but the request should go to the new search path.