Synchronize two Outlook calendars with Microsoft Power Automate

Having multiple calendars that are not synchronized often causes scheduling issues. To solve this problem, this post offers a Microsoft Power Automate flow to synchronize two Outlook calendars, as well as pseudo code of the synchronization algorithm. I explain the approach, and how it evolved from previous iterations (including their shortcomings).

Introduction

Managing your calendar events and availability at work is problematic if you have multiple calendars in different organizations. Here is a typical example scenario you may encounter:

  • You work in company #A which does some kind of work for your client, company #B.
  • #B creates external guest accounts for the employees of #A (for various technical reasons), meaning that employees of #A get an additional email and calendar account in #B (but employees of #B do not have such a guest account in #A).
  • Suppose that both #A and #B use Microsoft 365.

This scenario causes the following problems regarding scheduling of events, due to the lack of synchronization of the two calendars:

  1. Employees from #B will invite employees from #A to an event without knowing their actual availability (in the #A calendar). Employees of #A do not manually create events in both calendars, because it would be burdensome. The same problem happens in the other direction (when employees of #A invite people from #B or #A at time slots where they are already occupied, according to their #B calendar).
  2. People from either organization may miss out on events, because showing multiple calendars at once results in a crowded, illegible view in their email client. Consequently, most people only activate their own organization’s calendar, and are not shown any reminders for events that exist only in the other company’s calendar.

To solve these problems, I built a Microsoft Power Automate Flow. It regularly accesses both of your Microsoft 365 calendars. For every event in calendar #A it creates a corresponding blocker event in calendar #B, and vice versa. You can configure

  • how often the flow should run (e.g. every 2 hours),
  • the synchronization time window (measured in number of future days to synchronize, e.g. 30 days),
  • the prefix of each blocker event’s subject, meaning that an event with subject “Sprint Retrospective” in #A creates a blocker event titled “SyncBlocker: Sprint Retrospective” in #B, where the “SyncBlocker: ” prefix is configurable.

With such a regular synchronization in place, all that is left is for #A to communicate to #B employees that if they organize an event, they should always invite the #B guest accounts of #A-employees, rather than inviting #A-employees with their #A email address. This means that employees of #A still need to check mails in their #B guest account, to accept or reject event invitations, but they do not need to configure both calendars in Outlook.

Building this solution took me multiple iterations (more details below) and it was quite a lot of work. I wish there was an easier way. But alternatives I looked at always had some issues. For instance, let’s consider the subpar alternative of sharing your calendar. Being an #A employee, you could share your #A calendar with #B-employees. But that results in administrative effort for you, because you have to maintain the list of individual #B-employees you are sharing your calendar with. You also have to educate #B-employees to always invite your #A email address, and this might be politically difficult when #B expects #A to use their #B-guest-accounts.

Solution approach

When I did my PhD in synchronizing file systems, I also studied general data synchronization problems. The problem we need to solve in this particular case (calendars) is to apply two uni-directional synchronizations (from calendar A → B and B → A). With this approach, any change you make to the sync blocker events in the target calendar will be overwritten on the next synchronization, which is desired behavior (and this is why I did not solve the problem via bidirectional synchronization).

To get this synchronization scenario right, you need to take care of two things:

  • You need a stable identifier that helps main a relationship between a calendar-A-event and its corresponding calendar-B sync blocker event. This identifier helps you find (and update) the corresponding sync blocker event, if the calendar-A-event changes (e.g. its start/end time, subject or body).
  • You need to avoid infinite loops, which would happen if a calendar-A-event is synchronized to calendar B, that event is then synchronized back to calendar A, and so on, causing infinitely many duplicated events.

These two problems are solved as follows:

  • The Outlook calendar backend automatically generates a stable and unique ID for each event. When creating a sync blocker event, we need to write that original event’s ID to some field of the sync blocker event. I decided to use the location field, because all other fields seemed unsuitable to me.
  • To avoid infinite loops, I decided to add a prefix string to the subject field of a sync blocker event. The synchronization algorithm skips synchronizing those events, thereby stopping infinite loops. That prefix should be unique enough so that the chances are ~0% that I (or someone else) deliberately creates events that start with that prefix. I could also have used a different data field (e.g. adding a unique prefix to the body), but I like the side effect that I can discern calendar-A-events from sync blocker events at a first glance, just by looking at the event’s title/subject.

The following snippet shows the synchronization algorithm as TypeScript-esque pseudo code. Feel free to use it to build your own solution without Power Automate.


daysToSync = 30  // or any other number
syncBlockerEventPrefix = "SyncBlocker: " // or any other prefix
// Limit creating SyncBlocker events for only those events to which you responded in one of the following ways:
eventResponseTypes = ["organizer", "accepted"]

// Retrieve both calendars, such that cal1 and cal2 are arrays of objects with
// fields such as "subject", "body", "id", "location" or "responseType"
cal1 = getEventsFromCalendar(id=calId1, from=now(), to=now()+days(daysToSync))
cal2 = getEventsFromCalendar(id=calId2, from=now(), to=now()+days(daysToSync))

// Initialize filtered helper-arrays
syncblockerCal1 = cal1.filter(event => event["subject"].startsWith(syncBlockerEventPrefix))
realCal1 = cal1.filter(event => !event["subject"].startsWith(syncBlockerEventPrefix) && eventResponseTypes.contains(event["responseType"]))
syncblockerCal2 = cal2.filter(event => event["subject"].startsWith(syncBlockerEventPrefix))
realCal2 = cal2.filter(event => !event["subject"].startsWith(syncBlockerEventPrefix) && eventResponseTypes.contains(event["responseType"]))

// In cal2, create missing SyncBlocker events, or update the start/end times (or body/subject) of already existing SyncBlocker events
foreach (e in realCal1) {
  matchingCal2SbEvents = syncblockerCal2.filter(event => event["location"] == e["id"])
  if (isEmpty(matchingCal2SbEvents)) {
    createEventInCalendar(id=cal2Id, subject=syncBlockerEventPrefix+e["subject"], body=e["body"], location=e["id"], start=e["start"], end=["end"], timeZone=e["timeZone"], status="busy")
  } else {
    if (matchingCal2SbEvents[0]["start"] != e["start"]
      || matchingCal2SbEvents[0]["end"] != e["end"]
      || matchingCal2SbEvents[0]["body"] != e["body"]
      || matchingCal2SbEvents[0]["subject"] != syncBlockerEventPrefix+e["subject"]) {
      updateEventInCalendar(calId=cal2Id, eventId=matchingCal2SbEvents[0]["id"], subject=syncBlockerEventPrefix+e["subject"], body=e["body"], start=e["start"], end=e["end"])
    }
  }
}

// Find and delete orphaned SyncBlocker events in cal1
foreach (e in syncblockerCal1) {
  matchingRealCal2Events = realCal2.filter(event => event["id"] == e["location"])
  if (isEmpty(matchingRealCal2Events)) {
    deleteEventInCalendar(calId=cal1Id, eventId=e["id"])
  }
}

// Repeat both foreach-loops, but swap "1" with "2" for all variables (code omitted)Code language: JavaScript (javascript)

Step-by-step instructions to synchronize two Outlook calendars

Let’s see how to set up my Power Automate flow (that synchronizes two Outlook 365 calendars) in your own Power Automate environment. Please note that you need a corresponding subscription to be able to import the flow as a zip archive (or you set up the flow yourself, following the above pseudo code algorithm).

Try this first if you have a free Microsoft account

Before you continue reading the rest, I recommend that you first verify whether the importing feature is actually available to you. Open https://make.powerautomate.com, and click My flowsImportImport package (legacy). If you see an Upload form, you are good to go, otherwise you’ll need to pay for a subscription.

First, set up the two Outlook 365 connections. On https://make.powerautomate.com, click on DataConnections and add two “Office 365 Outlook” connections (for the calendars #A and #B), following the on-screen instructions.

Next, download my flow as zip archive from https://github.com/MShekow/outlook-calendar-sync.

Back on https://make.powerautomate.com, click My flowsImportImport package (legacy), click the Upload button, and choose the zip archive you just downloaded. In the form that is shown afterwards, click the links in the “IMPORT SETUP” column to fix any errors. The first row lets you configure the name of the flow that is created, and in the second and third row, you need to choose the two Outlook-connections you created earlier. Finally, click on “Import”.

    Next, we need to activate the flow, because it has been imported in a deactivated state (in which we cannot even trigger a test run). To do so, open the imported flow and click the “Turn On” button in the top menu.

    Next, we need to tune the settings of the flow and test whether it works as expected. Click on “Edit” to open the editor, then click on all those actions at the top that start with “Setting” and change the values as you want them. I added an explanatory comment to each setting that explains what it does. I highly recommend that you initially change the “Days to sync” setting to 1 day, then save the flow, and click on “Test” to run it.

    In the very first run, it is important to get the names of the calendars right (see actions “Setting: name of cal1/2”), or no synchronization will happen at all. Open your first run of the flow, and expand the “Check that calendars exist” action at the bottom. If you do not see any green checkmarks in the “If yes” half, the calendar names are not configured correctly yet. In this case, scroll up again, and expand the blue “Get calendars of Outlook account 1/2” actions. Take a look at their output (click Show raw outputs for better readability): the body > value > name field tells you the name of your Outlook calendar, as returned by the Outlook APIs. Please note that the returned name may differ from the calendar’s name shown in the Outlook UI. If the value of body > value > name of the calendar you want to synchronize is not “Calendar”, you need to open the edit view of the flow again and update its actions “Setting: name of cal1/2” respectively. Save the flow (top menu) and then run another test.

    If the calendar names are correct, you should verify (in Outlook) that your calendar events have been synchronized properly. Only then should you increase the “Days to sync”, and save+test the flow again. Please be aware that Power Automate is rather slow, so it’s perfectly normal if a test run takes a long time (e.g. 15 minutes for synchronizing 30 days). You can also check which write-operations were done by a flow execution, by expanding the “Check that calendars exist” box and then click on the very bottom action of that specific flow execution.

    Power Automate limitations

    Depending on your Power Automate plan, there are various limitations (see documentation). Most notably, the “Action request limits” limit the number of triggerable actions to 10’000 per day. Power Automate counts the requests in the worst possible way (for you): exemplary, if you have a for-each-loop that loops over 5 items, and you define two actions inside the loop, Power Automate counts these as 5*2=10 action requests. Consequently, you need to experiment with the number of days you want to sync, and the number of flow executions per day, to stay below the action request limits.

    Learnings

    It took me about 12 hours spread over multiple weekends to get the flow to its current working state that I consider “shareable”. I had to learn some truths the hard way, through experimentation.

    Event-based flows

    Initially, I started with an event-based flow (not a scheduled flow), where I used the “When an event is added, updated, or deleted (V3)” trigger, with a bunch of “if / else” blocks (and using the “Get Events” action) to determine what to do with the event. However, this solution had many problems:

    • It required configuring two flows (one for each sync-direction), and the logic was (in hindsight / comparison) much more complicated than the logic of the schedule-based version.
    • The Power Automate platform executes this trigger very often, which leads to throttling of the flow. For instance, the trigger executes when an event merely approaches (that is, when Outlook shows you the “15 minutes” notification). If it is a recurring event, the trigger is executed for many future instances of that event (sometimes dozens at once). Due to this spam, there is no way to debug flows, because there are hundreds of executions per day, and I wouldn’t even know which ones to look at if I wanted to diagnose a bug (e.g. a duplicated sync blocker event).
    • Because the synchronization trigger does not cover existing events, you would have to wait for an entire week for the synchronization to cover all events.
    • There are race-condition-related bugs leading to many duplicated blocker events (or failed flows). This is because the flows do not have a deterministic execution order or speed, and there is no atomicity. As a simple example: suppose you accept an event invitation at 10:00, and at 10:05 you decide differently and delete/reject the event. This triggers two flow runs (#1 and #2) that start at these respective times (10:00 and 10:05). In a perfect world, run #1 would complete, then run #2 would be executed. However, in practice, this can happen: at 10:01, run #1 already has verified that it needs to create a blocker event, but is now throttled mid-way, so that the actual “create event” action won’t be run before 10:10. Run #2, however, is not throttled and finishes by 10:06, not doing anything, because it did not even find any blocker event that it should have deleted. The end result is that a sync blocker event has been created, even though there is no originating event anymore. In hindsight, such a problem could be solved by disabling concurrent runs in the Concurrency control settings of the ‘When an event is added, updated or deleted (V3)’ action.

    Slow speed

    Power Automate is dead slow. For me, it takes about 15 minutes to synchronize 30 days worth of calendar events (which is about 120 events, coming down to ~6 events per day in a working week). The for-each loops are particularly horrible: a task that would take a slow language (such as Python) about 5 milliseconds takes about 10 minutes in Power Automate, e.g. iterating over an array of 120 items, filtering another array for matching values (not doing any network calls via Outlook-event-read/write/delete actions).

    Scheduled flows have problems, too

    Just like event-based flows, scheduled flows can also into throttling issues. These can be reduced by not running the flow at night, and also by disabling concurrent runs in the Concurrency control, to avoid completely overloading Power Automate. This requires that you manually overlook the flow executions during the first days after setting up the flow, to check that all flows executed successfully, or to reduce the execution frequency or number of future days to synchronize. Another disadvantage of scheduled flows is that the synchronization has a much higher delay than event-based synchronization.

    The Power Automate UI is a PITA

    Using Power Automate reaffirmed my loathing of low-code tools. While the visual blocks of low code tools reduce the learning effort that I would otherwise need to put into figuring out the right code snippets (e.g. to query the Outlook calendar), this quickly slows you down. Once your flow has reached a certain level of complexity, expanding all its action boxes results in a huge, unmanageable canvas in which I get disoriented immediately. I also found the Power Automate UI to be generally “jumpy”, with popups moving around randomly, or not closing properly. And it is slooooooooowww.

    Conclusion

    I built this solution out of a personal need, where the lacking synchronization between my employer’s calendar and our customer’s calendar caused me considerable scheduling headaches.

    The implemented Power Automate flow synchronizes two calendars, but it can be extended to synchronize three or more calendars: just duplicate the flow, always set the same Outlook calendar as the “cal1”, and use a different sync blocker prefix for each flow duplicate. The result is a star-shaped synchronization pattern, e.g. synchronizing A with B, A with C and A with D.

    If you need such a calendar synchronization, but want to implement it by other means than Power Automate, you can adapt the pseudo code I presented above and build a solution in any other programming language. If you need a synchronization that does not involve Outlook but Google calendars, you may be able to reuse the Power Automate flow I built, and replace the Outlook-specific actions with Google calendar actions, which are included by default (for free) in Power Automate.

    Did you have calendar issues of a similar type? How did you solve them? Let me know in the comments or on LinkedIn.

    Leave a Comment