Open Discussion and Examples for Exporting Calendar Appointments to CSV using Microsoft Graph PowerShell SDK
I’ve been posting a bit about the new Export/Import API in the Microsoft Graph recently but I received an eMail today about exporting calendar appointments to a CSV using a script that I wrote some time ago and published on the PowerShell gallery https://www.powershellgallery.com/packages/MSGraph-ExportCalendar/1.3.1.0/Content/functions%5CExport-GCECalendarToCSV.ps1. As usual the only time you get feedback on any script is when it doesn’t or stops working and this one because of its age has a few issues. Instead of fixing said issues I took the time to port this to the PowerShell Graph SDK which both simplified the code and made it less likely to break in the future but not guaranteed. To speed the process of porting the script along I employed co-pilot to help which I’ve embraced now in my every day routine even though at times it can be counter productive. For this process it was useful but it wasn’t a reference or expert source more a junior employee you had to tell what to do multiple times and still came back with the wrong answer that needs to be fixed. Overall though it did lower the time it took to do the port so I give it a 5 out of 10 its a solve for productivity but not accuracy.
Exporting calendars
In Exchange Online in the Microsoft Graph there are three main methods you could use to export a calendar (programmatically). First the csv method because everybody loves excel and also its an easy to use (or reuse-able) format. When dealing with calendar appointments (or "events" as they are called in the Graph), be aware of how calendar views and recurring appointment expansion will affect your export.
Calendar views and Recurring appointment expansion.
The Microsoft Graph and Exchange Web Services (EWS) handle recurring appointments differently depending on the query method you use. When you utilize a CalendarView in either API, the Exchange server automatically expands recurring appointments into their individual occurrences. Conversely, directly querying for events (or items in EWS) without specifying a CalendarView will only retrieve single, non-recurring appointments and the master instance for a recurring series. While accessing the master instance can be useful in certain situations, for most practical calendar applications, such as exporting a list of events for the next week, it's essential to expand recurring appointments to see each individual instance. For example
Get-MgUserCalendarEvent -UserId gscales@user.com -CalendarId Calendar -all
would make a request to get all the Calendar events using a request like
https://graph.microsoft.com/v1.0/users/user@domain/calendars/Calendar/events
while
Get-MgUserCalendarView -UserId user@domain -StartDateTime $queryStartTime -EndDateTime $queryEndTime -PageSize 500 -All
Would make a request to get all calendar events during the time period specified in StartDateTime and EndDateTime
https://graph.microsoft.com/v1.0/users/user@domain/calendar/calendarView?startDateTime=2024-06-13T11%3A34%3A50Z&endDateTime=2025-04-09T11%3A34%3A50Z&$top=500
Which would give you all the expanded appointments for that particular time period which is more useful then the first option which would give you inaccurate results for some use cases.
Time Window Constraints in Calendar Views:
When implementing CalendarView requests in the Microsoft Graph and Exchange Web Services (EWS), a time window limitation of 1825 days (5 years) applies to the specified StartDateTime and EndDateTime (time window). Consequently, retrieving calendar data spanning more than five years requires segmenting the queries into discrete 5-year blocks. It is important to note that EWS previously had a 2-year limit. The apparent lack of official documentation regarding this 5-year constraint is a concern, as it represents a potential point of failure for production applications.
In the context of the Export Calendar to CSV script, which is primarily designed for reporting purposes, I have leveraged extended properties to expose richer appointment information beyond the standard strongly-typed properties. For instance, the AppointmentState property provides valuable insights (see https://learn.microsoft.com/en-us/exchange/client-developer/web-service-reference/appointmentstate).
I’ve put the port of the Calendar Export to CSV on github https://github.com/gscales/Powershell-Scripts/blob/master/Graph101/GraphSDK/Export-CalendarToCSV.ps1
To use this you can use something like
Export-GSdkCalendarToCSV -MailboxName gscales@datarumble.com -StartTime (Get-Date).AddYears(-1) -EndTime (Get-Date) -FileName c:\export\lastyear.csv
Other ways of exporting Calendar Appointments
As mentioned, you can also programmatically export calendar appointments using other methods in the Microsoft Graph or EWS.
One common approach is exporting to the iCalendar (iCal) format, a widely used open standard for calendar sharing. EWS natively converts appointments to iCal when you retrieve the MimeContent (refer to https://learn.microsoft.com/en-us/exchange/client-developer/exchange-web-services/how-to-export-items-by-using-ews-in-exchange). However, this method wasn't always perfectly reliable due to formatting issues and is not the same as what Outlook or Calendar sharing produces.
Currently, the Microsoft Graph doesn't support direct iCal export. To achieve this, you must retrieve the raw data first, process it, and then create the iCal file from that data . I plan to demonstrate this with a script soon.
The Import/Export Endpoint: A High-Fidelity Export Solution
The latest and most reliable technique for exporting calendar appointments in the Microsoft Graph is using the dedicated Import/Export endpoint which I wrote about in the following series. This method ensures data fidelity by exporting the Item using the Fast Transfer Stream format which contains all the underlying exchange properties and attachments associated with the appointment/meetings. Consequently, single appointments are exported as individual items, while recurring appointments are represented by a single Master instance item that encapsulates all associated exceptions and deleted occurrences information .
However, a significant aspect to be mindful of when utilizing this endpoint is the handling of Ex/X500 addresses, as detailed in this post . When an exported appointment is subsequently imported into a different mailbox (or a mailbox in a separate tenant), the organizer details within the recipient collection and other relevant properties on the appointment's master instance will continue to reference the originating mailbox’s Ex/X500 address .
This can result in the imported appointment becoming non-functional if the original mailbox identifiers are no longer valid in the target environment, effectively creating an orphaned calendar entry.
An example of exporting all the data from a mailbox calendar using the new import and export API using the following script https://github.com/gscales/Powershell-Scripts/blob/master/Graph101/GraphSDK/Import-ExportMod.ps1 I did for my Import and Export series (each appointment and or master instance is exported to its own FTS file)> $Mailbox = Invoke-GetMailboxsetting -MailboxName gscales@datarumble.com
$CalendarItems = Invoke-ListMailboxFolderItems -MailboxId $mailbox.primaryMailboxId -MailFolderId Calendar -ItemCount 50000
Invoke-ExportItems -Items $CalendarItems -MailboxId $Mailbox.primaryMailboxId -ExportPath c:\temp\calendar\
Postscript more about filtering and customized reporting
Before I could finish and submit this post, another email arrived discussing a related but different approach: reporting on recurring meetings with specific attendee counts. This new question is worth exploring as it’s relevant to what I’ve been discussing in this post.
Filtering so you just see the Master instances of recurring appointments in a calendars
So going back a bit on what i said above the one use case of not using a Calendarview is when you want to access the master instances of recurring calendar appointments. To do this you can filter on the following extended property PidLidRecurring which is a boolean that tells you if an appointment is recurring. This looks like the following in the Graph api
Get-MgUserCalendarEvent -CalendarId Calendar -UserId gscales@user.com -PageSize 999 -All -Filter "singleValueExtendedProperties/Any(ep: ep/id eq 'Boolean {00062002-0000-0000-C000-000000000046} Id 0x8223' and cast(ep/value, Edm.Boolean) eq true)"
Note the use of casting the value to edm.boolean which is important when your dealing with properties that aren’t strings in Graph filters. So the above request would give you a list of master recurring instances of appointments. If you needed to dig a bit further into the type and pattern of the Recurrence you can use the Recurrence strongly typed property.
Example 1 Find Recurring appointments that have no end date
There are two methods you could do this with the first is using a filter on the PidLidClipEnd property which “For a recurring series, this property specifies midnight on the date of the last instance of the recurring series in UTC, unless the recurring series has no end, in which case the value must be 31 August 4500, 11:59 p.m.” To do this in the Graph API it would look something like
Get-MgUserCalendarEvent -CalendarId Calendar -UserId gscales@user.com -PageSize 999 -All -Filter "(singleValueExtendedProperties/Any(ep: ep/id eq 'Boolean {00062002-0000-0000-C000-000000000046} Id 0x8223' and cast(ep/value, Edm.Boolean) eq true)) AND (singleValueExtendedProperties/Any(ep: ep/id eq 'SystemTime {00062002-0000-0000-C000-000000000046} Id 0x8236' and cast(ep/value, Edm.DateTimeOffset) gt 4500-08-01T00:00:00Z))" -ExpandProperty "SingleValueExtendedProperties(`$filter=(Id eq 'SystemTime {00062002-0000-0000-C000-000000000046} Id 0x8236'))"
I made my time offset 4500-08-01T00:00:00Z rather then the 31 August 4500 because the actual time is still set relative to the clients timezone offset so will vary slightly between different clients using different time zone. But any recurring appointments with a PidLidClipEnd greater then 4500-08-01T00:00:00Z would have no end date set on the recurrence.
The other method which is slower but can avoid any issues with edge cases where the PidLidClipEnd doesn’t work is just to enumerate the recurring appointments that are returned a the client side and use the .Recurrence.Range.Type strongly typed property that is returned from the Graph API eg
Get-MgUserCalendarEvent -CalendarId Calendar -UserId gscales@datarumble.com -PageSize 999 -All -Filter "(singleValueExtendedProperties/Any(ep: ep/id eq 'Boolean {00062002-0000-0000-C000-000000000046} Id 0x8223' and cast(ep/value, Edm.Boolean) eq true))" -ExpandProperty "SingleValueExtendedProperties(`$filter=(Id eq 'SystemTime {00062002-0000-0000-C000-000000000046} Id 0x8236'))" | foreach-object{if($_.Recurrence.Range.Type -eq "noEnd"){$_.Subject}}
Script example I’ve added Export-GSdkMasterRecurrancesToCalendarToCSV to the script on github that does an export of recurring appointments only to csv and will produce a report like
Example 2 Find Recurring appointments that have a particular attendee count or no attendees
There is no way of doing this at the server side so it requires a bit of client side filtering so I’ve now included an Attendee Count property in the result set. So to do this instead of using the Export cmdlets in the example module you can use something like this
$appointments = Invoke-GSdkGetMasterRecurrencesFromCalendar -mailbox gscales@user.com
which will return a collection of recurring appointments in the $appointments varible then filter that by the attendee count you want to see eg for recurring meeting with no attendees
$appointments | Where-Object {$_.AttendeeCount -eq 0} | select Subject,ClipStart,ClipEnd, AttendeeCount
For recurring meeting with more then 10 attendees
$appointments | Where-Object {$_.AttendeeCount -gt 10} | select Subject,ClipStart,ClipEnd, AttendeeCount
To output any of the above to a csv use
$appointments | Where-Object {$_.AttendeeCount -eq 0} | Export-Csv -NoTypeInformation -Path $FileName
The module with all the PowerShell code is available on https://github.com/gscales/Powershell-Scripts/blob/master/Graph101/GraphSDK/Export-CalendarToCSV.ps1