Do you want to know who is in the office tomorrow or the day after? With some COVID restrictions still in place it can important to know who is in the office.
Use case
Table of Contents
Last week I was asked if it is possible to check who will be in the office tomorrow and for the rest of the week. As some of the COVID restrictions are removed some people decide to go back to their office. However, organisations still want to make sure that the offices are not over crowded. How can you handle this?
Remember people are lazy. Having to click on a link and book your seat in the office might not always be done by everybody. So how can we make this easy.
How about people add a calendar item in their outlook calendars and for each day they set the location of one of their meeting to the office location. Then a flow can read all calendars and send out an email to the management team
In my case I’m using this for a smaller organisation. For larger organisations some adjustment might need to be made.
Running the flow in Power Automate
In my case I want to run the flow that generates an email every day at 1pm and one at 5pm. This email lists all staff in the office for each of the following 5 days.
To make the flow run twice a day I’m going to create a scheduled flow with a trigger that look like this.
Microsoft Graph to access calendars
I’m going to use Power Automate to call Microsoft Graph to collect the calendar details for all of my staff.
There are two endpoints that could do the job.
However as I experimented with my solution I found that the List Events doesn’t give me all events that I need. Therefore in my post I will use the List Calendar View option to find out who is in the office tomorrow and the days after.
Access to calendars
To use Microsoft Graph I’m going to use an app registration in Microsoft Azure.
First of all you will need to have an administrator account to setup the permissions. Looking at the Permissions overview of the Microsoft Graph end point I will need.
So you will need to make sure that you give your app registration these permissions as shown below.
Now within my flow straight after the trigger, I created a Select action to hold all my settings.
This settings Select action is fed with [{}]. this is a single item array without any properties. Then in the mappings I list all my settings and their values. Is this not a lot better than using 3 or most likely more compose actions?
The parse json takes the one single item with my 3 settings and with the following expression the Parse json will give me just 3 properties.
first(body('Settings'))This construction can be used for as many settings as you like.
Now we can get an access token as shown in some of my previous posts. Have you noticed that the property names are visible? So much better than just using compose actions.
We now have an access token that we can use to view all the calendars.
Finding all the users
I’m using the Search for users to get me all the users within the organisation. Simply leave the search term empty and everybody will be returned.
now we will need to filter out all those accounts that we don’t want:
If you are lucky then there is an easy way to do this. I was less lucky and I filtered out inactive accounts, admin accounts and service account, using a number of Filter actions.
Once you have the right list of users returned with your filters, we can look at people’s calendars.
in this case I’m going to use the so much hated Apply to each step. But I’m not going to use it in a nested form so it should still perform ok.
Inside my apply to each I’m creating a Compose action that shows the user’s display name. this will be very helpful when you need to debug your flow.
Who is in the office MS Graph call
Now we will need to call the Microsoft Graph using the calendarView endpoint.
The Method is set to GET
The URI is set to the following endpoint
https://graph.microsoft.com/v1.0/users/@{items('Apply_to_each')?['Id']}/calendar/calendarview?startDateTime=@{adddays(utcNow(), -1)
}&endDateTime=@{adddays(utcNow(), 6)}
Okay, now we have a lot of data coming back. It’s time to massage the data so that we can find out Who is in the office tomorrow, the day after and for the rest of the week.
I will start by simplifying the data a bit. If you ever debug this flow you will find that getting all those events from calendars give a lot of date and a lot less information.
So I will use a select that takes the following expression as a From.
body('HTTP_-_Get_Events')?['value']Then the location, Person and Start are set to the following three expressions:
item()?['location/displayName']
outputs('Compose_Name')
item()?['start']
The above Select will give us a simplified version of all our calendar items.
using this output we can now filter for all the items that contain out office location.
To get all the events for tomorrow a simple expression can be created within yet another filter action.
formatDateTime(adddays(UtcNow(), 5), 'yyyy-MM-dd')
Similar action can be created for the next day and each day for the rest of the week.
For each set of events for each person, we will only take the first event. As long as there is one event with our office location in the calendar for a person for a specific day,w e know that they are in the office. No need to keep track of multiple event.
Now we will need a set of actions that can be run in parallel AFTER the apply to each. The All events tomorrow action for example will collect all the “First Event tomorrow” outputs using the Pieter’s method. with the following expression referencing the compose inside the apply to each.
outputs('First_Event_Tomorrow')
We will now have an array with the first events for each user within the organisation. however there will also be empty items for all the people who are not in the office tomorrow. To filter those people out we will use yet another filter array action as shown above.
For each staff member not in the office tomorrow will will get a null value. For every other record we have found someone who is in the office tomorrow.
To yet again simplify the data we use a select that just collects people’s names. from the array of people who are in the office using the below configuration:
This list of name can now be used within the Creaet HTML action to generate a table that we can then use in our final email.
There is however a chance that nobody has booked themselves into the office for this I will add a compose action to my flow with the following expression:
if(equals(length(body('Select_Tomorrow_People')), 0), 'Nobody is planning to be in the office', '')
Sending the email with who is in the office
The final step, is all we have left to do.
For each day I’ve created a heading that displays the date. For tomorrow the followimng expression can be used:
formatDateTime(adddays(utcnow(),1),'d')Then underneath each heading
I[‘m referencing the Create HTML action and the Empty list action as described earlier.
And that is it. One a daily basis you can receive an email telling you who is in the office.