Issue with the threshold limit SharePoint online/Office 365

Question

I have an issue, I have a document library with 1,00,000 files and folders,

I want to retrieve all using client object model, when i try to retrieve with "Recursive ALL" it is coming with all records but when i put the CAML query it is throwing error "Error Message the attempted operation is prohibited because it exceeds the threshold limit enforced by the administrator" -

I understand that this is related to threshold settings but i don't want to do any changes to existing threshold settings but i want to retrieve all the records with powershell script and client side object model with caml query

Answer 1

We need to use paging to fetch data from such a large list.

For that we use ListItemCollectionPosition.

Sample code would be as below using CSOM:

ClientContext clientContext = new ClientContext("Test");
List list = clientContext.Web.Lists.GetByTitle("CustomList");
ListItemCollectionPosition position = null;
do
{
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ListItemCollectionPosition = position;
    camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
                <QueryOptions><ViewAttributes Scope='Recursive'/></QueryOptions>
                <RowLimit>1000</RowLimit>
                <Query>
                    <Where>
                        <Eq>
                            <FieldRef Name='Title'/>
                            <Value Type='Text'>Test</Value>
                        </Eq>
                    </Where>
                </Query>
            </View>";
    ListItemCollection listItems = list.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
    position = listItems.ListItemCollectionPosition;
    foreach (ListItem listItem in listItems)
        string itemTitle = Convert.ToString(listItem["Title"]);
}
while(position != null)

Sample code would be as below using powershell:

$list = $ctx.Web.Lists.GetByTitle("CustomList")
$ctx.Load($list)
$ctx.ExecuteQuery()
## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@
## Page Position
$position = $null

## All Items
$allItems = @()
Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $camlQuery.ViewXml = $qCommand
 ## Executing the query
    $currentCollection = $list.GetItems($camlQuery)
    $ctx.Load($currentCollection)
    $ctx.ExecuteQuery()

 ## Getting the position of the previous page
    $position = $currentCollection.ListItemCollectionPosition

 # Adding current collection to the allItems collection
    $allItems += $currentCollection
}
# the position of the last page will be Null
Until($position -eq $null) 

Powershell reference - Get All Items in 5000+ large list with CSOM in PowerShell

Answer 2

var context, 
    web, 
    spItems, 
    position, 
    nextPagingInfo, 
    previousPagingInfo, 
    listName = 'ContactsList', 
    pageIndex = 1, // default page index value 
    pageSize = 4, // default page size value 
    list, 
    camlQuery; 

// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model  
$(document).ready(function () { 
    context = SP.ClientContext.get_current(); 
    list = context.get_web().get_lists().getByTitle(listName); 
    camlQuery = new SP.CamlQuery(); 

    $("#btnNext").click(function () { 
        pageIndex = pageIndex + 1; 
        if (nextPagingInfo) { 
            position = new SP.ListItemCollectionPosition(); 
            position.set_pagingInfo(nextPagingInfo); 
        } 
        else { 
            position = null; 
        } 

        GetListItems(); 
    }); 

    $("#btnBack").click(function () { 
        pageIndex = pageIndex - 1; 
        position = new SP.ListItemCollectionPosition(); 
        position.set_pagingInfo(previousPagingInfo); 
        GetListItems(); 
    }); 

    GetListItems(); 
}); 

function GetListItems() { 
    //Set the next or back list items collection position 
    //First time the position will be null 
    camlQuery.set_listItemCollectionPosition(position); 

    // Create a CAML view that retrieves all contacts items  with assigne RowLimit value to the query 
    camlQuery.set_viewXml("<View>" + 
                              "<ViewFields>" + 
                                     "<FieldRef Name='FirstName'/>" + 
                                     "<FieldRef Name='Title'/>" + 
                                     "<FieldRef Name='Company'/>" + 
                                "</ViewFields>" + 
                             "<RowLimit>" + pageSize + "</RowLimit></View>"); 

    spItems = list.getItems(camlQuery); 

    context.load(spItems); 
    context.executeQueryAsync( 
            Function.createDelegate(this, onSuccess), 
            Function.createDelegate(this, onFail) 
        ); 
} 

// This function is executed if the above OM call is successful 
// This function render the returns items to html table 
function onSuccess() { 

    var listEnumerator = spItems.getEnumerator(); 
    var items = []; 
    var item; 

    while (listEnumerator.moveNext()) { 
        item = listEnumerator.get_current(); 
        items.push("<td>" + item.get_item('FirstName') + "</td><td>" + item.get_item('Title') + "</td><td>" + item.get_item('Company') + "</td>"); 
    } 

    var content = "<table><tr><th>First Name</th><th>Last Name</th><th>Company</th></tr><tr>" 
                + items.join("</tr><tr>") + "</tr></table>"; 
    $('#content').html(content); 

    managePagerControl(); 
} 

function managePagerControl() { 

    if (spItems.get_listItemCollectionPosition()) { 
        nextPagingInfo = spItems.get_listItemCollectionPosition().get_pagingInfo(); 
    } else { 
        nextPagingInfo = null; 
    } 

    //The following code line shall add page information between the next and back buttons e.g. 1-4

    $("#pageInfo").html((((pageIndex - 1) * pageSize) + 1) + " - " + ((pageIndex * pageSize) - (pageSize - spItems.get_count()))); 

    previousPagingInfo = "PagedPrev=TRUE&Paged=TRUE&p_ID=" + spItems.itemAt(0).get_item('ID'); 

    if (pageIndex <= 1) { 
        $("#btnBack").attr('disabled', 'disabled'); 
    } 
    else { 
        $("#btnBack").removeAttr('disabled'); 
    } 

    if (nextPagingInfo) { 
        $("#btnNext").removeAttr('disabled'); 
    } 
    else { 
        $("#btnNext").attr('disabled', 'disabled'); 
    } 

}

Try this code, it might be helpful. Pagination is using SP.ListItemCollectionPosition in CSOM.

Answer 3

Another option is using REST, as detailed on IT Unity. This example processes everything on a library, but could be adapted for what ever you need. It recursively calls itself based on if the response has a next link.

function Get-Items{
    Param(
        [Parameter(Mandatory=$true,Position=0)][ValidateNotNull()]
        [string]$Url
    )
    $list = Invoke-SPORestMethod -Url "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')" 
    $itemType = $list.ListItemEntityTypeFullName 
    $digest = (Invoke-SPORestMethod -Url "https://tenant.sharepoint.com/teams/sitename/_api/contextinfo" -Method "POST").GetContextWebInformation.FormDigestValue 
    $listItems = Invoke-SPORestMethod -Url $Url
    Write-Host "Processing batch starting at "$listItems.results[0].Id -ForegroundColor Yellow

    foreach($li in $listItems.results){
        $newTitle = $li.FileLeafRef.ToString()
        if($li.Title -ne $newTitle){
            $id = $li.Id
            $pgurl = "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')/items($id)"
            Write-Host "Updating $pgurl" -ForegroundColor White
            $metadata = "{ '__metadata': { 'type': '$itemType' }, 'Title': '$newTitle'}"
            $item = Invoke-SPORestMethod -Url $pgurl -Method "POST" -XHTTPMethod "MERGE" -Metadata $metadata -RequestDigest $digest -ETag "*"
        }
    }
    Write-host "Batch complete" -ForegroundColor Green
    if($listItems.__next){
        Get-Items -Url $listItems.__next    
    }
}
cls
Get-Items -Url "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')/items?`$select=FileLeafRef,Title,Id&`$orderby=Id%20desc&`$filter=(ID%20le%20313031)"