nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

how to pull status changes in transitions using vba?

Chai Teng Ong (KXJN47)

I would like to pull the date of status change in transition from Jira to Excel

Accepted answers

All comments

Nic Brough -Adaptavist-

Your VBA will need to issue a REST call to get an issue.

See https://docs.atlassian.com/jira/REST/server/#api/2/issue-getIssue and look for the "changelog" section

Chai Teng Ong (KXJN47)

Hi, can I have a sample of code in Excel VBA format?

Nic Brough -Adaptavist-

Not from me I'm afraid, I don't do anything with BASIC and avoid spreadsheets as most of them are wrong.

Warren_mz9b4z

It's your lucky day, because until a few months ago I used to use VBA for my REST calls, so I still have the code (I've now moved to C#). Remember in the encoding function to add your e-mail address and password, separated by a colon (:)

Public JiraService As MSXML2.XMLHTTP60

' ***********************************************************
' *** Returns the issues as per the JQL query ***
' ***********************************************************
Public Function GetIssues(query As String) As String

Dim json As Object

If JiraService Is Nothing Then Set JiraService = New MSXML2.XMLHTTP60
UserNameP = UserPassBase64

With JiraService

.Open "GET", query

.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
If UserNameP = "NoAuth" Then
.SetRequestHeader "Authorization", "No Auth"
Else
.SetRequestHeader "Authorization: ", "Basic " & UserNameP
End If
.Send

If .Status = "401" Then
GetIssues = ""
Else
GetIssues = JiraService.ResponseText
End If
End With

End Function

' ***********************************************************
' *** Encodes the Jira Username and Password ***
' ***********************************************************
Public Function UserPassBase64() As String

Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim URL As String

UserNameP = "Jira_email:Jira_password"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.Text

End Function

Chai Teng Ong (KXJN47)

But this is for login purpose only right?

What if now I need to pull the date on each status change from Jira transition to Excel? Like from Created > In Progress > In Review > Resolved > Closed.

Warren_mz9b4z

This is generic code and any API call can be run through it. The query that you pass in to GetIssues is of the form :

"https://companyname.atlassian.net/rest..."

where after rest you can have any API call together with it's associated JQL

Chai Teng Ong (KXJN47)

Yes, I have did that.

But now, I'm stuck on how to extract and get the data in transition part.

Besides, I have another problem, where there is a debug error in Excel VBA when I try to get the custom fields which the value=null.

Mei Ying Phuah (RMJD64)

...

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira