How to get past SharePoint Online 5000 row limit restriction with Caml?

0
votes

Using Caml, I'm trying to pull back all items from a SharePoint Online site that have been modified in the last year. The query results in an error that states: "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." This threshold is 5000 items on SharePoint Online, and it cannot be modified as far as I can tell after discussing with a SharePoint admin.

This error is fairly well documented, but none of the solutions I've looked at have addressed my question.

I've gotten past this issue before by using the <RowLimit></RowLimit> tag inside my Caml query and by setting a $position variable that keeps track of where I am after every chunk of 5000 items. See below for the query that is working for lists with more than 5000 items:

$query = @"
<View Scope="RecursiveAll">
    <Query>
        <OrderBy><FieldRef Name="Created" Ascending="false"/></OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@

The above works. However, it simply gets everything from a certain library. What I'm trying to do now is get everything that's been modified in the past year. Here's what I have:

$oneYearAgo = (Get-Date).AddDays(-365)
$oneYearAgoString = $oneYearAgo.ToString("yyyy-MM-ddTHH:mm:ssZ")

$query = @"
<View Scope='RecursiveAll'>
    <Query>
        <OrderBy><FieldRef Name='Modified' Ascending='false'/></OrderBy>
        <Where>
            <Geq>
                <FieldRef Name="Modified"/>
                <Value Type="DateTime">
                    $oneYearAgoString
                </Value>
            </Geq>
        </Where>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@

This one returns that "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator" error, though the logic outside of the two Caml queries is almost identical.

Is there something wrong with my Caml query?

2
powershellsharepointsharepoint-onlinecaml
Has the Modified column been indexed? You can find the indexed columns of the list at the bottom of the columns section of the List Settings page. – Thriggle
I met with one of our SharePoint admins, and that column had not been indexed. It is now, though, and unfortunately we are still seeing the same error. – rtoken

2 Answers

0
votes

Option 1 : Create a filtered List View to get item count under 5000. Then, get items on that particular view.

Option 2 : Get all items in an item collection without querying. Then, use Linq to query the item collection. You can refer here for using Linq in PowerShell. Something like this:

$FilteredItems = $ItemCollection.Where({($_.Modified-eq $oneYearAgoString)
0
votes

i would like to suggest you to use the "ListItemCollectionPosition" field of the query. So you can create a recursive method and iterate through the whole list (even, if it contains more than 5000 items).

public static List<SharePointNode> GetDocumentsByCaml(ClientContext localCTX, List list, SharePointNode spCurrentNode, ListItemCollectionPosition position = null)
    {
        List<SharePointNode> lstDocuments = new List<SharePointNode>();
        try
        {
            CamlQuery camlQuery = new CamlQuery();
            camlQuery.ViewXml = @"<View Scope='Recursive'><Query>
                                   <Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Query><RowLimit>1000</RowLimit></View>";

            camlQuery.ListItemCollectionPosition = position;
            camlQuery.FolderServerRelativeUrl = spCurrentNode.URL;

            ListItemCollection listItems = list.GetItems(camlQuery);
            localCTX.Load(listItems);
            localCTX.Load(listItems, a => a.Include(item => item.File, 
                                                    item => item.File.CheckedOutByUser, 
                                                    item => item.File.CheckOutType));
            localCTX.ExecuteQuery();

            foreach (ListItem itemOfInterest in listItems)
            {
                SharePointNode spNodeDoc = new SharePointNode();
                // check here every listItem and compare the last modified date
                // if it was modified in the past year -> put it into your result list.

                lstDocuments.Add(spNodeDoc);
            }

            if (listItems.ListItemCollectionPosition != null)
            {
                lstDocuments.AddRange(GetDocumentsByCaml(localCTX, list, spCurrentNode, listItems.ListItemCollectionPosition));
            }
        }
        catch (Exception ex)
        {
            // log the message
        }

        return lstDocuments;
    }