Hi, everybody!
I’m trying to get the active Sprint ID using REST API in VBA Excel. I successfully got authentication, but when I try to get the Sprint ID, I have got the following message:
{"message":"Client must be authenticated to access this resource.","status-code":401}
Why do I getting this error once I have got a status = 200 in authentication request? Does somebody could show what I am doing wrong? This is my code:
Option Explicit
Sub GetSprintID()
Dim rc As Integer
Dim JiraLogin As String
Dim JiraPassword As String
Dim JiraURL As String
Dim JiraBoard As String
Dim sOutput As String
Dim sprintOutput As String
Dim sCookie As String
Dim oJiraAuth As MSXML2.ServerXMLHTTP60
Dim oJiraService As MSXML2.ServerXMLHTTP60
Set oJiraAuth = New MSXML2.ServerXMLHTTP60
Set oJiraService = New MSXML2.ServerXMLHTTP60
JiraLogin = "myLogin"
JiraPassword = "myPassword"
JiraURL = "myJiraURL"
JiraBoard = "myBoard"
With oJiraAuth
.Open "POST", JiraURL + "/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send "{""username"" : """ & JiraLogin & """, ""password"" : """ & JiraPassword & """}"
sOutput = .responseText
If .Status = "200" Then
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"
Else
rc = MsgBox("Fail of authentication.", vbOKOnly + vbCritical, "Error")
Exit Sub
End If
End With
With oJiraService
.Open "GET", JiraURL + "/rest/agile/1.0/board/" + JiraBoard + "/sprint?state=active", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Set-Cookie", sCookie
.send
sprintOutput = .responseText
End With
Sheets(1).Range("A1") = sprintOutput
With oJiraAuth
.Open "DELETE", JiraURL + "/rest/auth/1/session", False
.send
End With
End Sub
Best regards!
Fabiano