REST API Filter by Modified date ThrottledException

Jervic Andres 40 Reputation points
2023-11-23T00:54:57.16+00:00

Good day,

We have a SharePoint document library that has more than 5000 items. We are trying to retrieve a small set of files that we have calculated are less than 5000 based on a Modified date of earlier than September 17, 2023. We are using this API call:

https://[Tenant_URL]/sites/[Site_Collection]/_api/web/lists/getbytitle('Documents')/items?$select=ID,FileLeafRef,FileRef,Modified,ContentType,Author/Title,Editor/Title,Editor/EMail,FileSizeDisplay&$expand=Author,Editor&$filter=Modified lt '2023-09-17T00:00:00'&$orderby=Modified desc

But it throws a list view threshold error. We have confirmed that the column is Indexed and the criteria do not match more than 5000 files.

<m:error xmlns:m=http://schemas.microsoft.com/ado/2007/08/dataservices/metadata>

<m:code>-2147024860, Microsoft.SharePoint.SPQueryThrottledException</m:code>

<m:message xml:lang="en-US">The attempted operation is prohibited because it exceeds the list view threshold.</m:message>

</m:error>

Is there something we are missing to use Modified date column to filter a document library without getting ThrottledException?

Microsoft 365 and Office | SharePoint | For business | Windows
{count} votes

Accepted answer
  1. RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff
    2023-11-23T03:16:16.72+00:00

    Hi @Jervic Andres,

    There is a 5000 items limition in SharePoint List. You can not fetch the List items more than threshold limit. Ex: you have 10,000 items, there are 3000 items which meets your filter. you may think this is less than threshold limit, so it will retrieve. but here it won't retrieve rather you will get exceed threshold limit exception. We can only retrieve all the items by ecursive call and filter the items on local.

    You can do recursive call to the GetListItems() function. Please make a reference

     var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('DocumentList')/items?$top=1000";
        var response = response || [];  // this variable is used for storing list items
        function GetListItems(){
            return $.ajax({
                url: url,  
                method: "GET",  
                headers: {  
                    "Accept": "application/json; odata=verbose"  
                },
                success: function(data){
                    response = response.concat(data.d.results);
                    if (data.d.__next) {
                        url = data.d.__next;
                        GetListItems();
                    }
                },
                error: function(error){
                       // error handler code goes here
                }
            });
        }
    
    
    

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.