Hello. Today I’m writing about a new tool that I come up with, after not finding this, lemme say – basic, functionality in Microsoft’s Exchange Server 2010. What was needed was very simple – to synchronize the schedule from a database/web backend of some sort to the exchange calendar and it turns out Exchange doesn’t have this or I didn’t find it and I looked EVERYWHERE.
Everyone uses Outlook 2010 with Exchange, and Outlook has the ability to keep track of internet/shared calendars like Google Calendar does, but it turns out that it’s an outlook only feature, meaning that if you added this calendar in outlook, all the appointments would appear, but wouldn’t be sent to Exchange. And I guess there is a way of adding appointments for all or a group of people by hand, but it’s not what I want.
Having that in mind I set out to search for a 3rd party solution, however I didn’t expect to find anything, and I didn’t… only option left was to write a custom solution.
My solution is in Powershell using Microsoft Exchange Web Services managed API, or EWS for short. I had never done anything in powershell before and had never scripted for Exchange before, but this was fun once I got it down, but getting to the fun part was really painful and it was hell. Because for some reason, I wasn’t able to find any kind of normal tutorials, only unfinished code samples, so it was a slow learning process.
Eventually I understood the EWS managed API reference and then it was easy.
I uploaded the script here to my projects folder. Now I will explain a bit how it works.
First it tries the defined EWS installation, you can install it from this Microsoft site. Note this is version 2.0 which is what I used and it worked great, AFAIK there are no significant changes between 2.0 and 2.2 so meh. After finding the installation, it loads the API and hooks to your Exchange server. Initiation part is done.
Main part starts from downloading the main CSV file from your web backend which contains users and unique URL’s for them in a format “email,url”. Emails obviously has to be in Exchange and valid.
Moving on, when email is discovered, it downloads the appointments in CSV format “ICalUid,Subject,StartDate,StartTime,EndDate,EndTime,Location,Body”. “ICalUid” is a unique for that event type identifier.
First check is done to remove any non-existing future appointments from the calendar to avoid clutter.
Then a check is done to see if there is any data, and if it has something to import, it checks if this appointment already exists, this is done by filtering out 10 appointments of the same date and time. In my case there could only be 1 with the same date, time and ICalUid, so we check ICalUid and if it’s a match, then we can assume it’s the same appointment and then check for integrity by comparing subject and location. Comparing body isn’t simple, because it’s not stored in plaintext format, but rather some fancy object, so I skipped that part. And finally if the event doesn’t exist and it doesn’t match with anything, then it is created.
That’s pretty much it, add it to Windows Scheduler and done.
Update 2016.04.11:
Ever since we switched over Office365 to use MS’s services, the calendar has been acting up. Some random appointments (mostly future appointments), at random times would disappear and that happens to everyone. The way they disappear is inconsistent and random and I haven’t noticed anything weird going on – they just disappear, or more precisely they go to the trash. I have tested it by adding same 4 appointments to every day of the month at same times and few random future events would disappear every week. This isn’t related to devices, because my account isn’t linked to any, Outlook was turned off at the time of testing as well. Seems that if you add the events by hand, then everything is ok, but if I add them with the script, then they get moved to trash… tried asking in the tech forum, that wasn’t much of a help… tried writing to microsoft, but they don’t provide Powershell help.