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

I want to update a JIRA issue throught Excel VBA. I tried to create session cookie.

Prashanth Anbazhagan
Prashanth Anbazhagan August 25, 2017

When I try the below code, it gets 200 success status but it doesnt fetch me jession id.

Public JiraService As New MSXML2.XMLHTTP60
Public JiraAuth As New MSXML2.XMLHTTP60

With JiraAuth
.Open "POST", "https://<jiralink>/secure/jira/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send "{""username"" : """ & strUsername & """, "" password "" : """ & strPassword & """}"""

Cells(2, 2).Value = .responseText
MsgBox Mid(.responseText, 42, 32)


If .Status = "200" Then
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"
End If
End With

 

Response text:

<HTML><HEAD><TITLE></TITLE></HEAD><BODY onLoad="document.AUTOSUBMIT.submit();">This page is used to hold your data while you are being authorized for your request.<BR><BR>You will be forwarded to continue the authorization process. If this does not happen automatically, please click the Continue button below & form text which has some details.

Help me fetch Jsession id.

Answer
Watch
Like Be the first to like this
2683 views

1 answer

0 votes
Warren
Warren
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
August 25, 2017

Hi

Are you using the Jira username ro the e-mail address for the variable strUsername? It needs to be e-mail address. The name and password need to be encrypted - see my VBA routines

Public Function GetIssues(query As String) As String

' Dim JiraService As New MSXML2.XMLHTTP30
 Dim json As Object
 
 If JiraService Is Nothing Then Set JiraService = New MSXML2.XMLHTTP60
 UserNameP = UserPassBase64
 
 With JiraService
 
 .Open "GET", query
 
 .SetRequestHeader "Content-Type", "application/json"
 .SetRequestHeader "Accept", "application/json"
 If UserNameP = "NoAuth" Then
 .SetRequestHeader "Authorization", "No Auth"
 Else
 .SetRequestHeader "Authorization: ", "Basic " & UserNameP
 End If
 .Send
 
 If .Status = "401" Then
 GetIssues = ""
 Else
 GetIssues = JiraService.ResponseText
 End If
 End With
 
End Function

and

Public Function UserPassBase64() As String

Dim objXML As MSXML2.DOMDocument60
 Dim objNode As MSXML2.IXMLDOMElement
 Dim arrData() As Byte
 Dim URL As String
 
 UserNameP = "e-mail: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
View More Comments
Prashanth Anbazhagan
Prashanth Anbazhagan August 26, 2017

No luck. Getting error 404 on trying encryption method.

Other method:

On using username, I get 200 status just that response is different. Even if I use mail id, I get the same response.

Like
Satyabrat Sahoo
Satyabrat Sahoo July 26, 2018

Do you have any full excel vba jira connector that will save my time building codes ?

It would be of great help. Thanks

Like
Reply

Suggest an answer

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

Atlassian Community Events

    See all events