SharePoint Online - Issue with REST API call on a List exceeded the threshold limit

Question

I have a List in a SharePoint Online site collection which has exceeded 5000 items. I have indexed the columns that are needed in the REST API filter query during list creation itself.

Below is the Rest API query I use to fetch the data from the list.

https://people1v02.sharepoint.com/sites/spfxqaw44/_api/web/lists/getByTitle('Announcements')/items?$top=4999https://people1v02.sharepoint.com/sites/spfxqaw44/_api/web/lists/getByTitle(%27Load%27)/items?$filter=Title&xsdata=MDV8MDF8fGEyZmI0MWMzNTI0YjRkZjQyMjFlMDhkYWQ4ZWJmM2Q4fDVmMDdlNTU1ZDk5ODQ5MDBiNWM0OTAzZmFlMzA2MjgzfDB8MHw2MzgwNjA4MDUxNDYyODg2NzB8VW5rbm93bnxWR1ZoYlhOVFpXTjFjbWwwZVZObGNuWnBZMlY4ZXlKV0lqb2lNQzR3TGpBd01EQWlMQ0pRSWpvaVYybHVNeklpTENKQlRpSTZJazkwYUdWeUlpd2lWMVFpT2pFeGZRPT18MXxNVFkzTURRNE16Y3hNemszTWpzeE5qY3dORGd6TnpFek9UY3lPekU1T2pjMllUQTBNV0U0TFdGa1pUQXROR1F5T0MxaVkyUTBMV1ZpTVdFMFl6QTJPVFF6Wmw5a1pUSmhaalE0T1MwMlpXSTNMVFJqWXpNdFlURmpZeTFrT0RJeE5UUXlORGRoTkRSQWRXNXhMbWRpYkM1emNHRmpaWE09fDMyZGZjY2RiMmU5ZTRkNGQyMjFlMDhkYWQ4ZWJmM2Q4fGUxNWM3MGM5MmFmOTQ4MmM5ZDkxNzFhMmEzMTI5OGYz&sdata=eXFZS28xbGZvcCtOWmN0MW5yZjBvMjNZWFdVdURMakxRVTNEWEwyN29tRT0%3D&ovuser=5f07e555-d998-4900-b5c4-903fae306283%2Cnagarajan.muthukumar%40eleviant.comhttps://people1v02.sharepoint.com/sites/spfxqaw44/_api/web/lists/getByTitle('Announcements')/items?$top=4999
&$filter=(Expires ge '2022-12-06T18:30:00.000Z') and (RecordDate le '2022-12-06T18:30:00.000Z') and IsActive eq 1 and WorkflowCompleted eq 1
&$select=Id,Title,Expires,ShortDescription,Created,RecordDate,Attachments,LangTrans&$expand=AttachmentFiles&$orderby=RecordDate desc

The above query gave me an error as threshold limit exceeded. So I have created a simple list and made a API call as below,

https://people1v02.sharepoint.com/sites/spfxqaw44/_api/web/lists/getByTitle(%27Load%27)/items?$filter=Title&xsdata=MDV8MDF8fGEyZmI0MWMzNTI0YjRkZjQyMjFlMDhkYWQ4ZWJmM2Q4fDVmMDdlNTU1ZDk5ODQ5MDBiNWM0OTAzZmFlMzA2MjgzfDB8MHw2MzgwNjA4MDUxNDYyODg2NzB8VW5rbm93bnxWR1ZoYlhOVFpXTjFjbWwwZVZObGNuWnBZMlY4ZXlKV0lqb2lNQzR3TGpBd01EQWlMQ0pRSWpvaVYybHVNeklpTENKQlRpSTZJazkwYUdWeUlpd2lWMVFpT2pFeGZRPT18MXxNVFkzTURRNE16Y3hNemszTWpzeE5qY3dORGd6TnpFek9UY3lPekU1T2pjMllUQTBNV0U0TFdGa1pUQXROR1F5T0MxaVkyUTBMV1ZpTVdFMFl6QTJPVFF6Wmw5a1pUSmhaalE0T1MwMlpXSTNMVFJqWXpNdFlURmpZeTFrT0RJeE5UUXlORGRoTkRSQWRXNXhMbWRpYkM1emNHRmpaWE09fDMyZGZjY2RiMmU5ZTRkNGQyMjFlMDhkYWQ4ZWJmM2Q4fGUxNWM3MGM5MmFmOTQ4MmM5ZDkxNzFhMmEzMTI5OGYz&sdata=eXFZS28xbGZvcCtOWmN0MW5yZjBvMjNZWFdVdURMakxRVTNEWEwyN29tRT0%3D&ovuser=5f07e555-d998-4900-b5c4-903fae306283%2Cnagarajan.muthukumar%40eleviant.com ne 'Load Test'&$top=4999

Generally the return data count should be 4999 but I am able to get only 4970 items as response data. Also this response count is unpredictable, for some other lists I got response count less than 4000.

Any idea on this inconsistent behavior?

nicolaskheirallah · Answer

Nagarajan_M&nbsp;You need to use Pagingation to get more items that 5000. Here is a JS snippet that should do it for you&nbsp;var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Announcements')/items?$top=5000";
    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();
                }
                $.each(response, function(index, item) {
                    arrayListItem[index] = item;
                });
            },
            error: function(error){
            }
        });
    }&nbsp;

nagarajan_m · Answer

NicolasKheirallah How does pagination help my issue. I have 5000 items that matches my query and am trying to fetch only 4999 using $top. But am getting only 4970 items

Forum Discussion

SharePoint Online - Issue with REST API call on a List exceeded the threshold limit

2 Replies

Resources