Csom query All Items More than 5000

On 28/05/2024

In Powershell



$url = "https://test.sharepoint.com/sites/monSitte"
# Connect-PnPOnline -Url $url -Interactive
Clear-Host
$listTitle = "EarthStation_Authorisations"
$list = Get-PnPList -Identity "Antenna_Model_Ressources" -ThrowExceptionIfListNotFound

$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 3000
$datas = @();

#get first item
$reqFirst = "<View Scope='RecursiveAll'>
<Query>
    <ViewFields>
        <FieldRef Name='ID' />
    </ViewFields>
    <Where>
    </Where>
    <OrderBy>
        <FieldRef Name='ID' Ascending='TRUE' />
    </OrderBy>
</Query>
<RowLimit>1</RowLimit>
</View>"
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
$spqQuery.ViewXml = $reqFirst #
$itemki = $list.GetItems($spqQuery); 
$ctx.Load($itemki)
$ctx.ExecuteQuery();

$itemId = $itemki[0].ID

# get last item
$reqFirst = "<View Scope='RecursiveAll'>
<Query>
    <ViewFields>
        <FieldRef Name='ID' />
    </ViewFields>
    <Where>
    </Where>
    <OrderBy>
        <FieldRef Name='ID' Ascending='FALSE' />
    </OrderBy>
</Query>
<RowLimit>1</RowLimit>
</View>"
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
$spqQuery.ViewXml = $reqFirst #
$itemki = $list.GetItems($spqQuery); 
$ctx.Load($itemki)
$ctx.ExecuteQuery();

$max = $itemki[0].ID

$startDate = Get-Date
. ..\common.ps1
Do {
    $stringBuilder = New-Object System.Text.StringBuilder
  
    
  
    $stringBuilder.Append("<View Scope='RecursiveAll'>") | Out-Null
   
    $stringBuilder.Append("<Query><Where><And><And><Geq><FieldRef Name='Modified' /><Value Type='DateTime'>1900-12-01T19:49:00Z</Value></Geq><Gt><FieldRef Name='ID' /><Value Type='Counter'>$($itemId)</Value></Gt></And><Leq><FieldRef Name='ID' /><Value Type='Counter'>$($itemId + $rowLimit)</Value></Leq></And></Where>") | Out-Null
    # $stringBuilder.Append("<Query><Where><And><And><Geq><FieldRef Name='Modified' /><Value Type='DateTime'>1900-12-01T19:49:00Z</Value></Geq><Gt><FieldRef Name='ID' /><Value Type='Counter'>$($itemId)</Value></Gt></And><Leq><FieldRef Name='ID' /><Value Type='Counter'>$($itemId + $rowLimit)</Value></Leq></And></Where>") | Out-Null
    $stringBuilder.Append("<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>") | Out-Null
    $stringBuilder.Append("</Query>") | Out-Null
   
    $stringBuilder.Append("<ViewFields>") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Title' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='FileLeafRef' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='FileRef' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Modified' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Created' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='ID' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Editor' />") | Out-Null
    $stringBuilder.Append("</ViewFields>") | Out-Null
   
    $stringBuilder.Append("<RowLimit Paged='TRUE'>$($rowLimit)</RowLimit>") | Out-Null
    $stringBuilder.Append("</View>") | Out-Null
     
    $req123 = Get-Content -Path ".\req\req1.xml" -Encoding:UTF8
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $itemki = $list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    try {
        $ctx.Load($itemki)
        $ctx.ExecuteQuery();
    }
    catch {
        <#Do this if a terminating exception happens#>
        Write-Host "$($_)"
        Write-Host "$($_)"
        exit 0
    }


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count) itemId $($itemId) max $($max)"
    
    $Counter = 0;
    foreach ($item in $itemki) {
        # Write-Host "$($item["ID"]) title pageNumber '$($pageNumber)' : $($item["Title"])"
        $datas += [PSCustomObject]@{
            "FileLeafRef" = $item["FileLeafRef"]
            "Title"       = $item["Title"]
            "FileRef"     = $item["FileRef"]
            "Modified"    = $item["Modified"]
            "Created"    = $item["Created"]
            "Editor"    = $item["Editor"].Email
        }
        $itemId = $item.ID
    }

    $page = $itemki.ListItemCollectionPosition
    # $itemId += $rowLimit
}   
Until($itemId -ge $max) 
Write-Host "$($listTitle) list.ItemCount $($list.ItemCount)" 
$datas | Export-Csv -Path ".\datas\$($listTitle)_7.csv" -Encoding utf8 -NoTypeInformation -Delimiter ";" -Append
Write-Host "$(AddNiceTimeSpan -start $startDate)"
 

Sharepoint powershell CSOM

No ratings yet - be the first to rate this.