Blog

Outlook - How to add, edit, delete, and list Outlook calendar events

By integrating apps with Outlook calendars, we can create far more powerful apps, particaularly in the case of scheduling applications. This post describes how to integrate with Outlook through the Office 365 Outlook Connector Including tasks that include adding, editing, deleting, and searching Outlook calendar events.

A useful feature is the ability to integrate an app with the user's Outlook calendar. To give a use case scenario, a helpdesk type app could add target end dates for tickets into an Outlook calendar, and we could also configure the calendar entries to show reminders in Outlook.

The Outlook connector enables us to perform this type of integration, and this post describes the formula to carry out common tasks which include:

  • How to create a calendar entry
  • How to search and list entries from a calendar
  • How to edit a calendar entry
  • How to delete a calendar entry

Introduction to Outlook Calendars

An instance of Outlook can contain multiple calendars. The screenshot beneath shows how there are three available calendars in my Outlook calendar - Birthdays, Calendar, and United Kingdom holidays.



When integrating with Outlook, we need to specify an individual calendar. The default name of the user calendar is 'Calendar', and this is the calendar that we'll refer to in this post.

Getting started - adding the Office 365 Outlook connector

To get started, the first step is to add the Office 365 Outlook connector to the app.

Getting a list of calendars

Once we add the Office 365 Outlook connector, the next logical step is to retrieve a list of available calendars. To retrieve a list of calendars, we call the Office365Outlook.CalendarGetTablesV2 method.

The formula beneath highlights how to retrieve a list of calendars and how to store the result in a collection called colCalendars.


ClearCollect(colCalendars, 
             Office365Outlook.CalendarGetTablesV2()
)
The return value from CalendarGetTablesV2 is an object called 'value' - this is a table that contains calendar details, such as the calendar name and unique id. The calendar id value is very important because we require this to identify the calendar when we add, edit, delete entries.


Retrieving the Calendar ID for the current user

To retrieve the unique id of the user calendar and to store it in a variable called varUserCalendarId, we use the formula beneath. It's a good idea to add this formula to the OnStart property of an app for easy access. Alternatively, we can retrieve the calendar id prior to the point when we want to use the calendar.

Set(varUserCalendarId, 
    LookUp(Office365Outlook.CalendarGetTablesV2().value, 
           name="Calendar").id
)

Creating a simple calendar entry

The formula beneath highlights how to create a simple entry. 

Office365Outlook.V4CalendarPostItem(
      varUserCalendarId, 
      "Calendar subject text", 
      "2021-11-01T04:00:00", 
      "2021-11-01T05:00:00", 
      "UTC"
)
The V4CalendarPostItem method requires 4 arguments:

  • Table - this defines the calendar ID. The example here references the calendar id that's stored in varUserCalendarId.
  • Subject - this defines the subject text that appears in the calendar entry.
  • Start datetime - this defines the start date and time in string format.
  • End datetime - this defines the end date and time in string format.
  • Timezone - this defines the timezone.

Note that we specify a time zone with a time zone string that matches the format that appears in Outlook. As an example, the timezone string for England in November would be "(UTC+00:00) Dublin, Edinburgh, Lisbon, London"

Frustratingly, it's not possible to pass three-character timezones such as 'PST', 'EST', 'GMT' etc. The connector returns an error if we attempt to do this. 


For this reason, the easiest approach is to specify times in UTC format. We can call the Text function to convert a date time string value to UTC, and the formula to convert local start and end datetimes to UTC is shown below.

Office365Outlook.V4CalendarPostItem(
      varUserCalendarId, 
      "Ignite conference keynote", 
      Text(DateTimeValue("2021-11-02 17:00:00"),DateTimeFormat.UTC), 
      Text(DateTimeValue("2021-11-02 18:00:00"),DateTimeFormat.UTC),
      "UTC"
 )


Creating an all-day calendar entry

To create an all-day event, we use the formula that's shown below. This example creates an all-day entry for 3rd November 2021. Note that to create an all-day event, the start and end times must be at least 24hrs.

Office365Outlook.V4CalendarPostItem(
      varUserCalendarId, 
      "Team meeting", 
      Text(Date(2021,11,03),DateTimeFormat.UTC), 
      Text(Date(2021,11,04),DateTimeFormat.UTC),
      "UTC",
      {isAllDay:true}
 )

Creating a recurring entry calendar entry

We can create recurring calendar entries using the following formula. This example creates an entry that recurs daily, 3 times. 

Office365Outlook.V4CalendarPostItem(
      varUserCalendarId, 
      "Re-occuring entry", 
      Text(Date(2021,11,03),DateTimeFormat.UTC), 
      Text(Date(2021,11,04),DateTimeFormat.UTC),
      "UTC",
      {isAllDay:true, recurrence:"daily",numberOfOccurences:3}
 )
The acceptable recurrence values we can specify are daily, monthly, weekly, and yearly.

Creating a calendar entry with a reminder

To create a calendar entry that triggers an Outlook reminder, we can use the formula beneath. This creates a calendar entry with an Outlook reminder that appears 15 minutes before the event. 

Office365Outlook.V4CalendarPostItem(
      varUserCalendarId,
      "Calendar entry with reminder",
      Text(Date(2021,11,03),DateTimeFormat.UTC),
      Text(Date(2021,11,04),DateTimeFormat.UTC),
      "UTC",
      {isReminderOn:true, reminderMinutesBeforeStart:15}
 )

Listing calendar entries

To display a list of calendar entries for a given Outlook calendar, we call the GetEventsCalendarViewV3 method as shown beneath.

Office365Outlook.GetEventsCalendarViewV3(
    varUserCalendarId,
    Text(Date(2021,11,01),DateTimeFormat.UTC),
   Text(Date(2021,11,30),DateTimeFormat.UTC)
).value


This returns a table that includes the columns shown beneath. A notable field is ID. This value uniquely identifies a calendar entry and we require this to update or to delete an entry.

Subject, Start time, End time, Start time with time zone, End time with time zone, 
Body, Is HTML, Response type, Response time, Id, Created time, Last modified time, 
Organizer, Time zone, Series master id, iCalUId, Categories, Web link, 
Required attendees, Optional attendees, Resource attendees, Location, 
Importance, Is all day event?, Recurrence, Recurrence end date, 
Number of occurrences, Reminder, Is reminder on, Show as, 
Response requested, Sensitivity

Searching calendar entries

To search an Outlook calendar, we can specify a search term like so:
Office365Outlook.GetEventsCalendarViewV3(
    varUserCalendarId,
    Text(Date(2021,11,01),DateTimeFormat.UTC),
    Text(Date(2021,11,30),DateTimeFormat.UTC),
    {search:"SearchTerm"}
).value
This method returns results where the search term appears in both subject or the body of the calendar entry.

An important point to note is that the search is case sensitive. As highlighted in the screenshot beneath, a search for the text 'biz' (in lower case) does not return a calendar entry that contains the subject text 'Biz'.


To perform more powerful searches that incorporate additional search operators, we can specify an OData filter query as shown beneath. 

Office365Outlook.GetEventsCalendarViewV3(
    varUserCalendarId,
    Text(Date(2021,11,01),DateTimeFormat.UTC),
    Text(Date(2021,11,30),DateTimeFormat.UTC),
    {'$filter':"contains(subject,'biz')"}
).value

The example beneath demonstrates how to perform a case insensitive search on the search term 'biz'. As this screenshot highlights, this search returns the calendar entry that contains the text 'Biz' (with an upper case B).


Updating calendar entries

To update a calendar entry, we call the V3CalendarPatchItem method. This method requires the calendar ID, and calendar entry ID.

As a demonstration, here's the formula to update the first calendar entry on the 3rd November, where the subject text matches "Team meeting". This formula updates the subject text to "New Subject Text" and retains the existing start and end times. It also applies the body text to the calendar entry.

With({idCalendarEntry:
        First(
            Office365Outlook.GetEventsCalendarViewV3(
                    varUserCalendarId,
                    Text(DateTimeValue("2021-11-03 00:00:00"),DateTimeFormat.UTC),
                    Text(DateTimeValue("2021-11-03 23:59:59"),DateTimeFormat.UTC),
                    {search:"Team meeting"}
             ).value
            )
       },
    Office365Outlook.V3CalendarPatchItem(
           varUserCalendarId,
           idCalendarEntry.id,
           "New Subject Text",
           idCalendarEntry.start, 
           idCalendarEntry.end, 
           {Body:"Updated calendar entry body text"}
   )                    
)

Deleting calendar entries

To delete a calendar entry, we call the CalendarDeleteItemV2 method. This method requires the calendar ID, and calendar entry ID.

The formula beneath shows how to delete the first calendar entry on the 3rd November, where the subject text matches "Team meeting".

With({idCalendarEntry:
        First(
            Office365Outlook.GetEventsCalendarViewV3(
                    varUserCalendarId,
                    Text(DateTimeValue("2021-11-03 00:00:00"),DateTimeFormat.UTC),
                    Text(DateTimeValue("2021-11-03 23:59:59"),DateTimeFormat.UTC),
                    {search:"Team meeting"}
             ).value
            )
       },
    Office365Outlook.CalendarDeleteItemV2 (
           varUserCalendarId,
           idCalendarEntry.id
   )                    
)

Conclusion

We can integrate apps with Outlook calendars through the Office 365 Outlook connector. This post highlighted the formula to carry out common tasks, including how to add, edit, update, list, and search calendar entries.
Previous
Next
Related posts