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).

Flow summary

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.

Using a startOfDay() expression to determine the start time of the day to be reported on

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.

Using an addtoTime() expression to determine the end of the day

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')
Creating a blank array to hold shift details

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.

Shifts: List all shifts action using variables to restrict response to a given day

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.

Looping through the Shifts: List all shifts response

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
Example response from the Shifts: List all shifts action – Fields used in report are highlighted

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.

Get user profile action used to translate the Office 365 ID – Truncated dynamic item shown below

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.

Select action used to build array of properties – Truncated dynamic items shown below

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.

Updating the shifts array using a union expression and Set variable

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.

Create HTML table action

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

shifts array populated with two shifts
Table output from Create HTML Table action

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

Send email action using Output from Create HTML Table action

For simplicity I have not styled the table.

Simple daily email report

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

One thought on “Daily shift reports from Microsoft Shifts

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s