Get-SharePointSiteStorageReport

<#
.SYNOPSIS
    Get-SharePointSiteStorageReport.ps1 - Reports storage usage across SharePoint sites.

.DESCRIPTION
    Uses PnP PowerShell to list every site collection in the tenant with its
    storage usage and quota, sorted by percent full, so you know which sites
    are actually driving your SharePoint storage bill before you buy more.

.AUTHOR
    Shehryar Hassan

.EXAMPLE
    .\Get-SharePointSiteStorageReport.ps1 -TenantAdminUrl "https://contoso-admin.sharepoint.com"
#>

param(
    [Parameter(Mandatory=$true)]
    [string]$TenantAdminUrl,

    [Parameter(Mandatory=$false)]
    [string]$OutputPath = ".\SPStorageReport.csv"
)

Write-Host "=============================================" -ForegroundColor Cyan
Write-Host "  SharePoint Site Storage Report" -ForegroundColor Cyan
Write-Host "=============================================" -ForegroundColor Cyan

Connect-PnPOnline -Url $TenantAdminUrl -Interactive

$sites = Get-PnPTenantSite -Detailed

$report = $sites | Select-Object Url, Title,
    @{n = 'UsedGB'; e = { [math]::Round($_.StorageUsageCurrent / 1024, 2) } },
    @{n = 'QuotaGB'; e = { [math]::Round($_.StorageQuota / 1024, 2) } },
    @{n = 'PercentFull'; e = {
        if ($_.StorageQuota -gt 0) { [math]::Round(($_.StorageUsageCurrent / $_.StorageQuota) * 100, 1) } else { 0 }
    } } |
    Sort-Object PercentFull -Descending

$report | Export-Csv -Path $OutputPath -NoTypeInformation

$totalUsedGB = [math]::Round((($sites.StorageUsageCurrent | Measure-Object -Sum).Sum) / 1024, 2)
Write-Host "[SUCCESS] $($sites.Count) sites written to: $OutputPath" -ForegroundColor Green
Write-Host "[INFO] Total storage in use across the tenant: $totalUsedGB GB" -ForegroundColor Cyan

$hot = $report | Where-Object PercentFull -gt 80
if ($hot) {
    Write-Host "[WARNING] $($hot.Count) site(s) over 80% of quota:" -ForegroundColor Red
    $hot | Format-Table Title, UsedGB, QuotaGB, PercentFull -AutoSize
}