Bulk Check Files Count

Use PnP PowerShell to check file count in bulk using a CSV file.

#########################
# This account has to be a SharePoint Admin and also should connect to PnPOnline with this account before running the script
# Connect-PnPOnline -Url "https://x-admin.sharepoint.com" -Interactive
#########################
$SiteCollAdmin = "admin email"

# Parameters
$CSVInputFile = "Wave1.csv"  # CSV file with site URLs
$CSVFileCountOutputFile = "FileCount.csv"

# Delete the Output report file if exists
#If (Test-Path $CSVFileCountOutputFile) { Remove-Item $CSVFileCountOutputFile }

# Read the list of site URLs from the CSV file
$SiteURLs = Import-Csv -Path $CSVInputFile

$ExcludedLists = @("Form Templates", "Preservation Hold Library", "Site Assets", "Pages", "Site Pages", "Images",
                   "Site Collection Documents", "Site Collection Images", "Style Library")

ForEach ($Site in $SiteURLs) {
    Write-Host "Processing site:" $Site.SiteUrl

    Set-PnPTenantSite -Url $Site.SiteUrl -Owners $SiteCollAdmin -ErrorAction Stop
    
    # Connect to each site collection
    Connect-PnPOnline -Url $Site.SiteUrl -Interactive

    $Lists = Get-PnPList | Where-Object {
        $_.Hidden -eq $False -and $_.Title -notin $ExcludedLists -and $_.BaseType -eq "DocumentLibrary"
    }

    $TotalFileCount = 0  # Initialize total file count for the site

    # Iterate through all files from all document libraries
    ForEach ($List in $Lists) {
        if ($List.ItemCount -eq 0) {
            Write-Host "Skipping list '$($List.Title)' as it has no items."
            continue
        }
        
        $global:counter = 0
        $FolderItems = Get-PnPListItem -List $List -PageSize 500 -Fields FileLeafRef -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete `
            ($global:Counter / ($List.ItemCount) * 100) -Activity "Getting Items from List:" -Status "Processing Items $global:Counter to $($List.ItemCount)";}  | Where {$_.FileSystemObjectType -eq "Folder"}
        Write-Progress -Activity "Completed Retrieving Folders from List $List" -Completed
 
        $FolderStats = @()
        # Get Files count on each folder in the library
        ForEach($FolderItem in $FolderItems)
        {
            # Get Files of the Folder
            Get-PnPProperty -ClientObject $FolderItem.Folder -Property Files | Out-Null
     
            # Collect data
            $FilesCount = $FolderItem.Folder.Files.Count
            $Data = [PSCustomObject][ordered]@{
                FolderName = $FolderItem.FieldValues.FileLeafRef
                URL        = $FolderItem.FieldValues.FileRef
                FilesCount = $FilesCount
            }
            $FolderStats += $Data

            # Add to total file count
            $TotalFileCount += $FilesCount
        }

        # Export the data to CSV
        $FolderStats | Export-Csv -Path $CSVFileCountOutputFile -NoTypeInformation -Append

    }

    # Add total file count for the site to the CSV
    $TotalFileCountData = [PSCustomObject][ordered]@{
        FolderName = "Total Files"
        URL        = $Site.SiteUrl
        FilesCount = $TotalFileCount
    }
    $TotalFileCountData | Export-Csv -Path $CSVFileCountOutputFile -NoTypeInformation -Append

    Remove-PnPSiteCollectionAdmin -Owners $SiteCollAdmin
}

Write-Host "Report generation completed. Output file: $CSVFileCountOutputFile"