CODEWITHSUNDEEP
sharepoint-onlinepower-automatesharepoint-rest-api
M_66
M_66

Reputation: 299

How can I check all 15K rows in a SharePoint list using Power Automate flow

I have a SharePoint list that contains approximately 15000 records. My list has an Owner field for each record. There can be many owners of many different records. However, there can be only one owner per record. Each day people access this list and make updates depending on the record. I would like my script to run every evening and send an email to each owner with a list of the records that they own that were modified. So if one or more records that I own are modified, then I want to be notified at the end of the day with an email that has a link to the records that were modified. Similarly, if some records of 5 different owners were modified today, then they would each receive an email listing the records that they own that were modified.

I know how to compare a record's Modified field to today's date using the formatdatetime(utcNow(), 'MM-dd') functions. I'm currently experiencing 2 issues. The get items action in Power Automate relies on the SharePoint REST API and that only return 5000 rows at a time, so my results are missing 10K of rows. So, I need to figure out how to overcome that limitation. Also, I haven't figured out a best way to send out emails to the different record owners displaying only the records that they own. I currently send out a mass email with all the modified records - this is inefficient and not a pleasant experience for those receiving these mass emails.

Any help would be greatly appreciated, even if it includes restructuring or setting up indexes on my list, all responses will be considered.

Upvotes: 0

Views: 698

Answers (1)

teylyn
teylyn

Reputation: 35970

With a slightly different mind set, you may get a much easier solution.

  • Create a view that is filtered by Owner = [Me] and sort it by modified, descending.
  • let people check this view regularly.

People have an obligation to do their job. If they don't do that, it's more a behavioural problem, not a technical one.

Saying that, you don't have to loop through all 15000 records. You only need to loop through the ones that were changed Today. If you get fewer than the threshold changes per day, you could:

  • Get Items and use the OData filter to filter by Modified = today and sort order by owner
  • initialise previous owner variable with empty string
  • loop through the Get Items results
    • set current owner variable to the item's owner email
    • if previous owner variable <> current owner variable
      • start an email and put the list variable in the email body
      • send the email to the previous owner variable (unless it's still the empty string)
      • clear the list variable
    • set the previous owner variable to be = current owner variable
    • append the item details to the list variable
  • loop end

Upvotes: 0

Related Questions