Power Automate filter by 'Modified' date and update returned items

Question

I have what I thought was going to be a simple scenario to solve:

• Multiple SharePoint Lists, each with 40 000+ items in it
• Running queries based on different columns and export to Excel to provide MI
• To keep lists up to date and to a minimum I need power automate to check the last modified date and update items that haven't been modified for a month & Delete items that haven't been modified for 3 months.

I'm using the HTTP Get connector with a simple filter applied as _api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/Items?$select=@{outputs('querySettings')['fields']}&$filter=Modified lt 'formatDateTime(getPastTime(1, 'Month'), 'dd-MM-yyyy')'&$top=5000

Problem:

• The Get method is incredibly slow when using Modified date
• I tried all kinds of configurations, but whenever I try to filter for either the Created or Modified date, the HTTP GET part alone takes way over 15+ minutes (at which point I usually just cancel it)
• Updating items takes even longer.

If I filter for a single text column for instance, I get the results back in 1 second (about 3000 items)

I can't seem to understand why power automate takes soo long with a date??! What am I missing here?

Any help would be much appreciated!

Answer 1

I've verified that [Send an HTTP request to SharePoint] returns results immediately if columns [Created] and [Modified] are indexed. So, make sure these columns are indexed. Also, send the date filter as shown below.

/_api/web/lists/getByTitle('LibraryName')/items?$filter=(Modified ge datetime'2021-03-01T00:00:00.000Z') and (Modified le datetime'2021-03-03T00:00:00.000Z')&$select=ID, Created, Modified&$top=5000

Answer 2

Because the date format suggested by @MatiurRahman unfortunately didn't fully solve my problem, I did some more digging and found this on powerusers.microsoft.com.

It turns out, the flow sometimes ran into a "Bad Gateway" error, but due to the default retry policy it kept going and showing as working.

I had the $top=5000 filter applied so not sure why it would still try to keep going, but it did. This time, the performance was throttled, hence being incredibly slow.

The only reliable solution seems to be limiting the date range combined with other filters to always return less than 5000 items and the issue is now gone.

Never would have thought that even Power Automate has a 5000 item limit...

Hope this helps others too.