Calling JIRA REST API from Excel VBA – A Comprehensive Guide
Integrating Excel VBA with Jira REST API is a powerful way to automate Jira tasks, extract data, and generate customized reports without manual entry. In this article, we will guide you through the process of calling the Jira REST API from Excel VBA, allowing you to seamlessly connect and interact with your Jira account directly from Excel.
What is Jira REST API?
The Jira REST API is an interface that enables applications, like Excel, to communicate with Jira. It allows for the automation of tasks such as retrieving issues, project details, comments, and other data available in Jira. By integrating Excel VBA with Jira REST API, you can reduce manual data transfers and enhance productivity.
Key Terms and Concepts
- API Endpoint: The URL through which you can access specific data or perform operations on the Jira platform.
- JSON Format: Jira API responses are usually in JSON (JavaScript Object Notation), which is easy for both humans and machines to read and write.
- Access Token: Used for authentication to secure your access to Jira.
Prerequisites for Connecting Excel VBA to Jira API
Before making the connection, you’ll need:
- Jira Account with API access.
- API Token from Jira, used for authentication.
- Excel with VBA enabled to write scripts.
- Basic knowledge of VBA for implementing the API calls.
How to Set Up the Jira REST API in Excel VBA
To start using the Jira REST API in Excel VBA, follow these steps:
Step 1: Generate Your API Token in Jira
- Log in to your Jira account.
- Navigate to Account Settings.
- Under Security, select Create and Manage API Tokens.
- Generate a new API token and copy it for use in your VBA code.
Step 2: Prepare Your Excel Workbook
- Open Excel and go to the Developer tab. If it’s not enabled, go to File > Options > Customize Ribbon and enable the Developer tab.
- Open the Visual Basic for Applications (VBA) editor by clicking on Developer > Visual Basic.
Step 3: Write VBA Code to Call Jira API
The following VBA code uses HTTP GET requests to retrieve issues from a Jira project. This example will guide you through setting up a request to call the API and return the results in JSON format.
- In the VBA editor, go to Insert > Module to add a new module.
- Paste the following code, updating the variables with your Jira details:
Example Code to Retrieve Jira Issues
Sub GetJiraIssues()
Dim http As Object
Dim url As String
Dim jsonResponse As String
Dim apiToken As String
Dim projectKey As String
' Set your Jira details
apiToken = "YOUR_API_TOKEN" ' Replace with your API token
projectKey = "PROJECT_KEY" ' Replace with your Jira project key
url = "https://yourcompany.atlassian.net/rest/api/2/search?jql=project=" & projectKey
' Set up HTTP request
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.setRequestHeader "Authorization", "Basic " & Base64Encode("YOUR_EMAIL:" & apiToken)
http.setRequestHeader "Content-Type", "application/json"
' Send request
http.Send
jsonResponse = http.responseText
' Output response (for example, to Immediate Window)
Debug.Print jsonResponse
' Optionally, you can parse JSON response here (see later section)
' Clean up
Set http = Nothing
End Sub
Function Base64Encode(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Base64Encode = EncodeBase64(arrData)
End Function
Function EncodeBase64(arrData() As Byte) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.Text
Set objNode = Nothing
Set objXML = Nothing
End Function
Code Explanation
- Base64Encode function encodes your email and token to the required authentication format.
- HTTP Request setup defines the API endpoint and adds headers for authentication.
- jsonResponse stores the response from Jira, which can be printed in the Immediate Window (useful for debugging).
Step 4: Running the Code
- Press F5 in the VBA editor to run the
GetJiraIssues
macro. - The response will be printed in JSON format in the Immediate Window.
Sample JSON Response
A typical Jira JSON response for issues might look like this:
{
"issues": [
{
"id": "10000",
"key": "PROJ-1",
"fields": {
"summary": "Issue Summary",
"status": {
"name": "Open"
},
"assignee": {
"displayName": "John Doe"
}
}
}
]
}
Parsing JSON in Excel VBA
Since Excel VBA does not natively support JSON parsing, you can use a third-party JSON parser library, such as VBA-JSON. Download and add this library to your project for easier data manipulation.
Steps to Parse JSON
- Download the VBA-JSON library file and import it into your VBA editor.
- Modify your
GetJiraIssues
function to parse the JSON response, as shown below:
Dim json As Object
Set json = JsonConverter.ParseJson(jsonResponse)
' Loop through issues
Dim issue As Object
For Each issue In json("issues")
Debug.Print "Issue Key: " & issue("key")
Debug.Print "Summary: " & issue("fields")("summary")
Debug.Print "Status: " & issue("fields")("status")("name")
Next issue
Using Tables for Data Representation
You can format the data in Excel tables for better readability. Here’s how:
- Create a table in your Excel worksheet with headers like Issue Key, Summary, and Status.
- Modify the code to write parsed data into cells. For example:
Dim row As Integer
row = 2 ' Starting row for data
For Each issue In json("issues")
Cells(row, 1).Value = issue("key")
Cells(row, 2).Value = issue("fields")("summary")
Cells(row, 3).Value = issue("fields")("status")("name")
row = row + 1
Next issue
Handling Common Errors
When working with Jira API calls, you may encounter some common errors:
1. Authentication Errors
If the API token or credentials are incorrect, you will receive a 401 Unauthorized error. Double-check the token and ensure it’s in Base64 encoded format.
2. Invalid Endpoint or Project Key
If you receive a 404 Not Found error, the URL or project key might be incorrect. Ensure the API endpoint matches Jira’s requirements.
3. Rate Limiting
Jira might limit the number of API requests. If you see a 429 Too Many Requests error, wait a few seconds before trying again.
Example Use Cases for Jira API in Excel VBA
Automated Reporting
Use VBA to retrieve issue statuses across projects and generate weekly reports in Excel, saving time on manual data extraction.
Task Tracking
Automatically pull task details and display updates directly in an Excel sheet. This setup enables easy tracking of open tasks, assignee information, and due dates.
Customized Dashboards
Build a dashboard in Excel by pulling data from Jira and displaying KPIs such as project progress or issue status.
Final Thoughts
Connecting Excel VBA to the Jira REST API opens a range of possibilities for automating tasks and retrieving Jira data directly into Excel. By following the steps outlined in this guide, you can seamlessly interact with Jira’s API, saving time and creating tailored solutions for task management. The flexibility of Excel VBA, combined with Jira’s powerful API, allows for customized workflows and efficient reporting, improving productivity for Jira users.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.