SharePoint Online list items 5000 +, rest query

Question

I have few lists and libraries which may reach the threshold value of 5000 soon? I have several dashboards which I've used Rest queries to fetch data according to the status of the items. My question is if list exceeds 5000 items, will there be any effect on the dashboards I have, will the rest query function normally or breaks. Can anyone answer please

Thanks, Suzeeth

Answer 1

If you have used filters on status and/or on other columns which will always return less than 5000 items from list then it will not cause any problems.

Another way to avoid the issues due to threshold is add indexes on the columns you are using in filters(Check below link for more details).

So, either you should add filters to your REST query so that it will always return less than 5000 list items or you can use $top as given below:

siteUrl/_api/web/lists/getbytitle('List Display Name')/items?$top=1000

Also, there are some ways to manage such large lists in SharePoint using indexes and folders which you can find in below article:

Manage large lists and libraries in SharePoint.

Also you can use recursive call to get 5000 items like given below:

var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('ListName')/items?$select=Title&$top=1000&$skiptoken=Paged=TRUE";
    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();
                } else {
                    return response;
                }

            },
            error: function(error){
            }
        });
    }

Answer 2

Make sure your status column is indexed. At any point of time, if your list has items more than 5000 which has same status column value(like Completed or Pending). It will not return data.

You can still break your API calls to get 4999 items using paging techniques,

To implement paging, We have to add ?$skiptoken=Paged=TRUE&$top=4999 to the end of the URI.

In your response, The URI for the next page will be included under JSON.d.__next.

At the end of data(or last page) is reached __next will not be present.

So you have to handle this in your code logic to make recursive call until all data for a particular filter is returned.

Please note that if have to use any other filter in your queries, all such columns should be indexed before list threshold is reached.