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

May I'm New Here Jan 15, 2018

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 vote
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

May I'm New Here Jan 15, 2018

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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Apr 17, 2018 in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

765 views 2 19
Join discussion

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