Community
Products
Jira
Questions
I want to update a JIRA issue throught Excel VBA. I tried to create session cookie.

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

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.

1 answer

0 votes

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

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

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.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

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

It would be of great help. Thanks

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Forums

Product Q&A

Community resources

Support

Top groups

Community resources

Support

Learn

Community resources

Support

Events

Community resources

Support

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

1 answer

Suggest an answer

Was this helpful?

Thanks!

TAGS

Community showcase

Atlassian Community Events

Forums

Product Q&A

Community resources

Support

Top groups

Community resources

Support

Learn

Community resources

Support

Events

Community resources

Support

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

1 answer

Suggest an answer

Was this helpful?

Thanks!

TAGS

Community showcase

Atlassian Community Events

Welcome to the new Atlassian Community