How to create a Jira Ticket by using VBA?

[Li Chai]

I am trying to create a ticket by using VBA, would like to get some tips from experts here. Thanks

[Li Chai]

Pavel:

Thank you for your resposne. Don't worry. I have figured out and fixed the bug in the code.

Best regards,

Li Chai

[Pavel Junek]

Hi Li,

You can check this thread - VBA code to get issue from JIRA using REST API 

Pavel

[Li Chai]

Pavel Junek:

Thank you for your response. I did read this thread and tried to follow the code. However, it is for GET mothod while I am lookiing for a POST method to create a new issue on Jira. I worte a sub and got Response status: 403 error. It seems there is a permission iissue. 

Below is the code. Please take a look and provide me with your advce.

Thanks

Li Chai

%%%%%%%%%%%

Option Explicit

Sub CreateJiraTicket()
' Define the Jira REST API URL and authentication details
Const JiraUrl As String = "https://engsupport.juniper.net/rest/api/2/issue"
Const JiraUser As String = "glouser"
Const JiraPass As String = "glouser"

' Print the values of JiraUser and JiraPass for debugging
Debug.Print "JiraUser: " & JiraUser
Debug.Print "JiraPass: " & JiraPass

' Define the Jira ticket data
Dim IssueType As String
Dim Summary As String
Dim Description As String
Dim Campus As String
Dim Lab As String
Dim Watcher As String

' Set the Jira ticket data
IssueType = "GLO Lab Support"
Summary = "This is a test ticket please ignore."
Description = "This is a test ticket please ignore."
Campus = "Sunnyvale Building B"
Lab = "San Francisco Lab (B.5.210)"
Watcher = "glouser"

' Create an XMLHTTP object
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")

' Prepare the Jira REST API request
Debug.Print "Preparing request..."
http.Open "POST", JiraUrl, False
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", "Basic " & Base64Encode(JiraUser & ":" & JiraPass)

' Construct the Jira ticket JSON payload
Dim payload As String
payload = "{""fields"": {""project"": {""key"": ""GLO""}, ""summary"": """ & Summary & """, ""watcher"": """ & Watcher & """, ""description"": """ & Description & """, ""issuetype"": {""name"": """ & IssueType & """}, ""customfield_10371"": {""value"": """ & Campus & """, ""child"": {""value"": """ & Lab & """}}}}"
''' payload = "{""fields"": {""project"": {""key"": ""Project""}}}"

 ' Send the Jira REST API request
http.Send payload
Debug.Print "Sending request..."
Debug.Print ("This is the payload: " & payload)
Debug.Print ("This is the URL: " & JiraUrl)

' Check the response status
Debug.Print "Response status: " & http.Status
If http.Status = 201 Then
' Get the ticket key from the response
Dim JSON As Object
Set JSON = JsonConverter.ParseJson(http.responseText)
Dim ticketKey As String
ticketKey = JSON("key")

' Display the Jira ticket key
MsgBox "Jira ticket " & ticketKey & " created."
''' Else
''' ' Display an error message
''' MsgBox "Failed to create Jira ticket. Response status: " & http.Status
End If
End Sub

Function Base64Encode(ByVal sText As String) As String
Dim arrData() As Byte
arrData = StrConv(sText, vbFromUnicode)

Dim objXML As Object
Set objXML = CreateObject("MSXML2.DOMDocument")

Dim objNode As Object
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
Base64Encode = objNode.Text

Set objNode = Nothing
Set objXML = Nothing
End Function

[Pavel Junek]

Hi Li,

Unfortunately, I don't know how to program in VBA, so I can't advise you.

Pavel