Extract user details into Excel from JIRA API in Powershell

Function Get-JIRAExtract

{

Param(

[Parameter( Mandatory=$true,

HelpMessage="JIRA id of the user")]

[string]$assigneeId,



[Parameter( Mandatory=$true,

HelpMessage="Name of user.")]

[string]$assigneeName,



[Parameter( Mandatory=$false,

HelpMessage="Target Folder.")]

[string]$targetFolder = "c:\temp2"

)


try {

$token = "xxx"


$pair = "fname.lname@company.com:$token"

$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)

$base64 = [System.Convert]::ToBase64String($bytes)

$basicAuthValue = "Basic $base64"

$headers = @{ Authorization = $basicAuthValue }




$url = "https://mycompany.atlassian.net/rest/api/3/search?maxResults=1000&jql=assignee%20in%20($assigneeId)AND created >= -24w AND status in ('Complete', 'In Progress', 'Resolved', 'Closed')"



Write-Host "Call url $url"



$json = Invoke-RestMethod -Uri $url -Method Get -Headers $headers




$date = Get-Date -Format "yyyyMMdd_HHmmss"

$tempfile = New-TemporaryFile



Write-Host "Using temporary file $tempfile"



$excelFile = "$($targetFolder)\$($assigneeName)_ExcelReport_$($date).xls"

$counter = 0




"#,Key,Summary,Status,Created,ResolutionDate,Assignee" | add-content -path $tempfile




for($i=0; $i -lt $json.issues.Count; $i++)

{

$counter++

$summary = $json.issues[$i].fields.summary -replace "," , " "

$status = $json.issues[$i].fields.status.name -replace "," , " "



$line = "{0},{1},{2},{3},{4},{5},{6}" -f $counter, $json.issues[$i].key, $summary, $status, $json.issues[$i].fields.created, $json.issues[$i].fields.resolutiondate,$json.issues[$i].fields.assignee.displayName

$line | add-content -path $Tempfile

}




# Create Excel object (visible), workbook and worksheet

$excel = New-Object -ComObject excel.application

$excel.Visible = $True

$excel.DisplayAlerts = $false




$Workbook = $Excel.Workbooks.Add()

$Workbook.Title = 'Report'




$ws = $Workbook.Worksheets

$ws = $ws.Item(1)

$ws.Name = "Report"




# Select Worksheet (in case you have multiple worksheets)

$ws = $Workbook.Worksheets | where {$_.name -eq "Report"}

$ws.Select()




# Define the connection string and the starting cell for the data

$TxtConnector = ("TEXT;" + $Tempfile)

$CellRef = $ws.Range("A1")



# Build, use and remove the text file connector

$Connector = $ws.QueryTables.add($TxtConnector,$CellRef)

$ws.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True

$ws.QueryTables.item($Connector.name).TextFileParseType = 1

$ws.QueryTables.item($Connector.name).Refresh() | Out-Null

$ws.QueryTables.item($Connector.name).delete()




# Autofit the columns, freeze the top row

$ws.UsedRange.EntireColumn.AutoFit() | Out-Null

$ws.Application.ActiveWindow.SplitRow = 1

$ws.Application.ActiveWindow.FreezePanes = $true




# Set color & border to top header row

$Selection = $ws.cells.Item(1,1).EntireRow

$Selection.Interior.ColorIndex = 19

$Selection.BorderAround(1) | Out-Null

$Selection.Font.Bold=$True




$Workbook.SaveAs($excelFile,1)

$Workbook.Close()

$excel.Quit()




[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Remove-Variable excel




If(Test-Path $Tempfile){Remove-Item $Tempfile}




Write-Host "Successfull created $excelFile"

}

catch {

Write-Host "An error occurred:"

Write-Host $_.ScriptStackTrace

}

}