Next-Gen calendar sync for Outlook and Google feature

Next-Gen calendar sync for Outlook and Google

by

Having multiple calendars that are not synchronized often causes scheduling issues. To solve this problem, I built a completely new Next-Gen Microsoft Power Automate flow that synchronizes Outlook with Outlook, Outlook with Google, or Google with Google calendars. The flow is free and open-source. Here I explain how it works and how to set it up.

Table Of Contents
  1. Introduction
  2. How the NG flow works
  3. Data privacy considerations
  4. Step-by-step instructions to set up NG flow
  5. Conclusion

Introduction

If you have multiple calendars, you may know the following problem: when scheduling a calendar event, you need to check all calendars to determine your availability, and other people (who can access just one of your calendars) may send event invitations at time slots where you are not actually available (→ scheduling conflicts).

I certainly suffered from this, so that I built two Power Automate (PA) flows in the past, one to synchronize two Outlook 365 calendars (blog post, code), and one to sync Outlook 365 with Google (blog post, code), due to popular demand.

However, these flows had some severe issues, which is why I built a completely new calendar synchronization flow, referred to as the “next-gen” (NG) flow, which I’ll present in this post.

The new flow has the following features:

  • Microsoft Power Automate flow that runs in the cloud in configurable intervals, e.g. every 30 minutes (→ your computer can be turned off)
  • Synchronization directions:
    • Outlook 365 <-> Outlook 365 (within the same tenant, or across different tenants, optionally via an HTTP mirror file)
    • Outlook 365 <-> Google
    • Google <-> Google
  • Uni-directional or bi-directional synchronization
  • Synchronizes the event fields: title, location, and description. If you synchronize Outlook 365 <-> Outlook 365, the fields sensitivity and show as are also synchronized
  • Optional anonymization of event fields with potentially sensitive data (event title, location, and description)
  • Configurable number of upcoming days to synchronize
  • Various options to label the blocker events so that you can easily identify them in your calendar (e.g. by adding a prefix to the title, or using an Outlook category)
  • Configurable which events to synchronize (e.g. excluding events from the sync that have an Outlook response type that you deem unsuitable, e.g. “tentatively accepted”, or excluding events that you created and that have no other attendees than yourself)
  • Option to perform a “dry run”, where you can inspect what changes the flow would make, without actually making them

Those who knew and used the old flows might be interested in the history and differences between the new NG flow and the old ones:

There were many reasons:

  • Flow execution was very slow (6-10 minutes), and quickly consumed the free quota of actions per day (see the PA docs, which mentions 10000 actions per day on the “low” profile). This was because the executed actions depended on the number of calendar events. Consequently, you had to reduce the number of synchronizations per day (undesired consequence: long synchronization delay), and/or reduce the number of future days to sync.
  • Broken Outlook <> Outlook sync if Cross-Tenant access is disabled: if you want to synchronize two Outlook 365 calendars across different tenants, and the admin of just one of these tenants chooses to block cross-tenant access, you can no longer configure an Outlook 365 calendar connection to another tenant in Power Automate. Consequently, the old flows no longer worked, because each flow executed a bi-directional synchronization (and thus was tightly coupled to having set up both calendar connections).
  • Lack of testability: PA flows are not using a proper programming language, and you cannot really test them (think: unit or integration testing).
  • Difficult debugging of user problems: I’ve gotten some issue reports from users that were difficult to reproduce. Even if users managed to get the calendar event list as JSON output from the flow (by digging deep into the nested loops), I would have had to build a lot of boilerplate to load their data (instead of my actual calendar’s data) into one of my flows and to “debug” the execution. I never did. 
  • Burdensome maintenance: maintaining two forks of the PA flows (Outlook <> Outlook, Outlook <> Google) was tedious, and reduced the release frequency.

The next-gen (NG) flow works completely differently than the old ones:

  • Uni-directional sync: the NG flow only writes the blocker events to one calendar (“calendar 1” in the flow), and reads the corresponding events from either another calendar (“calendar 2”) or an Internet-hosted mirror file. Consequently, to achieve bi-directional synchronization, you need to import 2 instances of the NG flow into your PA environment.
  • Abstraction of calendar source and destination: the NG flow supports Outlook <> Outlook, Google <> Google, and Outlook <> Google. The old flows did not support Google <> Google. In the NG flow’s code repository, there are different flow variants you can download:
    • One where you only need to configure Outlook 365 connections
    • One where you only configure Google connections
    • One where you configure both an Outlook and a Google connection (this is the actual flow I maintain, and from which I generate the other two)
  • Sync helper service: the NG flow executes much faster (and its complexity is reduced), because it uses a new Python-based sync helper service. The NG flow uploads the events of both your calendars to that service via HTTP, the service then computes and returns the necessary calendar actions (creation, deletion, modification of blocker events). I provide free hosting of this service, but you can easily host your instance.
    • To illustrate this with concrete numbers: if you have around 150 events to synchronize, the old flow would have taken ~6-7 minutes. The NG flow takes ~15 seconds. Increasing the days to sync (such that 300 events are synchronized) increases the NG flow execution time from 15 to ~17 seconds (only due to increased transfer times of JSON data to/from the Sync helper service). The NG flow scales with the number of calendar modification actions, thus you can now synchronize much more often. Before, with around 150 events, I could only synchronize 6 times per day. Now, I can synchronize every 15 minutes, 24/7, without running into PA platform restrictions.
  • Easy problem debugging (and fixing): if the NG flow sync does not behave as expected, as a user, you can (privately) send me the inputs & outputs of the HTTP action in the NG flow (the one calling the sync helper service). As a developer, I can reproduce the problem locally (with a real Python debugger) and fix the problem. In case you use my hosted offer of the sync helper service, I’ll deploy the fix, and your synchronization will work again, without you having to update / re-import the PA flow.

How the NG flow works

Synchronization algorithm in a nutshell

In simplified terms, the PA flow iterates over all events in one calendar and creates blocker events in the other calendar. This happens in (configurable) regular intervals. In order to efficiently create, update, or delete only those blocker events that are really needed, the flow must correlate the respective events of both calendars. To do so, we use an unique identifier that each calendar provider (Google / Outlook) automatically assigns to the events. We copy the unique ID of a real event into a fake attendee email address of the corresponding blocker event (using an email such as “sync@<unique-id>.invalid“). By checking for the presence of such an email address, the synchronization algorithm can also disambiguate your real events from the blocker events it created.

Let’s take a closer look at how the synchronization works, starting with uni-directional synchronization.

Uni-directional sync

The following image depicts a summary of the activities (blue boxes) of the PA flow, and where data is sent to or retrieved from (see the colored arrows). Let’s assume that we refer to the two calendars that we want to synchronize as X and Y, shown on the right (they could each be either a Google or Outlook calendar). The image only shows one flow instance (called #A). This one instance will only achieve uni-directional synchronization, writing blocker events (a.k.a. “SyncBlocker” events) only into calendar X.

Architecture of a single Power Automate flow that achieves uni-directional synchronization of an Outlook and Google calendar

Open the tabs to learn more details about the steps:

  • 1.1: Retrieve calendar events between “now” and the configured number of future days, in the proprietary format of the calendar provider (Google vs. Outlook)
  • 1.2: Store the events retrieved in step 1.1 in a PA variable
  • 2.1: Submit the events retrieved in step 1 to the Sync helper service, which filters out blocker events, converts the “real” events into a “unified” format, and returns them. In that unified format, all fields not relevant for the synchronization are removed, and all relevant fields have the same name and date format (Google and Outlook do often use different field names for the same concept, e.g. the event title field is called subject by Outlook and summary by Google)
  • 2.2: optional step! If you want to achieve bi-directional synchronization where the other flow instance is configured in a different Microsoft tenant (or PA environment), you can tell the Sync helper service to upload the events (via HTTP PUT/POST) to some web server, so that the other flow instance can download it from there.
  • 2.3: Store the events retrieved in step 2.1 in a PA variable

This happens in one of two variants (you configure which one to use):

  • Variant A (via HTTP mirror file)
    • 3a.1 + 3a.2: retrieve the mirror file from some HTTP-based file server (such as Nextcloud) using a “HTTP GET” request. I’m assuming that the mirror file is access-protected, which typically happens via an “Authorization” HTTP header, that has e.g. “Basic 524dsf05..” as value (a.k.a. “Basic Auth”, e.g. used by Nextcloud), or maybe a different value (e.g. “Bearer …”). In my experiments, I found that it’s not possible to make the GET request with the PA action “Send an HTTP request to SharePoint”, because when setting a header named “Authorization”, the action hangs forever – presumably because of a bug in that action. Consequently, the “Send an HTTP request to SharePoint” action instead makes the GET request to the Sync helper service, provides it with custom headers (X-Auth-Header-Name, X-Auth-Header-Value), and the Sync helper service forwards the request to the mirror file server in step 3a.2.
    • 3a.3: Store the events retrieved in step 3a.1/3a.2 in a Power Automate variable
  • Variant B (direct calendar retrieval)
    • 3b.1: Retrieve calendar 2 events between “now” and the configured number of future days, in the proprietary format of the calendar provider (Google vs. Outlook)
    • 3b.2: Store the events retrieved in step 3b.1 in a PA variable
    • 3b.3: see step 2.1 (filter out blocker events, convert the “real” events into a “unified” format)
    • 3b.4: Store the events retrieved in step 3b.3 in a PA variable
  • 4.1: submit the filtered event lists of both calendars to the Sync helper service, which returns three blocker event lists (some of which could be empty). One list indicates which blocker events to create in calendar 1 (in this case, calendar X), another list indicates which ones to change/update (because some fields of the corresponding calendar 2 event changed), another list indicates which ones to delete (because the corresponding real calendar 2 event has disappeared).
  • 4.2: Store the three event lists retrieved in step 4.1 in PA variables

The PA flow iterates over the three lists and calls the right PA actions to make the changes to calendar 1 (X).

Bi-directional sync

To synchronize both calendars in both directions, you need to set up an additional second instance of the PA flow, called instance #B. In that second flow’s settings, “calendar 1” now refers to calendar Y, to which the flow writes blocker events. The following image shows the full view of the data flow of both flow instances. The steps (1.1, 1.2, etc.) are the same as for the uni-directional synchronization, please refer to the above section for a detailed explanation of the steps.

Architecture of a both Power Automate flows that achieves bi-synchronization of Outlook and Google calendars

Data privacy considerations

Calendar events often contain sensitive information (e.g. unprotected links, names, email excerpts, etc.). You should always be careful where you send such data to. With the Next-Gen PA flow, there are two aspects that might worry you:

  • By default, you are sending your calendar events (including all details) to the Sync helper service, which is a server hosted by “some dude on the Internet” (me, in this case). I provide and maintain that service for free, for your convenience. https://ng-outlook-google-calendar-sync.onrender.com/ is hosted on render.com and runs somewhere in the US. (spoiler: the PA platform also runs somewhere in the US)
  • If you use the Mirror File feature (see Step 3, Variant A), you are also transmitting your credentials to the mirror file server to the Sync helper service.
  • By default, the HTTP calls are made by the “Send an HTTP request to SharePoint” PA action. This may seem weird, given that we don’t actually want to use any SharePoint sites (instead, I configure that PA action with the URL of the Sync helper service). The only reason why the flow uses “Send an HTTP request to SharePoint” instead of the “HTTP” PA action is because the “HTTP” action is a Premium action, meaning that you need to pay extra for Power Automate. I’m assuming that you (or your employer’s IT department) don’t want to pay for the premium plan. The main problem with the “Send an HTTP request to SharePoint” action is that it also submits an “Authorization” header that contains your Microsoft 365 bearer token!

If you feel uneasy with these facts, consider self-hosting the Sync helper service. The code is open source (Git repo), so feel free to inspect the code for back doors. You will see that the service is completely stateless: no data is permanently stored, and the Authorization header (that contains your Microsoft 365 Bearer token) is not even accessible to the Python code (because I did not declare it as an argument in any of the endpoint handler functions).

This means that you can deploy the Sync helper service in your own trusted environment. The easiest approach would be render.com: fork my code, create a free render.com account, and configure it to build and deploy your forked repo. But you could also run the helper service in any other way you like, e.g. with Docker, or installing it onto a VM directly.

Step-by-step instructions to set up NG flow

Let’s see how we can synchronize two calendars, referred to as X and Y.

Preparation steps

Try this first

Before you continue reading the rest of the instructions, I recommend that you first verify whether the import feature is available to you. Open https://make.powerautomate.com, and click
My flows → Import → Import package (legacy)
If you managed to see these menu items and see an Upload form, you are good to go. Otherwise, you’ll need to pay for a PA subscription.

First, set up the necessary connections. On https://make.powerautomate.com, click on … More → Connections and add the following connections, by clicking on the “+ New connection” button at the top, then using the Search box at the top right, to find the correct connection type:

  • One SharePoint connection (to any SharePoint site / account you like – we don’t connect to it, we just use the HTTP-related action of SharePoint to call the Sync helper service)
  • If you want to synchronize between two Outlook 365 accounts: Two Office 365 Outlook connections (unless setting up the second connection fails because the tenant admin disabled cross-tenant replication – in this case, just set up one Outlook connection)
  • If you want to synchronize between two Google accounts: Two Google Calendar connections
  • If you want to synchronize between an Outlook 365 and a Google account: One Office 365 Outlook connection, one Google Calendar connection

Next, go to the GitHub repo and download the correct zip archive mentioned in the Readme, depending on which providers (Google / Outlook) your calendars have.

Setting up flow instance #A

We now set up the first flow instance (referred to as #A), which writes blocker events to calendar X. 

Back on https://make.powerautomate.com, click My flows → Import → Import 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 other rows, you need to choose the connections you created earlier. Choosing the connections may cause you some confusion. You can choose the same Outlook / Google connection in both respective Outlook / Google rows, if you are importing the Outlook-Google flow (to synchronize Outlook with Google), or if you need to synchronize via the mirror file (using step 3 variant A, as explained in the “Uni-directional sync” section above).

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 (the UI might take several seconds until your click shows any effect, just be patient).

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 a note to each setting that explains what it does. Note that “calendar 1”-related settings refer to calendar X, and the “calendar 2”-related settings refer to calendar Y.

I highly recommend that you leave the “Days to sync” setting at 1 day initially and leave the “Dry run” setting at true. Click on “Test” to save and run the flow.

Once the run of the flow has finished, open the run. Take a look at the output of the action called “Compute calendar modification actions”, to verify that the correct blocker events are created: in the “events_to_create” entry, there should be one entry for each calendar 2 (calendar Y) event that occurs during the configured “Days to sync” period (except for those calendar 2 events you wanted to be filtered out).

If everything works, edit the flow again, and change the “Dry run” to false, then test the flow again. Open the calendar software that you normally use to manage calendar X and verify that the events were successfully created (with the Outlook Desktop app, the update can sometimes take some time). If everything works, you can edit the flow again and increase the “Days to sync” setting to a higher number.

How to choose “Days to sync”?

The Google- and Outlook-specific actions that retrieve your calendar events have a platform-side upper limit of events these actions return. These limits may change over time. As of November 2024, the Google limit seems to be 250 events. For Outlook you can specify a “Top count” value in the “Get calendar view of events (V3)” action, but it won’t return more than 1000 events. Although it’s theoretically possible to detect that the limit was reached and run these event-retrieval-actions in a loop (kind of doing “paginated calls”), I refrained from implementing this, to keep the flow simple.

Personally, I find that a value of 30 days is usually good enough. I never needed to synchronize my calendar any further into the future than that. But if your goal is to synchronize as long into the future as technically possible, you need to determine the “Days to sync” value experimentally! This depends on how full your calendar is. The more events you have on your calendar, the faster you will reach the limit of 250 (Google) or 1000 (Outlook) events. Play around with the “Days to sync” value and check how many events the corresponding Google- or Outlook-specific actions return. And leave some head room, because your calendar pressure may vary over time.

In this case, don’t forget to increase the value of the “Abort flow on too many events” setting. It is set to 250 by default, but if you want to synchronize Outlook only (which allows up to 1000), you need to increase the value.

Setting up flow instance #B

Once instance #A works, and if you want bi-directional synchronization, you can set up the second PA flow instance #B, either in the same Microsoft account/tenant or a different one.

Just repeat the steps explained in the “Setting up flow instance #A” section. But this time, in flow instance #B, the “calendar 1”-related settings refer to calendar Y, and the “calendar 2” settings refer to calendar X.

Remarks for synchronizing via an HTTP mirror file

Normally, you should not configure the HTTP mirror file and instead synchronize the two calendars directly with each other (by using the respective “Google calendar” or “Office 365 Outlook” connections). But if you are affected by cross-tenant replication restrictions, you need to use the mirror file, and you need to find a file server to host your two calendar mirror files. Here are a few helpful tips:

Since version 0.2 of the NG flow, you can either use a normal HTTP-based server or tell the sync helper service to upload your calendar data to a GitHub repository (with optional end-to-end encryption).

Before we look at each option in detail, consider setting the “Setting: cal1 upload or cal2 download encrypt or decrypt password (optional)” value to a self-chosen password (which needs to be the same in both flow instances), to ensure that your file server provider is unable to inspect your calendar data.

Option 1: GitHub.com repository

Assuming that you have a GitHub.com account, I suggest you create a private GitHub repository that comes with a pre-initialized branch. To do so, open https://github.com/new, choose a repository name, switch the visibility to Private and check the “Add a README file” box. This will generate a new “main” branch automatically.

Use a URL of the structure https://github.com/yourUsername/yourRepo/main/yourfile for the URLs in these PA actions:
“Setting: Calendar provider for calendar 2” or
“Setting: location of remote file to upload calendar 1 data to (optional)”.

For the PA actions
“Setting: calendar 1 remote file upload – authentication header value (optional)” and
“Setting: calendar 2 download authentication header value (optional)”,
you need to use a GitHub personal access token (PAT). Go here to create a new (fine-grained) PAT. Make sure to scope it to the new repository you just created. In the “Repository permissions” area, leave everything at “Access: no access”, except for Contents, which you need to set to “Read and write”.

Option 2: HTTP server

Any HTTP-based service will work that lets you upload files via HTTP PUT or POST. The server needs to be reachable from any Internet IP address, so you’ll want to use a server that offers authentication/authorization via some header, whose value remains stable over time since you probably don’t want to be forced to regularly update that header value in your flow(s).

I found e.g. Nextcloud or ownCloud to work well. You might be able to use a free Nextcloud provider (see here). Some pointers:

  • To get the base part of the upload URL of your Nextcloud user (that you would e.g. use in the PA action “Setting: Calendar provider for calendar 2” ), log into the Nextcloud server, open the “Files” app, click the “Files settings” button in the bottom-left corner. The URL shown in the “WebDAV” section is the right one. For ownCloud it should work the same way.
  • To retrieve the Authorization header value (“Basic …”), build a string in the format
    "nextcloud-username:password" and encode it with Base64:
    • UNIX / Linux (Bash): echo -n 'user:password' | base64
    • Windows (PowerShell): [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("user:password"))

I recommend that you configure slightly delayed timings in the “Trigger interval” setting (the very first setting at the top of the flow). For instance, you could run flow #A at minutes 0 and 30, and flow #B at minutes 15 and 45. Otherwise, if #A and #B run at the same time, you might run into some issues where e.g. flow #A downloads a rather outdated mirror file, which #B updated just a few milliseconds later.

Suppose you want to anonymize the field values of blocker events written to calendar Y (events are created by flow instance #B): you need to set the “Setting: calendar 1 anonymize data in uploaded remote file (optional)” to true in flow #A, because in that scenario, the “Setting: hide event details in SyncBlocker events” in flow #B won’t have any effect!

Uninstalling and cleaning your calendar

In case you have problems and want to stop using the calendar sync flow (or if you want to reset the synchronization), proceed as follows:

  • Stop your calendar flow instance(s), e.g. by clicking the “Turn off” button, or deleting them completely
  • Go to the GitHub repo to download the Delete flow zip file for Outlook or Google, and import it on https://make.powerautomate.com. Navigate to the imported flow, click “Turn on”, then edit the flow, read the instructions, and configure the settings.

Conclusion

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

I hope the flow helps you just as much as it helps me. If you run into any problems, feel free to post here in the comments, or to create an issue on GitHub.

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/Google calendar as the “calendar 1”, and use a different “unique sync prefix” setting for each flow instance. The result is a star-shaped synchronization pattern, e.g. synchronizing calendars X with Y, X with Z, and X with W, which (by transitivity) achieves that all calendars (X, Y, Z, W) are synchronized.

6 thoughts on “Next-Gen calendar sync for Outlook and Google”

  1. Hi Marius,

    Thanks a lot for the Automation! It is extremely helpful!

    I got a question though. In one of my Google calendars there are quite a lot of events I declined, but I don’t want to delete them completely. Is it possible not to synchronize them from Google to Outlook? Or am I missing this feature?

    Reply

  2. Hello!
    first of all, thank you very much for developing this flow, it worked great for me for the past weeks.
    However, it seems like “https://ng-outlook-google-calendar-sync.onrender.com/” is now “{“detail”:”Not Found”}” and thus, the flow fails.
    Any help? Thanks!

    Reply

Leave a Comment