0

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!

2
  • I think [Created] and [Modified] columns are not indexed. And you many not be able to create it now with 40K+ items in them. Please check this article Mar 2 at 15:55
  • @MatiurRahman Thanks for the suggestion, I just checked to be on the safe side and both the Modified and Created columns show as indexed, so I guess that's not it... Mar 2 at 16:08
0

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


1
  • Thanks @MatiurRahman your idea of applying from to filters actually allowed me to find the solution. Wrote my own answer hoping to help others with the 5000 item limitation even in Power Automate that I never thought existed. Mar 3 at 8:29
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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