Excel Web-Query Parameters to connect to Jira and export issues

[Anand Chug]

Hi there!

I will highly appreciate if you can share an example of how to create an Excel-Query to export issues from Jira including how/where to enter token for Rest API

Excel=>Data=>FromWeb=>Advanced

URL-Parts:

HTTP-Request-Header-Paramets: Field & Values

[Aron Gombas _Midori_]

If you have thousands of work items to export, and you prefer a one-click solution, let me recommend our popular Better Excel Exporter app.

(Dicl. this paid and supported app developed by our team. Free for 10 users.)

[Tomislav Tobijas]

Hi @Anand Chug ,

I think I've used that method only once, and it was a while ago. Any chance you've tried using the following app for Excel: Jira Cloud for Excel (official)?

It's pretty decent, and it's officially by Atlassian (and it's free). 👈

You would only need to authenticate once and once that's done, you could pull data from Jira to Excel and further manipulate it there. 

If you really want to use the path you've mentioned, you could try this:

In Excel, navigate to Data > From Web > Advanced and configure the following:

URL Parts

• Part 1: https://<your-sitename>.atlassian.net/rest/api/3/search?jql=

• Part 2: Your URL-encoded JQL query (e.g., project%3D"PROJ").

HTTP Request Header Parameters

To authenticate, you must provide your credentials in the headers:

1. Field: Authorization

2. Value: Basic <base64_encoded_credentials>

   • The credentials must be in the format email@address.com:API_TOKEN and then encoded to Base64 2.

3. Field: Accept

4. Value: application/json

For more info about API tokens, check here: Manage API tokens for your Atlassian account

Hope this helps.

Cheers,
Tobi

[Anand Chug]

Thanks @Tomislav Tobijas , I had used below steps However, I only get response with three rows stating list/nextPageToken/False. I have also validated my token shows accessed on Atlassian website at that time. Hence, it seems my token has been validated by Atlassian. I think it is a Query/URL issue now.

Excel:  Data=>WebQuery

Part 1: https://.../rest/api/3/search/jql?jql=project%20%3D%20BDE%20ORDER%20BY%20created%20DESC

Field: Authorization

Value: Basic <base64_encoded_credentials> [credentials formatted as per left]

Field: Accept

Value: application/json

Followed by selecting Anonymous selection

It returns table with:

issues List

nextPageToken ...

isLast FALSE

[Tomislav Tobijas]

Hmm @Anand Chug indeed 👀

What happens if you double-click on "List" item/value?

Usually, something like this would work (but this is more related to Power Query and how it handles things/results:

When Excel returns the "List" for issues, you usually need to perform a few extra steps in the Power Query Editor:

1. Click on the word List next to the issues field.

2. This will transform the list into a new set of rows.

3. Select To Table from the menu.

4. Click the Expand icon (two opposite arrows) in the column header to choose which fields (like key, fields, etc.) you want to display.

[Anand Chug]

Yes, you are right List returned by Jira Rest-API is an object and does expand into multiple rows for ID. However, I still have a challenge. As soon as specify list of fields in Rest-API query then it only returns first 100 rows despite specifying different/larger maxResults. I think this is due to Rest API design limits. I am not sure if there is any simple way to export results of a saved query that has about 10 fields and 1500 records automatically by calling the query in Excel. (I know manual was of doing this using Jira menu options to export data presented by query)

[Tomislav Tobijas]

@Anand Chug you'll probably have to use pagination for that (as you've noticed, there's an API limitation). It's explained here a bit:

In all cases, we'll use the Search for issues using JQL (GET) API endpoint. Pagination for this endpoint now relies on the nextPageToken parameter instead of the deprecated startAt parameter. Ensure your script can parse and utilize nextPageToken values for retrieving successive pages of results.

I'd still probably recommend Jira Cloud for Excel (official) as I believe the cap there is 5000 (or even more) 👀 + it's quite easy to configure.

[Anand Chug]

@Tomislav Tobijas! I am not sure how to use nextPageToken value in Excel query script. Iwill look for some examples to iterate Excel-Query to get all needed pages and then assemble them into one response. We can't get Excel Add-In in our environment. Thanks