VBA code to get issue from JIRA using REST API

Franck Voufo

Hi All,

 

I have a problem trying to get issues from JIRA to Excel (VBA) using a REST API.

Can you please help me? This is my code and the error I get:

 

Option Explicit

Public UserNameP As String
Public JiraService As MSXML2.XMLHTTP60


Sub JiraRestGetCall()
    Call GetIssues("project = ""my new project""")
End Sub

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

Dim json As Object
Dim s As String

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

With JiraService
     s = "https://atc.mycompanyname.net/jira" + "/rest/api/2/issues/?jql="+query

     .Open "GET", s
     .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:password"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.Text

End Function

 

Response:

 

<!doctype html><html lang="en"><head><title>HTTP Status 400 – Bad Request</title><style type="text/css">body {font-family:Tahoma,Arial,sans-serif;} h1, h2, h3, b {color:white;background-color:#525D76;} h1 {font-size:22px;} h2 {font-size:16px;} h3 {font-size:14px;} p {font-size:12px;} a {color:black;} .line {height:1px;background-color:#525D76;border:none;}</style></head><body><h1>HTTP Status 400 – Bad Request</h1><hr class="line" /><p><b>Type</b> Exception Report</p><p><b>Message</b> Invalid character found in the request target [&#47;jira&#47;rest&#47;api&#47;2&#47;issues&#47;?jql=project%20=%20&quot;Ticketsteuerung%20Werkvertr0xe4ge%20Typzulassung&quot;%20AND%20Component%20!=%20Template%20AND%20(issuetype%20=%20Epic%20OR%20issuetype%20=%20Story)%20AND%20(resolved%20&gt;%20-8w%20OR%20status%20=%20&quot;In%20Review&quot;)%20ORDER%20BY%20resolutiondate%20ASC]. The valid characters are defined in RFC 7230 and RFC 3986</p><p><b>Description</b> The server cannot or will not process the request due to something t

hat is perceived to be a client error (e.g., malformed request syntax, invalid request message framing, or deceptive request routing).</p><p><b>Exception</b></p><pre>java.lang.IllegalArgumentException: Invalid character found in the request target [&#47;jira&#47;rest&#47;api&#47;2&#47;issues&#47;?jql=project%20=%20&quot;Ticketsteuerung%20Werkvertr0xe4ge%20Typzulassung&quot;%20AND%20Component%20!=%20Template%20AND%20(issuetype%20=%20Epic%20OR%20issuetype%20=%20Story)%20AND%20(resolved%20&gt;%20-8w%20OR%20status%20=%20&quot;In%20Review&quot;)%20ORDER%20BY%20resolutiondate%20ASC]. The valid characters are defined in RFC 7230 and RFC 3986

    org.apache.coyote.http11.Http11InputBuffer.parseRequestLine(Http11InputBuffer.java:504)

    org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:503)

    org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

    org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)

    org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1629)

    org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

    java.base&#47;java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

    java.base&#47;java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

    org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

    java.base&#47;java.lang.Thread.run(Thread.java:829)

</pre><p><b>Note</b> The full stack trace of the root cause is available in the server logs.</p><hr class="line" /><h3>Apache Tomcat/8.5.65</h3></body></html>

 

unknown

Hello @Franck Voufo 

You haven't tagged your question to say if you're using Jira CLOUD or Jira SERVER, but in either case, the V2 REST API Get Issue endpoint cannot be used to search for Issues using JQL. You must use the Issue Search endpoint:

https://atc.mycompanyname.net/rest/api/2/search?jql=jql_query

Also, the VBA code that defines the URL has a number of mistakes in it:

1. For Jira CLOUD, there is no '/Jira/' in a URL to an endpoint
2. The question mark denoting the start of the JQL statement is not preceded by a forward slash

So, the VBA would be:

s = "https://atc.mycompanyname.net" + "/rest/api/2/search?jql=" + query

Try testing your REST API requests with a tool like Postman etc. first, before you attempt to replicate that in code.