Going with the Flow: Letting Power Automate do all the work

Recently I’ve been playing around with automating more complex tasks using Microsoft’s Power Automate, which I had initially thought to be quite limited as a lot of the included connectors didn’t offer the functionality needed for more complicated tasks, but I have discovered that with careful planning and experimentation you can achieve much more than the given features might initially suggest.

The plan was to simply create a list of daily recurring tasks for our building services team to check off each day (reminders to check certain rooms etc). This initially seems simple enough but when you look closer consists of a number of pitfalls. Then also the requirement to change the tasks depending on the day became evident. My initial task started off simply enough but as I started testing it I realised it could offer so much more functionality. And so it grew…

I should say that this post doesn’t cover all the iterations I went through with this as I did so much testing and going back and forth that I can’t remember all the different processes I went through to get to where it is now. The process also isn’t finished as there are still a lot of improvements and additions I want to make but I thought I would get down what I can now in case it is of use to anyone else.

You’re going to need a plan to tame all those pesky little Flow steps

It’s always best to start with a plan. List what you want to achieve and then think logically about the best order to go about this. My plan was:

Our Building Manager needs to be able to say which staff member is in the next morning and which staff member is on in the evening.
All that days tasks need to be populated into Planner with morning tasks assigned to the morning staff and evening tasks assigned to evening staff.
The staff members then only need to open the Microsoft Tasks app on their phones and tick off their tasks.
At the end of the day all remaining tasks that weren’t ticked off need to be deleted with an email notification sent to the Building Manager.

As our Building Services team uses Microsoft Shifts to manage their work schedules it made sense to have the Power Automate flow read from this information.

So from this plan I decided that the logical order for the Power Automate flow to run in would be to:

Power Automate Flow runs automatically every day at 6am
Connect to Planner and check for tasks remaining in bucket
Email Building Manager a summary of remaining incomplete tasks
Delete the remaining tasks from the Planner bucket
Connect to Microsoft Shifts and check who is assigned to work next day
Add the next days tasks to the bucket
Assign the correct staff member to each task

Once your Flow starts getting bigger it can get harder and harder to reorder and keep track of the steps, especially if you haven’t been renaming them along the way so this is always a good practice to get into.

There were also a number of issues that arose during testing. One of the issues I came across was the need for this Planner to be completely separate and not used for anything else. The reason for this was that as the tasks built up in the Planner (including completed tasks) the Flow started to take longer and longer to run. The Flow initially took a minute to run but after a few test it went up to 10 minutes and longer. I realised this was because while Power Automate can read tasks from a Planner or specific bucket in a Plan (and ignore all tasks that are either complete or in a different bucket) in order for it to actually know that tasks are complete or in a different bucket it still needs to cycle through every task in the Plan. This would cause problems for a Flow that adds a number of tasks every day to a Plan as it would quickly fill up! This meant that all completed and incomplete tasks needed to be deleted each day before adding the new tasks to streamline the flow.

Some connectors may also initially seem limited in some functionality but once you get yourself into the mindset of creatively working around their limitations you realise that much more can be achieved than first thought. For example at first appearance there is no connector step to ‘move’ planner tasks to another plan. The workaround is to loop through all the tasks, create new tasks with the same details in the other planner, edit those tasks to add any additional missing information, and then delete all the original tasks.

My cobbled-together Flow

The steps currently used in my Power Automate Flow are detailed below and I will add details to each image. I haven’t included every step as there are quite a few but hopefully this will give an overview and be of help to anyone trying to get to grips with Power Automate themselves.

The first step was to setup an automated flow which runs daily at 6am.

The next load of steps had me initialising a bunch of variables to be used throughout the flow. The variables I initialised are (including my reasons for creating them):

remainingTasks – string variable to hold all remaining tasks.
completedTasks – string variable to hold all remaining completed tasks (with an inital value of ‘<table>’ to start off the HTML table).
incompleteTasks – string variable to hold all remaining incomplete tasks (also with an initial value of ‘<table>’).
tasksSummary – string variable to hold the tasks summary as an HTML table (initial value of ‘<table>’).
startofDay – variable to hold the value of start of day (initial value of ‘startOfDay(utcNow())‘).
endofDay – variable to hold the value of end of day (initial value of ‘addtoTime(addtoTime(addToTime(variables(‘startofday’),23,’Hour’),59,’Minute’),59,’Second’)‘).
startofYesterday – variable to hold the value of start of yesterday for finding yesterdays staff members (initial value of ‘addDays(variables(‘startofday’),-1)‘).
endofYesterday – variable to hold the value of end of yesterday for finding yesterdays staff members (initial value of ‘addtoTime(addtoTime(addToTime(variables(‘startofyesterday’), 23, ‘Hour’), 59, ‘Minute’), 59, ‘Second’)‘).
shifts – array variable to hold the data for all todays shifts.
yesterdaysShifts – array variable to hold the data for all yesterdays shifts.
morningStaff – string variable to hold the morning staff members names.
morningStaffMail – string variable to hold the evening staff members email addresses.
eveningStaff – string variable to hold the evening staff members names.
eveningStaffMail – string variable to hold the morning staff members email addresses.
yesterdaysMorningStaff – string variable to hold the yesterdays morning staff members names.
yesterdaysEveningStaff – string variable to hold the yesterdaysmorning staff members email addresses.
taskCompletedTime – string variable to hold the time completed for each task for the email.
todaysDate – string variable for holding todays date in a readable format (initial value of ‘convertFromUtc(utcNow(),’GMT Standard Time’,’f’)‘).
todaysDay – string variable for holding todays day in a readable format for use in retrieving the days tasks from named tables in an Excel document (initial value of ‘formatDateTime(utcNow(), ‘dddd’)‘).

The startofday variable has an initial value set which takes todays date and works out the start of the day and stores that in the variable (and a similar value for the endofday variable):

startOfDay(utcNow())

I then get a specific group (using the group ID) from Shifts. Finding the scheduling group ID is unfortunately not straightforward. You need to first use a ‘Get all groups’ for Shifts and then test the flow so that you can review the steps afterwards to find the group ID from the code provided. Once you have the ID you can add it here but if you only have one scheduling group you are working with then you can skip this step. I needed it because we also manage our reception rotas through the same Shifts instance but under a different scheduling group.

The next step is to list all shifts between the start of day and the end of the day (using the variables created above to populate the fields).

The next step then cycles through all the shifts returned and converts each shift start and end time (as the times were being returned as UTC).

I then added a condition which checks whether each shift returned belongs to the specific scheduling group above and if it does then it goes through the following steps.

The first step in the condition is to get the user profile for each shift assignee.

The second step in the condition then gets specific information from the shifts list (the shift start time, the shift end time and the staff member the shift was assigned to).

An array is then composed from this information…

union(variables('shifts'),body('Select_-_Properties_from_Shift_combined_with_User_Details'))

… and the output of this added to the shifts variable.

Then another condition is created which checks whether the staff member is working in the morning or the evening. If they are assigned a morning shift their display name (taken from the get user profile step) is appended to the morningStaff variable with a comma following it (so it displays nicely in the email), and their email address is appended to the morningStaffMail variable with a semi-colon following it (so it doesn’t cause errors when assigning mutiple email addresses to a task). These variables are then used later on in the email sent and for assigning tasks to the relevant staff members.

The same thing is then done for the evening staff members (appending their details to the eveningStaff and eveningStaffMail variables).

The next step was to list all remaining (incomplete) tasks from the previous days run using a Planner ‘list tasks’ step. Then the output from this is cycled through and a condition set which applies only to tasks that have a percentage completed of 0, a specific bucket ID and a specific colour value (the reason I used different colour values in the tasks was for a simpler method for the flow to identify morning and evening tasks – yellow for morning and orange for evening) and if the results are true then the title of the task and yesterdays staff member name are appended to the remainingTasks variable (with an HTML ‘<br />‘ following it so it displays nicely in the email). Details are also appended to the taskSummary variable (and wrapped in HTML table tags with CSS colour styling).

The same process is done for the tasks that have the orange colour assigned (for evening tasks).

Once all the incomplete tasks have been safely stored away in the appropriate variables it’s safe to delete all the tasks. If they are not deleted then cycling through all the tasks takes longer and longer each day as the number of tasks builds up and the flow becomes unwieldy).

The next step creates an HTML table showing the days staff members who have shifts assigned (and their shift start and end times).

A compose step is then included which just adds some CSS styling before the output so it displays nicer in the email.

Finally an HTML </table> is appended to the incompleteTasks variable to finalise the HTML table.

An email is then sent to the Building Manager including the tasksSummary output, which is now a nicely styled HTML table with green background for tasks which were completed and red background for tasks which were not, and including a table showing the staff members who are on shift today.

A todaysDate variable is initialised with the following value which formats the time as GMT and in a readable format:

convertFromUtc(utcNow(),'GMT Standard Time','f')

The todaysDay variable is initialised containing the following value (which formats the date as simply todays day displayed in a readable format – Monday, Tuesday, Wednesday etc):

formatDateTime(utcNow(), 'dddd')

An Excel spreadsheet with all the days tasks in is then read. The Excel document is formated as tables for each day and each table has been given the days name which means the output from the above variable (todaysDay) can then be used to define the table of tasks that is being listed.

The values from above are then looped through and for each value…

… a condition is set which find whether the task is assigned to the morning or evening staff member (the word ‘Morning’ or ‘Evening’ is in a column in the table).

(In the condition above the ‘Time’ column from the Excel document can’t be selected directly so needs to be typed in as the expression below)

items('Apply_to_each_3')?['Time']

A new Planner task is then created for each output using the details taken from the Excel tables and assigned to the appropriate staff member by using the previously created and appended to morningStaffMail variable. An ‘update task’ step is then added after to include the details in the task.

The same is then done for the evening tasks (assigning to the eveningStaffMail variable).

The Incredible Bulk

This Flow ended up MUCH bigger than I initially intended (I thought a few simple steps for creating tasks in Planner on a daily basis was all that would be needed) but as I experimented and realised it could do so much more the whole thing just grew. I still want to add more functionality to it (such as maybe moving completed and incomplete tasks to a SharePoint List instead so there is a record kept of them, the ability to capture notes added to a task by the staff member and store the notes and include them in the email summary, and the ability to have some more important tasks rollover to the next day when not completed) but I really think I could also separate this flow out into two or more flows, which kick each other off when certain actions happen, instead of one big flow.

But for now it works and provides a useful email to our Building Manager every day which shows the tasks that were ticked off (and the time they were completed) in green and the tasks that were not ticked off in red so they can be followed up on. And our Building Services staff have a nice easy process of being able to open the Microsoft Tasks app on their phones and under the ‘assigned to me’ section have a handy list of the days tasks ready to be checked off.

So until I make my planned improvements, here’s an overview of the current complete flow in all it’s glory!