How to retrieve the specific component and project key of the jira issue using vba? Edited

I was able to display the values of  other fields like JIRA KEY, status, priority, description and etc in excel. except for its designated COMPONENTS and PROJECT KEY.

I already have this code, can you help me where did I get wrong and how can I display its components and project key.. 

Ex. the components for NW093 is WEB and project key is NW00A

Thanks in advance

Public Function GetJiraComponentList(ByRef projectKey As String, ByRef url As String, ByRef user As String, ByRef password As String) As Collection
If right(url, 1) <> "/" Then
url = url & "/"
End If

Dim outList As New Collection
Dim project As Dictionary
Set project = GetJson(url & "rest/api/2/project/" & projectKey, "GET", user, password)

Dim index As Long
For index = 1 To project("components").Count
Dim id As String
id = project("components")(index)("id")

Dim component As Dictionary
Set component = GetJson(url & "rest/api/2/component/" & id, "GET", user, password)
Call outList.Add(component)
Next index

Set GetJiraComponentList = outList
End Function




1 answer

0 votes
Warren Levy Community Champion Jan 15, 2018

Hi May

A potential better way of doing this is to use a call like 


if you know the issue key. This returns all fields for the issue, but can be restricted by passing in the required fields


As you can see, to get the components you use component (note it's singular). I don't believe you are able to get the project key

Hi Warren, 

Thank you for your response.

How about this? Is this correct? I'm not really so sure about this, I'm still learning. 

Public Function GetIssueList(ByRef issueKey As String, _
ByRef url As String, _
Optional ByRef user As String = vbNullString, _
Optional ByRef password As String = vbNullString) As Dictionary

If right(url, 1) <> "/" Then
url = url & "/"
End If

Static map As Dictionary
If map Is Nothing Then
Set map = New Dictionary

Dim list As Collection
Set list = GetJson(url & "/rest/api/2/issue/" & issueKey, "GET", user, password)

Dim id As String
Dim Name As String
Dim index As Long
For index = 1 To list.Count
id = list(index)("id")
Name = list(index)("name")

Call map.Add(id, Name)
Next index
End If

Set GetIssueList= map
End Function

Warren Levy Community Champion Jan 15, 2018

There are numerous options / API calls that you can use, as per this Cloud REST API doc. I am not clear on what you're trying to achieve, but there are calls that will return all items in a sprint, all items on a board etc. On top of that, you can pass a JQL query in to further filter the results. Then, within your code, you will need to loop through all the results, extracting the relevant info. Take a look at the link (which has been significantly updated since I last looked at it) and work out what's the best way for you to get what you're trying to achieve

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,948 views 19 22
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you