How to detect room booking conflicts with Power Automate

My flows > New flow > Scheduled cloud flow

Create scheduled cloud flow

Navigate to make.powerautomate.com and sign in with your Microsoft account. Click 'My flows' in the left sidebar, then 'New flow'. Select 'Scheduled cloud flow' from the dropdown menu. Name your flow 'Room Booking Conflict Detector' and set it to run every 15 minutes.

1. 1Click 'My flows' in the left navigation
2. 2Click the 'New flow' dropdown button
3. 3Select 'Scheduled cloud flow'
4. 4Enter 'Room Booking Conflict Detector' as the flow name
5. 5Set repeat every 15 minutes

Flow designer > + New step > Google Calendar

Add Google Calendar connector

Click '+ New step' below the Recurrence trigger. Search for 'Google Calendar' in the connector search box and select it. Choose 'Get events (V4)' from the actions list. This action will fetch all events from your room calendar for conflict analysis.

1. 1Click '+ New step' under the Recurrence block
2. 2Type 'Google Calendar' in the search box
3. 3Select the Google Calendar connector
4. 4Choose 'Get events (V4)' action

Google Calendar action > Connection settings

Configure calendar connection

Click 'Sign in' to connect your Google account that has access to the room calendars. After authentication, you'll see a dropdown for Calendar id. Select the specific room calendar you want to monitor. Set 'Time Min' to formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ssZ') and 'Time Max' to formatDateTime(addHours(utcNow(),2),'yyyy-MM-ddTHH:mm:ssZ') to check the next 2 hours.

1. 1Click 'Sign in' and complete Google OAuth flow
2. 2Select your room calendar from the Calendar id dropdown
3. 3Click in Time Min field and select 'Expression'
4. 4Enter formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ssZ')
5. 5Set Time Max to formatDateTime(addHours(utcNow(),2),'yyyy-MM-ddTHH:mm:ssZ')

Flow designer > + New step > Data Operations > Compose

Add conflict detection logic

Add a new step and search for 'Compose' action under Data Operations. This will process the calendar events to detect overlaps. In the Compose inputs field, you'll create an expression that compares event start/end times to find conflicts. We'll use this to prepare data for the next filtering step.

1. 1Click '+ New step' below the Google Calendar action
2. 2Search for 'Compose' in Data Operations
3. 3Select the Compose action
4. 4Click in the Inputs field to add conflict detection expression

Compose action > Inputs > Expression

Configure overlap detection formula

In the Compose inputs, click 'Expression' and enter this formula to detect overlapping events. This expression compares each event's start and end times against all other events to identify scheduling conflicts. The formula accounts for events that start before another ends or end after another starts.

1. 1Click 'Expression' tab in the inputs field
2. 2Paste the overlap detection expression
3. 3Click 'OK' to save the expression
4. 4Verify the expression appears in the inputs field

Flow designer > + New step > Data Operations > Filter array

Filter for actual conflicts

Add another step and select 'Filter array' from Data Operations. Set the 'From' field to the output of your Compose action. In the filter condition, check if the array length is greater than 0, meaning conflicts were found. This prevents false alerts when no conflicts exist.

1. 1Add new step and search for 'Filter array'
2. 2Set 'From' to the Compose action output
3. 3Add condition where length is greater than 0
4. 4Set the filter to only pass when conflicts exist

Flow designer > + New step > Slack > Post message

Add Slack connector

Click '+ New step' and search for 'Slack'. Select the Slack connector and choose 'Post message' action. You'll need to sign in to your Slack workspace and authorize Power Automate to post messages. Select the #office channel where conflict alerts should appear.

1. 1Add new step and search for Slack
2. 2Select Slack connector
3. 3Choose 'Post message' action
4. 4Sign in to your Slack workspace
5. 5Select #office channel from dropdown

Slack Post message > Message field

Configure conflict alert message

In the Message field, create an alert that includes details about the conflicting bookings. Include event names, times, and organizers so the office manager can quickly resolve the issue. Use dynamic content from the Google Calendar events to populate specific conflict details.

1. 1Click in the Message text field
2. 2Type alert header text
3. 3Add dynamic content for event details
4. 4Include conflict time ranges and organizers
5. 5Format message for readability

Flow designer > Save > Test > Manually

Test the flow

Click 'Save' to save your flow, then 'Test' in the top menu. Select 'Manually' and click 'Test'. The flow will run immediately and check for conflicts in your room calendar. If conflicts exist, you should see a Slack message appear in your #office channel within 30 seconds.

1. 1Click 'Save' in the top right
2. 2Click 'Test' button
3. 3Select 'Manually' option
4. 4Click 'Test' to run immediately
5. 5Check #office channel for test message

Flow designer > Turn on

Enable automatic monitoring

After successful testing, click 'Turn on' to activate the scheduled flow. The flow will now run every 15 minutes and check for room booking conflicts automatically. You can monitor run history in the flow details page to ensure it's working correctly.

1. 1Click 'Turn on' in the flow header
2. 2Confirm the flow is enabled
3. 3Check the status shows as 'On'
4. 4Review run history after first scheduled execution

Add this expression in the Compose action to detect overlapping events by comparing start and end times across all calendar events in the response array.

// Overlap detection expression for Compose action
map(body('Get_events_(V4)')?['value'], lambda('event1', 
  {

// Overlap detection expression for Compose action
map(body('Get_events_(V4)')?['value'], lambda('event1', 
  {
    'summary': item()?['summary'],
    'start': item()?['start']?['dateTime'], 
    'end': item()?['end']?['dateTime'],
    'organizer': item()?['organizer']?['email'],
    'conflicts': filter(body('Get_events_(V4)')?['value'], lambda('event2',
      and(
        not(equals(item('event1')?['id'], item('event2')?['id'])),
        or(
          and(lessOrEquals(item('event1')?['start']?['dateTime'], item('event2')?['start']?['dateTime']), 
              greater(item('event1')?['end']?['dateTime'], item('event2')?['start']?['dateTime'])),
          and(less(item('event1')?['start']?['dateTime'], item('event2')?['end']?['dateTime']),
              greaterOrEquals(item('event1')?['end']?['dateTime'], item('event2')?['end']?['dateTime']))
        )
      )
    ))
  }
))