Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

VBA Macro to connect to JIRA REST API

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



1 answer

0 votes
Warren Community Leader Dec 22, 2020

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.

  1. 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?
  2. 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.
  3. 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 Community Leader Dec 22, 2020

@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

@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.

@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:-

JaiBhairavi.JPG


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

Warren Community Leader Jan 03, 2021

Hi @Arumugam 

Happy new year!

A few questions :

  1. Can you confirm that you're using the Jira email address and API token?
  2. Have you tried with a different API call e.g. /rest/api/2/myself 
  3. I have it successfully working using XMLHTTP60 - is there a reason you're using WinHTTPRequest? Try using my code as is, because I have it currently working but changing all refs of "30" to "60"

I was able to make it work for 1 request using:

https://myjira.site/si/jira.issueviews:issue-xml/BI-3047/BI-3047.xml

But when I want to retrieve from a filter:

https://myjira.site/sr/jira.issueviews:searchrequest-xml/?filter=10083/SearchRequest-10083.xml

I can't get it going. Any ideas? Maybe I am entering the wrong syntax?

Warren Community Leader Jun 27, 2021

Hi @Gustavo Miller 

I'm not familiar with the format that you're showing for the API calls, but it looks as though you possibly shouldn't have the / before the question mark. So try 

https://myjira.site/sr/jira.issueviews:searchrequest-xml?filter=10083/SearchRequest-10083.xml
Like Gustavo Miller likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you