Sharepoint Online get all files with no checked in version via powershell script with lists larger than 5000

Question

I'm wondering if anyone has a solution to this. I'm trying to write a powershell script, that would get all files that do not have checked in version for lists with files of about 150k and above. I've tried multiple options but with any of the options, I either can't get the list of files without a checked in version or the document library size is causing me issues. I've used multiple CAMLQueries and I can't seem to get the results I'm looking for.

For this CAML Query, it doesn't give me the files without a checked in version only all the other files

$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @" <View Scope='RecursiveAll'> <Query> <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy> </Query> <RowLimit Paged="TRUE">$BatchSize</RowLimit> </View> "@

For the CAMLQuery, I don't get any results. It doesn't seem work in getting any results even though I know there are files without a checked in version.
$CAMLQuery = " <View Scope='RecursiveAll'> <Query> <Where> <IsNotNull><FieldRef Name='CheckoutUser'/></IsNotNull> </Where> </Query> <RowLimit Paged='TRUE'>500</RowLimit> </View>"

Also, I've even tried using the below command; however, I can't seem to get past the 5000 threshold limit. I get the error, The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
$List = $Ctx.Web.lists.GetByTitle($ListName)
$CheckedOutFiles1 = $List.GetCheckedOutFiles()
$Ctx.Load($List)
$Ctx.Load($CheckedOutFiles1)
$Ctx.ExecuteQuery()

I've even tried PnP, Get-PnPListItem, but this doesn't seem to capture any of the files that have no checked in version.

None of the above options work to giving me the results I'm looked for. Does anyone have a script that would give me the a list of files that don't have a checked in version or can help me solve this problem. I can post my script if it would help in getting a resolution, I'm just stuck right now.

Thanks.

Answer 1

We couldn't find all files without checked in version via CAML query in SharePoint online.

We recommend that you move excess files to another library and use List.GetCheckedOutFiles method:

#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"
    
    
 $userName = "Your user name"
 $pwd = Read-Host -Prompt 'Enter your password' -AsSecureString
    
 $context = New-Object Microsoft.SharePoint.Client.ClientContext("Your site URL")
    
 $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $pwd)
    
    
 $web = $context.Web;
 $list = $web.Lists.GetByTitle("Library Name")
 $files = $list.GetCheckedOutFiles()
 $user = $web.CurrentUser;
    
 $context.Load($list);
 $context.Load($user);
 $context.Load($files);
 $context.ExecuteQuery();
    
    
 foreach($file in $files)
 {
    write-host($file.ServerRelativePath.DecodedUrl)
 }

Answer 2

Another option to get all the Checked out files from a SPO Document Library that has more than 5K documents would be to use MS Flow with Send an HTTP request to SharePoint action. I just tested with a library that has 12K documents. Please note that I had to create an index on Checked Out To column in the Library. I had 3 docs checked out and it returned the properties for those three documents.

Uri for Send an HTTP request to SharePoint

_api/web/lists/getbytitle('DocumentLibrary')/items?$filter=CheckoutUserId ne null