Find checked out files across multiple Site Collections using PowerShell

22-Feb-2020 22-Feb-2020 482 words 3 minutes

Contents

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.

Requirements

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

Script

And here we start writing down the logic 🙂

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

CSV format

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<#
.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

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)

Thanks for reading!