Calendar Item Aggregates with Exchange Web Services (EWS) Managed API 2.2

I love group calendars, who does not? Yet when you think about it, you would need to have dedicated calendars for specific purpose and I would imagine a nightmare afterwards to track. So, what about getting around this and instead asking people to create blockers on their calendars with specific subject format, type, or even details? This way you can use EWS to aggregate results from the various calendars and display them in one place.

What’s cooking for today?

We will use EWS managed API to get a list of calendar items for a specific group of people and put it in a table. To start, you will have to download and install EWS Managed API. At the time of writing this article, the latest version is 2.2 and can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=42951. Once it’s installed, we are ready to get some code on. So go ahead and create your project. I will use Web Forms, nothing personal but I could bind quickly to a ASP.NET GridView. You will need to add the API assembly to the project from here: C:\Program Files\Microsoft\Exchange\Web Services\2.2. You will need to add a using statement for this namespace: Microsoft.Exchange.WebServices.Data.

1. using Microsoft.Exchange.WebServices.Data;
2. using System;
3. using System.Collections.Generic;
4. using System.Data;

 

Connect me to EWS

Since this is a Web Form, I will have my fun on Page_Load. I created a function called Populate Appointments to run the query for me. Notice that I defined the time period of the next 60 days. So logically I will get all appointments that will take place in the next 60 days for a specific group of people.

1. protected void Page_Load(object sender, EventArgs e)
2. {
3.     //Let's create a reference to an Exchange service, I am running 2010 with SP2 in my Azure Lab
4.     var service = new ExchangeService(ExchangeVersion.Exchange2010_SP2);
5.  
6.     //and use Auto Discover to figure out where Exchange sits
7.     service.AutodiscoverUrl("your@li.as", RedirectionUrlValidationCallback);
8.     service.UseDefaultCredentials = true;
9.  
10.     //Define the query period time span
11.     var startDate = DateTime.Now;
12.     var endDate = startDate.AddDays(60);
13.  
14.     //Call the Popuolate Appoinments fuctions, which I will explain in a bit :-)
15.     PopulateAppointments(service, GetAttendees(), GetAvailabilityOptions(),
16.        startDate, endDate);
17. }

The PopulateAppointments function requires the following parameters:

• Instance of the Exchange Service which we defined at the beginning of the code block
• The group of people I want to query there calendars, To loosely-couple it, I created another function for you that returns the list of people called GetAttendees. It will return an IReadOnlyList of AttendeeInfo (Microsoft.Exchange.WebServices.Data.AttendeeInfo).
• A list of the types of meetings/empty slots to look for. The logic is kind of twisted here, yeah I get it. I could not find a way to send a query to get current meetings of a specific person, but if you do a lookup to schedule a meeting request you will get both empty and filled slots which is what we are looking for
• The time span, I used here 60 days. Note that by max you can do 62 days for a single request to EWS. I did not find this documented, I learned it when I got the following exception: (I used 90 days)

 

Finally, handle the redirection to validate if the callback is happening over a secure connection. I copied it from here: https://msdn.microsoft.com/en-us/library/office/dn567668(v=exchg.150).aspx

1. private static bool RedirectionUrlValidationCallback(string redirectionUrl)
2. {
3.     // The default for the validation callback is to reject the URL.
4.     var result = false;
5.  
6.     // Validate the contents of the redirection URL. In this simple validation
7.     // callback, the redirection URL is considered valid if it is using HTTPS
8.     // to encrypt the authentication credentials.
9.     var redirectionUri = new Uri(redirectionUrl);
10.  
11.     if (redirectionUri.Scheme == "https")
12.     {
13.         result = true;
14.     }
15.  
16.     return result;
17. }

Let’s get some items

Before we can get the query to happen, let’s first look at the functions we used as parameter inputs for the PopulateAppointments function.

1. private AvailabilityOptions GetAvailabilityOptions()
2. {
3.     //As I explained, logic is a bit twisted,
4.     //I pretend to schedule a meeting and thereofore I ask for a time slot
5.     //from the specifc person, and by design I would get back both free
6.     //and busy slots with there details
7.  
8.     var myOptions = new AvailabilityOptions
9.     {
10.         MeetingDuration = 30,
11.         RequestedFreeBusyView = FreeBusyViewType.DetailedMerged
12.     };
13.  
14.     return myOptions;
15. }

 

1. private IReadOnlyList<AttendeeInfo> GetAttendees()
2. {
3.     //Populate a static list of meeting attendees. This list will be the group
4.     //of people we are querying their calendar
5.     var attendees = new List<AttendeeInfo>
6.     {
7.         new AttendeeInfo("your@ali.as",
8.             MeetingAttendeeType.Required, false),
9.         new AttendeeInfo("another@ali.as",
10.             MeetingAttendeeType.Required, false)
11.     };
12.  
13.     return attendees;
14. }

So far we have an Exchange Server to query; a the time period less than 62 days, and a list of people. Let’s start digging in the PopulateAppointments function.

1. const string ResourceName = "Resource Name";
2.  
3. private void PopulateAppointments(ExchangeService service,
4.     IReadOnlyList<AttendeeInfo> attendees, AvailabilityOptions availabilityOptions,
5.     DateTime startDate, DateTime endDate)
6. {
7.     //Query Exchange for the Free and Busy timeslots from the specified list of people
8.     var freeBusyResults =
9.         service.GetUserAvailability(attendees, new TimeWindow(startDate, endDate),
10.             AvailabilityData.FreeBusy, availabilityOptions);
11.  
12.     //Being a web developer at heart, I think of a DataTable. Put some columns and
13.     //add the needed rows, aaaand voilaa just bind
14.     var calendarSet = new DataTable();
15.  
16.     //The table will have one column for an Alias and then the rest of the columns will
17.     //contain the meetings we are looking for to aggregate
18.  
19.     calendarSet.Columns.Add(ResourceName);
20.  
21.     //Create columns for the each day with the date as a title
22.     for (var col = 0; col < (endDate - startDate).Days; col++)
23.     {
24.         calendarSet.Columns.Add(startDate.AddDays(col).ToShortDateString());
25.     }
26.  
27.     var i = 0;
28.     //Let's loop the results set
29.     foreach (var availability in freeBusyResults.AttendeesAvailability)
30.     {
31.         //Logically table rows will be person driven, right? ;-)
32.         var userForecastRow = calendarSet.NewRow();
33.         userForecastRow[ResourceName] = attendees[i].SmtpAddress;
34.  
35.         //Let's loop the calendar items
36.         foreach (var calendarItem in availability.CalendarEvents)
37.         {
38.             //Are you a null? Would you break my code?
39.             if (calendarItem != null)
40.                 //Do you have the details I need?
41.                 if (calendarItem.Details != null)
42.                     //Do you have a subject? Remember, I am interested in meetings
43.                     //where the subject would contain specific prefix
44.                     if (!string.IsNullOrEmpty(calendarItem.Details.Subject))
45.                     {
46.                         //Do you have Prefix1 or Prefix2 in your subject?
47.                         if (calendarItem.Details.Subject.ToUpper().Replace(" ",
48.                             string.Empty).Trim().IndexOf("PREFIX1-", StringComparison.Ordinal) == 0 ||
49.                             calendarItem.Details.Subject.ToUpper().Replace(" ",
50.                                 string.Empty).Trim().IndexOf("PREFIX2-", StringComparison.Ordinal) == 0)
51.                         {
52.                             //If so then let's figure where in which column it will sit aka calendar day
53.                             int colIndex = 0;
54.                             foreach (DataColumn col in calendarSet.Columns)
55.                             {
56.                                 //Focus only on calendar days
57.                                 if (col.ColumnName != ResourceName)
58.                                 {
59.                                     if (DateTime.Parse(col.ColumnName).Date >= calendarItem.StartTime &&
60.                                             DateTime.Parse(col.ColumnName).Date <= calendarItem.EndTime.AddHours(-1))
61.                                     {
62.                                         //Are you a weekend end day?
63.                                         if (DateTime.Parse(col.ColumnName).DayOfWeek == DayOfWeek.Sunday)
64.                                         {
65.                                             userForecastRow[colIndex] = string.Empty;
66.                                         }
67.                                         //Are you a weekend end day?
68.                                         else if (DateTime.Parse(col.ColumnName).DayOfWeek == DayOfWeek.Saturday)
69.                                         {
70.                                             userForecastRow[colIndex] = string.Empty;
71.                                         }
72.                                         else
73.                                         {
74.                                             //You are the meeting I am looking for, let's add you to the calendar day column
75.                                             string preSeparator = string.Empty;
76.                                             if (userForecastRow[colIndex].ToString().IndexOf("-", StringComparison.Ordinal) > 0)
77.                                             {
78.                                                 preSeparator = ", ";
79.                                             }
80.                                             userForecastRow[colIndex] += preSeparator + calendarItem.Details.Subject;
81.                                         }
82.                                     }
83.                                 }
84.                                 colIndex++;
85.                             }
86.                         }
87.                     }
88.         }
89.  
90.         i++;
91.         //Done with this person, let's go and do it again untill we loop the list of people
92.         calendarSet.Rows.Add(userForecastRow);
93.     }
94.  
95.     //Since we have the DataTable ready, let's sort it by name and bind it to the GridView :-)
96.     var viewCalendarSet = calendarSet.DefaultView;
97.     viewCalendarSet.Sort = ResourceName;
98.     ForecastView.DataSource = viewCalendarSet;
99.     ForecastView.DataBind();
100. }

 

aaand this is how it looks

Yepee We can see now only the meetings that matched our prefixes for a specific group of people and a specific time period. You can take this an extra level and place some fields to allow users to select the time period, or an export button to push this GridView into an Excel sheet, etc.

Let me know if you have any questions in the comments section below. Enjoy.!

Comments

• Anonymous
  September 11, 2014
  I like how you twisted the logic from not useful to very useful
• Anonymous
  September 11, 2014
  Thanks =) It was the only way I found to get someone's calendar items without asking them specifically for permission.
• Anonymous
  September 12, 2014
  you talk about putting this in an excel sheet. how do you put it in excel sheet from grid view?
• Anonymous
  September 13, 2014
  There are a lot of ways to doing this. One would be using an HTMLWriter and then sending the response as an application/excel content type. Pick any of them: http://www.bing.com/search?q=export+gridview+to+excel+sheet&go=Submit&qs=n&form=QBLH&pq=export+gridview+to+excel+sheet&sc=0-10&sp=-1&sk=&cvid=6ed2b631ab52409eb2dfe52e625b8975
• Anonymous
  September 15, 2014
  Double good double again :)
• Anonymous
  December 20, 2014
  this was very useful and and I like bloggers who tell a real storyvery good