kwizcom banner advertisement

SharePoint Online: Find All Checked Out Files using PowerShell

Requirement: Find All Checked Out files in SharePoint Online

A bit background: Client have a large document library with bunch of check out documents and want them to make available to end users. So, tried creating a view which filters all checked out files, but it failed because of list view threshold issue, since the list item count is > 5000!

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:
Lets 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|salaudeen@crescent.com""

If($CheckedOutByUser.LoginName -eq $LoginName)
{
    $File.CheckIn("Checked in By Admin","MajorCheckIn")
}

To Check In multiple files in SharePoint Online using PowerShell, use: SharePoint Online: PowerShell to Check In All Documents
SharePoint Online: Find All Checked Out Files using PowerShell SharePoint Online: Find All Checked Out Files using PowerShell Reviewed by Salaudeen Rajack on June 08, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.