Click here to Skip to main content
14,024,092 members
Click here to Skip to main content
Add your own
alternative version

Stats

74.6K views
4.4K downloads
11 bookmarked
Posted 12 Apr 2016
Licenced CPOL

Excel, Jira, Rest API end to end example

, 12 Apr 2016
Rate this:
Please Sign up or sign in to vote.
An end to end view of how to integrate Jira REST API's with Excel.

Introduction

There is a lot of information on the web about Jira REST API's and do some simple REST calls to Jira from Excel but I have been unable to find a good end to end resource that glues all the concepts together to show one how to login, retrieve data, parse the returned data and insert into excel.  There are some 3rd party for purchase add-in's to excel out there but I wanted a general all purpose add-in for Jira integration with Excel that allowed me to get, update, create, and create word documents ie change request forms from Jira information without having to pay for something.

In this article I will share my learnings, Excel VBA code, and references to others code that provides a fairly elogant tho still in progress semi generic integration between Jira, Excel, and Word.

 

Background

The first problem I wanted to solve while using Jira was that I wanted to see a list of issues, and their associated issues with the associated issues current status and fixed versions.  I couldn't find away to do this in Jira so I decided to write an excel macro that would get issues from Jira and parse through the associated tickets and list out the status and fixed version so that in a single row/view I could see the status of the ticket and it's associated ticket.  In the image below columns O, P, and Q show the results I was looking for.  And if you look at CLOUD-8050 row you will see how when there are multiple issues linked to an issue how it's displayed in one cell.

 

After solving that problem I kept extending the Excel macro functionality to create new tickets update tickets and fill in a word document with data from a jira ticket.  The create and update features where more or less a one time need but the form project seemed like on an ongoing generic problem.

While working on this project my biggest issue was that there were plenty of articles out there that talked to single point solutions in using the Jira Rest API from Excel but nothing that really provided an end to end view of how to login, call the api, parse the Json, and the nauances around what I learned.

Using the Excel Jira Integration Macros

In this section I'll document how to use the excel workbook as an end user.  In the points of interest section I'll document the learnings and key code fragments.

Note: I have used data from https://jira.atlassian.com as examples.  One interesting thing I have noticed is that the rest api call will sometimes fail but a subsequent call will work just fine.  I have chaulked it up to load on the Jira server but have not introduced retry logic in that it has been easy enough just to hit the "Get Issues" button again and all works fine the 2nd time around.  This is also where I had to introduce the No Auth code into the login process in that apparently the jira site does not require authentication nor will it take it on the Jira Rest API's. 

 

The excel work book that comes with this article contains the custom menu additions and the macros both those that I developed on others.  

When you open the workbook there is a new menu on the ribbon called JIRA.  This is a custom menu that calls macros that perform various functions related to retrieving and working with Jira data.

                          

The predominate menus option is "Get Issues" this macro will in turn call Login if the user is not already logged into Jira through Excel.  It will execut the Jira JQL command in column B1 and return the results starting in the table at A8 which is referred to by a defined name in excel as "StartRow".

See the example of the excel workbook in the image above in the background section.

On the macroValues worksheet there is a table that is used to define how to extract the returned data from Jira that is in JSON format so that it can be inserted into the excel worksheet.

The the excel workbook sheet 'Tickets' row 5 which is hidden contains the key for each column.  This key looks up the information int this table on the macroValues worksheet to determone how to extract the data from the Jira Json and if a method should be applied to the data to transform the data.

You can add and remove columns to the Tickets worksheet simply by inserting a new column and indicating what key value is to be used to translate the data with.  You can update the table in the macroValues with your Jira's instance specific custom fields and other fields that you may want to pull into excel by following the format in the table.  Note that customfields will have different values even if you have the same name/label for that field.  These values are unique to each Jira instance.

When you make a change to the table you need to click on the Initialize button in the Ribbon Jira Group.  This dynamically creates a macro called getValue and uses this table to create a sub routine that knows how to reference the desired data from the data returned by Jira.

 

Field Value Reference  Check for Null Reference Method
key ("key") ("key")  
status ("fields")("status")("name") ("fields")("status")("name")  
project ("fields")("project")("name") ("fields")("project")("name")  
summary ("fields")("summary") ("fields")("summary")  
created ("fields")("created") ("fields")("created") fromISODateTimeNoZ
updated ("fields")("updated") ("fields")("updated") fromISODateTimeNoZ
assignee ("fields")("assignee")("name") ("fields")("assignee")  
reporter ("fields")("reporter")("name") ("fields")("reporter")  
fixversions ("fields")("fixversions") ("fields")("fixversions") GetFieldsWithCount
issuetype ("fields")("issuetype")("name") ("fields")("issuetype")  
labels ("fields")("labels") ("fields")("labels") GetFieldsWithCount
prioity ("fields")("priority")("name") ("fields")("priority")  
severity ("fields")("customfield_12520")("value") ("fields")("customfield_12520")("value")
resolution ("fields")("resolution")("name") ("fields")("resolution")  
issuelinks ("fields")("issuelinks") ("fields")("issuelinks") GetFieldsIssueLink
businessDriver ("fields")("customfield_15623") ("fields")("customfield_15623")  
businessSignificance ("fields")("customfield_12420") ("fields")("customfield_12420")  
description ("fields")("description") ("fields")("description")  
detailedDescription ("fields")("customfield_15621") ("fields")("customfield_15621")  
billable ("fields")("customfield_15926")(1)("value") ("fields")("customfield_15926")  
LOE ("fields")("customfield_15629") ("fields")("customfield_15629")  
approvalRequired ("fields")("customfield_16320")("value") ("fields")("customfield_16320")  
approvedBy ("fields")("customfield_11622")("displayName") ("fields")("customfield_11622")  
 
 
          
 
 
The login menu is available for those that may have multiple instances of Jira.  IE. different jira servers that you use and want to retrieve data from.  And it's only needed if you get data during the same session.  For example if you open and close excel and then switch servers the 'Get Issues' command button will prompt you for login information and let you switch servers. 
 
The 'Update Linked Status' command button is only used if all you want to do is update the status of the linked issues that are already in the 'Tickets' worksheet.  When you use the 'Get Tickets' command button it gets the Jira tickest and updates the linked status all at once.
 
The 'Fill Out Word Template' command button prompts for a word template and a Jira ticket key.  The drop down box for the word templates is populated form the table on the macroValues worksheet just add the full path to any word document that you want to use as a template. 
 
The command loads the word document loops through all of the custom form fields in the word document and matches the tag on each form field to the same table described above in the key column.  When there is a match the approriate Jira data is inserted into the form field.
 
The following excerpt from a word document show where the form fields are in the 2nd column of a table. 
 
To insert word customer form fields into a word document you must enable the developer tab on word.  The image below show where the developer tab in word has been enabled.  To insert fields select the 'Design Mode' command mode and it will highlight itself in grey to indicate that you are in words design mode.
 
 
Once in design mode you can insert a text box or check box where Jira data can be inserted by the macro.  After you have inserted a form field you can right click on it and select the properties menu option.
 
 
In the properties dialog box for the form field just inserted edit the tag field and insert the name of the key from the Jira table on the macroValues worksheet to indicate what data you want inserted into this form field.  It's also a good idea to put a check mark on the 'Contents cannot be edited' field so that users don't edit the word document thinking that they can make changes to Jira when a change is made to the word document.
 
A future version or seperate project might be to create code that would allow Jira data to be updated in the word document and then updated in Jira.  While this is technically possible the work flow and potential hazards of doing so would need to be well thought.  For example what if the data had changed since it was inserted into the word document.  Would the end user have access to Jira.  Typically this functionality if for the purpose of getting a static copy of the Jira data and getting a physical signature from a client or stake holder.
 
Repeat the process for as many Jira fields that you want inserted into your word template.
 
 
 
 
Once you have your word template in place it's simply a matter of select the command button from the JIRA custom menu and filling out the dialog box.   A new instance of word will be opened, the templated loaded, the data fetched from Jira and then inserted into the word document.
 
The create and update menu options where one time use deals for me.  They are hard coded to the values used for creating and updating tickets but can serve as a decent reference for using the Jira API's but there is nothing fancy and nothing that a number of other articles haven't touched on.

Tools and Dependancies

Before moving on to the points of interest and talking through some of the code.  A number of tools and others code have been used.  So to give credit where credits due...

  • Chrome Postman
    • I found the use of the chrome addin postman very valuable in that I could make sure that the basic format and structure of the rest uri was correct and test it in postman.  It was also very useful to be able to see the returned Json so that I could identify the hierarchy structure to know how to access the converted Json object structure. 
    • By sending the rest api string to Jira through postman I can see the raw json returned.  This was the best way to determine what custom fields are labeled with so that you can dereference the returned Json accurately.   You might have to update an existing ticket with some unique identify values so that you can pick out the field in the returned Json.

  • Save the username and server URL for the login dialog box uses WriteProp and ReadProp methods that I picked up from the following location.

http://word.mvps.org/faqs/macrosvba/MixedDocProps.htm

  • Custom UI Editor for Microsoft Office
    • I found this tool at http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx and utilized it to easily customize the Excel Ribbon Menu.
    • To add my own menu to an excel document I used the UI editor tool to edit and update the excel file so that the JIRA ribbon menu is available when opening the excel file.
  • Userful Stuff

There are several methods in the VBA project that start with useful stuff.  Check the following website for details and information on useful stuff.  I copied the fromISODateTime function and created fromISODateTimeNoZ which simply removed the 'Z' from the regular expression in order to parse the dates returned from Jira which were in iso format minus the Z at the end.  At one point I was attempting to use the cJobject provided with mcpher's code however the VBA-JSON project mentioned below was what I ended up using.  There looks like there is alot of cool stuff at ramblings.mcpher.com and worth reading up on.  I imported quite a bit of his code but ended up not use much of it.  Regardless I think there is some really cool concepts and stuff there that merits further investigation.


Licensing and copyrigt information for the useful Info stuff->

'gistThat@mcpher.com :do not modify this line - see ramblings.mcpher.com for details: updated on 2/16/2016 10:02:49 AM : from manifest:3414394 gist https://gist.github.com/brucemcpherson/3414346/raw
Option Explicit
' v2.23  3414346
 
' Acknowledgement for the microtimer procedures used here to
' thanks to Charles Wheeler - http://www.decisionmodels.com/
' ---

 

  • This tool was relatively easy to use but I didn't find much documentation on it's actual usage and had to learn alot through trail and error.  The biggest issue I had was deteremining if a value existed something along the lines of IsNULL.

 

  • The VBA-JSON tool is relatively easy to use
Set JsonObject = JsonConverter.ParseJson(JsonIssues)

Here the Json string returned from Jira is parsed into an object called JsonObject after it is parsed into an object you can reference the Json as an array of arrays type object.

From the image above of the Json returned in chrome's postman the following references can be made.

dim s as string
s = JsonObject("maxResults") 
s = JsonObject("issues")(1)("key")
s = JsonObject("issues")(1)("fields")("customfield_16730")("value")
s = JsonObject("issues")(1)("fields")("priority")("name")

The trick here is that when a field has sub arrays you have to check for null on the parent area. You can't use isnull(JsonObject("issues")(1)("fields")("priority")("name") you have to check for null on JsonObject("issues")(1)("fields")("priority")


Licensing and copyrigt information for the useful Info stuff->

' VBA-JSON v2.0.1

' (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON
'
' JSON Converter for VBA
'
' Errors:
' 10001 - JSON parse error
'
' @class JsonConverter
' @author tim.hall.engr@gmail.com
' @license MIT (http://www.opensource.org/licenses/mit-license.php)
'' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
'
' Based originally on vba-json (with extensive changes)
' BSD license included below
'
' JSONLib, http://code.google.com/p/vba-json/
'
' Copyright (c) 2013, Ryo Yokoyama
' All rights reserved.


Points of Interest

The Basic Code Flow

  • Login to Jira
  • Make Jira REST API call to return Json
  • Convert Json to Object
  • Dereference object using table defined in excel
  • Insert results in worksheet or word document

Login In

There are numerous examples on the internet for logging in to Jira.  To round out the examples I have added in a couple of features.

  • Login Dialog box
  • The ability to choose between basic Auth and No Auth
  • Saving the username and Jira URL for future logins
  • Saving the credentials during the same session so that successive logins are not required

Credentials are stored in a global variable called usernamep if that value is not blank "" then when a call is made to login that value is returend with the previously provided credentials.  Otherwise a dialog box is shown.  If the No Auth check box is marked then usernamep is set to "NoAuth". 

The function GetIssues is provided to show how the login information is used.  It's pretty basic when you put it together.  As you can see if it's NoAuth the header is set to "Authorization","No Auth" otherwise you set the header to basic and use the encoded username password.

Using the usernamep for two purposes is bad programming practice and ideally the authorization value should be a seperate variable all together.  A future version of the code will likely support this so that additional authorization methods can be supported.

             If usernamep = "NoAuth" Then
                .SetRequestHeader "Authorization", "No Auth"
             Else
                .SetRequestHeader "Authorization", "Basic " & usernamep
             End If

Public usernamep As String
Dim url As String
Dim noauth As Boolean

Public Function UserPassBase64() As String
    Dim objXML       As MSXML2.DOMDocument60
    Dim objNode      As MSXML2.IXMLDOMElement
    Dim arrData()    As Byte

    If usernamep = "" Then
        fLogin.Show
        If (fLogin.Cancel = False) Then
            usernamep = fLogin.txtUsername.Value + ":" + fLogin.txtPassword.Value
            url = fLogin.txtServer.Value
            noauth = fLogin.cbNoAuth.Value
            If noauth = True Then
                UserPassBase64 = "NoAuth"
                usernamep = "NoAuth"
            Else
                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 If
        Else
            UserPassBase64 = "cancel"
        End If
    Else
        UserPassBase64 = usernamep
    End If

End Function
 
 
 
 

Rest Call to Get Issues

The only real unique aspect about this method is the s = GetUrl line.  We could further abstract this method so that the actual Rest API is passed in so that queries that return multiple issues or single issues could be made in the same method and all the surrounding logic reused. 
 
Note: it's amazing how much refactoring and improvemens you find in your own code when you start to write about it.
 
Public Function GetIssues(query As String) As String

    Dim JiraService As New MSXML2.XMLHTTP60
    Dim json As Object
    Dim s As String

    usernamep = UserPassBase64
 

    If usernamep = "cancel" Then
        fStatus.AppendStatus ("User canceled login.")
    Else
        fStatus.AppendStatus ("Getting Jira Issues you might see Not Responding in title bar. The time this takes is dependant on the complexity of your query, network, and number of issues being returned.")

        With JiraService
             s = GetUrl + "/rest/api/2/search?jql=" + query + "&startAt=" + _
                        CStr(Range("StartAt").Value) + "&maxResults=" + _
                        CStr(Range("MaxResults").Value)
      .Open "GET", s         
             .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
                 fStatus.AppendStatus ("Something wrong with query in GetIssues, check your network connection, :  " + .ResponseText)
                 GetIssues = ""
             Else
                 GetIssues = JiraService.ResponseText
             End If
        End With
    End If
End Function
 
 
 
 

Conver Json to Object and Insert into Worksheet

The more interesting method here is once the Json is returned from Jira the code loops through the rows in the returned Json and the columns in the worksheet.  Using column headers to pull out what data is to be displayed.

 

The basic flow of this method is:

  • delete existing data in the table
  • Convert Json String to Object : set JsonObject = JsonConverter.ParseJson(JsonIssues)
  • Loop Rows, loop through the number of issues returned in the Json
  • Loop Columns and insert the data into the worksheet

 

Sub ProcessIssues(JsonIssues As String)
    Dim t As ListObject
    Dim z As Double
    Dim JsonObject As Object
    Dim r As Range
    Dim fpath As Range
    Dim s As String

    Set t = ActiveWorkbook.Sheets("Tickets").ListObjects("Table2")

    ' ignoring error if table is already empty
    On Error Resume Next
    t.DataBodyRange.Delete
    On Error GoTo 0
  
  

    Set r = Range("StartRow") ' row to start putting data in
    Set fpath = Range("StartFieldKey") ' Column headings to loop through

    'Convert Json to an object array that makes it easy to access the Json Hierarchy
    fStatus.AppendStatus ("Parsing Returned Json")
    Set JsonObject = JsonConverter.ParseJson(JsonIssues)

    Range("TotalReturned").Value = JsonObject("total")
    fStatus.AppendStatus ("Retrieved " & CStr(JsonObject("total")) & " issue(s) matching query from Jira. Max issues to be retrieved is set at " & CStr(Range("MaxResults").Value) & vbCrLf & " Update maxResults on Trickets sheet to retrieve more.")
    Set issues = JsonObject("issues")
    On Error GoTo gerr

    For z = 1 To issues.count ' row loop: through and process each issue
        fStatus.AppendStatusBar ("Inserting jira issue " & z & " of " & issues.count & " into worksheet.")
        fStatus.progress ((z * 100 / (issues.count)) / 2)
        While (fpath.Value <> "") ' column loop: for each column in excel get the value from the Json Object and put in the cell

            ' Select statement first handles fields that have to have special handling these are fields that have multiple values usually
            ' the case else : calls the default field handling where there is a simple translaction from the Json
            On Error Resume Next
            r.Offset(0, fpath.column - 1).Value = getValue(issues(z), fpath.Value)
            On Error GoTo gerr

            Set fpath = fpath.Offset(0, 1)

        Wend
        Set fpath = Range("StartFieldKey")
        Set r = r.Offset(1, 0)
    Next z
    GoTo finish
gerr:
    fStatus.AppendStatus ("Oops something went wrong: " & vbCrLf & err.description & vbCrLf & " source: " & err.Source & " at row: " & r.row & " column: " & fpath.Value)

finish:

End Sub

 

getValue

The getValue function is dynamically created from the table on the macroValues worksheet.  From columns D through G.  This allows for the addition of being able to extract data from the Json based on custom field values for each Jira instance that this code may be used against, in that custom field values are unique to a Jira instance.

 

The CreateCaseSub function creates a new function and dynamically adds it to the excel vba project utilizing the VBProject object references.

The function to create the function has the basic form of creating one long string then calling the appropriate method to insert the string as code.

  • Create the function header code
  • loop through the table and create the select case statement
  • Create the function footer code
  • Insert into the VBA Project
Function CreateCaseSub() As VBComponent
    Dim code As String
    Dim r As Range
   
    Set r = Range("getValueStart")
    code = "Public Function getValue(issue as object, key as String) as String" & vbNewLine & _
                vbTab & "dim rvalue as String" & vbNewLine & _
                vbTab & "on error goto gerr" & vbNewLine & _
                vbTab & "Select Case key" & vbNewLine
   
    While (r.Value <> "")
        code = code & vbTab & vbTab & "Case " & """" & r.Offset(0, 0).Value & """" & vbNewLine & _
        vbTab & vbTab & vbTab & "if IsNull(issue" & r.Offset(0, 2).Value & ") then" & vbNewLine & _
        vbTab & vbTab & vbTab & vbTab & "rvalue = """"" & vbNewLine & _
        vbTab & vbTab & vbTab & "else" & vbNewLine


        If r.Offset(0, 3).Value = "" Then
            code = code & vbTab & vbTab & vbTab & vbTab & "rvalue = issue" & r.Offset(0, 1).Value & vbNewLine
        Else
            code = code & vbTab & vbTab & vbTab & vbTab & "rvalue = " & r.Offset(0, 3).Value & "(issue" & r.Offset(0, 1).Value & ")" & vbNewLine
           
        End If
        code = code & vbTab & vbTab & vbTab & "End if" & vbNewLine
        Set r = r.Offset(1, 0)
    Wend
    code = code & vbTab & "End Select" & vbNewLine & _
            vbTab & "getValue = rvalue" & vbNewLine & _
            vbTab & "goto finish:" & vbNewLine & _
            "gerr:" & vbNewLine & _
            vbTab & "AppendStatus (""error getValue issue key: "" & key & "" "" & err.description )" & vbNewLine & _
            "finish:" & vbNewLine & _
            "End Function"
   
   
   
    Dim tempModule As VBComponent
    Set tempModule = ThisWorkbook.VBProject.VBComponents.add(VBIDE.vbext_ComponentType.vbext_ct_StdModule)
    Call tempModule.codeModule.DeleteLines(1, tempModule.codeModule.CountOfLines)
    Call tempModule.codeModule.AddFromString(code)
    tempModule.name = "Module1"
    Set CreateCaseSub = tempModule
End Function

The following code is an example of getValue.  Where for each key in the table the object is checked for a null reference and returns either "" for null or the actualy object data.

At first I used the iif function but it like many trinary operators actually parses the false expression and throws an error if the object can not be referenced thus the more verbose if then else clause had to be used.

Public Function getValue(issue As Object, key As String) As String
    Dim rvalue As String
    On Error GoTo gerr
    Select Case key
        Case "key"
            If IsNull(issue("key")) Then
                rvalue = ""
            Else
                rvalue = issue("key")
            End If
        Case "status"
            If IsNull(issue("fields")("status")("name")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("status")("name")
            End If
        Case "project"
            If IsNull(issue("fields")("project")("name")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("project")("name")
            End If
        Case "summary"
            If IsNull(issue("fields")("summary")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("summary")
            End If
        Case "created"
            If IsNull(issue("fields")("created")) Then
                rvalue = ""
            Else
                rvalue = fromISODateTimeNoZ(issue("fields")("created"))
            End If
        Case "updated"
            If IsNull(issue("fields")("updated")) Then
                rvalue = ""
            Else
                rvalue = fromISODateTimeNoZ(issue("fields")("updated"))
            End If
        Case "assignee"
            If IsNull(issue("fields")("assignee")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("assignee")("name")
            End If
        Case "reporter"
            If IsNull(issue("fields")("reporter")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("reporter")("name")
            End If
        Case "fixversions"
            If IsNull(issue("fields")("fixversions")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsWithCount(issue("fields")("fixversions"))
            End If
        Case "issuetype"
            If IsNull(issue("fields")("issuetype")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("issuetype")("name")
            End If
        Case "labels"
            If IsNull(issue("fields")("labels")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsWithCount(issue("fields")("labels"))
            End If
        Case "prioity"
            If IsNull(issue("fields")("priority")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("priority")("name")
            End If
        Case "severity"
            If IsNull(issue("fields")("customfield_12520")("value")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_12520")("value")
            End If
        Case "resolution"
            If IsNull(issue("fields")("resolution")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("resolution")("name")
            End If
        Case "issuelinks"
            If IsNull(issue("fields")("issuelinks")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsIssueLink(issue("fields")("issuelinks"))
            End If
        Case "businessDriver"
            If IsNull(issue("fields")("customfield_15623")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15623")
            End If
        Case "businessSignificance"
            If IsNull(issue("fields")("customfield_12420")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_12420")
            End If
        Case "description"
            If IsNull(issue("fields")("description")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("description")
            End If
        Case "detailedDescription"
            If IsNull(issue("fields")("customfield_15621")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15621")
            End If
        Case "billable"
            If IsNull(issue("fields")("customfield_15926")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15926")(1)("value")
            End If
        Case "LOE"
            If IsNull(issue("fields")("customfield_15629")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15629")
            End If
        Case "approvalRequired"
            If IsNull(issue("fields")("customfield_16320")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_16320")("value")
            End If
        Case "approvedBy"
            If IsNull(issue("fields")("customfield_11622")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_11622")("displayName")
            End If
    End Select
    getValue = rvalue
    GoTo finish:
gerr:
    AppendStatus ("error getValue issue key: " & key & " " & err.description)
finish:
End FunctionPublic Function 

 

Inserting Data into Word

The code to insert into word is similar.  It also uses getValue to translate the key value stored in the word custom form field tag property into the data used to insert into the word template.  The basic flow for creating a word templates is:

  • select word template and provide Jira Ticket Key
  • open word template
  • retrieve Jira data for that one ticket
  • loop through the custom form fields
  • match tag to key value and insert data

The following code does the parsing.  After having prompted for the template and key, retrieving the Json and converting it to an object this code loops through the word form fields. 

 

Sub FilloutWordTemplateWithJiraData(ByRef o As Object, template As String, s As fEnhancement)


    Dim w As Word.Application
    Dim wd As Word.Document
    Dim jiraKey As String   
    Dim oCC As ContentControl
    Dim i As Integer
    Dim lc As Boolean
   
     
    s.status.Caption = "Opening Microsoft Word"
    Set w = CreateObject("Word.Application")
    w.Visible = True
       
    s.status.Caption = "Opening template: " & template
    Set wd = w.Documents.Open(template)
    'wd.SaveAs MyDocuments() & "\" & jiraKey


    i = 1
    For Each oCC In wd.ContentControls
        strText = ""
        s.status.Caption = "Update form field " & i & " of " & wd.ContentControls.count
        i = i + 1
        Debug.Print oCC.tag
       
        strText = getValue(o, oCC.tag)
        lc = oCC.LockContents
     
        oCC.LockContents = False
        Select Case oCC.Type
            Case wdContentControlCheckBox
               
                If strText = "Yes" Then
                   oCC.Checked = True
                End If
               
            'Case wdContentControlDate ' todo: determine if special date handling needs to be added
            Case default
               
                If strText <> "" Then
                    oCC.Range.InsertAfter strText
                    oCC.SetPlaceholderText , , strText ' if placeholder text exist this removes it so that a clean document without unwanted texted is provided.
                End If
           
        End Select
        oCC.LockContents = lc
    Next oCC
    s.status.Caption = "Finished updating " & i & " fields in template."
  Exit Sub
End Sub

Parsing the Jira Json Learnings

Notice the billable field and how it has (1) before the ("value")
 
Row from the macroValues worksheet
billable ("fields")("customfield_15926")(1)("value") ("fields")("customfield_15926")
 
The following is a snippet of the Json that is returned from Jira notice how there is a bracket [ after the field name.  This indicate an array in Json.  Here I have decided to just grab the first array value.  One could write a special method to extract all values from this field similar to GetFieldsWithCount. However, in this instance I know that there will only ever be one value.  It's likely how the custom field was created with in Jira that causes this and likely a mistake in this case.  But it serves as a good reference and tip on how you can extract the Json.
...

   "customfield_13624": null,
    "customfield_15926": [
      {
        "self": "https://jira.mycompany.com/rest/api/2/customFieldOption/16883",
        "value": "Yes",
        "id": "16883"
      }
    ],
    "cus....
     

Creating the Excel Menus

It's pretty cool that you can create custom menus for each excel file.  Note this is not an add in of a permante nature.  The menu only appears when you have the associated excel file open and it has the focus.

To use the Custom UI Editor for Microsoft Office you simple open the excel file in the tool, note: the excel file cannot be open in excel, create the XML and save the file.  If there are errors in your XML the menu simple will not appear no error messages are displayed.  For this reason adding elements on at a time is recommended. 

Instead of trying to create custom icon's for my custom menu I picked from the stock set of office icon's.  I utilized the reference at the following link to browse through the available icons. 

http://soltechs.net/CustomUI/imageMso01.asp

One of the more annoying learnings I discovered is that the UI editor appears to load the excel macros in addition to the menus into it's memory.  So if you open the UI editor open an excel file and udpate the xml then save it, but keep the excel file open in the UI editor, any chances you make to your macros in excel are overwritten if you come back to the UI editor and make changes and save again.  So the learing open the excel file in the UI editor make your changes then close the file then open in excel.

 

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon>
  <tabs>
   <tab id="customTab" label="JIRA" insertAfterMso="TabView">
    <group idMso="GroupClipboard" />
    <group idMso="GroupFont" />
    <group id="customGroup" label="Jira Tools">
     <button id="customButton5" label="Login" size="large" onAction="Login" imageMso="TableIndexes" /> 
     <button id="customButton4" label="Get Issues" size="large" onAction="GetTickets" imageMso="CacheListData" />    
     <button id="customButton1" label="Get Linked Status" size="large" onAction="UpdateStatus" imageMso="AccessRelinkLists" />
     <button id="customButton3" label="Create Tickets" size="large" onAction="CreateTickets" imageMso="QueryShowTable" />
     <button id="customButton2" label="Update Dashboard" size="large" onAction="UpdateDashboard" imageMso="CreateForm"/>    
     <button id="customButton7" label="Create Enhancement" size="large" onAction="CreateEForm" imageMso="ColumnsDialog"/>
    </group>
   
   </tab>
    <group idMso="GroupEnterDataAlignment" />
    <group idMso="GroupEnterDataNumber" />
    <group idMso="GroupQuickFormatting" />
  </tabs>
 </ribbon>
</customUI>

 

 

Random Thoughts

At first I utilized the excel status bar in the lower left hand corner and updated the excel cursor to a xlWait but realized that the status bar was kind of out of the way and not as noticeable.  So I popuped up a dialog box with a status label to provide feed back to the user as to the progress.  This seemed to be more user friendly and user informative.  I also decided not to change the cursor in that the dialog blocked the user from doing anything until the macro was finished and provide the visual feedback needed.   I have found that when you try and update the cursor manually there are almost always unforseen scenarios that leave the cursor in an inaccurate state.

It took quite a bit of playing around with vbCrLf and commas before and after strings to line up the Linked Status and Linked Fixed Versions to align with in the cell to the associated linked tickets.  There is likely a better way to handle this but for now the brute force method of string manipulation worked.

 

History

4/13/2016: Editted article to give additional credit to http://word.mvps.org/faqs/macrosvba/MixedDocProps.htm

Updated attached excel workbook to include fix in GetIssues .send "" to only be .send as reported by Dirk_B

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

bradyguy
hCentive
United States United States
https://www.linkedin.com/in/bradychambers

You may also be interested in...

Comments and Discussions

 
QuestionThnaks, i have fixed the FixVersions Pin
Armin Klose12-Apr-19 1:43
memberArmin Klose12-Apr-19 1:43 
QuestionCompatibility with win10 x64 Pin
Member 1422418410-Apr-19 10:38
memberMember 1422418410-Apr-19 10:38 
SuggestionThanks Brad, great work. But, I had to make change for Login to work. Pin
WebSign10013-Feb-19 10:57
memberWebSign10013-Feb-19 10:57 
BugAdding new server name entries Pin
TheOtherKiwi14-Aug-18 23:48
memberTheOtherKiwi14-Aug-18 23:48 
PraiseThanks Brad Pin
Member 1393372031-Jul-18 23:52
memberMember 1393372031-Jul-18 23:52 
QuestionfixVersions not appearing Pin
tiggerz528-Jul-18 21:11
membertiggerz528-Jul-18 21:11 
QuestionCreate Tickets Pin
Member 139042309-Jul-18 5:31
memberMember 139042309-Jul-18 5:31 
AnswerRe: Create Tickets Pin
TheOtherKiwi24-Aug-18 22:59
memberTheOtherKiwi24-Aug-18 22:59 
QuestionAre you able to edit labels and fields with this? Pin
Member 1389285229-Jun-18 13:30
memberMember 1389285229-Jun-18 13:30 
QuestionGet Issues Pin
Member 1384705327-May-18 19:12
memberMember 1384705327-May-18 19:12 
QuestionPulling only 1K records Pin
Member 1378852219-Apr-18 10:16
memberMember 1378852219-Apr-18 10:16 
AnswerRe: Pulling only 1K records Pin
Puluke20-Apr-18 5:07
memberPuluke20-Apr-18 5:07 
QuestionNot able to pull component field value, please help Pin
Member 1376448810-Apr-18 6:32
memberMember 1376448810-Apr-18 6:32 
QuestionHelp: Adding Story Points column Pin
ninh pham14-Mar-18 23:25
memberninh pham14-Mar-18 23:25 
AnswerRe: Help: Adding Story Points column Pin
ninh pham19-Mar-18 20:45
memberninh pham19-Mar-18 20:45 
AnswerRe: Help: Adding Story Points column Pin
Puluke20-Apr-18 7:52
memberPuluke20-Apr-18 7:52 
Questionnot able to login - need help Pin
Member 1343850530-Sep-17 7:02
memberMember 1343850530-Sep-17 7:02 
AnswerRe: not able to login - need help Pin
Member 120736998-Oct-17 21:11
memberMember 120736998-Oct-17 21:11 
GeneralRe: not able to login - need help Pin
tofferlicious19-Oct-17 2:37
membertofferlicious19-Oct-17 2:37 
QuestionRetrieving comments for an issue Pin
Member 132897656-Jul-17 5:27
memberMember 132897656-Jul-17 5:27 
QuestionSeverity Type Mismatch. Not extracting value Pin
Member 131103307-Jun-17 21:02
memberMember 131103307-Jun-17 21:02 
QuestionCreate Ticket function not working - are we missing anything Pin
Raja Shekher24-Apr-17 4:41
memberRaja Shekher24-Apr-17 4:41 
Questionoops problem in GetTickets: Pin
FatFurryGuy29-Mar-17 11:31
memberFatFurryGuy29-Mar-17 11:31 
AnswerRe: oops problem in GetTickets: Pin
bsurana28-Jun-17 18:11
memberbsurana28-Jun-17 18:11 
AnswerRe: oops problem in GetTickets: Pin
Puluke19-Apr-18 13:10
memberPuluke19-Apr-18 13:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190419.4 | Last Updated 12 Apr 2016
Article Copyright 2016 by bradyguy
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid