As a SharePoint administrator, sometimes, you may need to find and retrieve the checked-out files. In this post, we will share CSOM based PowerShell script to get a list of all the checked-out files in a SharePoint Online document library. The script also exports the checked-out file details to a CSV file.
To use the SharePoint client object model (CSOM) in PowerShell, we need to install Microsoft SharePoint Online Client SDK components and load the required assembly files.
Export All Checked-Out File details to CSV
The following Powershell script exports all the documents in a SharePoint Online document library which are checkout by the current user or any other user.
With CSOM, we can use the Caml Query to filter and retrieve only the checked-out files. Also, the script queries the list items page by page (3000 items on a single page) to avoid the List View Threshold error.
#Add required references to SharePoint client assembly to use CSOM
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
$Result = @() #Result array
#Provide the SPO Site URL
$SiteUrl = "https://contoso.sharepoint.com/sites/sitename"
#Provide the List/Document Library name
$ListName = "Documents"
#Get SPO Site Admin Credentials
$Cred = Get-Credential
#Initialize the Site context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Load the web
$Ctx.Load($Ctx.Web)
$Ctx.ExecuteQuery()
#Load the list
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
$CamlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$CamlQuery.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
</Where>
</Query>
<RowLimit Paged='TRUE'>3000</RowLimit>
</View>"
#Query List items Page by Page to avoid List View Threshold error
Do {
#Query All Checked out files
$ListItems = $List.GetItems($CamlQuery)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Set the next page position
$CamlQuery.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
$i=0;
$TotalItems=$ListItems.Count;
#Iterate checked-out files and add it to the Result array
ForEach($Item in $ListItems)
{
$i++;
Write-Progress -activity "Processing $($Item["FileRef"])" -status "$i out of $TotalItems files completed"
#Get the Checked out File details
$File = $Ctx.Web.GetFileByServerRelativeUrl($Item["FileRef"])
$Ctx.Load($File)
$CheckedOutByUser = $File.CheckedOutByUser
$Ctx.Load($CheckedOutByUser)
$Ctx.ExecuteQuery()
#Add the file details to the Result array
$Result += New-Object PSObject -property $([ordered]@{
FileName = $File.Name
FileUrl = $Item['FileRef']
CheckedOutBy = $CheckedOutByUser.Email
})
}
}
While($CamlQuery.ListItemCollectionPosition -ne $Null) #Check next page is available or not
#Export the result to a CSV file
$Result | Export-CSV "C:\Temp\All-Checked-Out-Files.CSV" -NoTypeInformation -Encoding UTF8
Hi there! This blog post couldn't be written any better!
Reading through this post reminds me of my previous roommate!
He continually kept talking about this. I will send this article to
him. Pretty sure he will have a great read. I appreciate you for sharing!