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. Follow these steps
Get a Jira API Token
Go to Security API Tokens. To find this the normal way, click top-right icon and select “Manage Account”. Then, go to Security Tab, scroll down and select Create and Manage API Tokens. Click “Create a new key”, enter name then be sure to copy/save your Key in a safe place (for security reasons, it will only be visible this one time)… You’ll use this as your password in Excel Power Query to connect to Jira APIs.
Search for Jira items using Jira Query Language (JQL)
Jira Query Language allows you to filter, sort and retrieve Jira data. Click on “Issues” in your project or menu item “Filters” and “Search for all issues.” To search for targeted issues, you can modify the filter on Jira fields like Project, Type, Status, Assignee and more.
Retrieve Jira data from REST APIs
Many cloud applications transport data between data store and user interface via REST APIs (Representational State Transfer Application Programming Interface). Often, these APIs are made available for external access. We will use the APIs and the resulting data to feed into Excel. First, let’s try out the REST API. You can do this right in your browser. You’ll need:
-
- Jira Base URL (see section above)
- REST API (see JIRA Cloud REST API documentation online)… the request we’re looking for is Issue Search (/rest/api/3/search)
- JQL Query (notice the URL from the Search for Jira Items above (specifically jql=project%20in%20(HTS)… you could also take the JQL string (with spaces) and encode a URL friendly string using https://www.urlencoder.org/… I find it easier to just grab it from the browser URL address.
Putting it all together… [Jira Base URL] + [REST API] + “?” + [JQL Query] and paste into a browser window… you get a bunch of data in JSON format. JavaScript Object Notation (JSON), is an open data interchange format that is both human and machine-readable.
Create a Power Query (available in Excel since 2016 and Microsoft’s Power Query M Formula Language
OK… now, we want to pull the all this together and connect Excel to the JIRA APIs using Power Query. Go to Data –> Get Data –> From Other Sources –> From Web
Excel Power Query Get Data from JIRA
Copy in the full used above to retrieve JSON data from JIRA
You may (should) be prompted to enter your Jira credentials. Generally, you’ll only be prompted once for this as Excel will store your credentials / settings for each unique website in the OS for future workbook connections… OK, pay attention here, there are numerous authentication options available.. you want to use “Basic” Authentication… use your Jira username (your email address in Jira Cloud) and use the Jira Access Token created in the step above.
Once you are successfully authenticated… the JSON data will be interpreted by Microsoft Power Query and allow you to make selections (or use M Query in advanced editor)
Pretty cool.