(Link provided in the reference)
From couple of past days I was indulge developing the tool to do a seemingly simple operation for many of the business users i.e. bring the JIRA data to excel via VBA. In spite the JIRA providing many of the functionalities required of dashboard and reporting aggregated numbers, still business people ask for data analysis where excel sneak’s in with developers like me (pat on the back).
So I was working with JIRA version 4.4 and exploring the available options provided via JIRA API ranging from SOAP (not recommended any more via JIRA) to REST (recommended). Painfully I discovered that either of the options wasn’t the best, as the requirement consisted of fields which weren’t available by either of the methods to be imported to excel via VBA (like computed or custom).
Then I tried navigating to the search area (via JQL) of the JIRA portal where I fire-bugged the request of XML option gaining the link being fired and using the knowledge gained for authentication via REST. Then I tried combining the knowledge of both in the tool I created recently, using the basic authentication mechanism for the REST API and firing the URL for the XML data containing the JQL query which is used to filter the cases in JIRA. And surprisingly it worked in my favor giving me the ability to build the following tool.
modJira.bas
Public Sub extractJiraQuery(userName As String, password As String)
Dim MyRequest As New WinHttpRequest
Dim resultXml As MSXML2.DOMDocument, resultNode As IXMLDOMElement
Dim nodeContainer As IXMLDOMElement
Dim rowCount As Integer, colCount As Integer
Dim fixVersionString As String
Dim dumpRange As Range, tempValue As Variant
Set dumpRange = Sheet2.Range("A2")
Sheet2.Range("A2:AZ65536").Clear
Application.ScreenUpdating = False
Application.StatusBar = "JIRA: Preparing header..."
MyRequest.Open "GET", _
"https://JIRA_HOST_NAME/sr/jira.issueviews:searchrequest-xml/temp/SearchRequest.xml?jqlQuery=" & modCommonFunction.URLEncode(ThisWorkbook.Names("jqlQuery").RefersToRange.value) & "&tempMax=1000"
MyRequest.setRequestHeader "Authorization", "Basic " & modCommonFunction.EncodeBase64(userName & ":" & password)
MyRequest.setRequestHeader "Content-Type", "application/xml"
'Send Request.
Application.StatusBar = "JIRA: Querying request to JIRA..."
MyRequest.Send
Set resultXml = New MSXML2.DOMDocument
resultXml.LoadXML MyRequest.ResponseText
Application.StatusBar = "JIRA: Processing Response..."
For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes
fixVersionString = ""
If nodeContainer.BaseName = "issue" Then
Application.StatusBar = "JIRA: The total issues found: " & nodeContainer.Attributes(2).text
End If
If nodeContainer.BaseName = "item" Then
For Each resultNode In nodeContainer.ChildNodes
'Debug.Print resultNode.nodeName & " :: " & resultNode.text
If resultNode.nodeName = "fixVersion" Then
fixVersionString = fixVersionString & resultNode.text & " | "
GoTo nextNode
End If
If resultNode.nodeName = "aggregatetimeoriginalestimate" Then
tempValue = GetOriginalEstimate(resultNode)
dumpRange.Offset(rowCount, 23).value = tempValue
If tempValue <> "" Then
dumpRange.Offset(rowCount, 25).value = CLng(tempValue) / (60 * 60)
End If
End If
If resultNode.nodeName = "customfields" Then
dumpRange.Offset(rowCount, 22).value = GetStoryPoint(resultNode)
GoTo nextNode
End If
If resultNode.nodeName = "timespent" Then
tempValue = GetTimeSpent(resultNode)
dumpRange.Offset(rowCount, 24).value = tempValue
If tempValue <> "" Then
dumpRange.Offset(rowCount, 26).value = CLng(tempValue) / (60 * 60)
End If
End If
dumpRange.Offset(rowCount, GetColumnValueByName(resultNode.nodeName)).value = resultNode.text
nextNode:
Next resultNode
dumpRange.Offset(rowCount, 14).value = fixVersionString ' Fix Version
rowCount = rowCount + 1
End If
Next nodeContainer
Application.ScreenUpdating = True
MsgBox "The data extractions is now complete.", vbInformation, "Process Status"
End Sub
modCommonFunction.bas
Option Explicit
Public Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Set objXML = New MSXML2.DOMDocument
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Public Function URLEncode( _
StringVal As String, _
Optional SpaceAsPlus As Boolean = False _
) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
Function GetColumnValueByName(columnName As String) As Integer
Dim iCount As Integer
GetColumnValueByName = 100 'Default if not found
For iCount = 0 To Sheet2.Range("A1").End(xlToRight).Column
If Sheet2.Range("A1").Offset(0, iCount).value = columnName Then
GetColumnValueByName = iCount
Exit Function
End If
Next iCount
End Function
Function GetTimeSpent(node As IXMLDOMElement) As String
GetTimeSpent = node.Attributes(0).text
End Function
Function GetOriginalEstimate(node As IXMLDOMElement) As String
GetOriginalEstimate = node.Attributes(0).text
End Function
Function GetStoryPoint(node As IXMLDOMElement) As String
Dim itm As IXMLDOMElement
For Each itm In node.ChildNodes
If (itm.ChildNodes(0).text = "Story Points") Then
GetStoryPoint = itm.ChildNodes(1).text
Exit Function
End If
Next itm
End Function
* The code is in two module with couple of functions specific to the XML parsing. But the main core of the tool is in modJira where the request is being fired to JIRA portal and response being recived in as XML.
Though the tool doesn’t offer the auto-complete feature as JIRA itself, I would suggest forming the JQL’s in JIRA and transferring them on to excel for import would be a good idea.
Download solution
References:
Link1: http://www.atlassian.com/software/jira/overview
Link1: https://developer.atlassian.com/display/JIRADEV/JIRA+REST+API+Example+-+Basic+Authentication
Link3: http://docs.atlassian.com/rpc-jira-plugin/4.4/com/atlassian/jira/rpc/soap/JiraSoapService.html
Link4: http://getfirebug.com/
