Export Jira Filter to an Excel Table

Jira_Export_Filter.bas

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

See documentation https://tdalon.blogspot.com/jira-export-excel-tool