Exceleration: Import JIRA data into Excel using Power Query and JIRA REST API

What if you could create reports and inspect data from JIRA using Excel easily, efficiently and intelligently? JIRA by Atlassian may be the most popular Agile project management tool embraced by Scrum Masters, Product Owners, developers, testers and the organizations that depend on them. Excel by Microsoft is undoubtedly the most widely used spreadsheet (data analysis and reporting) tool used by leaders, communicators and basically anyone that uses a computer. What if you could have your Jira cake and eat it with Excel, too?

JIRA provides a full set of REST APIs giving programmable access to create, read, update and delete (CRUD) the data maintained behind-the-scenes. Microsoft Excel (since 2016) provides Power Query capabilities that enable “easy button” refresh capability within your worksheets, pivot tables, charts and dashboards. Wait… what? Here, I’ll show you. The steps below will detail how to:

  • Launch Excel (duh)
  • Launch Jira (ok, I can do that)
  • Create a JQL (what’s that? Jira Query Language allows you to filter, sort and retrieve Jira data)
  • Create a Jira API Token (basically a generated password that only you have access to)
  • Create a Power Query (available in Excel since 2016 and Microsoft’s Power Query M Formula Language

Launch Excel

Launching Excel… well… if you don’t know how to start Excel, then this article may not be for you

Launch Jira

Similarly to the Launch Excel section… you should probably know how to do this. Well, I will say, you should be using the “cloud” version of Jira… how can you know that? If the URL that you use to access Jira has “atlassian.net” in the name

Launch Jira

Create a JQL

Jira Query Language allows you to filter, sort and retrieve Jira data. Click on “Issues” and

Write query using Jira

Leave a Comment

Your email address will not be published. Required fields are marked *