{"id":7396,"date":"2024-01-28T01:09:52","date_gmt":"2024-01-28T01:09:52","guid":{"rendered":"https:\/\/haydentechnology.com\/htblog\/?p=7396"},"modified":"2024-01-28T18:26:15","modified_gmt":"2024-01-28T18:26:15","slug":"exceleration-import-jira-data-into-excel-using-power-query-and-jira-rest-api","status":"publish","type":"post","link":"https:\/\/haydentechnology.com\/htblog\/2024\/01\/exceleration-import-jira-data-into-excel-using-power-query-and-jira-rest-api\/","title":{"rendered":"Exceleration: Import JIRA data into Excel using Power Query and JIRA REST API"},"content":{"rendered":"\n

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?<\/p>\n\n\n\n

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<\/p>\n\n\n

Get a Jira API Token<\/h2>\n

<\/p>\n

<\/p>\n

Go to Security API Tokens<\/a>. To find this the normal way, click top-right icon and select “Manage Account”.\u00a0 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.<\/p>\n

<\/p>\n

<\/p>\n

\"\"<\/a><\/figure>\n\n

Search for Jira items using Jira Query Language (JQL)<\/h2>\n

<\/p>\n

<\/p>\n

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.”\u00a0 To search for targeted issues, you can modify the filter on Jira fields like Project, Type, Status, Assignee and more.<\/p>\n

<\/p>\n

<\/p>\n

\"\"<\/a><\/figure>\n\n

Retrieve Jira data from REST APIs<\/h2>\n

<\/p>\n

<\/p>\n

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:<\/p>\n

<\/p>\n

<\/p>\n