how to pull status changes in transitions using vba?

[Chai Teng Ong]

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

[Warren]

Hi

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]

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]

Hi

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]

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. 

[Warren]

For the transition, use something like :

status changed FROM Created TO "In Progress"

in your JQL bit - test it in Jira's Advanced issue editor.

For null values, use IIf and IsNull e.g.

estFrom = IIf(IsNull(histories(z)("items")(i)("fromString")), 0, histories(z)("items")(i)("fromString"))

[Chai Teng Ong]

For the null value problem, I tried with this 

AR = IIf(IsNull(Json("fields")("customfield_10010")(1)("value")), 0, Json("fields")("customfield_10010")(1)("value"))

but still have the Run-time error '13': Type mismatch, if the value of the custom field is equal to null. While it's working fine if there is some value in that custom field.

For transition, it does give me the result of a list of issues when I search through Advance search in Jira. But how do I get the date changes?

[Chai Teng Ong]

where the json code for the custom field is as shown in the picture

[Warren]

I think what you need to do is

AR = IIf(IsNull(Json("fields")("customfield_10010")), 0, Json("fields")("customfield_10010")(1)("value"))

so the IsNull check looks at the highest level of the customfield - if it isn't null, then you can use the lower level value. With your code, you're testing the value of a non-existent field

[Chai Teng Ong]

The same error still there with the code below,

AR = IIf(IsNull(Json("fields")("customfield_10010")), 0, Json("fields")("customfield_10010")(1)("value"))

[Mei Ying Phuah]

...

[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]

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.