Use of the REST API to reproduce Sprint burn down chart

Question

Hi

I have a need to produce an Excel workbook for business stakeholders that contains key sprint information including a burndown chart, list of issues added and list of issues removed.

I know I can consume the REST API from Excel - so that's not a problem.

What I actually need a steer on is whether this is actually possible via the REST API to pull the salient data out of JIRA in the first place?

Bill Sheboy · Answer

Yes...you can form a JQL query to get your needed data, and then access it with the REST API.

Here is the complete reference list:

https://docs.atlassian.com/software/jira/docs/api/REST/1000.824.0/

Here are some good examples:

https://developer.atlassian.com/server/jira/platform/jira-rest-api-examples/

graemejohnson · Answer

Thanks @Bill Sheboy - Not wanting to curse myself, but I think I'm good with how to call the REST API from within Excel and consume what it throws back.

What I'm struggling with is know which REST API's there are that I can call where I can get what in essence is shown on the JIRA Sprint report e.g. a burndown chart, issues completed, issues not completed, issues removed from sprint etc.

Is there, dare I say it, a one stop shop REST API that serves this information up on a plate?

Bill Sheboy · Answer

Hi @graemejohnson

Short answer: This *should* be possible, using Excel and Power Query functions, however...

I tried this a couple of months ago without success.  There are several how-to articles and free templates on the interwebs to do this.  I'll share what worked and did not work for me.  Hopefully you will have more success.

* Create a REST API string/URL to query your data.  Note this can only pull up to 100 issues at a time, so you will need to implement paging.
* Create an API token for security; this is configured by user from Atlassian
* Build a spreadsheet to use Power Query to call the REST API, and...
* Parse the JSON
* Add to the spreadsheet to consume your resulting data
* Regularly open/refresh the spreadsheet periodically or you will miss data.

I got everything to work except Excel could not call the API.  With Excel 2016 it never accepted the API token *and* successfully ran the call.  If I ran the call manually in a browser and pasted in the data, everything worked.  Maybe it will work with later versions of Excel.  In other posts, I saw people gave up and used PowerBI instead.

Good luck, and please post if you figure this out.  Thanks!

Best regards,

Bill