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

17 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=" &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

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

Comment

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?

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

Comment

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

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

Comment

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

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

Comment

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.

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

Comment

Hi Patrick,

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

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

Comment

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, …

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

Comment

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

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

Comment

1 vote

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

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

Comment

1 vote

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 :

Thank you

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

Comment

0 votes

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!

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

0 votes

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.

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

Comment

0 votes

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.

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

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 ?

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

Comment

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

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

Comment

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

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

Comment

0 votes

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

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

Comment

0 votes

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

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

Comment

0 votes

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!

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

Comment

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

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

Comment

Thanks, now its clear. Thank you very mutch!

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

Comment

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

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

Comment

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

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

Comment

Hi,

what ist the value of your variable "descJira"?

Please post it (exactly).

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

Comment

0 votes

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

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

Forums

Q&A

Community resources

Support

Top groups

Community resources

Support

Learn

Community resources

Support

Events

Community resources

Support

Excel VBA -> JIRA REST API

17 answers

1 accepted

Suggest an answer

Was this helpful?

Thanks!

TAGS

Community showcase

Atlassian Community Events