Use Case

Many a times, we need to send a single consolidated email to user for the list items present in SharePoint. It may be a reminder with table of pending tasks assigned to a user or sending email to manager with team details etc.

Here, I will take an example of Tasks list which are assigned to the user(s). Each item has status value, we will fetch the pending tasks based on the status and send a recurring email using schedule cloud flow.

SharePoint List Details

Here, Status is the single choice field and ‘Assigned To’ is the user field that can hold single value.

Power Automate cloud flow

1. As we are sending reminders, so we will create a schedule flow and set the daily recurrence which will trigger at 9 am. You can configure the recurrence as per the requirements.

You can set the time zone by editing the Recurrence action:

2. Now, we will fetch the list items from SharePoint list using “Get items” action. We will pass the filter query to get only the Pending items:

Status eq ‘Pending’

3. Now, we will collect the email addresses of the ‘Assigned To’ user in an array using “Select” action:

In ‘From’ parameter, select the value property from the dynamic content which is obtained from “Get items” action. For the ‘Map’ parameter, add plain text ‘AssignedTo’ in key textbox and select ‘Assigned To Email’ property from the dynamic content.

4. As multiple tasks are assigned to a user, so the output array from Select action will have duplicates email addresses. We will create an expression for getting the distinct or unique email address from the “Select” action output. The fx expression use the union function for this task. To iterate unique email address, we will add “Apply to each” action and add the fx expression as its input. Expressions needs to be entered in the expression box:

Expression used for getting the unique email addresses:

union(body(‘Select’),body(‘Select’))

4.1 Continue inside “Apply to each” block, add “Filter array” action to filter the list items based on value of each iteration (owned by the user):

In ‘From’ parameter, select output “value” from dynamic content.

For query, we will select ‘Assigned To email’ property from dynamic content and set on the left side of the comparison operator.

On the right side of comparison operator, we will add another expression to get the value of email address in each iteration.

Expression used on the right side:

items(‘Apply_to_each’)?[‘AssignedTo’]

4.2 Add “Create HTML table” to create a html table with the body or output of Filter array action. Choose “Custom” option in ‘Columns’ parameter:

For header, we will enter the plain text but for each value we need to add a fx expression to get the value of the corresponding field.

Header	Value
Title	item()?[‘Title’]
Description	item()?[‘Description’]
Status	item()?[‘Status/Value’]
Due Date	formatDateTime(item()?[‘DueDate’],’MM/dd/yyyy’)

As Status is the single choice field, so we need to get the value of it from the object. To change the format of date time, formatDateTime function is used to show date as ‘mm/dd/yyyy’ in html table.

4.3 Finally, add “Send an email” action to send the email to the respective user. In the email recipient (To), we will add an expression to get the value of email address in each iteration:

Pass the body of “Create HTML table” action in the email body as shown in the above screenshot. You may modify the email subject or body text as per your need.

You may apply custom style and formatting to html table by adding html code in compose action and pass it to the email body.

Output email

When flow executes, it sends the email to task owner in the below format.

Conclusion

As you have seen, using fx expressions and functions or formulas like union, item, items etc. we can group the list items based on user field in SharePoint and then send a single consolidated email to the required user. The actions “Select” & “Filter array” are so powerful that most of the time we replace “Apply to each” action with those to optimize the performance for large datasets.