Sync SharePoint Calendars Using Outlook Categories

We needed a dependable way to synchronize selected calendar items from several Outlook mailboxes with a central SharePoint calendar. Below you’ll find the approach, the hurdles, and the lessons we picked up along the way.

Overview

Only events with a clear marker were supposed to move from personal Outlook calendars to the team calendar in SharePoint. The sync had to run on a schedule so that leads always see recent vacation and absence data without manually comparing inboxes.

Requirements

• Synchronize selected events from multiple personal calendars with a SharePoint calendar.
• Cover the past X weeks and the upcoming Y weeks to capture short-notice changes.
• Run the synchronization every two hours without any user interaction.

Technical Implementation

A PowerShell script proved to be the most pragmatic solution. Via EWS we read Outlook events including their categories, filter them, and write the matches to SharePoint.

Collect categories and users

To decide which meetings should be synced, you can either rely on categories or on a fixed text prefix in the subject. Categories are easier to maintain because you define them once and avoid typos. The relevant mailboxes sit inside an Active Directory group that the script resolves.

$identities = Get-ADGroupMember Outlooktermine_Mitarbeiter -Recursive |
  Get-ADUser -Properties * |
  Select-Object Mail

Prepare the SharePoint calendar

Before writing new events we delete every list item in the SharePoint calendar that falls inside the target window. That keeps the calendar tidy and prevents stale duplicates when employees move or cancel appointments.

$cal = $web.lists.getbytitle('Name of your SharePoint calendar here')
$ctx.load($cal)
$ctx.ExecuteQuery()

$query = "<Query>
  <Where>
    <And>
      <Geq>
        <FieldRef Name='EventDate' />
        <Value IncludeTimeValue='TRUE' Type='DateTime'>" + $startDate + "</Value>
      </Geq>
      <Leq>
        <FieldRef Name='EndDate' />
        <Value IncludeTimeValue='TRUE' Type='DateTime'>" + $endDate + "</Value>
      </Leq>
    </And>
  </Where>
</Query>"

$items = [Microsoft.SharePoint.Client.ListItemCollection]$cal.GetItems($query)
$ctx.load($items)
$ctx.ExecuteQuery()

Filter and transfer appointments

Next we iterate through every mailbox in the AD group, load its events, and copy only the ones that carry one of the agreed-upon categories into the SharePoint calendar.

$categories = @("Urlaub", "Sonderurlaub")

foreach ($identity in $identities) {
  $appointments = Get-CalendarInformation -Identity $identity.Mail -weeksBefore $weeksBefore -weeksAfter $weeksAfter

  foreach ($appointment in $appointments) {
    $hasCategory = $false

    foreach ($appCategory in $appointment.Categories) {
      if ($categories -contains $appCategory) {
        $hasCategory = $true
        break
      }
    }

    if ($hasCategory) {
      Add-AppointmentIntoSPCalendar -Appointment $appointment
    }
  }
}

PowerShell function Get-CalendarInformation

The helper function below pulls the relevant appointments, limits them to the configured time span, and loads the properties we need for SharePoint.

function Get-CalendarInformation {
  <#
  .SYNOPSIS
      A PowerShell function to list calendar information
  .DESCRIPTION
      Long description
  .EXAMPLE
      PS C:\> Get-CalendarInformation -Identity "xxx" -weeksBefore 5 -weeksAfter 2
      Lists calendar information from the last 5 and the next 2 weeks
  #>
  [CmdletBinding()]
  param (
    [Parameter(Mandatory, ValueFromPipeline, ValueFromPipelineByPropertyName)]
    $Identity,

    [Parameter(Mandatory)]
    [int]
    $weeksBefore,

    [Parameter()]
    [int]
    $weeksAfter,

    [Parameter()]
    [System.Management.Automation.CredentialAttribute()]
    [pscredential]
    $Credential
  )

  begin {
    $libPath = $PSScriptRoot + "\libs\Microsoft.Exchange.WebServices.dll"
    Add-Type -Path $libPath
  }

  process {
    $Service = [Microsoft.Exchange.WebServices.Data.ExchangeService]::new()
    if ($PSBoundParameters.ContainsKey('Credential')) {
      $Service.Credentials = [System.Net.NetworkCredential]::new($Credential.UserName, $Credential.Password)
    }
    else {
      $Service.UseDefaultCredentials = $true
    }
    $Service.Url = "https://xx.xxx.at/EWS/Exchange.asmx"

    $FolderId = New-Object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar, $Identity)

    try {
      $Folder = [Microsoft.Exchange.WebServices.Data.CalendarFolder]::Bind($Service, $FolderId)
    }
    catch {
      Write-Host $_.Exception.Message "Name:" $FolderId.Mailbox.Address -ForegroundColor Red
      return $null
    }

    $startDate = [datetime]::Now.AddDays(-7 * $weeksBefore)
    if ($null -eq $startDate) {
      $startDate = [datetime]::Now
    }

    $endDate = [datetime]::Now.AddDays(7 * $weeksAfter)
    if ($null -eq $endDate) {
      $endDate = [datetime]::Now
    }

    $View = [Microsoft.Exchange.WebServices.Data.CalendarView]::new($startDate, $endDate)

    $View.PropertySet = [Microsoft.Exchange.WebServices.Data.PropertySet]::new(
      [Microsoft.Exchange.WebServices.Data.AppointmentSchema]::Subject,
      [Microsoft.Exchange.WebServices.Data.AppointmentSchema]::Start,
      [Microsoft.Exchange.WebServices.Data.AppointmentSchema]::End,
      [Microsoft.Exchange.WebServices.Data.AppointmentSchema]::Organizer,
      [Microsoft.Exchange.WebServices.Data.AppointmentSchema]::DateTimeCreated
    )

    $BodyPropertySet = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
    $BodyPropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text

    $FolderItems = $Service.FindItems($Folder.Id, $View)
    [void]$Service.LoadPropertiesForItems($FolderItems, $BodyPropertySet)
    return $FolderItems
  }
}

With this setup you can keep Outlook calendars and SharePoint in sync through PowerShell and EWS. Curious what else PowerShell can automate? Browse our blog.

Need the full script or want to adapt it to your scenario? Reach out and we’ll be happy to help.

Contact us