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

Access JIRA with VBA Excel 2010

Hello,

I am trying to access JIRA with VBA to export data to Excel. So, I start with the authentification and then try to export data using this code :

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 would like to have this result (screenshot): 

ExportJIRA.PNG

Can anyone help me please ? Sorry, i am new to VBA and JIRA 

 

22 answers

Add this line of code (in bold)

.setRequestHeader "X-Atlassian-Token", "nocheck"
.setRequestHeader "Origin", "your url: port"

@MR2001

Hello, i have the code in VBA but I'm still getting this error: "Anonymous users do not have permission to create issues in this project. Please try logging in first."

Set JiraService = New MSXML2.ServerXMLHTTP60
On Error Resume Next
With JiraService
.Open "POST", "https://myurl/rest/api/2/issue/", False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.SetRequestHeader "Authorization" & "Basic ", sEncbase64Auth
.SetRequestHeader "Set-Cookie", sCookie
.SetRequestHeader "X-Atlassian-Token", "nocheck"
.SetRequestHeader "Origin", "myurl: port"
.Send (sData)
sRestAntwort = .ResponseText
sStatus = .Status & " | " & .StatusText
End With

Set JiraService = Nothing

 

If you have any thoughts, it would be very helpful.

You must authenticate first, using something like this:

 

Dim oJiraAuth As MSXML2.ServerXMLHTTP60 
Dim oJiraService As MSXML2.ServerXMLHTTP60

Set oJiraAuth = New MSXML2.ServerXMLHTTP60
With oJiraAuth
.Open "POST", "https://jiralink/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
sOutput = .responseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira" 
End With

@MR2001, Can you look at my comment below, please? I am trying to fetch JIRA JQL result set and populate the worksheet. But I am getting 404 error. Please help!

0 votes

Maybe you get results faster with the Better Excel Plugin which exports to Excel and supports VBA, too.

Thanks for your reply, but I do not use plugins, that's why i am using VBA.

Do it this way (note that variable declaration is also changed compared to your code):

Dim oJiraAuth As MSXML2.ServerXMLHTTP60 
Dim oJiraService As MSXML2.ServerXMLHTTP60

Set oJiraAuth = New MSXML2.ServerXMLHTTP60
With oJiraAuth
.Open "POST", "https://jiralink/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
sOutput = .responseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira" 
End With

 Set oJiraService = New MSXML2.ServerXMLHTTP60
With oJiraService
.Open "GET","https://jiralink/rest/api/2/search?jql=project=YOURPROJECT", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & EncodeBase64(JIRA_USER & ":" & JIRA_PWD)
.setRequestHeader "Set-Cookie", sCookie 
.Send
sOutput = .responseText
sStatus = .Status & " | " & .statusText
End With

'sOutput  should contain all data in JSON format
'next, you must use a JSON parser to get the desired format

Set oJiraService = Nothing
Set oJiraAuth = Nothing
Private Function EncodeBase64(srcTxt As String) As String
  Dim arrData() As Byte
  arrData = StrConv(srcTxt, vbFromUnicode)
  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement
  
  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  
  EncodeBase64 = objNode.Text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

 

Hi @MR2001

Do you have a code to parse JSON response and access specific fields and display it in a cell?

You can use this VBA module that parses JSON files:

https://github.com/VBA-tools/VBA-JSON

Like Magnus Åkerström likes this

@M.R     I have been using the above format, however I couldn't able to connect to JIRA, always getting 404 error. Kindly let me know if there is any change in the format.

Sorry, both lines must be added.

Thanks M.R. 

It Worked!!! Now I could able to connect to JIRA

Hi Venkatesan,

I have been trying to connect with Jira using the above code but every time it throws the error:

"user defined type not defined" 

Code-

Sub test()
Dim
oJiraAuth As MSXML2.ServerXMLHTTP60  Dim oJiraService As MSXML2.ServerXMLHTTP60 Set oJiraAuth = New MSXML2.ServerXMLHTTP60 With oJiraAuth .Open "POST", "https://jiralink/rest/auth/1/session", False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Accept", "application/json" .Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}" sOutput = .responseText sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"  End With
End Sub

It throws the error and highlights the first line:Dim oJiraAuth As MSXML2.ServerXMLHTTP60

I tried adding reference i.e Microsoft Active x Data Objects 6.1 Library but it is still not working.Could you help me in resolving it ,i am very new to VB script.

You must add a reference to Microsft XML, v6.0

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

 

 

Hello, I have the following code that I am trying to connect to JIRA and fetch data from JIRA JQL. However, I am getting an error:

 

Code:

Sub test()
strJQL = "https://horizon.se.com/rest/auth/latest/session"


With JiraService
.Open "GET", strJQL, False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.SetRequestHeader "Authorization", "Basic " & UserPassBase64
.Send ""

If .Status = "401" Then
MsgBox "Not authorized or invalid username/password"
Else
End If
End With


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://horizon.se.bank.com/issues2/sr/jira.issueviews:searchrequest-excel-all-fields/temp/SearchRequest.html?jql=project%20%3D%20SISBTXTRPR%20AND%20issuetype%20%3D%20Story" _
, 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

Set oJiraService = Nothing
Set oJiraAuth = Nothing
End Sub

Private Function UserPassBase64() As String
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte

arrData = StrConv("MyUserName:MyPassword", vbFromUnicode)

Set objXML = New MSXML2.DOMDocument
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData

UserPassBase64 = objNode.Text

End Function

 

 

 

Error:

Notice to First Time Users!

First time users must register with Self Service Password Management (SSPM) before using SSO. Click Here for registration instructions.

Standard ID
Get Standard ID
Password

Having Trouble Signing On?

Sign On Help

Please use the following code, that works. 

If jiraRESTLogin(yourJIRA_User , yourJIRA_Password) Then
' Populate Excel
End If

Public Function jiraRESTLogin(jiraUser As String, jiraPass As String) As Boolean
Dim jResp As Object

Set jResp = sendPost("https://horizon.se.com/rest/auth/latest/session", "{""username"": """ & jiraUser & """,""password"": """ & jiraPass & """}")
If jResp.Status <> 200 Then
MsgBox "Incorrect JIRA password.", vbOKOnly + vbInformation, "W A R N I N G - JIRA Login Failed"
jiraRESTLogin = False
Else
jiraRESTLogin = True
End If

Set jResp = Nothing
End Function

Private Function sendPost(url As String, Optional postData As String = "") As Object
rest.Open "POST", url, False
rest.setRequestHeader "Content-type", "application/json"
rest.setRequestHeader "Content-length", Len(postData)
rest.setRequestHeader "Connection", "close"
rest.setTimeouts 10000, 10000, 10000, 15000
rest.Send (postData)
If rest.readyState = 4 Then Set sendPost = rest
End Function

Thank you, kind sir/madam :)

 

I'll test this code and let you know soon. May I also ask if I can use the code below in place where you said "populate excel"?

 

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://horizon.se.bank.com/issues2/sr/jira.issueviews:searchrequest-excel-all-fields/temp/SearchRequest.html?jql=project%20%3D%20SISBTXTRPR%20AND%20issuetype%20%3D%20Story" _
, 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

Yes, try using this code if you successfully login but I cannot test it. Hopefully works.

Sure thing. I will let you know soon.

So, the code errors on 

 

rest.Open "POST", url, False

 

It says "Object is required". Should I include any references? I don't think "rest" has been declared anywhere.

Declare it as:

Private rest As New MSXML2.ServerXMLHTTP60

The jResp.Status is '404'. My company uses SSO for JIRA logon. Does it matter?

Try using "me@domain.com" as user name or maybe "me\@domain.com" to escape "@" character

Unfortunately I am not able to test as we are using a different authentication method....

OK, so I was able to correct the URL by inspecting the elements of the logon page. Now, I am getting return code 200. However, when I am trying to create/add a query table, it errors out.

 

Here is the code that I am using:

 

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://horizon.se.bank.com/issues2/sr/jira.issueviews:searchrequest-csv-all-fields/temp/SearchRequest.csv?jqlQuery=project+%3D+SISBTXTRPR+AND+issuetype+%3D+Story" _
, 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

 

The excel that I have is 2013 version. I do not believe there is Power Query pre-installed. I am trying to contact my company's helpdesk to have PQ installed. Do I need to have Power Query installed in Excel 2013 in order to run this code?

For some reason, I suspect that the logon credentials from "sendPost" function in your code is not being persisted when I go to the QueryTables.Add

Did anybody use this method and it worked? I would suggest to get a file on your local drive as the result of your JQL and then parse it.

All I need is to download that JQL result set as a file programmatically. I followed the same code that the OP of this thread tried to do.

 

If you know of any other way to download the csv file from JQL, PLEASE let me know :) I need this code working more than anything...ha!

You don't need PowerQuery to use QueryTables, But yes, PowerQuery can connect to a CSV file  returned by your JQL (NOT a JSON file)

If the result of your JQL is a JSON file, then the option is to use a JSONParser. All VBA code can be found here: https://github.com/VBA-tools/VBA-JSON

Are you using JIRA on premises or JIRA cloud?

See this post:

https://community.atlassian.com/t5/Jira-questions/Fetch-data-from-JIRA-to-excel/qaq-p/198786

I, originally, started off with JSON. But, oddly enough, the fields that I need are custom fields (for e.g., Epic Link) and they are not available in the JSON that is being returned. Or at least I do not know how to retrieve those fields returned in JQL result set in JSON. Do you?

So, this is my code for fetching JSON response and oddly, I am able to persist the logon details. But, the problem is, I am using a GET instead of POST.

 

strStory = "https://horizon.se.bank.com/issues2/rest/api/latest/issue/xy11"
With JiraService
.Open "GET", strStory, False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.SetRequestHeader "Authorization", "Basic " & UserPassBase64
.Send ""

.
.
.
End With

 

Private Function UserPassBase64() As String
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte

arrData = StrConv("username:pwd", vbFromUnicode)

Set objXML = New MSXML2.DOMDocument
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData

UserPassBase64 = objNode.Text

End Function

Once you have a JSON file you should use the VBA JSON Parser (sent you the link) and loop through it. For custom fields however, you need to do a manual mapping for example: 

Private Const MY_CUSTOM_FIELD1 As String = "customfield_11104"

Private Const MY_CUSTOM_FIELD1_FRIENDLY_NAME As String = "Meeting Location"

To get the custom fields mapping, run this query:

JIRA_URL & "rest/api/latest/issue/createmeta?projectKeys=" & sProjectKey & "&issuetypeName=Bug&expand=projects.issuetypes.fields

I will give this a whirl. Thank you for patiently working with me. I cannot appreciate more.

 

1.jpg

So, this is what I would like to retrieve... the "Epic Link". I inspected the field to be "customfield_10371". I have a perfectly working code below that is fetching everything I want except for that one silly but vital field. I see that you want me to assign that custom field to a user defined variable. But, where would I put that piece of code in the code below?

This is the string I am building;

 

sJQL = "https://horizon.se.bank.com/issues2/rest/api/latest/search?jql=project%20%3D%20SISBTXTRPR%20AND%20issuetype%20%3D%20Story%20ORDER%20BY%20key%20ASC&startAt=" + iStartAt + "&maxResults=50"

I then pass it to a function like this which returns a JSON response:

Set JSONObj = ConnectToJIRA(sJQL)

And I parse the JSON like so:

While i <= iCounter
ActiveSheet.Cells(iCellIndex, 1) = JSONObj("issues")(i)("key")
iCounter = JSONObj("totals") - 50
i = i + 1
iCellIndex = iCellIndex + 1
Wend

 

Now, where would I assign the custom field as you suggested?

I tried this as well and while I get return code of 200 and session ID created, while I add the result from JQL to the query table, I get the SSO logon details asking me to log in first.

 

Sub JIRA()

Dim jiraUser, jiraPass As String

jiraUser = "something something"
jiraPass = "something something"

With JiraAuth
.Open "POST", "https://horizon.se.bank.com/issues2/rest/auth/1/session", False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.Send "{""username"": """ & jiraUser & """, ""password"": """ & jiraPass & """}"
MsgBox .Status
If .Status = "200" Then
sOutput = .ResponseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"
Login = True
End If
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://horizon.se.bankofamerica.com/issues2/issues/?jql=project%20%3D%20SISBTXTRPR%20AND%20issuetype%20%3D%20Story" _
, Destination:=Range("$A$1")) 
.SaveData = True 
.Refresh BackgroundQuery:=False
End With

End Sub

I would suggest to download a file, using SSO seems a difficult matter.

Can you use this approach in your VBA code (I use this to get labels but you can maybe adapt it to get the custom fields)?

JSONObj("issues")(i)("fields")("labels")(j)

this is the code:

For i = 1 To JSONObj("issues").Count
If Not IsEmpty(JSONObj("issues")(i)("fields")("labels")) Then
If JSONObj("issues")(i)("fields")("labels").Count > 0 Then
For j = 1 To JSONObj("issues")(i)("fields")("labels").Count
If Trim(JSONObj("issues")(i)("fields")("labels")(j)) <> vbNullString Then
k = k + 1
End If
Next
End If
End If
Next

I think this is a great idea. I am now able to access the custom field that I need. However, it is a lengthy workaround albeit it does the job. I am still interested in knowing a way to download the JQL result into excel using VBA. Unfortunately, the SSO is blocking/erroring out.

 

Thank you for your continued valuable support, MR2001.

I would suggest a different design (this is what I am doing at my company):

1. Create an extraction program (ETL) in .Net (C# or VB) using Atlassian.Net SDK, free JIRA connector https://bitbucket.org/farmas/atlassian.net-sdk/src/master/

2. This will copy all JIRA data into an SQL Server table. Depending on your JIRA database size you can extract several times a day or even every hour if not so large. You need a database server for this purpose, maybe free SQL Server lite edition

3. Connect Excel to this database and extract the data in seconds.

Or try triggering an immediate extraction from Excel as an alternative

Use the key "name" to identify custom field name and then map it

I used the Connect method which I modified to accept a config parameter for the base JIRA URL

Public Sub Connect()
Set oJiraAuth = New MSXML2.ServerXMLHTTP60
Set oJiraService = New MSXML2.ServerXMLHTTP60

With oJiraAuth
.Open "POST", getConfig("JIRA_URL") & "/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.Send " {""username"" : """ & getConfig("JIRA_USER") & """, ""password"" : """ & getConfig("JIRA_PWD") & """}"
sOutput = .responseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"
End With
End Sub

Then rather that query to return json with a .Open "GET" (which I did try, but failed to have issue id or key returned making that rather useless), I used a search request to download a csv with all fields.

t_URL = "[JIRA base URL]/sr/jira.issueviews:searchrequest-csv-all-fields/10529/SearchRequest-10529.csv"

This returned the cvs in the .responseText, which I wrote to a csv file then opened using VBA so I could copy the contents to my workbook.

With oJiraService
.Open "GET", t_URL, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & EncodeBase64(JIRA_USER & ":" & JIRA_PWD)
.setRequestHeader "Set-Cookie", sCookie
.Send
Close
t_FullName = ThisWorkbook.Path & "\JIRA\JIRA.csv"
Open t_FullName For Output As #1
Print #1, .responseText
Close
End With

Suggest an answer

Log in or Sign up to answer
TAGS

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