Avoiding the 5000 limit on SharePoint Online
This article will go over one way to get around the hard limit of 5000 items on SharePoint Online, when looking to interact with a view etc..
Introduction
Recently I was tasked to download all data from a clients SharePoint Online list. The data in question were attachments to list items (sigh) so I had a requirement to get all the items which were over 20 thousand. And with that I kept getting blocked by the 5000 limit. Even if batched cause the list is over the limit it just wouldn't work so I had a thought... Why not batch it myself? This is needed due to how the query grabs the items, no matter the filter if you bring back more than 5000 items it will error.
Why is there a limit?
This is a limitation on the SQL side of things rather than SharePoint. It's there to help execution times on queries, with SharePoint on-premises an administrator has the ability to increase this at web application level. Unfortunately this isn't the case with SharePoint Online, this is a hard limit which means work around's are needed.
What's the workaround?
How I approached this was to have the user enter a batch of IDs that they wanted to download. Additionally, they could find out the amount of items in the list (via site contents) and use that as their guide. The current script only has user intervention at the moment, this can be modified to get the count of the items etc..
So what's first? Well let's create a function:
function Set-CAMLQuery(){
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = "@<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>$number</Value></Eq></Where></Query></View>"
return $query
}
This function allows us to create a dynamic CAML query to retrieve the item with the ID it's given. So later in the script it'll loop through the range given.
Now we have the query.. What about connecting to your SPO tenant?
#User credentials user doesn't have to add each time it's run (testing only, you'd want to prompt when in production)
$UserName = "username@domain.com"
$Password = "yourPasswordHere"
$SecurePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
$ListName = "yourListNameHere"
#Setup the Context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$fields = $List.Fields
$Ctx.Load($fields)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
In the parameters you can add your credentials in for testing, in the real world this wouldn't be in the code in case it was compromised.
Let's prompt the user for a range:
#Asking user to input range of ID's they want to use
$batchRange = Read-Host "Range of SharePoint IDs (e.g. 100..150)"
while($batchRange -notmatch '^\d+\.\.\d+$'){
Write-Host "ERROR: Input not in correct format." -ForegroundColor Red
$batchRange = Read-Host "Range of SharePoint IDs (e.g. 100..150)"
}
$NumberRange = Invoke-Expression $batchRange
This will prompt the user to add a range, if it's not written correctly it'll fail and give an example
Now the actual process:
foreach($number in $numberRange){
#using the number in the range, get the query to look for the list item
$Query = Set-CAMLQuery $number
#Get List Items
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
#Check if items are returned, if none then stop script
if($ListItems.Count -eq 0 -or $ListItems.Count -eq $null){
#YOUR CODE HERE#
}
}
This process will use the query to go off and get that individual item based off the ID. There will be a case where the item has been deleted, in that case use a TRY/CATCH in your code that way you can handle the error correctly.
If you don't want any user interaction there is an update that can be made to the script:
$totalCount = $List.Count
Then build the range
$batchrange = "1..$($totalCount)"
Doing this will set the batch number to be the complete list. What I also found with this while running at a client site, when the internet connection dropped, or if someone unplugged the power the script wouldn't fail and once started it just continued.
Summary
This is one of many ways to get around the 5000 limit where no matter the view used you hit the limit. You can download the full script here