Reputation: 41
I have a library that has about 25k items in it. I have a csv with a couple folders that I want to grant item level permission on. But first, to do that I need to retrieve all the items from the library. As expected I get an error about threshold limit enforced by the administrator. I was wander if there was a way to get around this with PnP.
I basically used Get-PnPListItem -List Documents-PageSize 1000
to try an limit the number of items returned at once, but I still got the error message. I also tried the code below as well using a caml query;
that also didn't work.
$filename = test.docx
Get-PnPListItem -List Documents -Query
"<View><Query><Where><Eq><FieldRef Name='FileLeafRef'/>
<Value Type='File'>' +$filename+ '</Value></Eq></Where></Query></View>"
I was only expecting one result to show, which is when the filename is found in the document library. So I'm not sure why the error message is showing. Any ides?
Upvotes: 4
Views: 22079
Reputation: 71
If you use pnp.powershell you can do it in this way:
Connect-PnPOnline -Interactive
$ListName = "My list"
Get-PnPListItem -List $ListName -PageSize 2000 -ScriptBlock { Param($items) $items.Context.ExecuteQuery()} | ForEach-Object {
$_ #This is the list Item
}
This script will collect all list items even more than 5000 in batches of 2000.
Upvotes: 7
Reputation: 41
For SPO PNP.PowerShell
actually just need to use -PageSize 5000
.
Upvotes: 0
Reputation: 275
Its not supposed to work like that.
If you need to query on large lists, be sure to add Indexes for the field(s) you are querying.
But this will still NOT make your query limited by -PageSize or the RowLimit tag in your query using PnP-Powershell.
You will need to use the -Scriptblock option to process items in batches.
Upvotes: 0
Reputation: 865
This does work with the pnp.powershell module to get more rows out then 5000.
$query = "<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>"
$itemsInList = Get-PnPListItem -List 'TestList' -Query $query
Upvotes: 0
Reputation: 1889
This is a known issue. If you want to retrieve some items from a large list (over 5000 items for SharePoint Online), there is no possible way to use the -Query parameter to Get-PnPListItem. This is distressing because the main reason you're probably using -Query is that there are a lot of items and you want them filtered server-side to avoid excessive network bandwidth etc.
If you use -Query and also specify -PageSize, and/or put in the CAML query, it seems to have no effect and the server responds with "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".
The only workaround for this while still using PnP-PowerShell is to remove the -Query, get every item with -PageSize, then process the items locally with foreach/select/where-object. For more details, please refer to:
Or you can have a try CSOM cmdlets and add the filtered column into indexs list:
BR
Upvotes: 2
Reputation: 35970
You can download the list into Excel. In the List view, click Export to Excel. It will export all visible columns and all items. Then manipulate the Excel file and save as CSV.
Or, you can use Power Query to query the list and load it into Excel.
Both approaches will support way more than 25K rows.
Upvotes: 0