Team absence calendar

Everyone books time off the same way: an Outlook invite titled Firstname_OOO, sent to the team mailbox. The rules below are the ones the real system runs. Try sending an invite as anyone on the team.

Holiday Other absence Half day Public holiday in that person's calendar

Log

The Excel table the flows read and write. One line per absence.

Request IDNameTypeDurationStartEndStatusSourceNotes

What happens behind an invite

  1. A Power Automate flow watches the team mailbox calendar and picks up every invite whose title contains OOO.
  2. It calls an Office Script on the Excel file. The script checks the title belongs to the sender, counts working days against that person's public holidays, refuses overlaps and works out the leave balance.
  3. The script writes a line in the Log and returns the result. The flow emails the sender a confirmation or the reason it was refused.
  4. The mailbox sends an all-day OoO appointment to the whole team, shown as Free, so nobody's calendar is blocked.
  5. Moving or cancelling the meeting in Outlook updates the line and the OoO. Lines typed straight into the Log are picked up by a second flow.

This page re-implements those rules in the browser with a fictional team. Your changes stay in this browser only. Source and the Microsoft 365 version are in the repository.