Export checked-out files in all sites associated with a hub site to CSV

Summary

This script will export all Checked-Out files in all SharePoint Online sites associated with a Hub site to a CSV file.

Prerequisites

  • The user account that runs the script must have SharePoint Online tenant administrator access.
  • Before running the script, edit the script and update the variable values in the Config Variable section, such as SharePoint Tenant Admin URL, Hub Site URL, and the CSV output file path.

Screenshots

Screen Output

Screen Output

CSV Output

CSV Output


#Config variables
$tenantAdminURL = "https://contoso-admin.sharepoint.com"
$hubSiteURL = "https://contoso.sharepoint.com/sites/LegalHub"
$reportOutput = "C:\Temp\CheckedOutFiles.csv"

#Connect to SharePoint Online admin site 
$cred = Get-Credential
Connect-PnPOnline -Url $tenantAdminURL -Credentials $cred

#Get SPO sites
$spoSites = Get-PnPTenantSite -Detailed 

#Get hub site
$hubSite = Get-PnPHubSite -Identity $hubSiteURL
 
#Get associated sites with hub
$associatedSites = $spoSites | Where-Object { $_.HubSiteId -eq $hubSite.Id }

#Iterate through associated sites to find Checked-out files
if ($associatedSites -and $associatedSites.Count -gt 0) {
    ForEach ($site in $associatedSites) {
        if ($hubSite.SiteUrl -ne $site.Url) {
            Try {
                #Connect to the site
                Write-Host "Connect to : " $site.Url -f Green
                $siteConn = Connect-PnPOnline -Url $site.Url -Credentials $cred -ReturnConnection
 
                #Get all document libraries from the site
                $documentLibraries = Get-PnPList -Connection $siteConn | Where-Object { $_.BaseType -eq "DocumentLibrary" -and $_.ItemCount -gt 0 -and $_.Hidden -eq $False }
 
                #Iterate through document libraries in site
                ForEach ($library in $documentLibraries) {
                    
                    Write-host "Checking Library : " $library.Title -f Yellow

                    #CAML Query to filter Checked-out files
                    $query = "<View Scope='RecursiveAll'><Query><Where><IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull></Where></Query></View>"

                    #Get all Checked-out files of the library
                    $checkedOutFiles = Get-PnPListItem -List $library -Query $query
     
                    #Get details of each checked-out file
                    $results = @()                    
                    ForEach ($file in $checkedOutFiles) {
                        $results += [PSCustomObject][ordered]@{
                            LibraryName     = $library.Title
                            CheckedOutTo    = $file.FieldValues.CheckoutUser.LookupValue
                            CheckedOutSince = ($file.FieldValues.Last_x0020_Modified -as [datetime]).DateTime                           
                            FileSizeMB      = [Math]::Round((($file.FieldValues.File_x0020_Size/1024)/1024),2)
                            FileName        = $file.FieldValues.FileLeafRef
                            FileURL         = $file.FieldValues.FileDirRef
                        }
                        
                        #Export Checked out files data to CSV
                        $results | Export-Csv -Path $reportOutput -Append -NoTypeInformation
                    }                                            
                }
                                           
                Disconnect-PnPOnline -Connection $siteConn
            }
            Catch {
                write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
            }
        }        
    }   
}

Check out the PnP PowerShell to learn more at: https://aka.ms/pnp/powershell

The way you login into PnP PowerShell has changed please read PnP Management Shell EntraID app is deleted : what should I do ?

Contributors

Author(s)
Arash Aghajani

Disclaimer

THESE SAMPLES ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.