If you want to export a Jira Filter to Excel or csv, you will face the 1000 issues limit so that the built-in export does not work.
I present here a nice Excel/VBA-based tool that make Jira filter export to Excel a one-click solution.
Problem/ Challenge description
From a Filter view, Jira provides some built-in functionality to export the issues list to different format.
What we want here is to get an export to a plain Excel file in a table format.
When you have a lot of issues in the filter - in fact more than 1000, the export function does not work but returns the following error:
To bypass this error you will have manually to append ?tempMax=1000&startPage=0-1000-2000 etc. as explained by Atlassian here.
Solution implementation
You can download the file in github/tdalon/tools/Jira_Export.xlsm
Gist for the main macro is available here.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Public Function GetPassword() As String | |
| ' Get password from file stored in user profile as Proof of concept. Do not do in your work organization (Password shall be encrypted) | |
| File = Environ("userprofile") & "\password.txt" | |
| 'Read output tmp File | |
| Set FSO = CreateObject("Scripting.FileSystemObject") | |
| Set ts = FSO.OpenTextFile(File, 1) | |
| Password = ts.ReadLine | |
| ts.Close | |
| Password = Trim(Password) | |
| GetPassword = Password | |
| End Function | |
| Public Sub ExportFilter_Callback() | |
| Dim tbl As ListObject | |
| Dim JiraFilterUrl As String | |
| Set wsExport = ThisWorkbook.ActiveSheet | |
| JiraFilterUrl = "" | |
| On Error Resume Next | |
| JiraFilterUrl = wsExport.Range("JiraFilterUrl") | |
| On Error GoTo 0 | |
| Set tbl = wsExport.ListObjects(1) | |
| If sInput = "" Then | |
| JiraFilterUrl = InputBox("Input your Jira Filter Url." & vbCrLf & _ | |
| "Example: <JiraRootUrl>/issues/?filter=78795", "Export Jira Filter", JiraFilterUrl) | |
| If (JiraFilterUrl = "") Then | |
| 'cancelled | |
| Exit Sub | |
| End If | |
| End If | |
| On Error Resume Next | |
| wsExport.Range("JiraFilterUrl").Cells(1, 1).Value = JiraFilterUrl | |
| On Error GoTo 0 | |
| Call ExportCurrentFilter(JiraFilterUrl, tbl) | |
| End Sub | |
| Sub ExportCurrentFilter(JiraFilterUrl As String, tbl As ListObject) | |
| ' Export Filter to "Export" Sheet using "JIRAFilterId" Range Value with fields as defined in Filter columns | |
| ' Call ExportIssues | |
| Dim sJiraRootUrl As String | |
| Dim sFilterId As String | |
| Application.StatusBar = "Export Jira Filter to CSV..." | |
| ' Hide temp csv file | |
| Application.ScreenUpdating = False | |
| Dim sCsvFile As String | |
| sCsvFile = Environ("temp") & "\Jira_Export.csv" | |
| MaxIssues = 1000 | |
| StartPage = 0 | |
| RowInsert = 1 | |
| Set RE = New RegExp | |
| RE.Pattern = "\?filter=(.*)" | |
| Set allMatches = RE.Execute(JiraFilterUrl) | |
| If allMatches.Count <> 0 Then | |
| sFilterId = allMatches.Item(0).SubMatches.Item(0) | |
| Else | |
| End If | |
| RE.Pattern = "https?://[^/]*" | |
| Set allMatches = RE.Execute(JiraFilterUrl) | |
| If allMatches.Count <> 0 Then | |
| sJiraRootUrl = allMatches.Item(0).Value | |
| Else | |
| End If | |
| IsFiltered = tbl.AutoFilter.FilterMode | |
| If IsFiltered Then | |
| tbl.Range.AutoFilter | |
| End If | |
| ' Reset Table | |
| 'tbl.Range.Delete | |
| If tbl.ListRows.Count >= 1 Then | |
| tbl.DataBodyRange.Delete | |
| End If | |
| Dim sCmd As String | |
| Application.DisplayAlerts = False ' To close without prompt | |
| Dim wsh As Object | |
| Set wsh = VBA.CreateObject("WScript.Shell") | |
| Set FSO = CreateObject("Scripting.FileSystemObject") | |
| If FSO.FileExists(sCsvFile) Then | |
| FSO.DeleteFile sCsvFile, True | |
| ' Will make an error if file is already opened | |
| End If | |
| CsvExport: | |
| sUrl = sJiraRootUrl & "/sr/jira.issueviews:searchrequest-csv-current-fields/" & sFilterId & "/SearchRequest-" & sFilterId & _ | |
| ".csv?tempMax=" & CStr(MaxIssues) & "&pager/start=" & CStr(StartPage) | |
| sCmd = "curl -o " & sCsvFile & " -u " & Environ("username") & ":" & GetPassword() & " -X GET """ & sUrl & """" | |
| ' Shell&Wait https://stackoverflow.com/a/8906912/2043349 | |
| wsh.Run sCmd, 7, True ' 7: windowStyle=Hide, True= waitOnReturn | |
| ' https://learndataanalysis.org/merge-multiple-csv-files-in-excel-with-vba/ | |
| ' Read File | |
| Set wbTemp = Workbooks.Open(sCsvFile) | |
| If wbTemp Is Nothing Then | |
| ' Error | |
| Application.StatusBar = "ERROR: Export Jira Filter." | |
| Debug.Print "File does not exist" | |
| Debug.Print sCmd | |
| Exit Sub | |
| End If | |
| Set SrcRange = wbTemp.Worksheets(1).UsedRange | |
| If (StartPage = 0) Then ' Copy Header for first batch | |
| RowsCount = SrcRange.Rows.Count | |
| With tbl.Range | |
| tbl.Resize .Resize(, SrcRange.Columns.Count) ' resize to number of Columns | |
| .Cells(1, 1).Rows(1).ClearContents ' Clear first row / remaining header | |
| End With | |
| Else | |
| RowsCount = SrcRange.Rows.Count - 1 | |
| Set SrcRange = SrcRange.Offset(1, 0).Resize(RowsCount, SrcRange.Columns.Count) ' Remove header | |
| End If | |
| SrcRange.Copy | |
| tbl.Range.Cells(RowInsert, 1).PasteSpecial xlPasteValues | |
| wbTemp.Close (True) | |
| FSO.DeleteFile sCsvFile, True | |
| RowInsert = RowInsert + RowsCount | |
| If RowsCount < MaxIssues Then | |
| GoTo EndCsvExport | |
| Else | |
| StartPage = StartPage + MaxIssues | |
| GoTo CsvExport | |
| End If | |
| EndCsvExport: | |
| Debug.Print RowInsert - 1 & " issues exported" | |
| ' AutoFit | |
| 'tbl.Range.Columns.AutoFit | |
| If IsFiltered Then | |
| tbl.Range.AutoFilter | |
| End If | |
| Application.ScreenUpdating = True | |
| Application.StatusBar = "End: Export Jira Filter." | |
| End Sub |
It uses curl (available with Windows 10) and loop the csv export by 1000 batches.
The result is aggregated in a Table aka ListObject.
Usage
Download the file Jira_Export.xlsm.
From the ribbon, you can run the Export function. It runs the macro ExportFilter_Callback.
Prerequisite: you have your Windows password set under %temp%/password.txt (this file location is only accessible by the logged-in user. Feel free to adapt this part, delete the password file after usage)
In the Worksheet you run the macro, you need to have one Table defined. The first one in the current sheet will be used as target for the export.
If in the same sheet you have a named cell with the name "JiraFilterUrl", it will be used to store the filter exported.
The Table size and columns will be adapted automatically.
This is a one-way export without any merging of custom columns. You will get what you see in Jira.
