Find checked out files across multiple Site Collections and libraries in SPO using PowerShell PnP

It might be another day, and you might have another requirement!
In today’s blog post, we’ll have a look at we can find files that are checked out in SharePoint Online and extend the search across multiple Site Collections, multiple document libraries, using PowerShell PnP.

 

The requirements

Requirements are simple: Find all the checked out files in multiple sites, and multiple document libraries in my Office 365 tenant.

 

The script

And here we start writing down the logic 🙂

  1. Get a list of all the sites to search in a csv file
  2. Loop through all those sites by connecting to them
  3. Loop through all the libraries in each site
  4. Loop through each document and verify if they’re checked out or not
  5. Export the whole lot!

 

⚠️ Your CSV file must contain the following headers or change the properties in the script:  “SiteUrl” (full site URL) and “SiteName” (name of the site)

<#
.SYNOPSIS
    Get the checked out files.
.DESCRIPTION
    Get the checked out files from multiple sites in SharePoint Online.
    Provide a CSV file with all your sites, and the script will loop through each sites and document libraries.
.EXAMPLE
    PS C:\> .\Get-CheckedOutFiles.ps1
    Retrieve a list of all documents currently checked out in the sites you provided in the CSV, with the name of the person it's checked out to.
.INPUTS
    Inputs (if any)
.OUTPUTS
    Output (if any)
.NOTES
    None
#>
#Start Time
$startTime = "{0:G}" -f (Get-date)
Write-Host "*** Script started on $startTime ***" -f Black -b DarkYellow

# +++ CHANGE TO YOUR VALUES +++
$tenantName = "<YOUR_TENANT_NAME>"
$sitesCSV = "<PATH_FOR_CSV_FILE"


#Connect to SPO Admin Center
Connect-PnPOnline -Url "https://$tenantName-admin.sharepoint.com/"

$result = @()
$allSites = Import-Csv -Path $sitesCSV
$logFile = "C:\Users\$env:USERNAME\Desktop\CheckedOutFiles.csv"

foreach ($site in $allSites) {
    Write-Host "Connecting to: $($site.SiteUrl)" -ForegroundColor Cyan
    Connect-PnPOnline -Url $($site.SiteUrl)

    #Get all libraries
    $allLists = Get-PnPList ##If you want to target specific libraries -->> | Where-Object {($_.Title -like "documen*")}
    
    foreach ($list in $allLists) {
        $allDocs = (Get-PnPListItem -List $list) 
    
        foreach ($doc in $allDocs) {

            if ($null -ne $doc.FieldValues.CheckoutUser.LookupValue) {
                $result += [PSCustomObject][ordered]@{
                    Site            = $site.SiteName
                    Library         = $list.Title
                    FileName        = $doc["FileLeafRef"]
                    CheckedOutTo    = $doc.FieldValues.CheckoutUser.LookupValue
                    FullLocation    = $doc["FileRef"]
                }
            }
        }
    }
}
$result | Export-Csv -Path $logFile -NoTypeInformation

#End Time
$endTime = "{0:G}" -f (Get-date)
Write-Host "*** Script finished on $endTime ***" -f Black -b DarkYellow
Write-Host "Time elapsed: $(New-Timespan $startTime $endTime)" -f White -b DarkRed

 

Checked out files output

You can also target specific document libraries…
For example, to go through the Documents only by adding Where-Object {($_.Title -like "documen*")} (commented out in the above script)

 

That’s it! 🤓

 

 

4 Responses to “Find checked out files across multiple Site Collections and libraries in SPO using PowerShell PnP

  • Sorry I’m not a Powershell expert. so this is exactly what I need since we have a customer who has a staff managing the documents inside 2 document libraries but she normally gets lost of what files are check out and had to browse both doc library manually and look into each file.

    1. I assume the highlighted green ones are needed to be edited by us. $siteCSV this csv file contains all your doc libraries or sites? If yes, what’s the format?

    2. Connect to Sharepoint Online Connect-PnPOnline didn’t work for me but when I tried Connect-SPOnline so not sure if I’m doing it wrong?

    • Veronique Lengelle
      3 weeks ago

      Hi Jeff,
      Indeed $siteCSV is your csv file containing 2 headers called: “SiteUrl” and “SiteName”.
      Your “SiteUrl” format is the full URL of your sites. For example: https://tenant-name.sharepoint.com/sites/site1. So the script will loop through all those sites to find the files. If you have only 1 site, it’s OK, just have a csv file with 1 site url.

      This script uses the PowerShell PnP module (https://github.com/pnp/PnP-PowerShell), not the SharePoint Online module (from Microsoft). So if you don’t have it installed on your machine, that’s why you get an error.

  • Hi can I ask a question about the script?

Leave a Reply

%d bloggers like this: