Daily shift reports from Microsoft Shifts

We’ve started using Microsoft Shifts to reserve desks in our Offices. As part of the implementation I was asked to provide daily email reports for our Office Managers. I used the Microsoft Shifts actions for Power Automate to create the reports. (As an aside the actions are part of the Microsoft Teams preview actions).

Step-by-step breakdown

I am using a simple Recurrence trigger to run the flow every day at 00:00:59 but for testing you can switch this to a manual trigger for the flow.

Power Automate includes a handy expression called startOfDay() which I’ve combined with UTCNow(). I’ve done this as it means I can switch the triggers over (between Recurrence and Manual) without making other changes and the Recurrence trigger does not pass a timestamp through that can be used.

startOfDay(utcNow())

The maximum length of a shift is 23 hours and 59 minutes and so to catch all shifts in a given day I use the startOfDay() expression to define the start time of the day as 00:00:00. The reason why will be clearer in the Shifts: List all shifts action.

Once I know the start time for the day, I need to determine the end time and return it in a format that can be used in the Shifts: List all shifts action. I use a series of nested addtoTime() expressions to add 23 hours 59 minutes and 59 seconds.

addtoTime(addtoTime(addToTime(variables('startofday'),23,'Hour'),59,'Minute'),59,'Second')

I find working with arrays in Power Automate a little bit of a dark art. Later in the flow I need to append items to the response from the Shifts: List all shifts action. From past experience I have found that the easiest way is to create a new array to hold the appended response.

The Shifts: List all shifts action will return all shifts that have been booked on a given day. In order to filter on a given day, time values have to be formatted as yyyy-MM-ddTHH:mm:ss.fffZ (UTC format). The output of the expressions used by startofday and endofday variables is in the same format so the variables work without further manipulation.

As there may be multiple shifts on a given day an Apply to each action is used to loop through the response. For the report I need the:

• ID and Name of the specific Shift
• The start and end times for the shift
• The name of the person the shift is assigned to

One drawback of the Shifts: List all shifts action is that it only returns the Office 365 ID (shown as userID in the response) of the person who is assigned the shift. Hopefully when the actions emerge from preview they will include the display name of the assignee as well.

As mentioned earlier I find working with arrays can be tricky so I’ve opted to extract the information I need for the report and assemble it into a new array.

• 

• 

Using a Select action, I can gather up the relevant fields and merge them together. I have used the Shared Shift Name, Start and End details as we are only interested in published and approved shifts.

The shift details are then added to details for other shifts using a two-step process.

The union() expression combines the output from the prior Select action with the contents of the shifts array.

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

First time around the shifts array is empty so the Compose action merely stores the output of Select action. The Set Variable action updates the shifts array with the combined array. Subsequent loops of the Apply to each action results in the additional shifts being appended into the array (technically we are loading the expanding list of shifts into the union expression each time which might not be efficient for 100s of shifts). I tend to use Compose actions in flows when I need to do something temporary and hold long term items in variables. It seems to make working with arrays easier.

The value of each ‘Value’ comes e.g. Shift comes from the expression:

 item()?['Shift']

Power Automate has a neat (but sometimes frustrating) feature that it will convert expressions and hide the expression you used to create it. In order to enter the Values, I click into each field, switch to the Expression editor, add the expressions and click OK.

• 

• 

Expressions used in my example are:

item()?['id']
item()?['Shift']
item()?['Start']
item()?['End']
item()?['AssignedTo']

I draw the report together using a Create HTML table action. The action loops through the shifts array.

The table can then be used in Send an Email action.

For simplicity I have not styled the table.

And there you have it. A walk-through of gathering details of Shifts and sending email reports.

HTML is not your only option e.g. you could send an Adaptive Card to Teams as a message.