Need Assistance Modifying powershell script for libraries over 5,000 records

Regular Contributor

Microsoft support assisted me with getting this script setup so that I could get a bug fixed (which they are now saying is by design) that is preventing files from being moved.  For some reason, which Microsoft is saying is caused by Adobe Acrobat )even though we don't own that product nor do we have checkouts enabled in SharePoint), causes pdf files to show as checked out but is not listed in the library setting to list files without checked in version.  Therefore, we cannot fix them.

 

I need to move most of our existing libraires and because we have many pdfs, at least each subfolder won't move because it thinks the file is checked out.  The only way to move it, is to go into the subfolder (which could actually be 4-5 subfolders deep, cancel the checkout then manually move the single pdf.  Depending on how many files, in how many subfolders, this can take HOURS.  

I contacted Microsoft and they were able to provide me with a powershell command that would take any file checked out and check it in so that I wouldn't run into this issue the next time I had to move one of our libraries/sites.  

 

I tried to run it today, however, this library has more than 5,000 items so it won't work anymore.  I asked Microsoft and now they're saying they don't support powershell and that I should have thought about the 5,000 item threshold before I allowed them to go over.  Very helpful.

 

I will share this great working PS command, however, I need some guidance on how it can be changed to do the checks/updates in batches of under 5,000 files so the limit isn't hit.  The one workaround for this is if you use MFA like we do, you have to setup an app password as it won't work for MFA.  

The script also requires you to install Sharepoint Software Developer Kit (SDK).

1 Reply
Here is the script:

#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://tenantname.sharepoint.com/subsite/"
$LibraryName="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($LibraryName)

#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 items: sharepoint online powershell bulk check in
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)"

#Check in the document
$File.CheckIn("Checked-in By Administrator through PowerShell!", [Microsoft.SharePoint.Client.CheckinType]::MajorCheckIn)
$Ctx.ExecuteQuery()
Write-Host -f Green "File '$($File.Name)' Checked-In Successfully!"
}
}
While($Query.ListItemCollectionPosition -ne $Null)
}
Catch {
write-host -f Red "Error Check In Files!" $_.Exception.Message
}


#Read more: https://www.sharepointdiary.com/2017/07/sharepoint-online-powershell-to-bulk-check-in-all-documents....