Syncing Office 365 Outlook to Google calendar using Power Automate

When my employer switched over from the Google office suite to Microsoft's Office 365, it caused unexpected disruption in my family.

I had been sharing my work calendar with my wife (sharing no details, just busy times). It became an integral part of her calendar, to help her coordinate (between her job and my job) who picks up and drops off my son from school, and to let her know when I was free to take a phone call during my work day.

Since the switchover to O365, my ability to share a calendar externally was eliminated. Based on documentation I could find, it should be possible, but the option doesn't appear in my employer's implementation of Outlook. I can view my personal Google calendar in my work Outlook calendar, but I can't share it the other way.

Microsoft's Power Automate provides a way to solve this problem. It lets you create flows of data, triggered manually or automatically by events, to move data around, not only among the Microsoft products but also through connections to external services such as Google. It's pretty cool. Not perfect, not documented so newbies like me can understand, but still possible to figure out.

I had found a Power Automate flow to sync Outlook calendar to Google calendar, but it relies on out-of-date APIs and is no longer offered in the flow library. So I set out to create my own.

---

O365 Outlook to Google calendar sync flow description

This is a one-way sync, from Outlook to Google. That's all I need; I don't need to sync from Google to Outlook. I can already see my personal Google calendar in Outlook. The flow must do three things:

• Add new events to the Google calendar when they appear in the Outlook calendar.
• Update a Google calendar event when an Outlook event is updated, or create a new Google event if an updated Outlook event doesn't already exist in Google.
• Delete an event from the Google calendar when the corresponding Outlook event is deleted.

These requirements mean that we need a way to correlate an Outlook event ID with a Google event ID. Because Power Automate flows can interact with O365 Excel, the list of IDs can be maintained in an Excel table.

I'll go through the building blocks in detail below, and how to set them up. Here's what overall flow looks like (click to enlarge):

It starts out with the flow being triggered by an action: an event was added, modified, or deleted from the Outlook calendar). An action type switch then directs the flow to one of three paths: add, update, or delete an event in Google calendar.

For the added case, the flow must do this:

• Convert the start and end times of the event into a format that Google calendar accepts.
• Create the event in Google calendar.
• Record the Outlook event ID and the Google event ID in a spreadsheet table.

For the updated case, the flow must do this:

• Convert the start and end times of the event into a format that Google calendar accepts.
• Read the Outlook event ID from the spreadsheet table.
• If the Outlook event ID is found, then update the Google event for the corresponding Google event ID.
• If the ID isn't found, then create a new Google calendar and spreadsheet table row as a new event. This handles the case in which a pre-existing event in Outlook gets updated but a corresponding Google calendar event doesn't exist.

For the deleted case, the flow must do this:

• Find the Outlook event ID in the spreadsheet table and get the corresponding Google event ID.
• Delete the event from the Google calendar.
• Delete the row for that event from the spreadsheet table.

---

Preparation

Before you can get this working, there are some steps you need to do first.

1. Create a Google calendar

Go to your Google account calendar. On the left-hand pane, find "Other calendars", click on "+" to expand the menu, and select "Create new calendar". Name it whatever you want. In this document I'll call it "MyWork". This is the calendar that receives the additions, updates, and deletions from Outlook in O365.

2. Create an Excel spreadsheet to store event IDs

From your O365 apps, create a new Excel spreadsheet. In the screenshots, the spreadsheet I created is called CalendarSync.xlsx.

In the first row of the spreadsheet, fill in the first two cells with column header names. I called them "Outlook CalendarId" and "Google CalendarId". It doesn't matter what you call them. These two cells serve as the header row for the ID lookup table that the Power Automate flow uses.

Here's the critical part: Select the first header cell (Outlook CalendarId in my case), and click on Home → Format as table. Select a table style (doesn't matter which one) and tick the box "my table has headers".

Excel then creates an empty table called "Table1" in the spreadsheet. You can see this name appearing in some of the screenshots. The flow refers to this table.

3. Create a connection to Google calendar

In Power Automate, go to Data → Connections. If a connection to your Google calendar account doesn't exist, add it. The sync flow references the calendar you created through this connection.

4. Get the package (optional)

You can download the flow package, import it, and fill in the missing bits yourself, although once you've done the previous preparation steps you can also build it from scratch, which isn't hard to do. The information below should be sufficiently detailed to let you take whatever approach you want.

---

Setting up the sync flow

Set up the trigger

The top of the flow is the trigger, in this case "When an event us added, updated, or deleted (V2)". The "V2" is likely Microsoft's version. I don't think it matters. The important things are:

• Make sure the "Calendar Id" field is set to your Outlook calendar (typically "Calendar") that you want to sync.
• Click on the three-dot menu and make sure your Google account is selected, as shown (blacked out).

Below the trigger action, there's a "switch" control. Click on this to expand it. If you're using a template, all the switch cases are set up; otherwise you can add the "Action Type" switch as shown in the introduction, and set up cases for added, updated, and deleted events.

Set up case for adding an event

Here's what the case for adding an event looks like fully expanded (click to enlarge):

The case is called "Case added" because I renamed it that way, so I can tell them apart when they're collapsed. This isn't necessary though. Here are some things to note about each action:

• It is necessary to rename the two "Convert time zone" actions, so you can tell them apart when using the converted times later. In the screenshot, I renamed them by appending "(add start)" and "(add end)" to the default action titles. The time zones aren't actually being converted, just the time format. You need to convert the Outlook event's start and stop times to a format that Google calendar accepts. The format string isn't available in the drop-down; you have to select "Custom input" from the drop-down to type it in.
• The "Create an event" action is a Google action. If you're building this flow from scratch, when you add an action, type "Google" in the search filter to find it. You don't want to create an Outlook event here.
  • The Calendar ID field is the name of your Google calendar. If you called it "MyWork" then "MyWork" should be visible in the drop-down list.
  • The start and end times use the converted times from the previous actions.
  • Title, Description, and Location fields use Subject, Body, and Location data from the Outlook event.
• In the "Add a row into a table" action, the File is the name of the Excel file you created. You should select it from the drop-down, not type it in. Similarly, the "Table1" value in the Table field is the name of the table in your Excel file, and should also be selected from the drop-down.
• The last two input fields come from the header names you created in your Excel table. If you called them "OutlookID" and "GoogleID", those names will appear here. In my Excel file I called them "Outlook CalendarId" and "Google CalendarId". Note they get populated with two different data values, the Outlook event ID and the Google event ID (called "Event Event ID").

Set up case for updating an event

The case for updating an event (which I also renamed for clarity) is constructed with two parallel action flows. If the Outlook event ID could be found in the Excel table, the action on the left executes and updates the corresponding Google event. If an existing Outlook event received an update but it doesn't yet exist in the Google calendar, the "Get a row" action fails, and the action to the right executes to create a new event in exactly the same way as the case to add an event, described above.

Here is a screenshot of the expanded flow, with some actions collapsed because they've already been described above (click to enlarge).

• In the "Get a row" action, the File is your Excel file selected from the drop-down, as is the Table name. The Key Column field is also selected from the drop-down. In the screenshot this is "Outlook CalendarId" because that's how I named the header row in the Excel file. Whatever you named it should appear here. The Key Value field is the Outlook event ID to look up, to find the corresponding Google event ID used in the next action.
• The "Update an event" Google action has the same inputs as the "Add an event" action described previously, with the addition of the Event ID. This is selected from a pop-up list. In the screenshot, this is "Google CalendarId" because that's what I put in the header row for the Google ID column in my spreadsheet table. What appears here should be whatever you called your header row for Google ID in your spreadsheet table.
• If the "Get a row" action fails, we create a new Google event and add a row to the table, duplicating the actions in the case for adding events. However, you must designate this action as something to perform only if the prior action failed. To do this, click on the 3-dot icon on the right of the action, and select "Configure run after". Then you can check the box to do this action if "Get a row" failed.

Note that if the flow completes successfully via the failure branch, the run will still be designated as a "fail" in the run log.

Set up case for deleting an event

If an Outlook calendar event is deleted, then we must first find that event in the Excel table to get the corresponding Google ID, delete that event from the Google calendar, and finally delete the row from the table. Here's the expanded view:

All of these input fields have been described previously. Again, the Event ID in the "Delete an event" Google action is chosen from the pop-up list, and is the header column name in your Excel table for the Google event ID.

---

Pre-populating the Google calendar

The Google calendar starts out empty. This sync flow doesn't fill it up right away. The only events appearing on the Google calendar are those that get added or updated in Outlook while the flow is active.

So how do we start with a pre-populated calendar? We can't just export an Outlook calendar to Google, because then we wouldn't have a record of which Outlook event IDs correspond to which Google event IDs.

What we need is to create a simple flow to export Outlook events to Google calendar while building up an Excel table of ID values for each. It's a simple, manually-run flow. Here's how it looks:

The flow creates a new entry only if the "Get a row" action fails. Otherwise, it does nothing. The action after "Get a row" has its "Configure run after" setting to run only if the previous action failed.

You can download the package for this flow or build it yourself from scratch. You must configure all of the actions the same way as with the sync flow, with the same Excel spreadsheet and table, and the same calendar connections.

You can run this manually as many times as you want without creating duplicate entries because the flow creates entries only if they don't already exist.

---

Caveat

Both flows (sync and export) described here work OK, except they don't seem to handle recurring events very well. Some of them get into the Google calendar, but others don't. I suspect that recurring events that originated a long time ago (maybe due to my employer's recent switch-over to O365) don't appear in the Google calendar unless I update them in some way, like change my response to "Maybe" and back to "Yes", or make a small edit to the description if I own the event. Perhaps Microsoft doesn't support translating recurrence information between Office and Google (although it does seem to work for calendars shared from Google to Office). I am not sure how to fix this, but I can live with it.

At least now I can share this "MyWork" calendar with my wife, excluding the details, and she can once again see when I'm busy, when I have an early-day or late-day event. And I can see the events on my personal Google calendar without having to log into Outlook. So I'm satisfied.