We were looking for a way to synchronize certain calendar entries from different people with a SharePoint calendar. You can find out how we solved the whole thing and what to look out for and what problems there were in this blog post.
Request
- Synchronize specific calendar entries from different people with a calendar.
- Synchronize the dates of the last X weeks and the upcoming Y-weeks.
Implementation
Since the entries are to be synchronized every 2 hours, we decided to implement it with a PowerShell script. To find out which dates to synchronize, there are 2 options:
- Appointments with categories (each user must create the categories themselves)
- Synchronize appointments with a specific prefix
We have opted for Possibility 1. Because that is how the categories are created. This eliminates possible errors due to incorrect prefixes and typos. The color of the category does not matter. One drawback here is that there is no way to prevent the user from editing or deleting categories.
The calendar entries, including categories, are read using EWS.
First, their calendars must be synchronized to be read from the AD group.
$identities = Get-ADGroupMember Outlooktermine_Mitarbeiter -Recursive | Get-ADUser -Properties * | Select-Object Mail
Now you must delete all SPItems from the calendar where the start date is between the specified start and end date.
#*************************************
# Get calendar and items
#*************************************
$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()
Next, you go through each user of the AD group and read their calendar entries.
If one of these entries has the category you are looking for, that entry is applied to the SharePoint calendar.
$categories = @{"Urlaub", "Sonderurlaub"}
foreach ($identity in $identities) {
# Termine auslesen
$appointments = Get-CalendarInformation -Identity $identity.Mail -weeksBefore $weeksBefore -weeksAfter $weeksAfter
foreach ($appointment in $appointments) {
# Überprüfen, ob der Termin der richtigen Kategorie zugeordnet ist
$hasCategory = $false
foreach ($appCategory in $appointment.Categories) {
if ($null -ne $categories[$appCategory]) {
$hasCategory = $true
}
break
}
if ($hasCategory) {
# Termine in SharePoint anlegen
Add-AppointmentIntoSPCalendar -Appointment $appointment
}
}
}
Read out calendar information
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 CalenderInformation from the Last 5 and the next 2 Weeks
.NOTES
#>
[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
}
end {
}
}
Now only all found appointments with the corresponding category have to be added to the SP calendar.
Using this small PS script, it is possible to synchronize the calendar entries from Outlook using EWS in SharePoint.
What else you can do with PowerShell, you can read in these blog posts.
If you have further questions or are interested in the complete script – just contact and report to us by clicking on the button!