Introduction

For those of you that follow my blog, you may have caught that I’ve been doing some work lately migrating a non-profit organization to the Office 365/Azure platform, which includes SharePoint Online. As with many SharePoint environments, this non-profit depends heavily on calendars. The non-profit has various levels of leadership and membership and each level has its own calendar of events and important dates. However, this non-profit is made up largely of non-technical folks – some of which don’t even own a computer. They access O365 entirely via the browser on their phone and the apps that are available on the various mobile platforms. Unfortunately, SharePoint Online calendars and mobile devices just aren’t the best of friends, which means that it’s difficult for these members to access the events on their SharePoint calendars. To help with this situation, we needed to find a way to share those events outside of SharePoint itself. Our first pass was to create an application that runs nightly in an Azure WebJob, scanning each calendar in the system for events that are occurring in the next two days, and then send an e-mail to the membership of the related SharePoint site about those events. This system works ok and is still running in production today. However, what people really wanted was the ability to link their SharePoint calendars to the calendars on their mobile devices. This is how we did just that.

Converting SharePoint data to iCalendar data

The first step in providing an iCalendar file from a SharePoint calendar is to convert the SharePoint event information to iCalendar event information. The iCalendar RFC can be found here: http://tools.ietf.org/html/rfc5545. If you’ve ever dealt with RFCs before, you know that while they contain a wealth of information, they can be tough to process in your brain. To ease the pain a little, I found The Web Kanzaki site which provides excerpts from the RFC that are a little easier to follow, found here: http://www.kanzaki.com/docs/ical/. iCalendar format is pretty easy to follow and understand, but is not particularly forgiving. During development, I used a couple of iCalendar verifiers, http://severinghaus.org/projects/icv/ and http://icalvalid.cloudapp.net/, to confirm that my output was formatted correctly prior to going through all of the hurdles of actually loading the information into a calendar for rendering.

SharePoint event information is stored in a list item within the Calendar app. We can use the SharePoint CSOM to retrieve the calendar and its list items using code that looks something like this:

Uri uri = new Uri(remoteCalendarAccess.SiteAddress);
string realm = TokenHelper.GetRealmFromTargetUrl(uri);
var token = TokenHelper.GetAppOnlyAccessToken("00000003-0000-0ff1-ce00-000000000000", uri.Authority, realm);
ClientContext clientContext = TokenHelper.GetClientContextWithAccessToken(uri.ToString(), token.AccessToken);
 
clientContext.Load(clientContext.Web.Lists);
clientContext.ExecuteQuery();
 
List list = clientContext.Web.Lists.Where(l => l.Id == remoteCalendarAccess.CalendarId).First();
 
ListItemCollection items = list.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(items);
 
clientContext.ExecuteQuery();

After running this block of code, the items object should contain each of the list items (events) on the calendar. From there, we need to convert each of those list items into iCalendar format. For non-recurring events, this is pretty straightforward. If we consider a SharePoint event that looks something like this:

That same event in SP CAML Query Helper 2013 (http://spcamlqueryhelper.codeplex.com/) looks something like this:

Translated to iCalendar format, that event looks like this:

So, how do we do the conversion? The first step is to parse out the SharePoint fields into standard C# properties that can later be converted to iCalendar format. The following code accomplishes that task:

public class Event
{
    public int ID { get; set; }
    public string Title { get; set; }
    public DateTime Created { get; set; }
    public string Description { get; set; }
    public string Location { get; set; }
    public string Category { get; set; }
    public string UID { get; set; }
    public DateTime EventDate { get; set; }
    public DateTime EndDate { get; set; }
    public int Duration { get; set; }
    public bool Recurrence { get; set; }
    public string RecurrenceData { get; set; }
    public DateTime RecurrenceID { get; set; }
    public int MasterSeriesItemID { get; set; }
    public EventType EventType { get; set; }
    public bool AllDayEvent { get; set; }
    public DateTime LastModified { get; set; }
 
    public static Event Parse(ListItem sharepointListItem)
    {
        Event e = new Event()
        {
            ID = (int)sharepointListItem["ID"],
            Title = (string)sharepointListItem["Title"],
            Created = DateTime.Parse(sharepointListItem["Created"].ToString()),
            Description = (string)sharepointListItem["Description"],
            Location = (string)sharepointListItem["Location"],
            Category = (string)sharepointListItem["Category"],
            UID = sharepointListItem["UID"] == null ? Guid.NewGuid().ToString() : sharepointListItem["UID"].ToString(),
            EventDate = DateTime.Parse(sharepointListItem["EventDate"].ToString()),
            EndDate = DateTime.Parse(sharepointListItem["EndDate"].ToString()),
            Duration = (int)sharepointListItem["Duration"],
            Recurrence = (bool)sharepointListItem["fRecurrence"],
            RecurrenceData = (string)sharepointListItem["RecurrenceData"],
            RecurrenceID = sharepointListItem["RecurrenceID"] != null ? DateTime.Parse(sharepointListItem["RecurrenceID"].ToString()) : DateTime.MinValue,
            MasterSeriesItemID = sharepointListItem["MasterSeriesItemID"] == null ? -1 : (int)sharepointListItem["MasterSeriesItemID"],
            EventType = (EventType)Enum.Parse(typeof(EventType), sharepointListItem["EventType"].ToString()),
            AllDayEvent = (bool)sharepointListItem["fAllDayEvent"],
            LastModified = DateTime.Parse(sharepointListItem["Last_x0020_Modified"].ToString())
        };
 
        return e;
    }

Next, we need to take those data fields and inject them into the iCalendar format. This is basically simple string concatenation using a StringBuilder, like so:

public string ToString(List<Event> Events)
{
    StringBuilder builder = new StringBuilder();
 
    builder.AppendLine("BEGIN:VEVENT");
    builder.AppendLine("SUMMARY:" + CleanText(Title));
    builder.AppendLine("DTSTAMP:" + Created.ToString("yyyyMMddTHHmmssZ"));
    builder.AppendLine("DESCRIPTION:" + CleanText(Description));
    builder.AppendLine("LOCATION:" + CleanText(Location));
    builder.AppendLine("CATEGORIES:" + CleanText(Category));
    builder.AppendLine("UID:" + UID);
    builder.AppendLine("STATUS:CONFIRMED");
    builder.AppendLine("LAST-MODIFIED:" + LastModified.ToString("yyyyMMddTHHmmssZ"));
 
    if(AllDayEvent)
    {
        builder.AppendLine("DTSTART;VALUE=DATE:" + EventDate.ToString("yyyyMMdd"));
 
        double days = Math.Round(((Double)Duration / (double)(60 * 60 * 24)));
        builder.AppendLine("DTEND;VALUE=DATE:" + EventDate.AddDays(days).ToString("yyyyMMdd"));
    }
    else
    {
        builder.AppendLine("DTSTART:" + EventDate.ToString("yyyyMMddTHHmmssZ"));
        builder.AppendLine("DTEND:" + EventDate.AddSeconds(Duration).ToString("yyyyMMddTHHmmssZ"));
    }
 
    IEnumerable<Event> deletedEvents = Events.Where(e => e.MasterSeriesItemID == ID && e.EventType == EventType.Deleted);
    foreach(Event deletedEvent in deletedEvents)
    {
        if(AllDayEvent)
            builder.AppendLine("EXDATE;VALUE=DATE:" + deletedEvent.RecurrenceID.ToString("yyyyMMdd"));
        else
            builder.AppendLine("EXDATE:" + deletedEvent.RecurrenceID.ToString("yyyyMMddTHHmmssZ"));
    }
 
    if(RecurrenceID != DateTime.MinValue && EventType == EventType.Exception) //  Event is exception to recurring item
    {
        if(AllDayEvent)
            builder.AppendLine("RECURRENCE-ID;VALUE=DATE:" + RecurrenceID.ToString("yyyyMMdd"));
        else
            builder.AppendLine("RECURRENCE-ID:" + RecurrenceID.ToString("yyyyMMddTHHmmssZ"));
    }
    else if (Recurrence && !RecurrenceData.Contains("V3RecurrencePattern"))
    {
        RecurrenceHelper recurrenceHelper = new RecurrenceHelper();
        builder.AppendLine(recurrenceHelper.BuildRecurrence(RecurrenceData, EndDate));
    }
 
    if(EventType == CalendarModel.EventType.Exception)
    {
        List<Event> exceptions = Events.Where(e => e.MasterSeriesItemID == MasterSeriesItemID).OrderBy(e => e.Created).ToList<Event>();
        builder.AppendLine("SEQUENCE:" + (exceptions.IndexOf(this) + 1));
    }
    else
        builder.AppendLine("SEQUENCE:0");
 
    builder.AppendLine("BEGIN:VALARM");
    builder.AppendLine("ACTION:DISPLAY");
    builder.AppendLine("TRIGGER:-PT10M");
    builder.AppendLine("DESCRIPTION:Reminder");
    builder.AppendLine("END:VALARM");
 
    builder.AppendLine("END:VEVENT");
 
    return builder.ToString();
}
 
private string CleanText(string text)
{
    if (text != null)
        text = text.Replace(""", "DQUOTE")
                    .Replace("\", "\\")
                    .Replace(",", "\,")
                    .Replace(":", "":"")
                    .Replace(";", "\;")
                    .Replace("rn", "\n")
                    .Replace("n", "\n");
 
    return text;
}

Finally, it’s just a matter of calling .ToString() on our Event object to generate the iCalendar formatted information for that event. If we look closely at the .ToString() function, we’ll notice that there’s quite a bit of work involving recurrence information. That’s because converting the SharePoint recurrence information to iCalendar information is sort of challenging.

Dealing with recurrence

Learning about SharePoint recurrence information is exciting because there’s next to no documentation out there on how it actually works. I found a blog post on The High Tech Heels blog, http://thehightechheels.blogspot.com/2012/12/sharepoint-evenet-recurrencedata-xml.html, that explains it fairly simply and was a lifesaver. That still doesn’t make it easy.

Consider this SharePoint event that occurs once a week, on Tuesday:

Again, using SP CAML Query Helper, we can see the RecurrenceData field associated with that event, which looks something like this:

The necessary iCalendar entry for this SharePoint event looks like this:

Notice that the SharePoint recurrence information and the iCalendar RRULE contain the same information, but formatted very differently. To create that RRULE information, we used a helper class that parses out the SharePoint RecurrenceData field’s XML and then builds the RRULE entry. The class is lengthy, but is fairly straightforward. The great thing about converting from a SharePoint recurrence to an iCalendar recurrence is that most items map 1:1, which means there’s not any logic involved in trying to calculate and convert dates. It’s just a matter of mapping the fields. That helper class looks something like this:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;
 
namespace BusinessApps.RemoteCalendarAccess.Models.CalendarModel
{
    public class RecurrenceHelper
    {
        public string BuildRecurrence(string recurrenceData, DateTime endDate)
        {
            dynamic json = ConvertToDynamic(recurrenceData);
 
            dynamic repeat = json.recurrence.rule.repeat;
 
            StringBuilder builder = new StringBuilder();
            
            builder.Append("RRULE:FREQ=");
 
            if (repeat.daily != null)
                AppendDaily(builder, repeat.daily);
            else if (repeat.weekly != null)
                AppendWeekly(builder, repeat.weekly, json);
            else if (repeat.monthly != null)
                AppendMonthly(builder, repeat.monthly);
            else if (repeat.monthlyByDay != null)
                AppendMonthlyByDay(builder, repeat.monthlyByDay);
            else if (repeat.yearly != null)
                AppendYearly(builder, repeat.yearly);
            else if (repeat.yearlyByDay != null)
                AppendYearlyByDay(builder, repeat.yearlyByDay);
 
            if(repeat.repeatInstances != null)
                builder.Append(";COUNT=" + repeat.repeatInstances);
 
            if(json.recurrence.rule.windowEnd != null)
                builder.Append(";UNTIL=" + DateTime.Parse(json.recurrence.rule.windowEnd.ToString()).ToString("yyyyMMddTHHmmssZ"));
            else
                builder.Append(";UNTIL=" + endDate.ToString("yyyyMMddTHHmmssZ"));
            
            return builder.ToString();
        }
 
 
        private void AppendDaily(StringBuilder builder, dynamic daily)
        {
            builder.Append("DAILY;INTERVAL=" + daily.dayFrequency);
        }
 
        private void AppendWeekly(StringBuilder builder, dynamic weekly, dynamic json)
        {
            builder.Append("WEEKLY;INTERVAL=" + weekly.weekFrequency + ";WKST=" + json.recurrence.rule.firstDayOfWeek);
 
            if (weekly.su == "TRUE" || weekly.mo == "TRUE" || weekly.tu == "TRUE" || weekly.we == "TRUE" ||
                weekly.th == "TRUE" || weekly.fr == "TRUE" || weekly.sa == "TRUE")
            {
                builder.Append(";BYDAY=");
 
                if (weekly.su == "TRUE")
                    builder.Append("SU,");
                if (weekly.mo == "TRUE")
                    builder.Append("MO,");
                if (weekly.tu == "TRUE")
                    builder.Append("TU,");
                if (weekly.we == "TRUE")
                    builder.Append("WE,");
                if (weekly.th == "TRUE")
                    builder.Append("TH,");
                if (weekly.fr == "TRUE")
                    builder.Append("FR,");
                if (weekly.sa == "TRUE")
                    builder.Append("SA,");
 
                builder.Length = builder.Length - 1;  //  Remove the trailing comma from above.
            }
        }
 
        private void AppendMonthly(StringBuilder builder, dynamic monthly)
        {
            builder.Append("MONTHLY;BYMONTHDAY=" + monthly.day + ";INTERVAL=" + monthly.monthFrequency);
        }
 
        private void AppendMonthlyByDay(StringBuilder builder, dynamic monthlyByDay)
        {
            builder.Append("MONTHLY;INTERVAL=" + monthlyByDay.monthFrequency);
 
            if (monthlyByDay.su == "TRUE" || monthlyByDay.mo == "TRUE" || monthlyByDay.tu == "TRUE" || monthlyByDay.we == "TRUE" ||
                monthlyByDay.th == "TRUE" || monthlyByDay.fr == "TRUE" || monthlyByDay.sa == "TRUE")
            {
                builder.Append(";BYDAY=");
                
                if (monthlyByDay.su == "TRUE")
                    builder.Append("SU,");
                if (monthlyByDay.mo == "TRUE")
                    builder.Append("MO,");
                if (monthlyByDay.tu == "TRUE")
                    builder.Append("TU,");
                if (monthlyByDay.we == "TRUE")
                    builder.Append("WE,");
                if (monthlyByDay.th == "TRUE")
                    builder.Append("TH,");
                if (monthlyByDay.fr == "TRUE")
                    builder.Append("FR,");
                if (monthlyByDay.sa == "TRUE")
                    builder.Append("SA,");
 
                builder.Length = builder.Length - 1;  //  Remove the trailing comma from above.
            }
 
            if (monthlyByDay.weekday == "TRUE" || monthlyByDay.weekend_day == "TRUE")
            {
                if(monthlyByDay.weekday == "TRUE")
                    builder.Append(";BYDAY=MO,TU,WE,TH,FR");
                else
                    builder.Append(";BYDAY=SA,SU");
            }
 
            switch ((string)monthlyByDay.weekdayOfMonth)
            {
                case "first":
                    builder.Append(";BYSETPOS=1");
                    break;
                case "second":
                    builder.Append(";BYSETPOS=2");
                    break;
                case "third":
                    builder.Append(";BYSETPOS=3");
                    break;
                case "fourth":
                    builder.Append(";BYSETPOS=4");
                    break;
                case "last":
                    builder.Append(";BYSETPOS=-1");
                    break;
                default:
                    throw new Exception("Invalid weekdayOfMonth value");
            }
        }    
 
        private void AppendYearly(StringBuilder builder, dynamic yearly)
        {
            builder.Append("YEARLY;INTERVAL=" + yearly.yearFrequency + ";BYMONTH=" + yearly.month + ";BYMONTHDAY=" + yearly.day);
        }
 
        private void AppendYearlyByDay(StringBuilder builder, dynamic yearlyByDay)
        {
            builder.Append("YEARLY" + ";BYMONTH=" + yearlyByDay.month);
 
            if (yearlyByDay.su == "TRUE" || yearlyByDay.mo == "TRUE" || yearlyByDay.tu == "TRUE" || yearlyByDay.we == "TRUE" ||
                yearlyByDay.th == "TRUE" || yearlyByDay.fr == "TRUE" || yearlyByDay.sa == "TRUE")
            {
                builder.Append(";BYDAY=");
 
                if (yearlyByDay.su == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "SU,");
                if (yearlyByDay.mo == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "MO,");
                if (yearlyByDay.tu == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "TU,");
                if (yearlyByDay.we == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "WE,");
                if (yearlyByDay.th == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "TH,");
                if (yearlyByDay.fr == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "FR,");
                if (yearlyByDay.sa == "TRUE")
                    builder.Append(yearlyByDay.yearFrequency + "SA,");
 
                builder.Length = builder.Length - 1;  //  Remove the trailing comma from above.
            }
 
            if (yearlyByDay.day == "TRUE")
            {
                switch ((string)yearlyByDay.weekdayOfMonth)
                {
                    case "first":
                        builder.Append(";BYMONTHDAY=1");
                        break;
                    case "second":
                        builder.Append(";BYMONTHDAY=2");
                        break;
                    case "third":
                        builder.Append(";BYMONTHDAY=3");
                        break;
                    case "fourth":
                        builder.Append(";BYMONTHDAY=4");
                        break;
                    case "last":
                        builder.Append(";BYMONTHDAY=-1");
                        break;
                    default:
                        throw new Exception("Invalid weekdayOfMonth value");
                }
            }
 
            if (yearlyByDay.weekday == "TRUE" || yearlyByDay.weekend_day == "TRUE")
            {
                if (yearlyByDay.weekday == "TRUE")
                    builder.Append(";BYDAY=MO,TU,WE,TH,FR");
                else
                    builder.Append(";BYDAY=SA,SU");
                switch ((string)yearlyByDay.weekdayOfMonth)
                {
                    case "first":
                        builder.Append(";BYMONTHPOS=1");
                        break;
                    case "second":
                        builder.Append(";BYMONTHPOS=2");
                        break;
                    case "third":
                        builder.Append(";BYMONTHPOS=3");
                        break;
                    case "fourth":
                        builder.Append(";BYMONTHPOS=4");
                        break;
                    case "last":
                        builder.Append(";BYMONTHPOS=-1");
                        break;
                    default:
                        throw new Exception("Invalid weekdayOfMonth value");
                }
            }
        }
 
        private dynamic ConvertToDynamic(string xmlData)
        {
            StringReader reader = new StringReader(xmlData);
 
            XmlDocument doc = new XmlDocument();
            doc.Load(reader);
            string json = JsonConvert.SerializeXmlNode(doc);
            json = json.Replace("@", "");
            dynamic jsonData = JsonConvert.DeserializeObject(json);
 
            return jsonData;
        }
    }
}

The next challenge is dealing with exceptions to recurring events. Exceptions to recurring events are stored as a stand-alone SharePoint list item with an ows_MasterSeriesItemID that equals the recurring event’s ows_ID value, an ows_RecurrenceID that contains the start date/time of the original event instance that was changed, and an ows_EventType value that indicates whether the exception was a date/time change or a deletion. EventType specifics can be found here: https://msdn.microsoft.com/en-us/library/office/microsoft.sharepoint.speventtype.aspx.

Providing user access

Now that we more or less know how to convert a SharePoint calendar to iCalendar format, we need to find a way to provide user access to the iCalendar file. The first idea was to simply create the app within the SharePoint site and allow the user to call into the app. Generally, this idea works fine. However, in practice, it turns out that very few calendar clients are able to pass credentials when requesting the iCalendar file. So, even though we could point the calendar client to the SharePoint app to retrieve the information, we couldn’t pass credentials, which of course meant that SharePoint would reject the request entirely. Bummer. We finally settled on a solution where the user logs into the SharePoint site in the browser, clicks on a button that we added to the SharePoint calendar and is given a unique GUID based URL that provides a reference in a database to the particular user and the requested calendar. This accomplishes a few things:

1. Users are authenticated to Office 365 and are automatically authorized (or not) to the site and the calendar itself.
2. Users can access the iCalendar provider outside of O365 without providing credentials so long as they know the correct GUID.
3. When the user’s calendar client requests an updated iCalendar file, the system can confirm that the user’s account is still active and valid.

To provide these features to the user, we need to add a custom button to the SharePoint calendar. We can do that by starting with a standard App for SharePoint MVC project (walkthrough in the first few steps here: https://www.jonathanhuss.com/building-a-sharepoint-online-chat-room-with-signalr-and-azure-part-1-create-the-sharepoint-online-app-and-associated-website/), right clicking the App project and selecting Add -> New Item… and choosing Ribbon Custom Action, here:

You can name the button whatever you like. We chose ‘RemoteCalendarAccessorButton’. Adding the Ribbon Custom Action will take us through a wizard to configure the custom action. In the first screen, change the ‘Which particular item is the custom action scoped to?’ drop down to ‘Calendar’:

Leave the next screen as-is, because we’re going to modify the generated file anyway, and click Finish:

If all goes well, we should have a new custom action in our App for SharePoint project. If we expand the action, we should find the Elements.xml file. Opening Elements.xml looks something like this:

We need to make some changes here so that our button is added to the right place, opens a window that’s useful to us, and displays the image that we want it to display. We can change the Elements.xml file to look something like this:

The changes are as follows:

• HostWebDialog: This tells SharePoint that we want a dialog window to be opened when the user clicks the button.
• HostWebDialogHeight: This tells SharePoint the height of our dialog window.
• HostWebDialogWidth: This tells SharePoint the width of our dialog window.
• CommandUIDefinition.Location: This tells SharePoint that we want the button to be added to the Calendar.Actions section of the ribbon. We can find the names of the sections by using the F12 dev tools in any browser and looking at the ids of the HTML.
• CommandUIDefiniition.Button.Id: This is a unique identifier for the button that is ultimately stored in the Id attribute of the HTML tag when the button is rendered.
• Image32by32 and Image16by16: This is the location of the image that we want to use for the button. Notice that in this case, the image is relative to the root of the application.
• CommandUIHandler.CommandAction: This tells SharePoint which URL to load when the button is clicked.

Next, we need to add some code to our HomeController so that when the user clicks the button and is routed to the URL Action of the Controller, a screen is generated that gives the user the URL of the iCalendar file. The URL Action method needs to perform the following steps:

1. Create a context for SharePoint to determine the current user.
2. Retrieve the current user’s e-mail address and the URL of the site.
3. Create an entry in the database to store the user’s email address, the URL of the site, and the Id of the calendar.
4. Generate the URL and return it to the user.

The code behind the Action looks something like this:

[SharePointContextFilter]
public ActionResult URL()
{
    var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
    using (var clientContext = spContext.CreateUserClientContextForSPHost())
    {
        clientContext.Load(clientContext.Web, w => w.CurrentUser.Email);
        clientContext.Load(clientContext.Web, w => w.Url);
        clientContext.ExecuteQuery();
 
        RemoteCalendarAccessManager manager = new RemoteCalendarAccessManager();
        BLM.RemoteCalendarAccess rca = manager.AddRemoteCalendarAccess(Guid.Parse(HttpContext.Request.QueryString["SPListId"]),
                                                                        clientContext.Web.Url,
                                                                        clientContext.Web.CurrentUser.Email);
 
        ICSURLViewModel model = new ICSURLViewModel();
        model.URL = Request.Url.Scheme + "://" + Request.Url.Authority + Url.Content("~") + "?Id=" + rca.ID;
        return View(model);
    }                  
}

The code behind RemoteCalendarAccessManager.AddRemoteCalendarAccess uses standard Entity Framework that simply adds the RemoteCalendarAccess record to the database. We’ll talk about that more below.

Next, we need to create a View that will display the URL from this Action. That View looks like this:

@model BusinessApps.RemoteCalendarAccess.Models.ICSURLViewModel
 
@{
    ViewBag.Title = "URL";
}
 
<h4>Please use the following address to access this calendar from other applications.</h4>
 
<div>
    <a href="@Model.URL">@Html.DisplayFor(model => model.URL)</a>
</div>

At this point, assuming we’ve already added the necessary ConnectionStrings and SharePoint ClientId and ClientSecret records to the web.config, set permissions correctly in the AppManifest.xml file, and pointed our app to our development site, we should be able to run our new app from Visual Studio (walkthrough here: https://www.jonathanhuss.com/building-a-sharepoint-online-chat-room-with-signalr-and-azure-part-2-test-the-app-in-visual-studio/). Once the app is installed, if we navigate over to our Calendar and click the Calendar tab, we should find our shiny new button:

Suh-weet! If we click that button and all goes well, we should be presented with a window that looks something like this:

We could grab this URL and go stuff it into our favorite calendar client, but when the client tries to load the URL it would bomb because we haven’t created the Home/Index controller action yet. So, let’s go do that.

Accessing the database

In this case, we’ve chosen to deploy our new application to Azure, so we may as well use a SQL Azure database to store our user data as well. Only one table is necessary here, the RemoteCalendarAccess table, which contains a unique ID, the calendar ID, the site address, and the user name. For our production installation, we’ve also added a LastAccess which is described in the ‘Thoughts and Gotchas’ section at the end of this article. The table, with some data after a few tests, looks something like this:

Access to the database is done via Entity Framework Code First. All of the Data Layer functions are very straightforward with no logic in them. The Repository class that accesses the database looks like this:

using DLM = BusinessApps.RemoteCalendarAccessWeb.DataLayer.Model;
using BusinessApps.RemoteCalendarAccessWeb.DataLayer.Repositories;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace BusinessApps.RemoteCalendarAccessWeb.DataLayer.Repositories
{
    public class RemoteCalendarAccessRepository : Repository
    {
        public Model.RemoteCalendarAccess GetRemoteCalendarAccessById(Guid Id)
        {
            return _db.RemoteCalendarAccess.Find(Id);
        }
 
        public void CreateRemoteCalendarAccess(DLM.RemoteCalendarAccess remoteCalendarAccess)
        {
            remoteCalendarAccess = _db.RemoteCalendarAccess.Add(remoteCalendarAccess);
            _db.SaveChanges();
        }
 
        public DLM.RemoteCalendarAccess UpdateRemoteCalendarAccess(DLM.RemoteCalendarAccess remoteCalendarAccess)
        {
            DLM.RemoteCalendarAccess originalRCA = _db.RemoteCalendarAccess.Find(remoteCalendarAccess.ID);
            _db.Entry<DLM.RemoteCalendarAccess>(originalRCA).CurrentValues.SetValues(remoteCalendarAccess);
            _db.SaveChanges();
            return originalRCA;
        }
    }
}

In this class, _db is just the object that represents our data model. In our case, it’s a DataModel type, but by default, Entity Framework typically uses ‘Entities1’.

Verifying the user account

Each time a request is made into the iCalendar provider, we need to verify that the user’s account is still active. This is done by using the Azure Graph API and passing the alias that’s associated with the RemoteCalendarAccess record. The entire class that interfaces with Azure Active Directory looks like this:

using Microsoft.Azure.ActiveDirectory.GraphClient;
using Microsoft.Azure.ActiveDirectory.GraphClient.Extensions;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
 
namespace BusinessApps.RemoteCalendarAccessWeb.Utils
{
    public class AzureActiveDirectory
    {
        private string _accessToken;
        private ActiveDirectoryClient _client;
 
        public AzureActiveDirectory()
        {
            _client = GetClient();
        }
 
        private async Task<String> GetAccessToken()
        {
            if (string.IsNullOrEmpty(_accessToken))
            {
                string url = ConfigurationManager.AppSettings["AzureGraphAuthURL"];
                string appId = ConfigurationManager.AppSettings["AzureID"];
                string appSecret = ConfigurationManager.AppSettings["AzureSecret"];
                string serviceRealm = ConfigurationManager.AppSettings["AzureServiceRealm"];
 
                var context = new AuthenticationContext(url);
 
                var credential = new ClientCredential(appId, appSecret);
 
                var token = await context.AcquireTokenAsync(serviceRealm, credential);
                _accessToken = token.AccessToken;
            }
 
            return _accessToken;
        }
 
        private ActiveDirectoryClient GetClient()
        {
            Uri baseServiceUri = new Uri(ConfigurationManager.AppSettings["AzureGraphURL"]);
            ActiveDirectoryClient activeDirectoryClient = new ActiveDirectoryClient(new Uri(baseServiceUri, ConfigurationManager.AppSettings["O365Domain"]), 
                                                                                    async () => { return await GetAccessToken(); });
            return activeDirectoryClient;
        }
 
        public async Task<IUser> GetUser(string userPrincipalName)
        {
            IPagedCollection<IUser> users = await _client.Users.Where(u => u.UserPrincipalName == userPrincipalName).ExecuteAsync().ConfigureAwait(false);
            if (!users.CurrentPage.Any())
                throw new Exception("User " + userPrincipalName + " not found.");
 
            return users.CurrentPage.First();
        }
    }
}

Sharing the iCalendar data

Now that we’ve provided a URL for the user to subscribe, we should probably provide some data when that URL is requested. This can be done by adding some code to the Index action of the Home controller. That code needs to perform the following steps:

1. Validate that an Id is provided.
2. Lookup that Id and confirm that it exists.
3. Connect to Azure Active Directory and confirm that the user account associated to the Id is still active.
4. Get the event data from the SharePoint calendar.
5. Convert the events from SharePoint data to iCalendar format.
6. Return the iCalendar information as a text/calendar file to the client.

The code in the Index action looks like this:

public FileResult Index(Guid? Id)
{
    if (Id == null)
        return AccessDenied();
 
    RemoteCalendarAccessManager manager = new RemoteCalendarAccessManager();
    BLM.RemoteCalendarAccess remoteCalendarAccess = manager.GetRemoteCalendarAccess(Id.Value);
 
    if (remoteCalendarAccess == null)
        return AccessDenied();
 
    AzureActiveDirectory azureAD = new AzureActiveDirectory();
 
    IUser user = null;
    try
    {
        user = azureAD.GetUser(remoteCalendarAccess.UserId).Result;
    }
    catch (AggregateException e)
    {
        if (!e.InnerExceptions.Any(i => i.Message == "User " + remoteCalendarAccess.UserId + " not found."))
            throw;
    }
 
    if (user == null || user.AccountEnabled == false)
        return AccessDenied();
 
    manager.UpdateLastAccessTime(remoteCalendarAccess);
 
    Uri uri = new Uri(remoteCalendarAccess.SiteAddress);
    string realm = TokenHelper.GetRealmFromTargetUrl(uri);
    var token = TokenHelper.GetAppOnlyAccessToken("00000003-0000-0ff1-ce00-000000000000", uri.Authority, realm);
    ClientContext clientContext = TokenHelper.GetClientContextWithAccessToken(uri.ToString(), token.AccessToken);
 
    clientContext.Load(clientContext.Web.Lists);
    clientContext.ExecuteQuery();
 
    List list = clientContext.Web.Lists.Where(l => l.Id == remoteCalendarAccess.CalendarId).First();
 
    if (list == null)
        return AccessDenied();
 
    ListItemCollection items = list.GetItems(CamlQuery.CreateAllItemsQuery());
    clientContext.Load(items);
 
    clientContext.Load(clientContext.Web);
    clientContext.Load(clientContext.Web.RegionalSettings);
    clientContext.Load(clientContext.Web.RegionalSettings.TimeZone);
    clientContext.Load(clientContext.Web, w => w.Title);
 
    clientContext.ExecuteQuery();
 
    Calendar calendar = new Calendar();
    calendar.Title = clientContext.Web.Title + " - " + list.Title;
    calendar.Timezone = Timezone.Parse(clientContext.Web.RegionalSettings.TimeZone.Description);
    calendar.Events = items.Select(i => Event.Parse(i)).ToList<Event>();
 
    FileContentResult result = File(System.Text.Encoding.Default.GetBytes(calendar.ToString()), "text/calendar", "calendar.ics");
 
    return result;
}
 
private FileResult AccessDenied()
{
    Response.StatusCode = 403;
    return null;
}

And that’s it! We now have an application that allows users to subscribe to their SharePoint calendars from any calendar client.

Giving it a test drive

Now that the application is built, let’s give it a try. First, our SharePoint calendar looks like this. It has a single event on the 9^th, a weekly recurring event that occurs every Tuesday, and an exception to the recurring event on the 29^th.

If we click our ‘Remote Calendar Access’ button, the following window opens, and we can copy our Calendar URL:

Then, we can close the window and head over to our Office 365 Calendar:

Next, right click on Other Calendars and choose ‘Open Calendar’:

In the window that opens, paste our URL into the ‘Internet calendar’ box and click ‘Open’:

If all goes well, our SharePoint calendar should now be loaded into our OWA Calendar:

How freaking awesome is that?!

Better yet, in our testing, the built in e-mail systems on all of the major mobile platforms will automatically sync the calendar when you connect an Office 365 account as well.

Thoughts and Gotchas

• When we implemented this, we decided to add a ‘LastAccess’ column to the database. This allowed us to know if a particular link was still being used in the wild. Then we wrote a janitor Azure WebJob that runs once a day and cleans up anything that hasn’t been used in 30 days. This allowed us to ensure that any old, unused links weren’t just out there for someone to find.
• This implementation only checks that the user has authorization to the site/calendar when the URL is first generated, but not when the iCalendar information is retrieved. Thus, if permissions were changed on the calendar or SharePoint site to remove the user’s access, that user may still have access to the calendar data via this application. There are a couple of ways that this check could be implemented and might be worth a thought.
• We’ve had some challenges with event reminders. Sometimes they work and sometimes they don’t, despite being included on each iCalendar event. We haven’t put many cycles into discovering why this might be, but is definitely frustrating for some of our users. We did learn that if reminders are added to OWA directly, they work fine for that particular user.
• OWA syncs on some sort of schedule. This schedule appears to be every 3 – 4 hours. If an item is added/updated on the SharePoint calendar, it can take quite a while before OWA is updated with the new information.
• When adding the calendar to OWA, if something goes wrong on the back end, the calendar is still added with the name ‘Calendar’, but with no events and no other indication that an error has occurred.

Enjoy!