Rest API export labels (JSON array)

Roman Kirchmeier
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 8, 2024

Hi there

We are using Jira Server version at one of my clients. I have a working solution to export and parse a JSON to Excel using VBA. But when it comes to fields like Labels, which is an array, I can't export is even declaring it as a Object or Variant doesn't work.

Do you have experience how that can be solved? I am actually happy if all values would be in one string.

Please find here the code:

 

Sub getPMTEpic() Dim wsStartPage As Worksheet Dim jsonResp As Object Dim currentRow As Long Dim issueType As String Dim issueName As String Dim summary As String Dim release As String Dim status As String Dim labels As Object Dim allLabels As Object Dim i As Long Dim moreJiratoRead As Boolean Dim jiraPosition As Long currentRow = 1 On Error Resume Next Set wsStartPage = ActiveWorkbook.Sheets("PMT Epics") On Error GoTo 0 'Error Handling in case Tab is not found If wsStartPage Is Nothing Then MsgBox "Blatt 'Test' nicht gefunden.", vbExclamation Exit Sub End If 'Prompt the user for the token token = InputBox("Enter your Jira token:") 'Delete Jira Items from Sheet "Jira Input" Sheets("PMT Epics").Select Columns("A:F").Select Selection.ClearContents 'Worksheets("Config").Activate 'Application.Wait Now + TimeSerial(0, 0, 3) 'Write Header of each column wsStartPage.Cells(currentRow, 1) = "Type" wsStartPage.Cells(currentRow, 2) = "Key" wsStartPage.Cells(currentRow, 3) = "Summary" wsStartPage.Cells(currentRow, 4) = "Status" wsStartPage.Cells(currentRow, 5) = "Labels" wsStartPage.Cells(currentRow, 6) = "TargetRelease" currentRow = currentRow + 1 moreJiratoRead = True jiraPosition = 0 'Use the token to authenticate the request Dim http As Object 'startPos as variable for paging over the JIRA Items Dim startPos As Integer startPos = 0 Set http = CreateObject("MSXML2.XMLHTTP") 'Use URL with the relevant JQL Sheets("Config").Select Dim URL As String URL = Range("M4").Value http.Open "GET", "URL & startPos, False 'Send Authorzation so you can access Jira http.setRequestHeader "Authorization", "Bearer " & token http.Send 'Respnse from above call in JSON responseContent = http.ResponseText 'Convert http Response (JSON) to VBA Set jsonResp = JsonConverter.ParseJson(responseContent) Set issues = jsonResp("issues") Dim issuesCount As Integer issuesCount = jsonResp("total") Do While startPos <= issuesCount 'MsgBox startPos http.Open "GET", "https://issues.pnet.ch/rest/api/latest/search?jql=" & URL & startPos, False 'Send Authorzation so you can access Jira http.setRequestHeader "Authorization", "Bearer " & token http.Send responseContent = http.ResponseText 'Convert http Response (JSON) to VBA Set jsonResp = JsonConverter.ParseJson(responseContent) Set issues = jsonResp("issues") 'Iterate over the JSON results and read the relevant values i = 1 Do While i <= WorksheetFunction.Min(50, issuesCount - startPos) issueType = CStr(EnhancedNz(issues(i)("fields")("issuetype"), "name")) issueName = CStr(issues(i)("key")) summary = CStr(EnhancedNz(issues(i)("fields"), "summary")) status = CStr(EnhancedNz(issues(i)("fields")("status"), "name")) release = CStr(EnhancedNz(issues(i)("fields")("customfield_11401"), "value")) labels = CStr(EnhancedNz(issues(i)("fields"), "labels")) 'Write value from JSON to each column iterative wsStartPage.Cells(currentRow, 1) = issueType wsStartPage.Cells(currentRow, 2) = issueName wsStartPage.Cells(currentRow, 3) = summary wsStartPage.Cells(currentRow, 4) = status wsStartPage.Cells(currentRow, 5) = labels wsStartPage.Cells(currentRow, 6) = release currentRow = currentRow + 1 i = i + 1 Loop 'Add +50 to the variable startPos for the paging startPos = startPos + 50 Loop

 

1 answer

0 votes
Tuncay Senturk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 12, 2024

Hi @Roman Kirchmeier 

Welcome to the Community!

Thank you for sharing the code. However, for readability and to help others assist you better, it’s always a good idea to format your code properly before posting. I recommend pasting your code using "code block" under formats and ensuring it's well-formatted before posting. This makes it easier for us to review and provide helpful feedback. 

 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events