Barret
Friday, April 14, 2023 | 12 minutes
Using C# and Power Automate To Sync An Outlook Calendar
There are some good tools out there for syncing your Outlook calendar to other calendars, especially for O365 or Outlook.com calendars. And they’re well worth the money for the good ones. But sometimes, in certain circumstances (such as if you have an on-prem exchange server that you can’t access from the cloud), you just need a quick and dirty tool to copy calendar events from a local Outlook instance to another calendar. That’s something you can do pretty quickly with a little C# and some Power Automate.
In my particular case, I use Calendly to allow people to set up appointments with me. But Calendly wouldn’t connect to one of the calendars I need to use to block off time I am not available. That calendar was for an on-prem Exchange server account. So I knocked up a little utility to create copies of the events from that calendar that could be imported into a calendar that Calendly does monitor. And it’s a one-way refresh, so I’m not too concerned about overlap and things like that.
The C# App
Let’s start with the C# app. The app will grab the list of events from the default calendar and export them into a JSON formatted text file that the Power Automate flow will pick up and process. Depending on your setup you can automate dropping the file somewhere that Power Automate can automatically pick it up, but in this case it will just drop the file in the app’s bin folder when it runs.
We’ll go ahead and use the latest .NET Core C# Console app template for this. That’s v7 .NET Core for the one I created. And we don’t need any Nuget packages. However, we do need to reference a couple of COM DLL files for our Outlook interop. You’ll need to reference the packages that are installed with Outlook on your machine, since the version will need to match whatever version of Office you have installed locally. The two files you need to reference are:
Interop.Microsoft.Office.Core
Interop.Microsoft.Office.Interop.Outlook
Next I created a serializable class to represent the details I need for the events. I don’t need everything, so I kept it pretty simple.
[Serializable]
class EventItem
{
public string? Id { get; set; }
public string? Subject { get; set; }
public string? Content { get; set; }
public string? Location { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public bool IsAllDay { get; set; }
}
Since my only purpose was to create copies, there were a lot of details I didn’t need. So this information was sufficient.
Main Program
Now, let’s take a look at the parts of the main program. First, our Using statements. We’ll add references to our Interop classes and Json:
using Microsoft.Office.Interop.Outlook;
using Outlook = Microsoft.Office.Interop.Outlook;
using System.Text.Json;
Nothing else is needed for references, so the next thing we’ll need is a file to write to. This code will remove the file from the previous run and recreate it.
if (File.Exists("events.json"))
{
File.Delete("events.json");
}
using (var writer = File.Create("events.json"))
{
writer.Close();
}
For my purpose, I was only concerned with events over the next 90 days. So I set up a couple of date variables to mark my start and end periods. And I created a variable to store the list of events to export.
var items = new List<EventItem>();
DateTime starter = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day);
DateTime ending = starter.AddDays(90);
Prep work done, it’s time to move on to creating an instance of the Outlook interop to use to retrieve our calendar events. This assumes you’re using the default profile and default calendar in Outlook. If you’re using some other calendar you’ll need to do some manipulation to get the correct calendar to pull from. You’ll have to check the docs on the Outlook Interop to figure that out if that’s the case.
Outlook.Application oApp = new Outlook.Application();
Outlook.NameSpace oNS = oApp.GetNamespace("MAPI");
MAPIFolder calendar = oNS.GetDefaultFolder(OlDefaultFolders.olFolderCalendar);
Now that we’ve got a reference to our Outlook object and calendar, we can iterate through all the items in that calendar and extract the ones we want to export to our other calendar. First, let’s see the code, then I’ll call out some details of note.
if (calendar != null)
{
foreach (var item in calendar.Items)
{
var cItem = (AppointmentItem)item;
var startTime = cItem.StartUTC;
var endTime = cItem.EndUTC;
var subject = cItem.Subject;
var location = cItem.Location;
var content = cItem.Body;
var id = cItem.EntryID;
var allDay = cItem.AllDayEvent;
if (cItem.BusyStatus == OlBusyStatus.olFree || cItem.BusyStatus == OlBusyStatus.olTentative) continue;
if (cItem.StartUTC < DateTime.UtcNow.AddDays(-1) && !cItem.IsRecurring) continue;
if (cItem.IsRecurring)
{
Microsoft.Office.Interop.Outlook.RecurrencePattern rp = cItem.GetRecurrencePattern();
DateTime first = new DateTime(starter.Year, starter.Month, starter.Day, cItem.Start.Hour, cItem.Start.Minute, 0);
for(DateTime cur = first; cur <= ending; cur = cur.AddDays(1))
{
try
{
AppointmentItem recur = rp.GetOccurrence(cur);
if(recur != null)
{
var eventItem = new EventItem();
eventItem.Id = id;
eventItem.Subject = subject;
eventItem.Location = location;
eventItem.Content = content;
eventItem.StartDate = recur.StartUTC;
eventItem.EndDate = recur.EndUTC;
eventItem.IsAllDay = allDay;
items.Add(eventItem);
}
}
catch { }
}
}
else
{
var eventItem = new EventItem();
eventItem.Id = id;
eventItem.Subject = subject;
eventItem.Location = location;
eventItem.Content = content;
eventItem.StartDate = startTime;
eventItem.EndDate = endTime;
eventItem.IsAllDay = allDay;
items.Add(eventItem);
}
}
}
By default, the list in calendar.Items will retrieve everything on our calendar. But there are certain things we’re not interested in. First, we don’t care about any item where we’ve marked our status as free or tentative.
if (cItem.BusyStatus == OlBusyStatus.olFree || cItem.BusyStatus == OlBusyStatus.olTentative) continue;
We don’t want those to block off our calendar for other events. So, we’ll skip any items with those two status levels. Next, we also don’t want any items that fall outside of our 90 day window. So the next line of code:
if (cItem.StartUTC < DateTime.UtcNow.AddDays(-1) && !cItem.IsRecurring) continue;
will skip any item that is a single event and isn’t in our timeframe. That just leaves one block of items we need to work through: recurring events. For recurring events, we only want to grab any instances that fall within our 90 day window. So the next block of code parses recurring events, examines the recurrence pattern, and determines if any of those occurences happen during our 90 day window. Any that do get added to our list.
if (cItem.IsRecurring)
{
Microsoft.Office.Interop.Outlook.RecurrencePattern rp = cItem.GetRecurrencePattern();
DateTime first = new DateTime(starter.Year, starter.Month, starter.Day, cItem.Start.Hour, cItem.Start.Minute, 0);
for(DateTime cur = first; cur <= ending; cur = cur.AddDays(1))
{
try
{
AppointmentItem recur = rp.GetOccurrence(cur);
if(recur != null)
{
var eventItem = new EventItem();
eventItem.Id = id;
eventItem.Subject = subject;
eventItem.Location = location;
eventItem.Content = content;
eventItem.StartDate = recur.StartUTC;
eventItem.EndDate = recur.EndUTC;
eventItem.IsAllDay = allDay;
items.Add(eventItem);
}
}
catch { }
}
}
else
{
var eventItem = new EventItem();
eventItem.Id = id;
eventItem.Subject = subject;
eventItem.Location = location;
eventItem.Content = content;
eventItem.StartDate = startTime;
eventItem.EndDate = endTime;
eventItem.IsAllDay = allDay;
items.Add(eventItem);
}
And you’ll see in the else block above, anything that isn’t recurring and hasn’t already been eliminated, we’ll go ahead and add to our list to export.
Now that we have our list of events, let’s go ahead and serialize the list to JSON and export it to our file.
var json = JsonSerializer.Serialize(items);
using (var writer = File.AppendText("events.json"))
{
writer.Write(json);
writer.Close();
}
And that’s it for our C#. Run the console app and it will write the file out to the bin folder by default. As I mentioned, a better option is to drop the file into a OneDrive folder that Power Automate is watching, but that wasn’t an option for me in this case and I have to manually drop the file to a OneDrive folder via a web browser. Another thing you can do is set up a Windows Task Scheduler task to automatically run the console app daily, or weekly, so that the process is fully automated on the desktop end of things.
Power Automate
Now that our C# is done and we’re generating a file, let’s take a look at the Power Automate flow that will process our file. There are a few steps that we’ll need to implement. Because we’re doing a one-way sync to a destination calendar, we want to flag those messages that came from our on-prem calendar in some way so that we can keep track of which ones they are. By using that flag, we can do a quick remove and replace of those events that fall into our 90 day window that we’re wanting to maintain. It’s easier than doing a one for one comparison between existing events and the latest batch being imported.
Our trigger will be a file watcher that observes a folder in OneDrive waiting for a new events.json file to drop. Easy peasy.
Out With The Old
The next step is to get all calendar events from our destination calendar that fall within our 90 day window. For each of these events, we’ll look for the flag and if it exists, we’ll delete the event from the calendar. You can set up the flag however you want, but a simple approach is to add a piece of text onto the front of the subject line of the event. This is something easy to add, and easy to check for as we’re parsing our events list. But you could also add some kind of tag or flag that is supported by your calendar. For me, adding text to an event’s subject makes it easy to identify that those events came from my on-prem calendar at a glance.
In this case, I’m importing into an Outlook.com calendar and it’s very easy to filter those events using a start and end time to search within my next 90 days window. As you can see above, the actions we use are the “Get calendar view of events (V2)” and “Delete event” from the Outlook.com connector.
Now, in my screenshot above you can see I also included the “Get event (V2)” action, but you don’t actually need it. The “Get calendar view of events (V2)” actually contains all the information you need, such as the subject and ID of each event. I added it in here because I was playing around with examining some of the additional details that action gives you to see if there was something I liked better than adding text to the subject line. There wasn’t, but I didn’t clean up after myself. Shame on me. I’ve added technical debt. If this were a serious production app, I would make sure to clean that out. But since it’s a little hobby tool for myself, I haven’t gotten around to it. :)
In With The New
Now that we’ve cleaned out the events for the next 90 days, let’s add the new import items. At it’s simplest, we can just iterate the import file and add each of the items to our destination calendar, adding our flag to the subject line before we do. But we also want to maintain a clean calendar and avoid as much duplication as possible.
But what if we have events where perhaps invites were already sent to both calendars? We don’t want to duplicate it. Or perhaps we manually added the event to the other calendar before we set up our handy dandy sync tool. Again, we don’t want to duplicate it if possible. To avoid this as we parse through the events to import, we’ll check to see if any events already on our calendar already have the same subject and start date. And if so, we’ll skip the import for that item.
We’ll start with a Parse JSON action to parse through our import file content. For the file we set up with our C# app, we’ll use the following schema for the parse.
{
"type": "array",
"items": {
"type": "object",
"properties": {
"Id": {
"type": "string"
},
"Subject": {
"type": "string"
},
"Content": {
"type": [
"string",
"null"
]
},
"Location": {
"type": [
"string",
"null"
]
},
"StartDate": {
"type": "string"
},
"EndDate": {
"type": "string"
}
},
"required": [
"Id",
"Subject",
"StartDate",
"EndDate"
]
}
}
This will help us parse the file. Next we’ll loop through the body and pull out a few details from each item. We’ll use a Parse JSON action for the current item of our iteration, then a group of Compose actions to set up our location, body and to create our tagged subject line.
Again, the SetLocation and SetBody aren’t strictly needed, but I was playing around with different methods of tagging and matching the imported events.
Next, we’ll look for any matching events by subject line. We’ll use a “Get Events (V3)” action from our Outlook.com calendar connector and search for matching events by subject.
The formula for the replace you see is:
replace(replace(outputs('NewSubject'), '&', '%26'), '''', '''''')
This is to deal with a couple of special characters that need to be specially encoded to be able to pass in ODATA queries: specifically the ampersand and the single quote characters.
If we find no matches, we’ll go ahead an create our new event.
If we found a match by subject name, then we’ll check to see if the start date matches. To do so, we’ll iterate through the returned matches, do a Compose action to examine the start date using the following expression:
substring(items('Apply_to_each')?['Start'], 0, 19)
and then compare that value to the date of our new calendar item:
substring(body('CalItem')?['StartDate'], 0, 19)
And if no matches are found, we add the event to our calendar.
And finally, once we’ve parsed the file, we delete it from the OneDrive folder as our final step.
Conclusion
It’s a quick and dirty solution for a problem that you would think would no longer exist in our current internet driven tech world. But, sadly, the occasional need still arises. This was my solution. Maybe it will be of use to some of you who still have to deal with an on-prem Exchange server, yet need to consolidate multiple calendars into one so you know where you can be when.
For everything else… there’s Masterca…. oh, wait, wrong commercial. For everyone else, try Calendly or CalendarBridge, or about 50 other alternatives to syncing your calendars and making yourself available in one spot for scheduling.