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

[Jeffery Cavallaro]

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!

[Jeffery Cavallaro]

Ah, this looks promising from another issue:

http://<JIRA SERVER>/sr/jira.issueviews:searchrequest-excel-all-fields/temp/SearchRequest.xls?jqlQuery=<JQL QUERY>&os_username=<USERNAME>&os_password=<PASSWORD>

Is this the best path to take?

[James_byrd]

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://<SERVER NAME>/sr/jira.issueviews:searchrequest-csv-current-fields/<FILTER NUMBER>/SearchRequest-/<FILTER NUMBER>.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.

[Jeffery Cavallaro]

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

[James_byrd]

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/