SharePoint To Google Calendar Synchronization

SharePoint To Google Calendar Synchronization

The SharePoint calendar can be a useful tool within an organization, allowing team members in different geographical locations to coordinate and share team-related events and meetings. Users would often like to view SharePoint events directly in their personal calendar. SharePoint has a feature that allows you to link your calendars with Outlook, enabling you to look at your calendars side-by-side. In this post I will focus on Google and investigate one-way synchronization from SharePoint to Google using the Camelot .NET Connector and the Google Data API.

Requirements

The examples require that you have a WSS3.0 or MOSS 2007/2010 environment available and Visual Studio 2010 installed on your development computer.

You also need to download and install Camelot .NET Connector from http://www.bendsoft.com together with a valid license key. Developer licenses are free for anyone.

In addition, you need to install the free Google Data API, which allows you to read and update events in your Google calendars.

External links

I found the following links helpful when learning the Google Data API.

Data API Developer’s Guide: .NET

Data API Atom Reference

C# Google Data API Unit Tests (good examples)

Introduction

Single events and recurring events

Calendar events generally fall into two different categories. First we have the single events, which don’t repeat and only appears once in the calendar. The second type is recurring events, which shows up at regular times depending on a given repeating pattern. Either type will have a defined start and end time or it may be a full-day event that lasts for an entire day. While single events are quite simple to handle, recurring events are a little more difficult to work with. The Google API implements the iCalendar (RFC 2445) standard for recurring events. The iCalendar format is widely spread standard for exchanging calendar information between different applications. Unfortunately, SharePoint has its own XML based format for describing recurring events and we need to figure out how to convert between the two.

Selecting events from SharePoint

SharePoint calendar events are stored within lists and can be easily read using the Camelot .NET Connector as shown in the following example. The query used here selects all types of events. The ParticipantsPicker column is synonymous to attendees. The Lookup function expands the user information field and makes it include name and e-mail address.

private static DataTable LoadSharePointEvents(SharePointConnection connection)
{
    using (var adapter = new SharePointDataAdapter("SELECT ID, GUID, Title, Description, Location, EventDate, EndDate, Duration, fAllDayEvent, fRecurrence, RecurrenceData, MasterSeriesItemID, RecurrenceID, EventType, Lookup(ParticipantsPicker) AS Attendees, owshiddenversion FROM `Calendar` ORDER BY ID ASC", connection))
    {
        var dtEvents = new DataTable();
        adapter.Fill(dtEvents);
        return dtEvents;
    }
}

Selecting events from Google

The next example shows how to select events from your default Google calendar. The query is configured to include deleted events and the SingleEvent property tells the server that recurring events should not be expanded, which is typically what you want in synchronization scenarios. The query returns a feed of events known as EventFeed. It is a good thing to specify the max-results option because the default value is set to 25 events.

private static EventFeed LoadGoogleEvents()
{
    var calendarService = new CalendarService("myprogram");
    calendarService.setUserCredentials("myuseraccount.gmail.com", "mypassword");

    var eventQuery = new EventQuery()
    {
        Uri = new Uri("https://www.google.com/calendar/feeds/default/private/full"),
        SingleEvents = false,
        ExtraParameters = "showdeleted=true&max-results=1000"
    };

    var eventFeed = calendarService.Query(eventQuery);
    return eventFeed;
}

Inserting events into Google

The Google calendar API is quite easy to use once you have got into it. Most of the work lies in learning the difference between SharePoint and Google and understanding how to map the information between the two systems. The following example shows how to insert a single (non-repeating) event, represented by the EventEntry class, into Google from one of the data rows selected in the first example.

private static void CreateSingleGoogleEvent(DataRow drEvent)
{
    var eventEntry = new EventEntry();
    string eventUid = ((Guid)drEvent["GUID"]).ToString("N") + "@domain.com";

    eventEntry.Uid = new GCalUid(eventUid);

    eventEntry.Sequence = new GCalSequence()
    {
        IntegerValue = (Int32)drEvent["owshiddenversion"]
    };

    eventEntry.Title.Text = (string)drEvent["Title"];
    eventEntry.Content.Content = (string)drEvent["Description"];

    eventEntry.Locations.Add(new Where()
    {
        ValueString = (string)drEvent["Location"]
    });

    eventEntry.Times.Add(new When()
    {
        StartTime = ((DateTime)drEvent["EventDate"]),
        EndTime = (DateTime)drEvent["EndDate"]
    });

    var calendarService = new CalendarService("myprogram");
    calendarService.setUserCredentials("myuseraccount.gmail.com", "mypassword");
    calendarService.Insert(new Uri("https://www.google.com/calendar/feeds/default/private/full"), eventEntry);
}

Data mapping

The SharePoint calendar list

The calendar list has the following set of columns:

SharePoint column EventEntry property Datatype Description
ID None Int32 Event ID
GUID Uid Guid Globally unique identifier. It maps well to the iCal UID property defined in RFC 2445.
Title Title String Headline
Description Content String Description / Content
Location Locations[] String Location of the event
EventDate Times[].StartTime DateTime Start date and time of the event. For recurring events, the date part may be earlier than then the first instance in the series.
EndDate Times[].EndTime DateTime End date and time of the event. For recurring events, this column normally shows the end time of the last event in the series. For recurring events with no end date, this value is computed several years in the future.
Duration None Int32 Duration (in seconds) of the event
fAllDayEvent Times[].AllDay Boolean Indicates that the event is all-day
fRecurrence None Boolean Indicates that it is a recurring event
RecurrenceData Recurrence String For recurring events, this column contains a recurrence rule in XML format. This content can to be translated into a iCal recurrence rule defined in RFC 2445.
EventType None Int32 [0] Single event
[1] Recurring event
[3] Deleted recurring event
[4] Recurrence exception (modified event)
MasterSeriesItemID None Int32 For deleted or modified recurring events, this is the ID of the master event
RecurrenceID Sequence DateTime For deleted or modified recurring events, this column contains the date and time of the replaced event instance in the series
ParticipantsPicker (Attendees) Participants[] User List of attendees of the event. This column is not visible by default in the Event content type
owshiddenversion Sequence Int32 Event revision number

Converting the recurrence pattern

The recurrence pattern conversion from SharePoint to Google is one of the main tasks. In SharePoint, the recurrence pattern is stored in the RecurrenceData column and typically looks something like:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <daily weekday="TRUE" />
    </repeat>
    <windowEnd>2011-04-30T07:15:00Z</windowEnd>
  </rule>
</recurrence>

This example describes a daily event, which occurs on each weekday until the April 30th 2011. The time and duration of the events is found in the EventDate and Duration columns.

The corresponding iCal recurrence rule that Google understands is:

RRULE:FREQ=DAILY;WKST=SU;BYDAY=MO,TU,WE,TH,FR;UNTIL=20110430T071500Z;

First day of week

The firstDayOfWeek element specifies the day on which the workweek starts, which is important in some cases. This element corresponds to the iCal WKST rule part and the possible values are SU, MO, TU, WE, TH, FR or SA.

Start date and duration of recurring events

The event start date and time is given by the EventDate column. For recurring events, this value can be set to a date earlier than the first instance. The time part however, gives the exact time for the event and the Duration column shows the event length in seconds. Similarly, the iCal specification has the DTSTART and DURATION/DTEND property pairs.

Generally, date and time can be specified in three ways:

  • Local time without timezone. The time is said “floating” and not bound to a specific time zone. Example: 19701010T120000.
  • Local time with timezone. Example: TZID=Europe/Stockholm:19701010T120000
  • In UTC-time, identified by the “Z” character. Example: 19701010T120000Z

The iCal specification states that the value of DTSTART must be specified with time zone when used with a recurrence rule. It also states that each time zone must have a corresponding VTIMEZONE defintion. However, Google seems to accept known time zones without this definition so we will ignore it for now. In some cases, it can be more convenient to specify DTEND rather than the DURATION.

Example, this event that starts at 9:15 AM Stockholm time and lasts for 30 minutes:

DTSTART;TZID=Europe/Stockholm:20110101T091500

DURATION:PT1800S

Example, an all-day event that starts on December 23 and continues for two days:

DTSTART;VALUE=DATE:20111223;

DTEND;VALUE=DATE:20111225;

End date of recurring events

Recurring events can either end on a specific date, which is given by the windowEnd element or repeat a certain number of times, which is in that case given by the repeatInstances element. If none of these are provided in RecurrenceData, the event has no end date.

For recurring events, the EndDate column normally contains the date and time of the last instance in the series. If the event has no end date, EndDate still contains a computed date several years in the future. It is not very useful for us.

Similary, use the UNTIL rule part to specify end date in the recurrence rule. The specification states that it must be specified in UTC time. Use the COUNT rule part to specify number of occurrences.

Example, daily event that ends after 5 occurences:

RRULE:FREQ=DAILY;COUNT=5;

Example, daily event that ends on June 6th at 15:00 UTC time:

RRULE:FREQ=DAILY;UNTIL=20110606T150000Z;

Daily

Describes an event that occurs:

  • every dayFrequency day(s)
  • every weekday

Example, every second day for all eternity:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <daily dayFrequency="2" />
    </repeat>
  </rule>
</recurrence>

The dayFrequence attribute corresponds to the INTERVAL rule part and the weekday attribute can be translated to the BYDAY rule part as shown below.

Example, every second day:

RRULE:FREQ=DAILY;INTERVAL=2;

Example, every weekday:

RRULE:FREQ=DAILY;BYDAY=MO,TU,WE,TH,FR;

Weekly

Describes an event that occurs:

  • every [weekFrequency] week(s) on: [mo,tu,we,th,fr,sa,su]

Example, every third week on wednesdays and fridays:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <weekly weekFrequency="3" we="true" fr="true"/>
    </repeat>
  </rule>
</recurrence>

The weekFrequence attribute corresponds to the INTERVAL rule part and each day must be parsed into BYDAY as shown below.

Example, every third week on wednesdays and fridays:

RRULE:FREQ=WEEKLY;INTERVAL=3;BYDAY=WE,FR;

Monthly

Describes an event that occurs:

  • every [monthFrequency] month(s) on day [day]

Example, every month on day 25:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <monthly day="25" />
    </repeat>
  </rule>
</recurrence>

The monthFrequence attribute corresponds to the INTERVAL rule part and the day in month is given by BYMONTHDAY as shown below.

Example, every month on day 25:

RRULE:FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=25

Yearly

Describes an event that occurs:

  • every [yearFrequency] year(s) on day [day] in month [month]

Example, every year on December 24th:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <yearly month="12" day="24" />
    </repeat>
  </rule>
</recurrence>

The yearFrequence attribute corresponds to the INTERVAL rule part, the month in the year is given by the BYMONTH rule part and the day in month is given by BYMONTHDAY.

Example, yearly on December 24th:

RRULE:FREQ=YEARLY;INTERVAL=1;BYMONTH=12;BYMONTHDAY=24;

Monthly by day / Yearly by day

The montly-by-day and yearly-by-day recurrence patterns are extended versions of the two simplier forms, allowing a little more specific scheduling options.

Describes an event that occurs:

  • every [monthFrequency] month(s) on the [weekdayOfMonth=first, second, third, fourth, last] [day, weekday, weekend_day, su-sa]
  • every [yearFrequency] year(s) on the [weekdayOfMonth=first, second, third, fourth, last] [day, weekday, weekend_day, su-sa] in month [month]

Example, every month on the first weekend-day:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <monthlyByDay weekdayOfMonth="first" weekend_day="true" />
    </repeat>
  </rule>
</recurrence>

Example, every year on the last day of June:

<recurrence>
  <rule>
    <firstDayOfWeek>su</firstDayOfWeek>
    <repeat>
      <yearlyByDay weekdayOfMonth="last" day="true" month="6" />
    </repeat>
  </rule>
</recurrence>

As you can see, the weekdayOfMonth attribute points out a day relative to the beginning or the end of the month. In combination with one of the day, weekday or weekend_day attributes or a named day, it gives you the exact day in the month. These rules can be expressed using a combination of different iCal rule parts.

Example, monthly on the first weekend-day:

RRULE:FREQ=MONTHLY;INTERVAL=1;BYDAY=SA,SU;BYSETPOS=1;

Example, yearly on the last day of June:

RRULE:FREQ=YEARLY;INTERVAL=1;BYMONTHDAY=-1;BYMONTH=6;

Recurrence exceptions

Recurrence exceptions are exceptions to the recurrence rules. It can be either deleted or modified instances as indicated by the EventType column. For these events we need to know the MasterSeriesItemID and RecurrenceID values. The MasterSeriesItemID column gives the ID of the master item to which the exception belongs. The RecurrenceID columns provides the date and time of the instance that the exception replaces. Modified events are not specified in the recurrence rule, but instead via the OriginalEvent on the EventEntry. For deleted instances we can simply use the iCal EXDATE property as shown below.

Example, the following event occurs every day at 9:15 AM except on December 24th 2011:

RRULE:FREQ=DAILY;

EXDATE:20111224T091500Z;

Recurrence code implementation

The following piece of code implements the recurrence conversion from SharePoint to Google according to the research in the previous section. The method ConvertRecurrenceRule takes a DataRow with event data and a dictionary containing deleted instances that we know.

private static Recurrence ConvertRecurrenceRule(DataRow drEvent, Dictionary<Int32, List<DateTime>> deletedRecurringInstances)
{
    var doc = new System.Xml.XmlDocument();
    doc.LoadXml((string)drEvent["RecurrenceData"]);

    string iCalRule;

    //start time and duration
    if (!(bool)drEvent["fAllDayEvent"])
    {
        //specify start time and duration for normal events, time-zone is required
        iCalRule = "DTSTART;TZID=Europe/Stockholm:" + ((DateTime)drEvent["EventDate"]).ToString("yyyyMMddTHHmmss") + "rn";
        iCalRule += "DURATION:PT" + ((Int32)(drEvent["Duration"])).ToString() + "Srn";
    }
    else
    {
        //specify start and end date for all day events
        var date = ((DateTime)drEvent["EventDate"]).Date;
        double days = Math.Ceiling((double)(Int32)(drEvent["Duration"]) / 86400);
        iCalRule = "DTSTART;VALUE=DATE:" + date.ToString("yyyyMMdd") + "rn";
        iCalRule += "DTEND;VALUE=DATE:" + date.AddDays(days).ToString("yyyyMMdd") + "rn";
    }

    System.Xml.XmlNode nodeRepeat = doc.SelectSingleNode("recurrence/rule/repeat/*");
    var attributes = new System.Collections.Generic.Dictionary<string, string>();
    foreach (System.Xml.XmlAttribute attribute in nodeRepeat.Attributes)
        attributes.Add(attribute.Name, attribute.Value);

    switch (nodeRepeat.Name)
    {
        case "daily":
            //every [dayFrequency] day(s)
            //every [weekday]
            iCalRule += "RRULE:FREQ=DAILY;";

            if (attributes.ContainsKey("weekday") && attributes["weekday"] == "TRUE")
                iCalRule += "BYDAY=MO,TU,WE,TH,FR;";

            if (attributes.ContainsKey("dayFrequency"))
                iCalRule += "INTERVAL=" + attributes["dayFrequency"] + ";";

            break;
        case "weekly":
            //every [weekFrequency] week(s) on: [mo,tu,we,th,fr,sa,su]
            iCalRule += "RRULE:FREQ=WEEKLY;";

            System.Text.RegularExpressions.MatchCollection byday = System.Text.RegularExpressions.Regex.Matches(nodeRepeat.OuterXml, "(su|mo|tu|we|th|fr|sa)="true"", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            if (byday.Count > 0)
            {
                iCalRule += "BYDAY=";
                for (int i = 0; i < byday.Count; i++)
                {
                    iCalRule += byday[i].Groups[1].Value.ToUpper();
                    if (i < byday.Count - 1)
                    {
                        iCalRule += ",";
                    }
                }
                iCalRule += ";";
            }

            if (attributes.ContainsKey("weekFrequency"))
                iCalRule += "INTERVAL=" + attributes["weekFrequency"] + ";";

            break;
        case "monthly":
            //every [monthFrequency] month(s) on day [day]
            iCalRule += "RRULE:FREQ=MONTHLY;";

            if (attributes.ContainsKey("day"))
                iCalRule += "BYMONTHDAY=" + attributes["day"] + ";";

            if (attributes.ContainsKey("monthFrequency"))
                iCalRule += "INTERVAL=" + attributes["monthFrequency"] + ";";

            break;
        case "monthlyByDay":
        case "yearlyByDay":
            //every [monthFrequency] month(s) on the [weekdayOfMonth=first,second,third,fourth,last] [day,weekday,weekend_day,su-sa]
            //every [yearFrequency] year(s) on the [weekdayOfMonth=first,second,third,fourth,last] [day,weekday,weekend_day,su-sa] in month [month]
            iCalRule += "RRULE:FREQ=" + (nodeRepeat.Name == "monthlyByDay" ? "MONTHLY" : "YEARLY") + ";";

            string weekdayOfMonth = "";
            if (attributes.ContainsKey("weekdayOfMonth"))
            {
                switch (attributes["weekdayOfMonth"])
                {
                    case "first":
                        weekdayOfMonth = "1";
                        break;
                    case "second":
                        weekdayOfMonth = "2";
                        break;
                    case "third":
                        weekdayOfMonth = "3";
                        break;
                    case "fourth":
                        weekdayOfMonth = "4";
                        break;
                    case "last":
                        weekdayOfMonth = "-1";
                        break;
                }
            }

            if (attributes.ContainsKey("day") && attributes["day"] == "TRUE")
                iCalRule += "BYMONTHDAY=" + weekdayOfMonth + ";";

            if (attributes.ContainsKey("weekday") && attributes["weekday"] == "TRUE")
            {
                iCalRule += "BYDAY=MO,TU,WE,TH,FR;";
                iCalRule += "BYSETPOS=" + weekdayOfMonth + ";";
            }

            if (attributes.ContainsKey("weekend_day") && attributes["weekend_day"] == "TRUE")
            {
                iCalRule += "BYDAY=SA,SU;";
                iCalRule += "BYSETPOS=" + weekdayOfMonth + ";";
            }

            System.Text.RegularExpressions.Match namedday = System.Text.RegularExpressions.Regex.Match(nodeRepeat.OuterXml, "(su|mo|tu|we|th|fr|sa)="true"", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            if (namedday.Success)
                iCalRule += "BYDAY=" + weekdayOfMonth + namedday.Groups[1].Value.ToUpper() + ";";

            if (attributes.ContainsKey("month"))
                iCalRule += "BYMONTH=" + attributes["month"] + ";";

            if (attributes.ContainsKey("monthFrequency"))
                iCalRule += "INTERVAL=" + attributes["monthFrequency"] + ";";

            if (attributes.ContainsKey("yearFrequency"))
                iCalRule += "INTERVAL=" + attributes["yearFrequency"] + ";";

            break;
        case "yearly":
            //every [yearFrequency] year(s) on day [day] in month [month]
            iCalRule += "RRULE:FREQ=YEARLY;";

            if (attributes.ContainsKey("day"))
                iCalRule += "BYMONTHDAY=" + attributes["day"] + ";";

            if (attributes.ContainsKey("month"))
                iCalRule += "BYMONTH=" + attributes["month"] + ";";

            if (attributes.ContainsKey("yearFrequency"))
                iCalRule += "INTERVAL=" + attributes["yearFrequency"] + ";";

            break;
    }

    //first day of the week
    System.Xml.XmlNode nodeFirstDayOfWeek = doc.SelectSingleNode("recurrence/rule/firstDayOfWeek");
    if (nodeFirstDayOfWeek != null)
        iCalRule += "WKST=" + nodeFirstDayOfWeek.InnerText.ToUpper() + ";";

    //recurrence repeat count
    System.Xml.XmlNode nodeRepeatInstances = doc.SelectSingleNode("recurrence/rule/repeatInstances");
    if (nodeRepeatInstances != null)
    {
        var repeatInstances = Int32.Parse(nodeRepeatInstances.InnerText);
        iCalRule += "COUNT=" + repeatInstances.ToString() + "rn";
    }

    //recurrence end date (must be in universal time)
    System.Xml.XmlNode nodeWindowEnd = doc.SelectSingleNode("recurrence/rule/windowEnd");
    if (nodeWindowEnd != null)
    {
        var windowEnd = DateTime.ParseExact(nodeWindowEnd.InnerText, "yyyy-MM-ddTHH:mm:ssZ", null);
        iCalRule += "UNTIL=" + windowEnd.ToUniversalTime().ToString("yyyyMMddTHHmmssZ") + "rn";
    }

    if (nodeRepeatInstances == null && nodeWindowEnd == null)
        iCalRule += "rn";

    //deleted instances of the recurring event
    if (deletedRecurringInstances.ContainsKey((Int32)drEvent["ID"]))
    {
        foreach (DateTime deletedTime in deletedRecurringInstances[(Int32)drEvent["ID"]])
            iCalRule += "EXDATE:" + deletedTime.ToUniversalTime().ToString("yyyyMMddTHHmmssZ") + "rn";
    }

    return new Recurrence() { Value = iCalRule };
}

Google calendar synchronization FAQ

In this post I have outlined the basics for how to use the Camelot .NET Connector and the Google Data API to build rich calendar synchronization tools. We have also seen how to convert recurrence rules, which is a big part of the work. The next step is to design a complete and comprehensive solution for the whole synchronization process.

Bendsoft is planning to release services and open source tools for various synchronization tasks in short time including calendar synchronization. We will present more details on this site as soon as possible!

The following section contains answers to some frequently asked questions related to the Google Data API or calendar synchronization in general. It felt like a good idea to share these answers on one place!

My friends are being spammed! How do I prevent Google from sending notifications and invitations to attendees when inserting or updating events?

Use SyncEvent to prevent all UI-related activities, such as inviting attendees with email. Setting this property to true tells the server that the operation is going to be in “sync” mode. SyncEvent cannot be used with non-primary calendars without setting the “X-Redirect-Calendar-Shard: true” header.

Why does Google give error ”syncEvent requires the X-Redirect-Calendar-Shard header”?

This error occurs when you try set SyncEvent on a non-primary calendar. For some reason, you need to set the ”Redirect-Calendar-Shard: true” header to be able to do so. As far as I can see, there is no reason why you cannot always set this.

((GDataRequestFactory)calendarService.RequestFactory).CustomHeaders.Add("X-Redirect-Calendar-Shard: true");

Why can’t I get more than 25 events from Google?

By default, maximum 25 events are returned from the server. You can change this through the max-results query parameter.

How can I detect deleted Google events?

When an event is deleted, it is assigned status CANCELED and no longer shows up in the UI. You can tell the server to include deleted events by adding the showdeleted query parameter.

How can I restore a deleted Google event?

You can retrieve the deleted event and change the event status from CANCELED to CONFIRMED.

I am trying to restore a deleted Google event but it does not work. What am I doing wrong?

Make sure that you have set the SyncEvent property. That should do it!

How do I stop recurring events from being expanded?

Set the SingleEvents property to false on the EventQuery to NOT expand recurring events.

How do I create recurrence exceptions in Google?

Create a new event with the same UID as the master event and configure the OriginalEvent property. The master event must be created before creating the recurrence exception.

eventEntry.Uid = masterEventEntry.Uid;
eventEntry.OriginalEvent = new OriginalEvent()
{
    IdOriginal = masterEventEntry.EventId,
    OriginalStartTime = new When() { StartTime = (DateTime)drEvent["RecurrenceID"] },
    Href = masterEventEntry.SelfUri.ToString()
};

Why do I get error “Participant is neither attendee nor organizer”?

This happens when you try to insert events into a non-primary calendar. You need to add the calendar ID (randomcharacters@group.calendar.google.com) as organizer to participants.

How do I find the ID of a non-primary calendar?

In Google, open calendar settings for the calendar and scroll down to the “Calendar Address” section where you will find the calendar ID printed.

I am trying to synchronize SharePoint “Attendees” . How can I see the user’s e-mail?

You must set the ExpandUserFields option to true in your connection string. This will expand the information displayed in user columns.

How do I choose UID when synchronzing SharePoint events?

Since every item in SharePoint already has its own GUID it makes sense to use this. It allows tracking every event to its original source. The specification recommends that a domain suffix is added on the right side of the UID separated by the character @.

Example UID: 21EC20203AEA1069A2DD08002B30309D@mysharepointdomain.com

Can I search for event UID in my Google calendar?

No, it is not possible. You can however add an extended property to all your events. Extended properties can be searched for using the extq query parameter.

Example: extq=[uid:21EC20203AEA1069A2DD08002B30309D@mysharepointdomain.com]

What is sequence number?

The sequence number is the event revision number as defined in RFC 2445. It should be incremented by the organizer whenever significant changes are done. This can be mapped to the SharePoint item version (owshiddenversion).

This entry was posted in Google, SharePoint. Bookmark the permalink.

2 Responses to SharePoint To Google Calendar Synchronization

  1. Glen Jackson says:

    Very interesting! Is it possible to go the other way; sync a Google calendar into SharePoint?

  2. Hi Glen,

    Great question. Yes, it is definitely doable! In fact, the tricky part is to convert the recurrence pattern and dealing with recurrence exceptions. So we need to create something similar to the ConvertRecurrenceRule method that converts the other way around. Another thing to know about is that there is a column named “UID” in the SharePoint calendar list. We need set this column to the Google event’s UID so that we know where each event comes from. The rest is more or less a straight inversion of this example!

    The following example shows how to insert a new (single) event in SharePoint using the connector:

    Dim command As New Camelot.SharePointConnector.Data.SharePointCommand(“INSERT INTO Calendar SET Title = ‘MyTitle’, Description = ‘MyDescription’, Location = ‘Stockholm’, EventDate = ’2011-05-01 22:15:00′, EndDate = ’2011-05-01 23:15:00′, Duration = 3600, fAllDayEvent = False, fRecurrence = False, RecurrenceData = NULL, MasterSeriesItemID = 0, RecurrenceID = NULL, EventType = 0, UID = ’59cc32ab38e045c9974a8a5bc5bb20cd’”, connection)
    command.ExecuteNonQuery()

    /Ulf

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>