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,
},
},
);