SharePoint Online: Get List Items from Large Lists ( >5000 Items) using PowerShell without List View Threshold Exceeded Error
Problem: When you try to get items from larger lists with more than 5000 items through PowerShell, You get the list view threshold exceeded issue in SharePoint Online!
A typical PowerShell CSOM script to get list items from a list resulted in an error:
“Exception calling “ExecuteQuery” with “0” argument(s): “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
Root Cause: 5000 Item list view threshold in SharePoint Online!
As the error message says, we have 5000 as the hard limit for the list view threshold in SharePoint Online. Any attempt to perform bulk operations such as read/write more than 5000 items in SharePoint Online invokes this issue. Unlike SharePoint on-premises, this threshold limit can’t be increased, unfortunately! So, Let’s see how to use CAML to query large lists in SharePoint Online.
How to get Items from a large list using PowerShell without List view threshold exceeded Error?
How to overcome the list view threshold in SharePoint Online? There are various solutions to deal with this issue. Here is mine: Get list items in batch!
PowerShell to Get List Items from Large Lists:
Here is how to get more than 5000 records using PowerShell in SharePoint Online List without List view threshold exceeded Error.
#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"
#Config Parameters
$SiteURL="https://Crescent.SharePoint.com"
$ListName="Projects"
$BatchSize= 2000
Try {
$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 List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "<View Scope='RecursiveAll'><RowLimit>$BatchSize</RowLimit></View>"
#Get List Items in Batch
Do
{
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
$ListItems.count
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)
}
Catch {
write-host -f Red "Error Getting List Items:" $_.Exception.Message
}
Just make sure your batch is lesser than 5000 to avoid the 5000 limit threshold issue on SharePoint Online!
Get List Items from Larger lists using PnP PowerShell
The PnP PowerShell module provides a “PageSize” switch to handle larger lists in batches:
#Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get all list items from list in batches
$ListItems = Get-PnPListItem -List $ListName -PageSize 500
Write-host "Total Number of List Items:" $($ListItems.Count)
#Loop through each Item
ForEach($Item in $ListItems)
{
Write-Host "Id :" $Item["ID"]
Write-Host "Title :" $Item["Title"]
}
No matter what I try SharePoint Online. I still getting the same error. The only way I can delete large folder is if I Sync the folder with OneDrive and use File Explorer.
This script works great on small folders only
#Config Variables
$SiteURL = “https://mysite.sharepoint.com”
$ListName =”Test”
$FolderServerRelativeURL = “/Test/folder/Folder1/Folder2”
Try {
>> #Connect to PnP Online
>> Connect-PnPOnline -Url $SiteURL -Interactive
>>
>> #Get All Items from Folder in Batch
>> $ListItems = Get-PnPListItem -List $ListName -FolderServerRelativeUrl $FolderServerRelativeURL -PageSize 500
>>
>> Write-host “Total Number of Items Found:”$(ListItems.count)
>>
>> #Get List Items from the folder
>> $ItemsFromFolder = $ListItems | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }
>>
>> #Powershell to delete all files from a folder
>> ForEach ($Item in $ListItems)
>> {
>> Write-Host “Id :” $Item[“ID”]
>> Write-Host “Title :” $Item[“Title”]
>>
>> Remove-PnPListItem -List $ListName -Identity $Item.Id -Recycle -Force
>> Write-host “Removed File:”$Item.FieldValues.FileRef
>> }
>> }
>> Catch {
>> write-host “Error: $($_.Exception.Message)” -foregroundcolor Red
>> }
Error: The attempted operation is prohibited because it exceeds the list view threshold.
The Get-PnPListItem cmdlet with FolderServerRelativeUrl parameter will fail on larger lists and libraries – a known issue. Instead, use: Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }
I changed it and now I get this error
Error: The term ‘ListItems.count’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
Try {
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get All Items from Folder in Batch
$ListItems = Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }
Write-host “Total Number of Items Found:”$(ListItems.count)
#Get List Items from the folder
$ItemsFromFolder = $ListItems | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }
#Powershell to delete all files from a folder
ForEach ($Item in $ListItems)
{
Write-Host “Id :” $Item[“ID”]
Write-Host “Title :” $Item[“Title”]
Remove-PnPListItem -List $ListName -Identity $Item.Id -Recycle -Force
Write-host “Removed File:”$Item.FieldValues.FileRef
}
}
Catch {
write-host “Error: $($_.Exception.Message)” -foregroundcolor Red
}
Instead of “$(ListItems.count)”, use $($ListItems.count)
Hi! I am getting error “Error Getting List Items: Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.” after authentication. Confirmed that my username and password is correct. Can you please help? Thank you!
Could be because your account is MFA Enabled! Try the PnP PowerShell method.
Hi Salaudeen,
You are right. Figured out that it was caused by MFA. The PnP PowerShell method worked for me. Thank you very much for all scripts!
Is there a way to get all the ListNames from a Site? We were originally using $dstList = Get-List -Name $destLib.Name -Site $dstSite but this is returning the threshold limit error as we can have thousands of lists. All the work arounds seem to be for the next level down and get the items within the list
Hello, can you pleae help me, how can i filter( search) in list, which have more than 5000 records? Because if i try to add Where, im ending on Threshold error. Thanks much
Unfortunately, You can’t apply where condition directly on larger lists. However, you can filter list items after retrieving them in batches. E.g.,
#Filter List Items $TimeStamp = (Get-Date).AddDays(-90) $FilteredItems = $ListItems | Where {$_["Created"] -Lt $TimeStamp}How would l do the same for SP 2013. I tried it but get the same message again “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
For SharePoint On-premises, You can either Increase the threshold limits in SharePoint Server or use the batch method to process list items in batches! Reference: Delete All Items from List or Library using PowerShell
How to retreive all items with a specific content type from a large list. If I CAML query , it throws an list threshold error.
Yes! CAML method may end-up in Threshold issue on larger lists. Use: How to Find Content Type usage in SharePoint Online?
I could swear this worked for me the first time though. I’m stepping through it manually. During the loop, the only thing returned is $listitems.count. The data is never returned and the end $listitems.count is what ever was left after the last full 1000. Entering $ListItems to see what it contains, I receive this message:
format-default : The collection has not been initialized. It has not been requested or the request has not been executed. It may need
to be explicitly requested.
+ CategoryInfo : NotSpecified: (:) [format-default], CollectionNotInitializedException
+ FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException,Microsoft.PowerShell.Commands.FormatDefaul
tCommand
Would you be able to post a version of this where it also checks out the files as well? I am trying to combine this script with the one posted here: https://www.sharepointdiary.com/2017/07/sharepoint-online-powershell-to-bulk-check-in-all-documents.html
Sure! Article updated to handle larger lists.
Any way to pull all the list columns in a csv file using this logic?
Use: SharePoint Online: Export List Items to CSV using PowerShell
Any wayto export the list ina csv file using this logic?
Hi! I’m using this to try and export a large list I have. It ran successfully, but I would like to know where this export went? I read through the code, but I’m not sure how this data is exported.
Thanks for the assistance
Usually on the same directory where your script is saved. Or in your Default Home directory (Start >> Run >> type “.” without quote and Enter)