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 to find the 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, now for the main part.
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, the script uses Autodiscover to get information about the email, I guess it'd be faster to set that data yourself, but this way it is doing everything for you, including error handling. 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.
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.
But there is a minor problem with it, however the way I did IMO is the best solution and most optimized.
In my case, appointments sometimes gets canceled and therefore gets removed from the database or moved to a different date/time, so old appointments do not get removed. Solution would be to have a 2 way check, one as it is now, and second in reverse - from Exchange against the CSV, but that wasn't a big problem for me and it'd take more time to do it - it's taking long enough as it is (for ~160 emails to process it takes ~12 minutes to finish, this takes so long due to Exchange having long response times when you do stuff in bulk)
Or, another solution would be to put all appointments in a separate folder and then when processing the mailbox, simply delete the folder and start anew, however that didn't work out right because phones for some reason wouldn't synchronize the calendar.
Or you could prepend the calendar item subject with something to identify the entries and delete them, but if you delete the calendar entries anyway and add old entries again (let's say your backend returns all the appointments from current month's 1st and today is 20th) then Outlook will show a popup that the user is late on his appointment that he had like weeks ago.
