Report SPO file versions

Import-Module ImportExcel -ErrorAction SilentlyContinue

function Get-FileSize {
    # Format File Size nicely
    param (
            [parameter(Mandatory = $true)]
            $InFileSize
        )
 
    If ($InFileSize -lt 1KB) { # Format the size of a document
        $FileSize = $InFileSize.ToString() + " B"
    }
    ElseIf ($InFileSize -lt 1MB) {
        $FileSize = $InFileSize / 1KB
        $FileSize = ("{0:n2}" -f $FileSize) + " KB"
    }
    Elseif ($InFileSize -lt 1GB) {
        $FileSize = $InFileSize / 1MB
        $FileSize = ("{0:n2}" -f $FileSize) + " MB"
    }
    Elseif ($InFileSize -ge 1GB) {
        $FileSize = $InFileSize / 1GB
        $FileSize = ("{0:n2}" -f $FileSize) + " GB"
    }
    Return $FileSize
}
 
$InputCSVFile = Read-Host "Enter the path to the SharePoint Online file versions report CSV file"
If (-not (Test-Path $InputCSVFile)) {
    Write-Host "File $InputCSVFile not found... exiting"
    Break
}
 
$SPOData = Import-CSV $InputCSVFile
# Reported site can be found in the first record of the report file
$WebURL = $SPOData[0].'WebUrl.Compact'
 
$Report = [System.Collections.Generic.List[Object]]::new()
 
Write-Host ("Processing {0} records" -f $SPOData.Count)
ForEach ($Item in $SPOData) {
    If (!([string]::IsNullOrWhiteSpace($Item.'DocId.Compact'))) {
       # Start of records for a new document
        $WebId                           = $Item.'WebId.Compact'
        $DocId                           = $Item.'DocId.Compact'
        $MajorVersion                    = $Item.'MajorVersion'
        $MinorVersion                    = $Item.'MinorVersion'
        $FileUrl                         = $Item.'FileUrl.Compact'
        $Size                            = $Item.Size
        $LastModifiedDate                = $Item.LastModifiedDate
        $ModifiedBy                      = $Item.'ModifiedBy_DisplayName.Compact'
        $SnapShotDate                    = $Item.SnapShotDate
        $SnapShotEstimated               = $Item.IsSnapshotDateEstimated
        $CurrentExpirationDate           = $Item.CurrentExpirationDate
        $AutomaticPolicyExpirationDate   = $Item.AutomaticPolicyExpirationDate
        $TargetExpirationDate            = $Item.TargetExpirationDate
 
        $Document = $FileURL.Split("/")[-1]
        $Folder = $FileUrl.SubString(0,(($FileUrl.Length-1) - ($Document.Length)))
 
    } Else {
        # A continuation of the previous document, so only populate some variables
        $MajorVersion                   = $Item.'MajorVersion'
        $MinorVersion                   = $Item.'MinorVersion'
        $Size                           = $Item.Size
        $LastModifiedDate               = $Item.LastModifiedDate
        $ModifiedBy                     = $Item.'ModifiedBy_DisplayName.Compact'
        $SnapShotDate                   = $Item.SnapShotDate
        $SnapShotEstimated              = $Item.IsSnapshotDateEstimated
        $CurrentExpirationDate          = $Item.CurrentExpirationDate
        $AutomaticPolicyExpirationDate  = $Item.AutomaticPolicyExpirationDate
        $TargetExpirationDate           = $Item.TargetExpirationDate
 
    }
    $ReportLine = [PSCustomObject] @{
        WebId                               = $WebId
        DocId                               = $DocId
        'Major version'                     = $MajorVersion
        'Minor version'                     = $MinorVersion
        Site                                = $WebUrl
        FileURL                             = $FileUrl
        Folder                              = $Folder
        Document                            = $Document
        Size                                = $Size
        'Last Modified Date'                = $LastModifiedDate
        'Last Modified By'                  = $ModifiedBy
        SnapShotDate                        = $SnapShotDate
        'SnapShot Estimated'                = $SnapShotEstimated
        'Current Expiration Date'           = $CurrentExpirationDate
        'Automatic Policy Expiration Date'  = $AutomaticPolicyExpirationDate
        'Target Expiration Date'            = $TargetExpirationDate
    }
    $Report.Add($ReportLine)
}
 
[array]$SPOFiles = $Report | Sort-Object DocId -unique | Select-Object -ExpandProperty DocId
Write-Host ("Found {0} unique files from {1} records in the report" -f $SPOFiles.Count, $Report.count)
 
$FilesReport = [System.Collections.Generic.List[Object]]::new()
[int]$i = 0
ForEach ($File in $SPOFiles) {
    $i++
    Write-Host ("Processing file {0} {1}/{2}" -f $File, $i, $SPOFiles.Count)
    $FileData = $Report | Where-Object {$_.DocId -eq $File}
    $FileURL = $FileData[0].FileURL
    $FileData = $FileData | Sort-Object {$_.'Last Modified Date' -as [datetime]} -Descending
    $LastModifiedDate = $FileData[0].'Last Modified Date'
    $FirstModifiedDate = $FileData[-1].'Last Modified Date'
    $Sizes = $FileData | Measure-Object -Property Size -Minimum -Maximum -Sum -Average
    # Find records with a 'Last Modified By' value. Find unique values. Sort by date and select the first one
    # to find who last modified the file
    $LastModifiedBy = $FileData | Where-Object { -not [string]::IsNullOrEmpty($_.'Last Modified By') } `
        | Sort-Object 'Last Modified By' -Unique | Sort-Object {$_.LastModifiedDate -as [datetime]} `
        | Select-Object -First 1 |Select-Object -ExpandProperty 'Last Modified By'
 
    $ReportLine = [PSCustomObject] @{
        Folder                              = $FileData[0].Folder
        Document                            = $FileData[0].Document
        'Count of versions'                 = $FileData.Count
        'First modified'                    = Get-Date $FirstModifiedDate -format 'dd-MMM-yyyy HH:mm'
        'Last modified'                     = Get-Date $LastModifiedDate -format 'dd-MMM-yyyy HH:mm'
        'Average version size'              = Get-FileSize -InFileSize $Sizes.Average
        'Total versions size'               = Get-FileSize -InFileSize $Sizes.Sum
        'Last Modified by'                  = $LastModifiedBy
        'Last SnapShot Date'                = Get-Date $FileData[0].SnapShotDate -format 'dd-MMM-yyyy HH:mm'
        DocId                               = $File
        Site                                = $WebURL
        FileURL                             = $FileURL
        TotalSizeBytes                      = $Sizes.Sum
 
    }
    $FilesReport.Add($ReportLine)
 
}
 
$FilesReport = $FilesReport | Sort-Object {$_.'Last modified' -as [datetime]} -Descending
$FilesReport | Out-GridView -Title ("SPO File Versions Report for the {0} site" -f $WebURL)
 
$MostVersions = ($FilesReport | Sort-Object {'Count of versions'} -Descending | Select-Object -First 1)
$MostStorage = ($FilesReport | Sort-Object {[int]($_.'TotalSizeBytes') } -Descending | Select-Object -First 1)
[array]$Folders = $FilesReport | Group-Object -Property Folder -NoElement | Sort-Object Count -Descending
$TotalStorageUsed = Get-FileSize -InFileSize ($FilesReport | Measure-Object -Property 'TotalSizeBytes' -Sum).Sum
 
# Display some details of what we found
 
Write-Host ""
Write-Host ("Summary of the SPO File Versions Report for the {0} site" -f $WebURL)
Write-Host ""
Write-Host ("Individual files processed: {0}" -f $SPOFiles.Count)
Write-Host ("File versions processed:    {0}" -f $Report.Count)
Write-Host ("Total storage used:         {0}" -f $TotalStorageUsed)
Write-Host ("File with most versions     {0}/{1} ({2})" -f $MostVersions.Folder, $MostVersions.Document, $MostVersions.'Count of versions')
Write-Host ("File using most storage:    {0}/{1} ({2})" -f $MostStorage.Folder, $MostStorage.Document, $MostStorage.'Total Versions Size')
Write-Host ""
Write-Host "Folders including files with versions"
Write-Host "-------------------------------------"
$Folders | Format-Table Name, Count -AutoSize
 
# Create some output files
If ($Report.count -gt 10000) {
    $TimeWarning = "The detailed report is large and might take some time to process"
} Else {
    $TimeWarning = $null
}
 
If (Get-Module ImportExcel -ListAvailable) {
    $ExcelGenerated = $True
    Write-Host "Saving output Excel worksheets in your Downloads folder"
    If ($TimeWarning) {
        Write-Host $TimeWarning
    }
    Import-Module ImportExcel -ErrorAction SilentlyContinue
    $SummaryOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO File Versions Summary Report.xlsx"
    If (Get-Item $SummaryOutputFile -ErrorAction SilentlyContinue) {
        Remove-Item $SummaryOutputFile -ErrorAction SilentlyContinue
    }
    $FilesReport | Export-Excel -Path $SummaryOutputFile -WorksheetName "SPO File Versions Detail" `
        -Title "SPO File Versions Summary Report" -TitleBold -TableName "SPOFileVersions"
    $DetailedOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO File Versions Detailed Report.xlsx"
    If (Get-Item $DetailedOutputFile -ErrorAction SilentlyContinue) {
        Remove-Item $DetailedOutputFile -ErrorAction SilentlyContinue
    }
    $Report | Export-Excel -Path $DetailedOutputFile -WorksheetName "SPO File Versions Details" `
        -Title "SPO File Versions Details" -TitleBold -TableName "SPOFileVersionsDetails"
} Else {
    Write-Host "Saving output CSV files in your Downloads folder"
    $SummaryCSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO File Versions Summary Report.CSV"
    $FilesReport | Export-Csv -Path $SummaryCSVOutputFile -NoTypeInformation -Encoding Utf8
    $DetailedCSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO File Versions Detailed Report.CSV"
    $Report | Export-Csv -Path $DetailedCSVOutputFile -NoTypeInformation -Encoding Utf8
}
 
# Display some details of what we found
 
Write-Host ""
Write-Host ("Summary of the SPO File Versions Report for the {0} site" -f $WebURL)
Write-Host ""
Write-Host ("Individual files processed: {0}" -f $SPOFiles.Count)
Write-Host ("File versions processed:    {0}" -f $Report.Count)
Write-Host ("Total storage used:         {0}" -f $TotalStorageUsed)
Write-Host ("File with most versions     {0}/{1} ({2})" -f $MostVersions.Folder, $MostVersions.Document, $MostVersions.'Count of versions')
Write-Host ("File using most storage:    {0}/{1} ({2})" -f $MostStorage.Folder, $MostStorage.Document, $MostStorage.'Total Versions Size')
Write-Host ""
Write-Host "Folders including files with versions"
Write-Host "-------------------------------------"
$Folders | Format-Table Name, Count -AutoSize
Write-Host ""
 
If ($ExcelGenerated) {
    Write-Host ("Excel worksheets generated in your Downloads folder: {0}, {1}" -f $SummaryOutputFile, $DetailedOutputFile)
} Else {
    Write-Host ("CSV files generated in your Downloads folder: {0}, {1}" -f $SummaryCSVOutputFile, $DetailedCSVOutputFile)
}