Jira Excel VBA Basic functions

Jira.bas

    
' 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

See details https://tdalon.blogspot.com/2023/03/jira-excel-vba.html