SharePoint Online: Find All Checked Out Files using PowerShell
Requirement: Find All Checked Out files in SharePoint Online.
A bit of background: The client has a large document library with a bunch of checked-out documents and wants them to make available to end users. So, I tried creating a view that filters all checked-out files, but it failed because of the list view threshold issue since the list item count is > 5000!
How to Find Checked out Documents in SharePoint Online?
To get all checked out files from a SharePoint Online document library, follow these steps:
- Navigate to the document library, and make sure the “Checked Out To” column is added to the List View.
- Sort-Filter the view based checked out to column to get all checked out documents.
PowerShell to Get All Checked Out Documents in SharePoint Online Library
Here is the SharePoint Online PowerShell to get checked out files from a given document library:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Documents"
Try{
#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
#Get the Web
$Web = $Ctx.Web
$Ctx.Load($Web)
$Ctx.ExecuteQuery()
#Get the list
$List = $Web.Lists.GetByTitle($ListName)
#Prepare the query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
</Where>
</Query>
<RowLimit Paged='TRUE'>2000</RowLimit>
</View>"
#Batch Process List items
Do {
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
#Get All Checked out files
ForEach($Item in $ListItems)
{
#Get the Checked out File data
$File = $Ctx.Web.GetFileByServerRelativeUrl($Item["FileRef"])
$Ctx.Load($File)
$CheckedOutByUser = $File.CheckedOutByUser
$Ctx.Load($CheckedOutByUser)
$Ctx.ExecuteQuery()
Write-Host -f Yellow "Found a Checked out File '$($File.Name)' at $($Web.url)$($Item['FileRef']), Checked Out By: $($CheckedOutByUser.LoginName)"
}
}
While($Query.ListItemCollectionPosition -ne $Null)
}
Catch {
write-host -f Red "Error Getting Checked Out Files!" $_.Exception.Message
}
Find All Checked Out Files in a SharePoint Online Site Collection:
Let’s find all checked-out files in a SharePoint Online site collection and export the data to a CSV file using PowerShell.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Function Get-SPOCheckedOutFiles([String]$SiteURL,[String]$ReportOutput)
{
Try{
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
#Get the Web
$Web = $Ctx.Web
$Ctx.Load($Web)
$Ctx.Load($Web.Webs)
$Ctx.ExecuteQuery()
#Get All Lists from the web
$Lists = $Web.Lists
$Ctx.Load($Lists)
$Ctx.ExecuteQuery()
#Prepare the CAML query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
</Where>
</Query>
<RowLimit Paged='TRUE'>2000</RowLimit>
</View>"
#Array to hold Checked out files
$CheckedOutFiles = @()
Write-host -f Yellow "Processing Web:"$Web.Url
#Iterate through each document library in the web
ForEach($List in ($Lists | Where-Object {$_.BaseTemplate -eq 101}) )
{
Write-host -f Yellow "`t Processing Document Library:"$List.Title
#Exclude System Lists
If($List.Hidden -eq $False)
{
#Batch Process List items
Do {
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
#Get All Checked out files
ForEach($Item in $ListItems)
{
#Get the Checked out File data
$File = $Web.GetFileByServerRelativeUrl($Item["FileRef"])
$Ctx.Load($File)
$CheckedOutByUser = $File.CheckedOutByUser
$Ctx.Load($CheckedOutByUser)
$Ctx.ExecuteQuery()
Write-Host -f Green "`t`t Found a Checked out File '$($File.Name)' at $($Web.url)$($Item['FileRef']), Checked Out By: $($CheckedOutByUser.LoginName)"
$CheckedOutFiles += New-Object -TypeName PSObject -Property @{
FileName = $File.Name
URL = $Web.url+$Item['FileRef']
CheckedOutBy = $CheckedOutByUser.LoginName
}
}
}While($Query.ListItemCollectionPosition -ne $Null)
}
}
#Export the Findings to CSV File
$CheckedOutFiles| Export-CSV $ReportOutput -NoTypeInformation -Append
#Iterate through each subsite of the current web and call the function recursively
ForEach($Subweb in $Web.Webs)
{
#Call the function recursively to process all subsites underneaththe current web
Get-SPOCheckedOutFiles -SiteURL $Subweb.URL -ReportOutput $ReportOutput
}
}
Catch {
write-host -f Red "Error Generating Checked Out Files Report!" $_.Exception.Message
}
}
#Config Parameters
$SiteURL="https://crescent.sharepoint.com/"
$ReportOutput="C:\Temp\CheckedOutFiles.csv"
#Setup Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Delete the Output Report, if exists
if (Test-Path $ReportOutput) { Remove-Item $ReportOutput }
#Call the function
Get-SPOCheckedOutFiles -SiteURL $SiteURL -ReportOutput $ReportOutput
Can I Find All Files Checked Out to a Particular User?
Sure, Just add an IF condition to filter all documents checked out to a particular user:
#Set the Login Name variable
$LoginName=""i:0#.f|membership|salaudeen@crescent.com"
If($CheckedOutByUser.LoginName -eq $LoginName)
{
$File.CheckIn("Checked in By Admin","MajorCheckIn")
}
PnP PowerShell to Get All Checked Out Files in a SharePoint Online Site
Here is the SharePoint Online PowerShell to list all documents checked out in a site collection.
#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$CSVFilePath = "C:\Temp\CheckedOutFiles.csv"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
#Get all document libraries
$CheckedOutFiles = @()
$DocumentLibraries = Get-PnPList | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $False}
#Iterate through document libraries
ForEach ($List in $DocumentLibraries)
{
Write-host "Processing Library:"$List.Title -f Yellow
#Get All Checked out Files of the library
$FilesCheckedOut = Get-PnPListItem -List $List -PageSize 500 | Where {$_["CheckoutUser"] -ne $Null}
#Collect data from each checked-out file
ForEach ($File in $FilesCheckedOut)
{
$CheckedOutFiles += [PSCustomObject][ordered]@{
Library = $List.Title
FileName = $File.FieldValues.FileLeafRef
CheckedOutTo = $File.FieldValues.CheckoutUser.LookupValue
Location = $File.FieldValues.FileRef
}
}
}
#Export Checked out Files data to CSV File
$CheckedOutFiles
$CheckedOutFiles | Export-Csv -Path $CSVFilePath -NoTypeInformation
To Check In multiple files in SharePoint Online using PowerShell, use: SharePoint Online: PowerShell to Check In All Documents