nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Excel VBA -> JIRA REST API

Patrick Patrick9

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? . . .

Accepted answers

Patrick Patrick9

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=" &amp; Mid(sErg, 42, 32) &amp; "; 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

All comments

Patrick Patrick9

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=" &amp; Mid(sErg, 42, 32) &amp; "; 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

Arek Patek

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?

Patrick Patrick9

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

Arek Patek

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

work2014

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<br>    .Open "POST", "<a href="http://localhost:8282/jira/rest/auth/1/session" rel="nofollow noopener noreferrer">http://localhost:8282/jira/rest/auth/1/session</a>", False<br>    .setRequestHeader "Content-Type", "application/json"<br>    .setRequestHeader "Accept", "application/json"<br>    .send "{""username"":""admin"",""password"":""admin""}"<br> <br>    sErg = .responseText<br>    sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"

End With

Steve52

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!

Patrick Patrick9

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

Steve52

Thanks, now its clear. Thank you very mutch!

Steve52

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

Steve52

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

BROCHARD Emmanuel

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

Patrick Patrick9

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....

BROCHARD Emmanuel

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 ?

Patrick Patrick9

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

BROCHARD Emmanuel

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

demian kurejwowski

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

Nik0

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

Midori

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.

grv87

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.

Lina

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+=+NAME+AND+Sprint+=+1+ORDER+BY+priority+DESC,+updated+DESC&amp;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 :

Thank you

tuyiwopiru boxme

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

Daniel Almeida_zu30qc

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

Alejandro Martin_z0watj

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

Craig Booth_5h4njj

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

Amit_aoaljx

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

Bruce Henry

If your Jira server is running OAuth 1.0 and you're having trouble connecting because Excel and Power Query both do very poorly at the old OAuth 1.0 standard, you might try taking a look at:

https://github.com/brucephenry/aegle
and
https://github.com/brucephenry/oaf

Aegle is a thin proxy to allow you to address unauthenticated localhost and proxy that request to OAuth1.0 authenticated Jira calls.
OAF is a helper that handles doing the "OAuth dance" and writing keys to files for use by Aegle.

These each will require installing Ruby, so there's that.

Cheers!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-development

ms-excel

visual-basic