Get the list of Views those are crossing 5000 items

Question

Recently I have migrated a SP 2013 site to SP Online. Most of the list has items more than 30K to 40K(records). Unfortunately - some of the list has more than 100 and even 200 views! Obviously each view has their respective filters. In my case most of the filters bring more than 5K records and hence views get failed to render with the following error:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

I am okay with the error and I know how to fix them. To fix them, first I need to know how many views are there those are crossing 5K records. My SharePoint neighbors know very well how painful it is to see a view that crosses 5K records (surely I am talking about the speed to load data). It is an excruciating pain to check each view and record it somewhere that this view is crossing 5K records!

What I need: A mechanism (A tool like thing that might be written in CSOM or SPFx or anyway - I will be able to write if I get a pointer how to read views filter and how to check if that filter will cross the 5K records) that will take the input a site URL, iterate trough all the list and then all the views producing a report that will give me an insight of total number views (With names) that crosses 5K records so that I can quickly jump in and fix them.

Advance thanks for any pointer!

Answer 1

you can set your list.EnableThrottling = false to desactivate your throttling exeption

it's possible with PowerShell. With REST and CSOM Ko, will check for any REST solution

then you can add index on your spfields, and reactivate throttling to improve performances

http://fdi-sharepoint.e-monsite.com/blog/javascript/sharepointhidelistwithrest.html

the following code give me this:

function getJson(url) {
    return jq.ajax({
        url: url,
        type: "GET",
        contentType: "application/json;odata=verbose",
        headers: {
            "Accept": "application/json;odata=verbose"
        }
    });
}

function getListItems(webUrl, listTitle, queryText, viewTitle) {
    var viewXml = '' + queryText + '';
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems";
    var queryPayload = {
        'query': {
            '__metadata': { 'type': 'SP.CamlQuery' },
            'ViewXml': viewXml
        }
    };

    jq.ajax({
        url: url,
        method: "POST",
        data: JSON.stringify(queryPayload),
        headers: {
            "X-RequestDigest": jq("#__REQUESTDIGEST").val(),
            "Accept": "application/json; odata=verbose",
            "content-type": "application/json; odata=verbose"
        },
        success: function (data) {
            console.log("'" + viewTitle + "' itemsCount : " + data.d.results.length);
        },
        error: function (data) {
            console.dir(data);
        }
    });
}

function getListItemsForView(webUrl, listTitle, viewTitle) {
    var viewQueryUrl = webUrl + "/_api/web/lists/getByTitle('" + listTitle + "')/Views/getbytitle('" + viewTitle + "')/ViewQuery";
    return getJson(viewQueryUrl).then(function (data) {
        var viewQuery = data.d.ViewQuery;
        return getListItems(webUrl, listTitle, viewQuery, viewTitle);
    });
}

var listTitle1 = "testThrottling";
var viewTitle1 = "Tous les éléments";
var url = _spPageContextInfo.webAbsoluteUrl;
// getListItemsForView(url, listTitle1, viewTitle1);

var xhr = new XMLHttpRequest();

xhr.open('GET', url + "/_api/web/lists/getbytitle('" + listTitle1 + "')?&select=Views&$expand=Views");
xhr.setRequestHeader("Accept", "application/json; odata=verbose");
xhr.setRequestHeader("Connection", "close");
xhr.onload = function () {
    if (xhr.status === 200) {
        var kk = JSON.parse(xhr.responseText);
        console.dir(kk.d.Views.results.length);

        for (var k = 0; k < kk.d.Views.results.length; k++) {
            console.log(kk.d.Views.results[k].Title);
            getListItemsForView(url, listTitle1, kk.d.Views.results[k].Title);
        }
    }
    else {
        console.dir(xhr);
        alert('Request failed.  Returned status of ' + xhr.status);
    }
};

xhr.send();