What is the best way to query Jira and upload the results into an Excel spreadsheet

Question

I am new to Jira and I am inheriting an application composed of an excel spreadsheet that is using a connector to query the mysql database behind Jira directly.  Now, the database and Jira are going behind a firewall and the only access is to Jira behind a reverse proxy.  I see that there is a plugin on the marketplace for a Jira connector; however, for reasons I won't go into I cannot use it.  I was hoping that there is a way to issue a JQL query via the REST API; however, I didn't see any obvious support for this. I don't mind learning how to be a Jira developer, but I don't want to waste time if there is an easy solution.  Any suggestions would be greatly appreciated!

James_byrd · Answer

I couldn't find the  original Atlassian community post I used, but I found this blog post that takes you through the steps just as well. https://www.rightstar.com/atlassian-jira-community/integrating-jira-filters-with-excel/

Jeffery Cavallaro_za2fj2 · Answer

Awesome.  Thanks for the heads up.  I will give it a try.

James_byrd · Answer

It looks like that ability to log in directly with the URL has been removed: https://confluence.atlassian.com/cloudkb/jira-and-confluence-cloud-os_username-and-os_password-embedded-in-url-no-longer-available-872007971.html Until recently, I was able to get data into Excel by first saving a filter in Jira, noticing the Filter number in the URL that is generated, and then putting the filter number into a Power Query web data source like this: https:///sr/jira.issueviews:searchrequest-csv-current-fields//SearchRequest-/.csv? I used Basic Authentication and generated an API key from Jira to use as the password. This was working to download directly into Excel using the refresh button. For me, this method stopped working sometime last week and now gives an XSRF Security Token Missing error. But perhaps this method will still work for you.