nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

VBA Macro to connect to JIRA REST API

Arumugam_8i8s9d

Hi All,

I use the below code to connect to my JIRA project.

However the response I get is not the complete one.

When I use postman I get the complete list but when connected using VBA,
I get only empty response in projects.

HAs anyone successfully worked in connecting to JIRA API using excel macro.

Set objHTTP = CreateObject("MSXML2.XMLHTTP60")
Url = "https://t/rest/api/2/issue/createmeta/"
objHTTP.Open "GET", Url, False
'objHTTP.setRequestHeader "Content-Type", "application/json,Charset=ISO-8859-1"
'objHTTP.setRequestHeader "Content-Type", "application/json;charset=UTF-8"
objHTTP.setRequestHeader "Content-Type", "application/json"
'objHTTP.setRequestHeader "Accept-Encoding", "gzip, deflate,br"
'objHTTP.setRequestHeader "Accept-Encoding", "identity, deflate"
'objHTTP.setRequestHeader "Accept", "application/json"
objHTTP.setProxy 2, "proxy..com:80", ""
'MsgBox strPaylodValue
objHTTP.send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
strResponseStatus = objHTTP.Status
strResponseText = objHTTP.ResponseText

Accepted answers

All comments

Warren_mz9b4z

Hi @Arumugam

Yes I've definitely managed to successfully run all different API calls through VBA, although not recently as I've moved to using C# applications.

I'm going to throw a bunch of questions / suggestions your way, try things, reply and we'll see if we can get you up and running.

JIRA_PWD isn't actually your Jira password, it's an API token? Correct? The password isn't used anymore. JIRA_USER is your email address, not username?
The combination of email / API token needs to be 64 bit encoded - Postman does it automatically, but it doesn't look like you're doing it in your code.
Has this code successfully been used with any other API call? If not, try a different call and see what gets returned. Something like /rest/api/2/myself which just returns your Jira details - does that work?

I've managed to find my VBA code from 2017, so ignore this and give me half an hour to put the code coherently together for you

Warren_mz9b4z

@Arumugam

Here's my old VBA code - I was using HTTP3, but you can just change each one to HTTP6.

I think what you're missing / doing wrong is

Base 64 encoding
.SetRequestHeader "Authorization", "Basic " & UserNameP

Let me know how this goes. I haven't tried running this code since it was used in 2017, but it used to work.

Public UserNameP As String
Public JiraService As MSXML2.XMLHTTP30


' ***********************************************************
' *** Returns the issues as per the JQL query ***
' ***********************************************************
Public Function GetIssues(query As String) As String

Dim json As Object

If JiraService Is Nothing Then Set JiraService = New MSXML2.XMLHTTP30
UserNameP = UserPassBase64

With JiraService
.Open "GET", query
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.SetRequestHeader "Authorization", "Basic " & UserNameP
.Send
If .Status = "401" Then
GetIssues = ""
Else
GetIssues = JiraService.ResponseText
End If
End With

End Function


' ***********************************************************
' *** Encodes the Jira Username and Password ***
' ***********************************************************
Public Function UserPassBase64() As String

Dim objXML As MSXML2.DOMDocument30
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim URL As String

UserNameP = "JiraEmail:APIToken"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument30
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.Text

End Function

Arumugam_8i8s9d

@Warren : Thank you so much for your response. I feel motivated. I will try as per your suggestions and get back if it works for me.

Arumugam_8i8s9d

@Warren : No Luck. I tried as you had mentioned , did base 64 conversion of user id and password which I am sending to JIRA but still not proper result.

I am getting 200 success in response.status . However getting the below error in line
objHTTP.responseText

Error Received:-

Code Used:-

Function Test()
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
UserNameP = UserPassBase64

URL = "https://jira/rest/api/2/issue/createmeta/"
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "Content-Type", "application/json"
objHTTP.setRequestHeader "Accept-Encoding", "gzip, deflate,br"
objHTTP.setRequestHeader "Accept-Encoding", "identity, deflate"
objHTTP.setRequestHeader "Authorization", "Basic " & UserNameP
objHTTP.setProxy 2, "proxy:80", ""
objHTTP.send
strResponseStatus = objHTTP.Status
strResponseText = objHTTP.responseText
strResponseText = VBA.Strings.StrConv(objHTTP.responseText, vbUnicode)
strResponseText = CStr(strResponseText)
End Function

' ***********************************************************
' *** Encodes the Jira Username and Password ***
' ***********************************************************
Public Function UserPassBase64() As String

Dim objXML As MSXML2.DOMDocument30
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim URL As String

UserNameP = "30047:pwd**"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument30
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.Text

End Function

Vallabh Shirodkar

Hello,

I tried with command prompt to create JIRA issue. and it was successful. Below is command. I placed this command in batch file and invoked batch file. and able to create JIRA issues.

curl -D- -u username:password -X POST --data "{\"fields\":{\"project\":{\"key\": \"prjectKey\"},\"summary\": \"sampleSummary\",\"issuetype\": {\"id\": \"11503\"},}" -H "Content-Type: application/json" https://<server>/jira/rest/api/2/issue/

Same way you can get done other operations also.

I hope it helps :-)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

api

jira-server

atl-gruland