It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Excel VBA -> JIRA REST API

How can I access JIRA with VBA (Excel 2010) using the REST-API?

I read about the URL's, but I don't know how to integrate in VBA.

How can I send a message from VBA to JIRA? . . .

16 answers

1 accepted

9 votes
Answer accepted

Here is my solution for Excel-VBA with "Cookie" in JIRA 4.4.

I did it with MSXML2.XMLHTTP60 in a VBA-Classmodule.


Private JiraService As New MSXML2.XMLHTTP60 
Private JiraAuth As New MSXML2.XMLHTTP60

1. Create the "Cookie"/ session:

With JiraAuth
        .Open "POST", "https://YourJIRAAddress/jira/rest/auth/1/session", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .send " {""username"" : ""user"", ""password"" : ""passwort""}"""
        sErg = .responseText
        sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"  '*** Extract the Session-ID
    End With

2. Get the issue (here TEST-150):

With JiraService
   	.Open "GET", "https://YourJIRAAddress/jira/rest/api/2.0.alpha1/issue/TEST-150", False  
       .setRequestHeader "Content-Type", "application/json"
       .setRequestHeader "Accept", "application/json"
       .setRequestHeader "Set-Cookie", sCookie '*** see Create a "Cookie"
        
        .send
        sRestAntwort = .responseText
    End With

You can parse the result with VBScript.RegExp.

3.Delete the "Cookie"/ session

With JiraAuth
        .Open "DELETE", "https://YourJIRAAddress/jira/rest/auth/1/session", False 
        .send
    End With

Hi all,

I cannot get authenticated. I use jira v5.1 and the same code as above.

I always get response number 404, and the sCookie = "JSESSIONID= <title>Not Found (404)</title; Path=/Jira"

Does anybody know why it is not working?

Double check your URL. The first part until 'https://www.yourJIRAddress/jira/' is specific to your installation.

You can also use the 'REST-API-Browser' from then 'Atlassian Developer Toolbox' to check and develop your code.

Another point to 2.:

Since JIRA 5 the API-Version has changed. To Get the Issue you must use:

.Open "GET", "https://YourJIRAAddress/ jira/rest/api/2/issue/TEST-150", False

OK, i left '/jira/' in the URL that's whyI couldn't get authenticated, thanks a lot.

to the point 2, I'm trying to download the attachment of the issue, not the issue itself, but it doesn't work here:

With JiraService
    .Open "GET", "https://myjira/secure/attachment/AttID/test.txt", False
   .setRequestHeader "Content-Type", "application/json"
   .setRequestHeader "Accept", "application/json"
   .setRequestHeader "Set-Cookie", sCookie 
   .send
    oStream.Open
    oStream.Type = 1
    oStream.Write .responseBody
    oStream.SaveToFile ("C:\test.txt")
    oStream.Close
End With

Hi Arek,

solve this problem?

You know how to attach a file?

I created a issue using basic authentication, see:

https://answers.atlassian.com/questions/180214/creating-jira-issue-from-excel-vba?page=1#255588

I want to attach a file, but not working.

Hi Patrick,

why do you extract the session-id and set cookie? Why do you need this?

It's not really a cookie.

I extract the Session-ID. So it's not necessaryto send the credentials in every request. In my opinion that’s faster and easier. You don't need to use EncodeBase64, …

Hi. I am getting status 200 & but I dont find session ID in my response.(Create thje cookie code part)

My response looks something like this. Please help.

<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

Please can anyone help me, I am stuck, I have to extract data from JIRA web page and put it into EXCEL without using plugins that's way i am trying to do it with VBA. I have tried several methods but it dosen't work for me, Is there anyone who has done that already ?

Private JiraService As New MSXML2.XMLHTTP60
Private JiraAuth As New MSXML2.XMLHTTP60

Sub JIRA()

  With JiraAuth
       .Open "POST", "https://jiralink/rest/auth/1/session", False
       .setRequestHeader "Content-Type", "application/json"
       .setRequestHeader "Accept", "application/json"
       .send " {""username"" : """username""", ""password"" : """password"""}"""
       MsgBox .Status
       If .Status = "200" Then
           sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/" & sPfad
           Login = True
       End If
  End With

  With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://jiralink/sr/jira.issueviews:searchrequest-excel-all-fields/temp/SearchRequest.html?jqlQuery=project+%3D+NAME+AND+Sprint+%3D+1+ORDER+BY+priority+DESC%2C+updated+DESC&tempMax=1000" _
        , Destination:=Range("$A$1"))
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

I want to get this result :

ExportJIRA.PNG

Thank you

Hi

i'm getting a 404 in sErg any one can help me ?

i'm using JIRA6.1.4 and excel 2007

is there a probleme with the syntaxe or QUOTES (") in this code specialy for ".send"?

With JiraAuth
.Open "POST", "https://YourJIRAAddress/jira/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send " {""username"" : ""user"", ""password"" : ""passwort""}"""
sErg = .responseText
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"
End With
please note that i'm only in "http" protocol (not in "https") and below all my code for Auth
With JiraAuth
.Open "POST", "http://localhost:8282/jira/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send "{""username"":""admin"",""password"":""admin""}"

sErg = .responseText
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"
End With

Hi Patrick,

thanks for your answer.

But i would like understand the code correct:

Step 1 (I start with the authentification):

With JiraAuth
.Open "POST", jiralink & "rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send " {""username"" : """ & sUsername & """, ""password"" : """ & spassword & """}"""
sErg = .responseText
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira" '*** Extract the Session-ID
End With

Step 2 (I create the issue):

With JiraService
.Open "POST", jiralink & "rest/api/2/issue/", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Set-Cookie", sCookie '*** see Create a "Cookie"
.send sData
sRestAntwort = .responseText
sStatus = .Status & " | " & .StatusText
End With

Step 3 (I delete the authentification):

With JiraAuth
.Open "DELETE", jiralink & "rest/auth/1/session", False
.send
End With

For every "create issue" i take this code.

Step 1 - Step 2 - Step 3

If i have understand you correct, i do not need the step 1 and 3 for every issue?

Can you help me please. I use your code and it works perfect, but i would like understand what I do!

Thanks!

Exactly you don't need step 1 and 3 for every request.

I've put the different Function in a VBA-Classmodule (e.g. clsJIRARest).

In your Codemodule you can use easily the JIRA-API.

Here a simple example:

Classmodule: clsJiraRest

Private JiraService As New MSXML2.XMLHTTP60
Private JiraAuth As New MSXML2.XMLHTTP60

Private sJIRAUserID As String
Private sJIRAPass As String
Private sURL As String
Private sCookie As String

Public Property Let UserName(ByVal vNewValue As String)
    sJIRAUserID = vNewValue
End Property
Public Property Let Password(ByVal vNewValue As String)
    sJIRAPass = vNewValue
End Property
Public Property Let URL(ByVal vNewValue As String)
    sURL = vNewValue
End Property

Public Function Login() As Boolean
    
    Login = False

    With JiraAuth
        .Open "POST", sURL &amp; "/rest/auth/1/session", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"

        .send " {""username"" : """ &amp; sJIRAUserID &amp; """, ""password"" : """ &amp; sJIRAPass &amp; """}"" '*** HTTP-Request senden"

        If .Status = "200" Then
            sCookie = "JSESSIONID=" &amp; Mid(sErg, 42, 32) &amp; "; Path=/" &amp; sPfad
            Login = True
        End If

    End With

End Function


Public Function DoSomething(sJIRAID As String) As Boolean

'*** Your Code here

End Function

Public Function GetSomething() As String

'*** Your Code here

End Function


Public Function Logout()

    With JiraAuth
        .Open "DELETE", sURL &amp; "/rest/auth/1/session", False
        .send
    End With

End Function

Modul

Option Explicit

Sub JIRA()

Dim myJIRA As New clsJiraRest

    With myJIRA
    
        .UserName = "User"
        .Password = "Pass"
        .URL = "https://YourJIRAAddress/jira"
    
        If .Login = False Then Exit Sub
        
        .DoSomething "TEST-1"
        .DoSomething "TEST-2"
        
        .GetSomething
        
        .Logout
    
    End With

End Sub


Thanks, now its clear. Thank you very mutch!

Hi Patrick,

a have one more question.

We have a problem with the special characters and the wordwraps in the description or other fields.

Do you have the same problems? Do you know a solution?

We use jira v5.2.4.

Thanks for your time!

Steve

****************************************************************

For anyone interested in it!

Now we have a solution:

For the descritipn we use this function:

Function Substitute_description(ByVal Text As String) As String
'

Text = Application.Substitute(Text, "\", "\\") ' \
Text = Application.Substitute(Text, Chr(10), "\n") ' wordwrap
Text = Application.Substitute(Text, """", "\""") ' "
Substitute_description = Text


End Function

For the summary we change the wordwraps in blanks:

Function Substitute_summary(ByVal Text As String) As String
'

Text = Application.Substitute(Text, "\", "\\") ' \
Text = Application.Substitute(Text, Chr(10), " ") ' wordwrap
Text = Application.Substitute(Text, """", "\""") ' "
Substitute_summary_description = Text


End Function

Another question on the same lines:

This is my request:

.send "{""fields"": {""project"":{""id"": " &amp; projectID &amp; "},""summary"": ""Test for REST"",""description"": " &amp; descJira &amp; ",""issuetype"": {""id"": ""11""},""duedate"":""2014-08-06"",""labels"":[""Fulfillment""],""assignee"":{""name"":""-1""},""customfield_10400"":{""value"": ""Low""}}}"

In the above request, I am using two variables 'ProjectID' and 'descJira'.

If I use just one variable 'ProjectID' and regular text for the description like "test", the issue gets created without any errors.

But when I use descJira too in the send request, it does not work.

I get this error:

{"errorMessages":["Unexpected character ('M' (code 77)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')\n at [Source: org.apache.catalina.connector.CoyoteInputStream@1131b4a; line: 1, column: 229]"]}

I can't see what I'm doing wrong. Need help.

Thanks

Hi,

what ist the value of your variable "descJira"?

Please post it (exactly).

Hi Patrick,

a have one more question.

We have a problem with the special characters and the wordwraps in the description or other fields.

Do you have the same problems? Do you know a solution?

We use jira v5.2.4.

Thanks for your time!

Steve

Hello, 

 

Can you explain how tu use VBScript.RegExp with VBA for parsing the responseText ? 

 

I want to check a particular attribute of my issu in a VBA macro ...

 

Thanks

Here an older example:

Dim myRegEx As New clsRegEx 
 
sErg = myRegEx.RegExSub(sRestAntwort, """" &amp; vValues(1) &amp; """:""(.+?)""}", 1)
Function RegExSub(sInput As String, sRegEx As String, Optional iSubMatch As Integer) As String
Dim objRegEx As Object, objMatch  As Object, objMatch1 As Object, sErg As String, i1 As Integer
Set objRegEx = CreateObject("VBScript.RegExp")
    With objRegEx
        .Global = True  
        .IgnoreCase = False  
        .Pattern = sRegEx
        Set objMatch1 = .Execute(sInput)
        
        Set objMatch = objMatch1  
        
        If objMatch1.Count &gt; 0 Then  
           For i1 = 0 To objMatch1.Count - 1 
                If iSubMatch &gt; 0 Then  
                    If objMatch1.Item(i1).submatches.Count &gt;= iSubMatch Then sErg = sErg &amp; "," &amp; objMatch1.Item(i1).submatches.Item(iSubMatch - 1)
                Else
                    sErg = sErg &amp; "," &amp; objMatch1.Item(i1).Value 
                End If
            Next i1
            If Len(sErg) &gt; 1 Then sErg = Mid(sErg, 2) 

        End If
    
    End With

    RegExSub = sErg
End Function

It's possible to do it easier....

Thank you But I don't understand what is "vValue(1)", when I try your code, it doesn't work ... Then VBA does not recognize "clsRegEx" (I had Microsoft VBScript Regular Expressions 5.5) Can you help me please ?

It's a variable. You can use also: sErg = myRegEx.RegExSub(sRestAntwort, """customfield_11420"":""(.+?)""}", 1)

Hi When I get the return value of ResponseText, all the file is cut with many 'CRLF' ... do you know how to remove them ? Thanks

updating the use winHttpReq

 

Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
winHttpReq.setTimeouts 50000, 50000, 50000, 50000
winHttpReq.Open "POST", "https://mySite.com/rest/auth/1/session", False
winHttpReq.setRequestHeader "Content-Type", "application/json"
winHttpReq.setRequestHeader "Accept", "application/json"
Dim jup As String
jup = "{""username"": ""myUserName"",""password"": ""myPassword""}"
winHttpReq.send jup
Debug.Print winHttpReq.responseText

from here you need to parse the JSESSIONID

Hi, 

When I try to get some data on a ticket (with ?expand=changelog) I only get 15321 characters however my data should be 17443 ... It is working with my browser 

Any idea ? I tried many winhttp options, but I am stuck there ...

 

Thanks,

Nik0

If the goal is to have Excel spreadsheets that can execute VBA code for calculations and updates, then the Better Excel add-on's VBA tutorial may be interesting for all VBA developers.

This "inverts" the workflow in some sense: the data collection work can be done on the server side, but also allows do post-processing in Excel using VBA.

I have the same task and I plan to use https://github.com/VBA-tools/VBA-Web/.

I used this library earlier in another similar task, and it's excellent. JSON is also supported.

hi @Patrick Patrick9 can you please provide complete vbs file to log an issue in JIRA
Thanks in advance

This code didnt work for me, because the authentication response was not JSESSIONID, it was cloud.session.token = cookie

i could get it to work using curl, but not vba, did anyone had the same problem?

 

thanks

Im trying update Issue with PUT mode but my excel crash when i send data to server 

How do i log in if i use a google account, i have the API Token, how do i pass this in VBA ?

For those who would like to learn fresh, here is an awesome tutorial (Playlist) of connecting to JIRA using VBA. 

Red Stapler - JIRA Rest API

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Calling all Jira Cloud users! Give us feedback on our exploration of a new navigation.

Hi everyone! My name’s Matt and I’m a product manager at Atlassian. I work in the navigation & findability space for all our Jira Cloud products. We’ve been working on trying to improve the exp...

884 views 14 12
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you