How to overcome the „list view threshold“ in Sharepoint?
For The Developers …
One of the main obstacles a user faces while working with Sharepoint lists is the limit of 5,000 displayable and filterable items, called „list view threshold“. The reason for this constraint is a potentially reduced performance if a larger number of items is retrieved from the Sharepoint server. To overcome this issue, the LargeListView was developed. It allows to display and filter any desired number of elements by utilizing the REST endpoints of Sharepoint. The essential steps that are required to bypass the list view threshold are the following:
1. Due to the sandbox behavior of any Sharepoint add-in, it is not possible to access context that is located beyond the add-in (e.g. properties of the browser’s Window object like Window.location.href to get the URL of the current Sharepoint site). Therefore, the user must specify the current URL in a special input field in the Edit Menu of the add-in. This field is be created by adding a new parameter to the Content element beneath the ClientWebPart element in the add-in‘s Elements.xml (the respective parameter is listUrl):
2. The URL of the underlying Sharepoint list can now be used from the input field. To get the string that is connected with the listUrl parameter as described before, the related parameter must be read:
spContext.getQueryStringParameter = function(paramToRetrieve) {
var allparams = document.URL.split("?");
if (allparams.length > 1) {
var params =
allparams[1].split("&");
var strParams = "";
for (var i = 0; i < params.length; i = i + 1) {
var singleParam = params[i].split("=");
if (singleParam[0] == paramToRetrieve)
return singleParam[1];
}
}
return undefined;
};
In order to obtain the URL string from the edit menu of the app part, the correct method invocation is:
spHelper.getQueryStringParameter("listUrl");
At first, all available views of the list are retrieved by the following REST call (here, listName is name of the list extracted from the URL, and the spContext.hostUrl refers to the entire URL):
spContext.getViewsREST = function (listName, onSuccess, onError) {
if (onError == null) {
onError = errorHandler;
}
var executor = new SP.RequestExecutor(spContext.appWebUrl);
executor.executeAsync({
url: spContext.appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('" + listName + "')/views?@target='" + spContext.hostUrl + "'",
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
onSuccess(JSON.parse(data.body));
},
error: function (error) {
onError(error);
}
});
}
3. As soon as the various views of the list are known, the fields of each view can be retrieved by:
spContext.getViewFieldsREST = function (listName, currentViewName, onSuccess, onError) {
if (onError == null) {
onError = errorHandler;
}
var executor = new SP.RequestExecutor(spContext.appWebUrl);
executor.executeAsync({
url: spContext.appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('" + listName + "')/views/getbytitle('" + currentViewName + "')/ViewFields?@target='" + spContext.hostUrl + "'",
method: "GET",
headers: {
"Accept": "application/json; odata=verbose"
},
success: function (data) {
onSuccess(JSON.parse(data.body));
},
error: function (error) {
onError(error);
}
});
}
4. Now where all of the view’s fields are known, the items endpoint can be addressed with multiple REST calls to obtain all of the items of a view. This is done in the error branch of the the executor.executeAsync() in a recursive way by retrieving the items recursively in ID ranges of 5000 (this is happening in the error branch because the list view threshold is triggered before without the ID restriction). To do this, the ID range must be defined in the $filter parameter. So, for example, in the first call, all items within the interval 0 – 4999 are obtained, whereas in the second call, all items within the intervall 5000 – 9999 are retrieved. This REST call for increasing ID ranges is repeated until the list’s last item ID is reached (this integer can be retrieved by a previously invoked REST call of the form: …select=ID&$top=1&$orderby=ID%20desc). The method for the recursive invocation of the REST calls over the full list is shown below: The URL of the items endpoint has to be complemented with the parameters $select (on which the desired fields follow) and $expand (required for lookup fields). Moreover, other filters different than those for the IDs can be applied by adding them to the $filter parameter.
function getListRESTRecursive(listName, columns, columnsAsList, expandLookups, predefFilters, filterString, hasIdRange, onSuccess, onError, taggedObject, onFirstSuccess, lastItemId, urlParams) {
var Url = spContext.appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + listName + "')/Items?@target='" + spContext.hostUrl + "'&$top=5000&$select=" + columnsAsList;
if (expandLookups != undefined && expandLookups != "") {
Url = Url + "&$expand=" + expandLookups;
}
console.log("filterString = " + filterString);
if (filterString !== "") {
Url = Url + "&$filter=" + encodeURIComponent(filterString);
}
var executor = new SP.RequestExecutor(spContext.appWebUrl);
executor.executeAsync({
url: Url,
method: "GET",
dataType: "json",
headers: {
"Accept": "application/json; odata=verbose"
},
success: function(data) {
if (onFirstSuccess !== null) {
onFirstSuccess(JSON.parse(data.body), listName, columns, hasIdRange, "", taggedObject, null, urlParams);
}
else {
onSuccess(JSON.parse(data.body), listName, taggedObject);
}
},
error: function (error) {
var errorObj = JSON.parse(error.body);
if (errorObj.error.code === "-2147024860, Microsoft.SharePoint.SPQueryThrottledException"){
var idx = 0;
var runningAsyncCalls = 0;
var filteredResult = [];
while (idx != lastItemId+1) {
endIdx = Math.min(idx + 5000, lastItemId + 1);
var filterStringWithIdRange = encodeURIComponent(filterString) + " and ID ge " + idx + " and ID lt " + endIdx;
Url = spContext.appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbyTitle('" + listName + "')/Items?@target='" + spContext.hostUrl + "'&$top=5000&$select=" + columnsAsList + "&$filter=" + filterStringWithIdRange;
if (expandLookups != undefined && expandLookups != "") {
Url = Url + "&$expand=" + expandLookups;
}
runningAsyncCalls++;
executor.executeAsync({
url: Url,
method: "GET",
dataType: "json",
headers: {
"Accept": "application/json; odata=verbose"
},
success: function(data) {
var results = (JSON.parse(data.body)).d.results;
$.merge(filteredResult, results);
runningAsyncCalls--;
if (onFirstSuccess !== null && runningAsyncCalls === 0) {
onFirstSuccess(filteredResult, listName, columns, hasIdRange, "", taggedObject, null, urlParams);
}
},
error: function (error) {
runningAsyncCalls--;
if (onFirstSuccess !== null && runningAsyncCalls === 0) {
onFirstSuccess(filteredResult, listName, columns, hasIdRange, "", taggedObject, null, urlParams);
}
}
});
idx = endIdx;
}
}
else {
onError(error);
}
}
});
}
By that, a full example URL of a REST query made by the add-in (without a filter) looks like the following:
In this example URL, all items between the IDs 10.000 and 15.000 get retrieved (as defined in the $filter parameter, i.e. …$filter=ID ge 10000 and ID lt 15000). E.g., for a list with a last item with ID of 22.455, the ranges of the iterations are as follows:
1st call: 0 – 4999
2nd call: 5000 – 9999
3rd call: 10000 – 14999
4th call: 15000 – 19999
5th call: 20000 – 22455
If one or more filters are included in the REST query, the iterations in ID ranges of 5000 are the same, but the $filter parameter is inserted into the URL:
With this filter, only those items are returned by Sharepoint that match the criteria. In this case:
Choice eq Enter Choice #1 and DateAndTime le datetime‘2018-9-12T23:59:59‘ and ID ge 10000 and ID lt 15000
Author: Stefan Gierke