0

I'm working on a Power Automate flow where the flow is supposed to connect to Planner, get the tasks which are due tomorrow and send a message to an MS Teams Channel.

I got the entire flow working but with my current implementation there's 1 MS Teams Channel message per each task. What I'm trying to do is - every day send only 1 message to the given channel which contains the information about all of the tasks which are due tomorrow.

Here's my current flow:

  1. Getting the list of Tasks from Planner,
  2. Filter those which have a Due Date set,
  3. Filter the ones which have the Due Date tomorrow,
  4. Get the Name of the Task Creator using Get User Profile,
  5. Get the Name of the First Assignee by his ID using Get User Profile
  6. Send the data into an MS Teams Channel.

You can see the flow image here (Stack Overflow has upload problems currently): https://i.ibb.co/xmXZQVr/upload.jpg

Currently I'm taking only the first Assignee ID and getting the name, but I'd also like to get the names of all of the assignees and join them with a comma. I understand there is going to be a "Apply to Each" action, but not sure how to join the data of them and use in the Send to Teams Channel action.

I would be very grateful if anybody could give some hints on what changes should be done in my current flow to:

  1. Send only 1 message to the MS Teams Channel with the data of all tasks,
  2. Get the names of all assignees, join them with a comma and use it in that message.

Thanks!

cycero
  • 4,547
  • 20
  • 53
  • 78

1 Answers1

1

I would use a select to retrieve the UserId of each assigned user and loop through that array. The result of the get user profile can be appended to a temporary array variable. After that you can join it back together as a comma separated string.

Below is an example

  1. I would start by using to temp Array variables (OverdueTaskArray and AssignedToUserArray)

  2. In this setup I filtered the lists tasks by using an expression in a Filter Array action. In the where I used the following expression

    @and(equals(empty(item()?['dueDateTime']), false), equals(formatdatetime(item()?['dueDateTime'], 'yyyy-MM-dd'), addDays(utcNow(), 1, 'yyyy-MM-dd')))

enter image description here

  1. In the Apply to each the Body of the Filter Array is used.

  2. With a Select the list of Assigned users is retrieved

  3. In a second nested apply to each we loop through the items of that Select outputs.

  4. Per item the profile is retrieved and the mail property is appended to the AssignedToUserArray

  5. Per task the Created By User is retrieved

  6. The task title, Created By User Display Name, and the AssignedToUserArray items joined with a comma are appended to the OverdueTaskArray variable

  7. The AssignedToUserArray variable is reset to null for the next loop.

enter image description here

  1. The OverdueTaskArray is used in a Create HTML table action

  2. The output of that Create HTML table action is used in the Teams Post a message action.

enter image description here

Expiscornovus
  • 1,222
  • 1
  • 3
  • 7
  • thank you so much! This works perfectly well. Just one thing - is it possible to somehow add padding to the resulting HTML table which is sent to the Teams Channel?. Currently there's no padding and it makes hard reading the contents of the table. I've tried adding with a style in Compose but it seems that Teams is stripping the style tag from the message. – cycero Oct 17 '22 at 10:52
  • I am not sure if the Post message in a chat or channels supports CSS. But if you have already tried that with a Compose and it strips it away, I guess not. Workaround might be to use an adaptive card instead of an HTML table? You have more formatting options in that json: https://learn.microsoft.com/en-us/power-automate/lead-collection-sample – Expiscornovus Oct 17 '22 at 10:57
  • Thank you very much for your help. For styling I've found the following solution which works perfectly fine: https://www.sharepointsiren.com/2019/07/formatting-html-tables-in-flow/. Thanks again and have a lovely day! – cycero Oct 17 '22 at 12:01
  • I had another question regarding mentioning the users/task assignees. I understand that a mention is just a tag mention token or email but am not sure how to render them in the resulting table since we have all of the information in a JSON array. Also, what would be the way of showing a link to the actual Planner task in that table? Any thoughts on this would be greatly appreciated. – cycero Oct 19 '22 at 16:34
  • 1
    You could get the mention token in the Apply to each - assignedtoUsers loop. Per user you could get a mention token. That token can be appended into the OverdueTaskArray. – Expiscornovus Oct 19 '22 at 20:09