how to pull status changes in transitions using vba?

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

3 answers

1 vote
Warren Levy Community Champion Aug 23, 2017


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"
.SetRequestHeader "Authorization: ", "Basic " & UserNameP
End If

If .Status = "401" Then
GetIssues = ""
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

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 Levy Community Champion Aug 23, 2017


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 :


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

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 Levy Community Champion Aug 23, 2017

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"))

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?


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

Warren Levy Community Champion Aug 25, 2017

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

The same error still there with the code below,

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

0 votes

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

See and look for the "changelog" section

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

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in Jira

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,083 views 0 8
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