JIRA Excel Tool (Data Import via VBA)

JIRA is one the majorly used tools for managing large software development projects in many of the big organizations these days. It’s one the products which is quite multi-faceted, providing functionality for varied users ranging from project managers, business users, developers and more. So for the non familiar ones the better option is to explore the tool themselves.
(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/