Create
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Products
See all
Community resources
Top groups
Explore all groups
Community resources
Learn
Community resources
Events
Community resources

VBA code to get issue from JIRA using REST API

Franck Voufo
Franck Voufo January 24, 2022

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>

 

Answer
Watch
Like Be the first to like this
9840 views

2 answers

1 accepted

0 votes
Answer accepted
David Bakkers
David Bakkers
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
January 24, 2022

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.

View More Comments
Franck Voufo
Franck Voufo January 26, 2022

Hello @David Bakkers , 

 

Thank you very much for your answer. I am indeed using Jira Server. I changed the url to s = "https://atc.mycompanyname.net/jira" + "/rest/api/2/search?jql=" + query as you mentioned. And I also found out that another reason I got the "HTTP Status 400 – Bad Request" was because I used a special language character in the jql_query like "ä". I then translated the special character in ISO-88592 with % in hexa, then it works: so instead of query = "***ä***" I wrote query = "***%C3%A4***" and now everything works without error. Thanks again

Like
Reply
0 votes
Josh
Josh January 24, 2022

It looks like it may be a related to this [CONFSERVER-57582] Later version of Tomcat hit into "Invalid character found in the request target" when subscribing to RSS feeds - Create and track feature requests for Atlassian products..  I'm wondering if this is related to the double quotes you have around your project.  Have you tried changing 

"project = ""my new project""" to
"project = 'my new project'"
View More Comments
Franck Voufo
Franck Voufo January 24, 2022

Hello @Josh  ,

thanks for your quick reply. I changed the double quotes as you suggested, but I still get the same error 😞

Like
Reply

Suggest an answer

Log in or Sign up to answer
Jira
Jira Software
TAGS
AUG Leaders

Atlassian Community Events

    See all events