Jira cloud excel add-in export limit changes

[Stuart Hamilton]

About a week back the Jira Cloud for Excel plug in has started to limit the amount of records you can extract to 19900. Previously I was able to export our entire JSM history into an excel file to facilitate a set of reports that we run on an ongoing basis. We are using a Filter in jira to show all the ticket data and export 18 fields only of important data.

We currently have just under 400k records and this feature to just suddenly stop working is very disruptive. Also adding addtional friction to get to the data is also not helpful by any changes that have been made to set limits on access to the data.

I have had a look at the relevant support docs and AI suggestions - some of which contradict each other and having to "batch" data out is also just adding a bunch of friction to the process.

[AmandaBezerra]

I've been having the same issue and not find anything about it.

[David Nickell]

Are you willing to use the REST API and Power Query to pull your data into Excel?    If so, let me know and I will add a link where you can download the tool I built (free).   All you need is a URL, and API key, and the filter id, and the fields.    

Its the same code (literally the exact same code) I use in Power BI...   Power BI and Excel share the Power Query Component.

I'd be curious for myself if the API hiccups at that sae threshold... I'll try to do some quick testing with the ecosystem.atlassian.net site

[Stuart Hamilton]

David

Happy to take a look at it, anything that gives us some easier access to the data will help the team.

Cheers

Stuart

[David Nickell]

Cool—- I’m away from my desk but will post a link later tonight 

[David Nickell]

So -- Success (so far).  In the ecosystem.atlassian.net, I can see 58,414 issues which is what my Excel Power Query is also returning.  I was also surprised at the speed -- under 10 minutes (which includes date formatting etc).

I'll spend some time polishing up some instructions and as promised, will post a link before morning.

(unless someone needs a solution tonight and wants to jump on a zoom call).

Side note - no need to answer, but as a "Data Engineering" guy ... I would ask whether you really want to pull 400k issues. Do they all change with each extract?   I ask because from my very narrow point of view (Excel and PowerBI) - those tools can process a folder as easily as a single input.  So you could archive your results in some named "month/year" file and just extract your new records and any updates.

screen shots below....   stay tuned

---

---

[David Nickell]

Hello -- the excel download can be found here:  

https://splitdimedata.com/downloads

The Excel File is at the bottom of the page.   I don't want to "junk up" this thread with details,  so I will publish a PDF of instructions shortly along with a YouTube video (where comments and collaboration are welcome).

Some quick points:

1. you will need an API Token.  I will provide instructions for that as well if you are unfamiliar with the process
2. I have included a second sheet in the file that will extract YOUR custom fields.  It may help you identify the fields you want to add to the extract
3. This version does the work item fields only.  I have another version that will grab the "child/detail" records for each of the issues (meaning Changelog, Worklog, etc).  
4. If you have ever used Power Query in Excel before, you can probably grab the file and go.  If you have not -- it'll only be another hour or so before I've pubslihed the PDF Instructions.

---

Here is a cheat sheet... not the full instructions.  Note the "Lock" symbol I put next to "In PowerQuery" indicates you can't work in Excel so long as the Power Query editor is open.  You either have to close the editor or save and load.

Happy data retrieval.

[David Nickell]

here is a YouTube Video I perepared on creating an API Token https://youtu.be/VHG9tUei9Xc 

[David Nickell]

Here is a YouTube for the actual extract process

https://youtu.be/EP-VJRzrdrg?si=6neEWnZFLJN4gJcG

[Stuart Hamilton]

Thanks, I will have a bit of a play with it - if I get stuck anywhere I will let you know. I assume I can specify the individual projects that I might want to pull from? Assume that could come from JQL or filter

Also re your earlier question on the data engineering side - the only reason that I am pulling all the data is that it was easier to just keep the whole thing updated as there is some aged tickets specifically and its helping on some broad metrics for JSM reporting.

The existing plugin wouldnt allow for updates only to then add new records or update ones that may have changed.

[David Nickell]

Yes -- you can use any JQL or Filter.  you may find it easier to run the JQL in the Issue Navigator first, then simply paste into the Excel Parameter

[David Nickell]

when you're ready to grab the non-changing records and set them aside to save time with future extracts, let me know.   There are JQL statements we can use like last updatedDates and./or resolutions to establish your archive. You wold use the REST API to grab the new records and once you have those, we'd merge with your archive.  

And when you're ready for your REST API Phd, we can add the changelog or other child records like comments and worklogs.

Note: I am going to revoke and re-issue my API Tokens.  If you've run my spreadsheet as is, you would have been using my credentials. As always - let me know if you have issues adding your API Token and User

Also - here is a link to the PowerBI Version of what you are running in the spreadsheet

https://app.powerbi.com/view?r=eyJrIjoiODA0ODFkZGUtYzBjYS00OWM5LTkzODEtOWEwNWM0MzQ2YmYzIiwidCI6IjNjZDVhOTg1LTBkM2YtNGM0ZC1hY2M1LTU2NTlhMTJiMTEyMyJ9