0

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.

0

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)
 }
1
  • Thanks, though breaking up a library of over 290,000 files into 3 separate libraries, I've been told could take days for the proper actions in the backend to occur. Is this true, or could we utilize the GetCheckedOutFiles method immediately. Also, is it even possible to utilize the GetCheckedOutFiles against folders instead of a library. I'm pretty sure the answer is no, but thought I'd ask anyways
    – helpo11
    Oct 6 '20 at 15:51
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.