Error : it exceeds the list view threshold enforced by the administrator

Question

How to access SharePoint online list data which has 60000 items in it using JSOM . Indexing is not done on any of the list columns .Whenever trying to access SharePoint list data , it is giving "Error : it exceeds the list view threshold enforced by the administrator".

Answer 1

Below is the JSOM code to fetch all the items from the list more than 5000 iteams. I have used "get_listItemCollectionPosition" for this purpose:

<script type="text/javascript" src="/_layouts/15/sp.runtime.js"></script>
<script type="text/javascript" src="/_layouts/15/sp.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function(){
    getAllItems();
});

    var items;
    var query;
    var ctx;
    var list;
    function getAllItems() {
        ctx = SP.ClientContext.get_current();
        list = ctx.get_web().get_lists().getByTitle('TestList');
        ctx.load(list);
        query = new SP.CamlQuery();
        var camalQueryString = "<View><ViewFields><FieldRef Name = 'Id'/></ViewFields><RowLimit>150</RowLimit></View>";
        query.set_viewXml(camalQueryString);
        items = list.getItems(query);
        ctx.load(items);
        ctx.executeQueryAsync(
            Function.createDelegate(this, this.onQuerySucceeded),
            Function.createDelegate(this, this.onQueryFailed)
        );
    }
    function onQuerySucceeded(sender, args) {
        var enumerator = items.getEnumerator(),
            simpleArray = [];
        while (enumerator.moveNext()) {
            simpleArray.push(enumerator.get_current());          
        }

        console.log(simpleArray);

        var position = items.get_listItemCollectionPosition();
        if (position != null) {
            query.set_listItemCollectionPosition(position);
            getAllItems();
        } else {
            alert("All items retrieve successfully!!");
        }
    }
    function onQueryFailed(sender, args) {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }
    </script>

Reference Post are :

http://www.sharepointpals.com/post/The-attempted-operation-is-prohibited-because-it-exceeds-the-list-view-threshold-enforced-by-the-administrator-SharePoint-Office-365 (Shared by Max576_SPWX)

Let me know if it helps you.

Answer 2

You need to perform a filter within a View on an indexed column. If you don’t create an indexed column, the query will go through 60000 rows in content database, which causes the error.

A workaround: http://www.sharepointpals.com/post/The-attempted-operation-is-prohibited-because-it-exceeds-the-list-view-threshold-enforced-by-the-administrator-SharePoint-Office-365

Answer 3

If list has more than 5000 items

1. Indexing the Columns that CAML query apply filters
2. Specifying a row limit

Even though the Columns are indexed if the the list have more than 5000 items that match a filter on a column in the query it still throws the exception.

https://avnibhatt.wordpress.com/2014/06/10/client-object-model-access-large-listsovercome-listview-threshold-while-accessing-large-list/

Here listItemCollectionPostion property is being used to execute the query in batch, so that throttling limit does not effect the query execution.