Targeting a specific list view - PowerShell CAML (SharePoint Online)

Question

I need some support in how to target specific views within a SharePoint Online list using CAML.

Just last week our script started to fail because we've now hit the view thresholds of our list - it's now sitting at 7500 items.

We've gone through the columns and indexed what fields are being referenced but the error is still appearing. My thought is to now target a particular view instead of the default as we only need those items which are of a particular status retrieved.

I've tried to use the View element examples found here but no matter what I input I'm just getting the default back.

MSDN View element(list)

Here's the CAML query:

<View>
   <Query>
      <Where>
         <Or>
            <Eq>
                <FieldRef Name='Status' />
                <Value Type='Choice'>Provisioning Site</Value>
            </Eq>
            <Eq>
                <FieldRef Name='Status' />
                <Value Type='Choice'>Requested</Value>
            </Eq>
         </Or>
     </Where>
     <OrderBy><FieldRef Name='Status' Ascending='FALSE' /></OrderBy>
  </Query>
</View>

And here's the error we're currently getting.

System.Management.Automation.MethodInvocationException: Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." ---> Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. at Microsoft.SharePoint.Client.ClientRequest.ProcessResponseStream(Stream responseStream) at Microsoft.SharePoint.Client.ClientRequest.ProcessResponse() at CallSite.Target(Closure , CallSite , ClientContext ) --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception) at GetAllListItems(Closure , FunctionContext ) at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0) at System.Management.Automation.PSScriptCmdlet.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess) at System.Management.Automation.CommandProcessorBase.Complete()

Answer 1

You can query list with more than list view threshold as below : Index your status columns It will query 5000 number of items we give in CAML query in RowLimit tag. Next items will be queried with position. In this way it will query all items. Source

You can use below code for reference :

$list = $ctx.Web.Lists.GetByTitle($YourListName)
$ctx.Load($list)
$ctx.ExecuteQuery()

## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <Where>
        <Or>
        <Eq><FieldRef Name='Status' /><Value Type='Choice'>Provisioning Site</Value></Eq>
        <Eq><FieldRef Name='Status' /><Value Type='Choice'>Requested</Value></Eq>
        </Or>
        </Where>
        <OrderBy>
        <FieldRef Name='Status' Ascending='FALSE' />
        </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) 

Targetting specific View if you want to go with your approach by querying view if they contains less than threshold: You can go with below one

    $view = $list.Views.GetByTitle("Viewname")
    $context.Load($view)
    $context.ExecuteQuery()
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ViewXml = $view.ViewQuery
    $items = list.GetItems(camlQuery);
    $context.Load(items);
    $context.ExecuteQuery();