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.

Comments

  1. Works really fine. Thank you, saved my life!

    ReplyDelete
    Replies
    1. Well, after using it for a couple months, it does have problems not syncing perfectly, leaving duplicated events in the Google calendar when they change, or deleted events not deleting. These seem to be always other people's invents that I'm invited to, not events I create. I am not sure why this happens. If anyone has improvements to suggest, please let me know.

      Delete
    2. Thanks very much for this. The duplicated events seems to be from a change to the API that means they're being put through the switch statement as a "created" event rather than an "updated" one, which then means you get two entries in the spreadsheet for two different google calendar IDs (with the same outlook ID). Then when it gets deleted, only one of these will be removed.

      I fixed this by using a conditional statement instead to branch off the delete action, and then doing the "get a row" test to see if it's an update (row exists) or new event (no row).

      P.S. You can add a "terminate" action after adding the new row to the table to prevent the "get row failed" event from making the flow as a whole failed.

      Delete
    3. OK, I am not sure I understand what you said about branching off the delete action. Also, if updates are being passed through the switch as "create", then why am I seeing flow failure reports, which indicate that the "fail" condition in the "update" switch happened? It seems to me, if updates are put through the switch as "create", then when a "create" event happens, I need to change the "create" switch to see if the spreadsheet row already exists, and if it does, then update the Google event instead of create a new one. If I do this, wouldn't the delete action remain unchanged?

      Delete
  2. Thank you very much for the sync flow. It works very well after a few tests.

    The link to download the flow for "Pre-populating the Google calendar" seems to be wrong, it is the exactly the same as the one for the previous flow.
    Could you please update the link to download this flow. Thanks in advance.

    ReplyDelete
    Replies
    1. You are correct. I updated it. Thanks for pointing that out.

      Delete
  3. Thank you very much for the sync flow. It works very well after a few tests.

    The link to download the flow for "Pre-populating the Google calendar" seems to be wrong, it is the exactly the same as the one for the previous flow.
    Could you please update the link to download this flow. Thanks in advance.

    ReplyDelete
  4. Alex, this rocks. Thank you!

    I built upon your work with a Flow that doesn't rely on Excel spreadsheets to keep track of event updates. For some reason it didn't function properly within my org. When an event is created, it puts the Outlook event ID into the Google event description. Then, when the Outlook event is edited, it finds the proper event in Google calendar by doing a search for this ID (or the Master event ID).

    It's available here: https://jacobfilipp.com/sync-outlook-calendar-to-google-calendar-using-microsoft-power-automate/

    ReplyDelete
    Replies
    1. Thanks Jacob, that's a good information page you have there. Good idea using the description field for the Outlook event ID. I couldn't figure out how to do this while preserving the original description at the same time. Two things I've learned from this:

      1. One doesn't need separate cases for Add and Update. It's enough to have just Delete and Other, with Other being the Update event handling in my flow, which takes care of both add and update. I simplified my flow accordingly and it's been working fine.

      2. Many of my flows fail because Google has its own limits on how often its calendar API may be accessed. Many of my flows fail with an error that the Google API call allowance was exceeded. I am not sure how to handle that yet. Most of the extra operations are from calendar entry updates, so perhaps I could maintain a second spreadsheet table to collect and consolidate these and run a second flow twice a day to manage those.

      Delete
  5. Hi there,

    First of all: great job of documenting and thank you for providing the templates. Really appreciated!

    I have a bit more info on the recurring appointments, in order to narrow down the problem:
    I recently started with a new employer, setting up all the good stuff you typically do when you get started - including setting up recurring events.
    For me, none of the recurring events was imported. Even though I did set them up very recently, even though I am the owner of the event, they are not synced. So I put my money more on "can't translate"

    Again, I owe you a beer!

    ReplyDelete
    Replies
    1. I eventually get the recurring events in the Google calendar, but they seem to recur as separate events, so deleting one doesn't delete them all. The Flows integration between O365 and Google certainly isn't perfect.

      Delete
    2. Yes, I can confirm this: for some reasons, the recurring events first get added... and then added again, again and again. Even when deleting the duplicates on GCal, they are added again and again :-/

      Delete
  6. Hello, use "Get calendar view of events (V3)" instead of "Get events (V4)" to catch recurring events.

    ReplyDelete
    Replies
    1. Thanks for the tip. The only time I'm using that, though, is in the flow to pre-populate the Google calendar, which is run just once. Subsequent recurring events that come in real time aren't added properly as recurring events, it seems, but as repeated individual events.

      Delete
  7. Hi, good work. It work well for me in the sync. But I found one issue is when I create calendar event in Outlook 365 software, it will show duplication event in Google calendar, while create an event in outlook.office.com at browser calendar page it wont happen a duplication event in Google calendar. Do you all face this same issue as well?

    ReplyDelete
    Replies
    1. I do see a lot of event duplication in the Google calendar. I assumed this happens when someone updates an event, my Outlook receives a notification of a change, and the change has a different event ID, so it gets duplicated again.

      Delete
  8. I am looking for a 2 way sync solution. Do you have any recommendations for that?
    I am testing this one way sync right now. Should I be able to build on it to have a reasonable solution for 2 way syncing?

    ReplyDelete
    Replies
    1. Possibly. I find that event communication from Google to Outlook is spotty. Also, with the one-way solution described here, I am getting a lot of duplicate events. It seems whenever an event changes, it gets a new ID and the old one isn't removed. I periodically have to go into the Google calendar and manually clean up the duplicates. I don't have a solution for that problem. It isn't hard to manage if the duplications are on one side only, but if both sides start getting duplications, it becomes really tedious to clean up.

      My purpose in creating this was to have a way to see my employer events (Outlook) on my personal calendar (Google) because my employer does not allow employee calendars to be shared externally. I can already set up my Outlook calendar to display my Google calendar in the same view with my Outlook calendar so I can see conflicts between events on both calendars while I'm at work. I wouldn't want personal and work events to be synced on the same calendar, though.

      Delete
  9. Amazing post that inspired me to look for an improved version that would deal with the recurring-event bug. Right now I'm test driving a new 'flow': 1/export all Outlook events to an ICAL ics file to Google Drive and 2/ Run an App Script that sync the ICAL ics file to a Google Calendar on a regular basis.

    In fact, only the first step is necessary as Google Calendar can import ICS files, but the update frequency is terrible (once every 1-2 days). So the second step helps to update the Google Calendar more frequently.

    Just give me a heads up if anyone is intrested in a write up of this (if everything continues to work :) )

    ReplyDelete
    Replies
    1. I'm interested to see what you come up with. In addition to the problems with recurring events piling on each other and not getting removed correctly, I have also experienced problems where Google's calendar API reaches a limit for queries, and Outlook sends me an email saying that one or more of my flows have failed. The error is always due to Google refusing the request after reaching a limit.

      Delete
    2. Took me a few days, but you can read what I have come up with here: https://dotsoltecti.github.io/blog/Outlook-Gcal-sync/

      Delete
    3. I want to try what you did but I get an error once I enter the yes and no conditions... Would you accept to export your Flow? I wouldn't dare to ask you for support on something you generously share with all of us but I'm sure I can figure out my mistake if I access the exported file.

      As for the method that is on top of this page, I tried it and it works very well except for recurring events. I may live with this one if I can't test Matthias' method.

      Delete
    4. I'm sorry, I figured it out.
      Now I'm trying to figure out how to add it to Google Calendar but I'm sure I'll get it. I think shareing the power automate flow you did would help other people. I didn't way a way to contact you directly so I'm asking you here : would you accept to share it on your site? Or if you don't mind can I share it here?

      Delete
    5. I got everything to work properly. Thank you very much.

      Only downside is I can't find a way to have the ''Body'' of the events... I didn't find a way to list the ''organizer'' and the ''attendees'' but I think it might be due to a limitation of the ICS standard. I'm still quite happy! If I need to see the details of a specific event, I'll simply swich to the Outlook calendar.

      At least now I can see my work events in the same calendar widget that has all my calendars.

      Delete
    6. Hi JBL - the description matches to the body. See the graphic above showing the "update event" details.

      Delete
    7. Sorry for not getting back earlier. I'm not really following the comments on this blog (don't have the time), but you could have left a comment on my blog. ;) I'm certainly open to share the 'flow' which has been working very well for me in the last months. Any pointers how I should to do that? That is, I'm not sure how I can do that without including in the flow my personal details?

      Delete
    8. Hi Matthias. I remember looking at your blog post when you first published it, but I was super busy that day and it slipped off my radar. You have an interesting approach. While it is a bit hard to follow without more screenshots (and you can black out parts of them for privacy, like I did in screenshots 2-4 on this page), I like that it doesn't make too many requests of the Google API. Mine sometimes sends me error messages when Google refuses the connection due to too many prior requests in a time interval. I also am not sure how your method deals with duplicating events.

      Delete
  10. This is so helpful to me, thank you very much!

    ReplyDelete
  11. Hi there, if I update the meeting in my Outlook, it somehow registers as a "create" action in Flow and creates a new Google calendar event, instead of updating the existing one. Any ideas how to address this issue?

    ReplyDelete
  12. When an event is updated, it somehow creates a new event instead of updating the existing event. The weird thing is, I can see in the Flow log that the action "Update an event" was used instead of "Create an event". Any ideas?

    ReplyDelete
  13. When you update an event in Outlook, sometimes this gets picked up as an "added" action instead of an "update" action. This means that in the Excel spreadsheet, you'll have 2 of the same Outlook calendar IDs with different Google calendar IDs. In other words, your Google Calendar will show 2 different entries, instead of one.

    I fixed this by copying the parallel branch from the "updated" action to the "added" action. Essentially, it'll just scan for an existing Outlook calendar ID and create one if it doesn't exist, or update it.

    ReplyDelete
  14. Seems like recurring events get duplicated constantly?

    ReplyDelete
  15. Thanks a lot, this works really good for my purpose. Also I had no idea I could program office365 this way, will definitely look into automate other tasks!

    ReplyDelete
  16. Alex,
    This information has led me on a three-month journey of success. Thank you so much for sharing this with all of us.

    I’ve designed three Power Automate flows to keep my Google Calendar synced with a SharePoint (online) calendar (list) based on your design in this article. While your employer uses Outlook, mine uses SharePoint.

    The first flow is triggered when a SharePoint item (calendar event) is created or modified, and if that event pertains to me, it either adds a corresponding new Google Calendar event or modifies one that currently exists. If the event already existed (was modified as opposed to being a new event) the flow also looks to see that it still pertains to me. If not, it deletes the event and the table row. I use an excel table just as you describe to keep these events synced—but my flows only flow in one direction (no data goes back into the SharePoint calendar [which serves as the calendar of record]).

    The second flow looks daily for any SharePoint events (their IDs) in the Excel table (which upon their entry to the Excel table pertained to me) that no longer exist in the SharePoint calendar (in case somebody deleted an event from the SharePoint calendar). If the table row exists (but the SharePoint calendar event does not), the corresponding Google Calendar event and the table row are deleted.

    The third flow (really the first to be run) is designed to pre-populate the Google Calendar with all my events that already exist in SharePoint—and this flow was really only run once (once all my testing was satisfactory). Again, all of this is based on your work.

    This system of flows works great to keep my personal Google Calendar synced with my own SharePoint events, but I am responsible to schedule the events of twelve other employees into that SharePoint calendar. Ideally, I’d like to provide each of them with a Google calendar of their own (which we each use regularly to reference from our mobile devices to establish leave or accept personal appointments around our working schedules). Currently I keep these twelve Google Calendars updated just once a week. I have a spreadsheet that helps do the conversions from SharePoint to Google Calendar, but the process takes about an hour each week.

    So, I’m wondering if I need 3 flows for each employee or are you aware of any way for which I can use another table of information to differentiate which Excel table (within that same Excel workbook) or which Google Calendar (within that same group Google account) to work with—based on the DisplayName of the employee which is passed through the flows via an ‘Apply to each’ step. What do you think?

    Thank you, and Happy Holidays!

    -Mark Langenkamp

    ReplyDelete

Post a Comment

Popular posts from this blog

New approach to screw threads in OpenSCAD

The water rocket: Thrust from water