Tuesday, 3 April 2012

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
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/

25 comments:

Anonymous said...

Great work buddy.. It is very helpful to me.

Anonymous said...

Great Work...

We could use Rest API as well for creating Issues and updating it..

Anonymous said...

very very cool! Thank you

Anonymous said...

thanks again. Trying this out now!

Anonymous said...

its not getting connected to my jira instance. While working for another instance of same Jira version.

What can be wrong ?

thanks,
Sumit

Debasis Mondal said...

This is very helpfool for me..however I am getting the below error while connecting to the JIRA server - "the certificate for this server is invalid". Please help me to resolve this. Thanks in advance!

Anonymous said...

is there a way to call a local XML file instead of the browser url. i have certificate errors here in my vpn

Anonymous said...

Amol, that is some excellent work and works very well.
In case anyone works behind a proxy as most companies seem to do :(.
Add MyRequest.SetProxy after the MyRequest.setRequestHeader/

MyRequest.setRequestHeader "Content-Type", "application/xml"
MyRequest.SetProxy 2, "10.8.99.3:8080", "" 'Set proxy

Fabian said...

Nice work. how can I include a specific custom field?

Anonymous said...

Nice Work Amol,

Would you mind assisting me with inserting 4 JQL comment boxes that will extract the information to 4 separate sheets in the document..

Thanks,
NealRutt

Anonymous said...

Nice Work Amol,

Would you mind assisting me with inserting 4 JQL comment boxes that will extract the information to 4 separate sheets in the document..

Thanks,
NealRutt

Anonymous said...

Thanks a lot... great job.

Hiren said...

Hi Amol,
Great work. Here, I am getting error for -
For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes

The error is " Object variable or with block variable not set "

Can you or anyone pplease assist me in resolving this ?

Thanks in advance.

Hitesh Bhatia said...

Hi,

I am using JIRA Agile v6.6 and getting the exception(time out expired) while executing the code Request.Send.

Is this code compatible with version 6.6??

Thanks

Marcus Widerberg said...

Thanks for this. I was inspired by this and created another script for getting jira data into excel, also as a VBA script. I posted the file to github, download it from there.

https://github.com/chipbite/JiraToExcel

Karan Mehta said...

There is another way is by XML Maps functionality.
Create your own schema for the XML content in Excel & just refresh it.
Data will be updated from the XML link mapped.

Anonymous said...

Great article, buddy!

You can also try add-on JExcel which you can easily import/export JIRA data from/to Excel with.

It is also possible to use Ctrl C + Ctrl V for importing and exporting.

It totally feels like an Excel in JIRA. And Its completely free!

You can download it from the Marketplace: https://marketplace.atlassian.com/plugins/com.moresimp.jexcel/server/overview

Anonymous said...

Hi Amol
I am not sure if I am too late posting this question or downloading this good stuff but here is what I am facing.

I am facing issue on the below line as object variable or with block variable not set.

For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes

Can you please help me in that?

Thanks in advance
Sharad

Nitin Sawhney said...

Hi Amol,

IT was excellent work done by you. I am able to fetch the data from Jira to excel but somehow not getting few more column i need to add to my report like Severity, env,Resolver group. I am struglling to fetch those fields as well but finding it difficult. I will appriciate if you can let me know how to add those few more column.I can find those in XML nodes but they are not reading it to excel.

Regard
Nitin

Vikram Singh said...

I am facing issue on the below line as object variable or with block variable not set.

For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes

Can someone please help me in resolving this error? it would be a great help to get this tool working. Thanks Amol for sharing.

Sundar said...

Solution for the error - For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes


Those who have downloaded - workbook - "Download solution" attachment.
In the "Control" sheet, remove the value for JQL = "project="DEMO" AND assignee="DEMO""(leave the cell blank) or give existing correct value, it will work.
I also faced the same issue, then I remove JQL condition, it worked well.

tanera said...

Many thanks for the sample...

Anonymous said...

I am facing issue on the below line as object variable or with block variable not set.

For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes

Can someone please help me in resolving this error? it

Anonymous said...

I don't see the Sprint values in the report. Can you please help me on this

Anonymous said...

I am facing issue on the below line as object variable or with block variable not set.

For Each nodeContainer In resultXml.ChildNodes(2).ChildNodes(0).ChildNodes

Can someone please help me in resolving this error? it