0

We are facing some problems dealing with large list on SharePoint online via API, we tried below methods but no luck, please advise if there is a proper way to handle such cases.

Requirements

  • Pagination
  • Sorting – single column asc / desc
  • Filtering – multiple columns filtering, the first filter does not guarantee the result would be limited within 5000 records

Method 1: Sharepoint List API - REST

Problem: It throws Microsoft.SharePoint.SPQueryThrottledException when the first filter condition return more than 5000 records

We have tried indexing the columns but still we couldn't guarantee it is processing less than 5000 records and it will still fail on some filter conditions and order by condition

await sp.web.lists
        .getByTitle(TABLE)
        .items
        .filter('FILTER QUERIES HERE')
        .top(10)
        .orderBy('fieldName', true)

Method 2:

a) Sharepoint RenderListDataAsStream API - REST in CAML via PnP

Problem: When it has filter and order by descending, then it will return zero result. Noticed that Sharepoint UI is calling SP.FavoriteLists.GetListsBy API when a list is ordered by descending

await sp.web.lists.getByTitle(TABLE).renderListDataAsStream({
      ViewXml: `<View><Query><OrderBy><FieldRef Name='fieldName' Ascending='FALSE'/></OrderBy>
      <Where><Eq><FieldRef Name="fieldName" /><Value Type="Number">1</Value></Eq></Where></Query>
      <ViewFields><FieldRef Name='ID'/><FieldRef Name='Title'/></ViewFields>
      <RowLimit Paged='TRUE'>100</RowLimit></View>`,
    });

b) Sharepoint RenderListDataAsStream API - REST in query params via axios.post

Problem: Same as above and even have to mix the use of CAML if we need to select fields or filter column by free text such as checking string with ‘contains’

await axios.post(
      `https://SITE_URL/_api/web/GetListUsingPath(DecodedUrl=@a1)/RenderListDataAsStream?@a1='LIST_NAME'&TryNewExperienceSingle=TRUE&SortField=fieldName&SortDir=Desc&RowLimit=500&&FilterField1=fieldName&FilterValue1=2&FilterType1=Number`,
      {
        parameters: {
          ViewXml:
            caml,
          AddRequiredFields: true,
        },
      },
    );
Improve this question

1 Answer 1

Reset to default
0

I was looking for a solution to solve a similar requirement, and found a JQUERY solution as to how to call REST API recursively at here

I was able to retrieve all IDs from a list that has 12K items. I've added a Web Part Page in SPO and added a Content Editor and link to a text file containing the HTML and JQUERY. I've not noticed any issues retrieving 12K IDs recursively. Please note that I used $top=1000 in the REST API call

Please refer to the code and screenshot below.

JQUERY/HTML/REST Call

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('MyLibrary')/items?$select=ID&$top=1000";
var response = response || [];  // this variable is used for storing list items

    function GetListItems(){
        $.ajax({
            url: url,  
            method: "GET",  
            headers: {  
                "Accept": "application/json; odata=verbose"  
            },
            success: function(data){

             //response = response.concat(data.d.results);//Not used here
       
             data.d.results.forEach(function(item){  //print out the ID

            $("#divData").append(item.ID + "|");

            });

        $("#divData").append("</br>")

              //Calling API Recursively 
                if (data.d.__next) {                    
                    url = data.d.__next;
                    GetListItems();
                }       
                    
        //console.log(response);
            },
            error: function(error){
                   // error handler code goes here
        alert("Error Retrieving Data");
            }
        });
    }

$(document).ready(function()
  
GetListItems();

});


</script>

</head>

<body>

<h1>More than 5K Items</h1>

<div id="divData">
 
 <p>Data will be copied here</p>

</div>

</body>


</html>
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.