Jira Excel VBA Module with Basic functions

I share in this post some examples how to use Jira REST API from Excel VBA. This post is a WIP/Draft. If you are interested in me explaining more, leave a comment.

1. Resources
2. Jira Integration
   1. Login/ WebService
   2. Run WebRequest
   3. Parse Json Response
3. Code
4. See also

Resources

The solution here makes use of 2 VBA Libraries provided by @timhall (many thanks!):

• VBA-Web
• VBA-JSON

Jira Integration

Login/ WebService

This implementation example is for the Jira server version with a Basic authentication.

Run WebRequest

Parse Json Response

Code

Code is available in this Gist.

	' Password can be stored in %userprofile%/JiraPassword.txt (see GetPassword function) to avoid being prompted each time
	Private pJiraClient As WebClient
	Private pJiraPassword As String
	Private pJiraRootUrl As String
	Private pJiraUserName As String
	Private Property Get JiraPassword() As String
	If pJiraPassword = "" Then
	' Try get from file
	pJiraPassword = GetPassword()
	If pJiraPassword = "" Then
	pJiraPassword = InputBox("Please Enter Jira Password or API Token")
	End If
	End If
	JiraPassword = pJiraPassword
	End Property
	Private Property Get JiraUserName() As String
	If pJiraUserName = "" Then
	' Try get value from named cell
	pJiraUserName = ThisWorkbook.Names("JiraUserName").RefersToRange(1, 1).Value
	If pJiraUserName = "" Then
	pJiraUserName = InputBox("Please enter your Jira Username")
	End If
	End If
	JiraUserName = pJiraUserName
	End Property
	Private Property Get JiraRootUrl() As String
	If pJiraRootUrl = "" Then
	' Try get value from named cell
	pJiraRootUrl = ThisWorkbook.Names("JiraRootUrl").RefersToRange(1, 1).Value
	If pJiraRootUrl = "" Then
	pJiraRootUrl = InputBox("Please enter your Jira Root Url")
	End If
	' Root Url must not end with /
	If (Right(pJiraRootUrl, 1) = "/") Then
	pJiraRootUrl = Left(pJiraRootUrl, Len(pJiraRootUrl) - 1)
	End If
	End If
	JiraRootUrl = pJiraRootUrl
	End Property
	Private Property Get JiraClient() As WebClient
	If pJiraClient Is Nothing Then
	Set pJiraClient = New WebClient
	pJiraClient.BaseUrl = JiraRootUrl
	Dim Auth As New HttpBasicAuthenticator
	Auth.Setup _
	UserName:=JiraUserName, _
	Password:=JiraPassword
	Set pJiraClient.Authenticator = Auth
	End If
	Set JiraClient = pJiraClient
	End Property
	Private Function GetPassword() As String
	' Get password from file stored in user profile
	File = Environ("userprofile") & "\JiraPassword.txt"
	'Read output tmp File
	Set fso = CreateObject("Scripting.FileSystemObject")
	If Not (fso.FileExists(File)) Then
	Exit Function
	End If
	Set ts = fso.OpenTextFile(File, 1)
	Password = ts.ReadLine
	ts.Close
	Password = Trim(Password)
	GetPassword = Password
	End Function
	Function Jira_GetIssueJson(IssueKey As String) As Object
	Dim Response As WebResponse
	Set Response = JiraClient.GetJson("rest/api/2/issue/" & IssueKey)
	Dim Json As Object
	Dim FoundKeys() As String ' Dynamic array. Allocate with ReDim Preserve
	Set Json = JsonConverter.ParseJson(Response.Content)
	Set Jira_GetIssueJson = Json
	End Function
	Public Function Jira_GetAssigneeEmail(IssueKey As String, Optional defUnassignedEmail As String = "") As String
	Dim Json As Object
	Dim AssigneeJson As Object
	Set Json = Jira_GetIssueJson(IssueKey)
	Jira_GetAssigneeEmail = defUnassignedEmail
	On Error GoTo ExitFun
	Set AssigneeJson = Json("fields")("assignee")
	On Error GoTo 0
	Jira_GetAssigneeEmail = AssigneeJson("emailAddress")
	ExitFun:
	End Function
	Public Function Jira_GetLinkedIssues(IssueKey As String, LinkType As String, Optional ByVal inProjectKey As String = "") As String()
	' LinkType is case-sensitive
	Dim Json As Object
	Set Json = Jira_GetIssueJson(IssueKey)
	Dim FoundKeys() As String ' Dynamic array. Allocate with ReDim Preserve
	cntFound = 0
	Dim FoundKey As String
	Dim linkdir As String
	If (inProjectKey <> "") Then
	Dim re As Object
	Set re = New RegExp
	re.Pattern = "^" & inProjectKey & "\-"
	End If
	For cnt = 1 To Json("fields")("issuelinks").Count
	linkdir = ""
	'Debug.Print Json("fields")("issuelinks")(cnt)("type")("name")
	If (Json("fields")("issuelinks")(cnt)("type")("inward") = LinkType) Then
	linkdir = "inward"
	ElseIf (Json("fields")("issuelinks")(cnt)("type")("outward") = LinkType) Then
	linkdir = "outward"
	End If
	If (linkdir = "") Then GoTo ContinueLoop
	FoundKey = Json("fields")("issuelinks")(cnt)(linkdir & "Issue")("key")
	If (inProjectKey <> "") Then
	If Not (re.Test(FoundKey)) Then GoTo ContinueLoop
	End If
	cntFound = cntFound + 1
	ReDim Preserve FoundKeys(1 To cntFound)
	FoundKeys(cntFound) = FoundKey
	Debug.Print IssueKey & "->Found linked issue: " & FoundKey & " (linktype:" & LinkType & ") [" & linkdir & "]"
	ContinueLoop:
	Next
	Jira_GetLinkedIssues = FoundKeys
	End Function
	Sub Jira_Login()
	WebHelpers.EnableLogging = True
	Dim Request As New WebRequest
	Dim Response As WebResponse
	'Request.Resource = "rest/auth/1/session"
	'Request.Method = WebMethod.HttpPost
	Request.Resource = "rest/api/2/serverInfo"
	Request.Method = WebMethod.HttpGet
	Set Response = JiraClient.Execute(Request)
	If Response.StatusCode <> Ok Then
	MsgBox (Response.StatusCode & vbCrLf & Response.StatusDescription)
	Else
	MsgBox ("Login Ok!")
	End If
	End Sub

view raw Jira.bas hosted with ❤ by GitHub

See also