SharePoint REST api SPQueryThrottledException

Question

Wondering if anyone can help with an issue I am having with the SharePoint REST API. I currently have a Power Automate flow that runs on a daily schedule, this uses the 'SharePoint HTTP' action to get filtered items from a SharePoint list.

Currently when the flow is running I am getting an error as follows:

{ "odata.error": { "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException", "message": { "lang": "en-US", "value": "The attempted operation is prohibited because it exceeds the list view threshold." } } }

Using Postman I have run the same REST call and I get the same issue. The REST endpoint I am calling is as follows:

_api/web/GetList('/sites/SiteCollection/Lists/mylist')/items?$select=responsibleUser/EMail,resposibleOwner/EMail,Title,dueDate,customerName,Modified,Id,&$expand=responsibleOwner/EMail,responsibleUser/EMail&$filter=((dueDate le '2021-06-04')  and (status ne 'Closed') and (region eq 'EUR'))&$top=500

The list in question does currently have over 5000 items, however the query above should only return approximately 59 items. The three fields that I am using to filter on the list have all been indexed but I continue to get the above error.

The only way I appear to be able get this working is by removing the 'dueDate' field from the filter expression then I get results returned. The date field is however indexed as I previously stated, I have even tried removing the index and adding it again but this still fails.

Appreciate any help anyone can give on this.

Answer 1

If first filter in your query returns more than 5000 items, you will get this error/exception.

So, try adding the filter which return less than 5000 items first in filter query.

For example: If status ne 'Closed' returns less than 5000 items, you should add it first in filter query & then other filters like:

$filter=((status ne 'Closed') and (dueDate le '2021-06-04') and (region eq 'EUR'))