Client Side Object Model (CSOM) File Office 365 PnP PowerShell PowerShell SharePoint SharePoint Online 

SharePoint Online: Find All Checked Out Files using PowerShell

Salaudeen Rajack 0 Comments , , , ,

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, tried creating a view which 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 SharePoint Online document library, follow these steps:

  • Navigate to the document library, Make sure “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.
    sharepoint online powershell get checked out files

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 collections 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

#Set the Login Name variable
$LoginName=""i:0#.f|membership|[email protected]""

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}

#Set CAML Query to Filter Checked-out Files
$CAMLQuery = "
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
        </Where>
    </Query>
</View>"

#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 -Query $CAMLQuery -PageSize 500
    
    #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

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

Leave a Reply