APEX & Office 365 Calendar Integration

APEX & Office 365 Calendar Integration

·

7 min read

In my previous post, I described how to configure an Azure AD App registration to facilitate access to the Microsoft Graph APIs. I also showed you how to call an MS Graph API from Postman and create an APEX Web Credential to access MS Graph APIs from Oracle APEX.

In this post, I will build on that knowledge and provide steps to build an integration between APEX and the Office 365 calendar using the calendar resource. We will create an APEX Calendar Region that directly sources its calendar entries from an Office 365 calendar. This integration is a great way to see someone's availability instantly from an APEX App.

Oh, and did I mention it will only require two lines of code?

Here is a YouTube clip that contains a sneak peek into what the finished calendar will look like. The users and calendar entries are coming directly from Office 365.

There are many other calendar-related MS Graph APIs available that enable you to do the following directly from an APEX App:

Azure AD Configuration

Follow the steps from my APEX & Office 365 Integration - Introduction post to see how to create an AD App registration using 'Microsoft Entra'. Once you have an App registration, complete the following steps from within the AD App registration:

  • Navigate to 'API permissions' and click Click '+ Add a permission' App registration add permissions step 1
  • Click 'Microsoft Graph' then Click 'Application Permissions' App registration add permissions step 2
  • Search for and select Calendars.Read, then click 'Add permissions' App registration add permissions step 4
  • Click 'Grant admin consent for XX', where 'XX' is your Microsoft Tenant App registration add permissions step 5
  • You should see a green check against the Calendars.Read permission App registration add permissions step 6

Test the API with Postman

The first step is to test calling the Calendar Graph APIs from Postman.

MS Graph API Access Token

Please refer to my APEX & Office 365 Integration - Introduction post for details on obtaining an MS Graph token using Postman. The token returned from this step will be used as a Bearer Token in the following steps.

Default Calendar Events API

📖 Documentation for the calendar Graph API can be found here.

An MS Office user could have more than one calendar, in which case, you can get a list of all their calendars using the list calendars resource.

For my example, I will get events for a user's default calendar. To identify which user's calendar, we can use their user id or their userPrincipalName (typically their email address). Refer to this post to get a list of users (including their id or their userPrincipalName) for your MS office 365 Tenant.

Endpoint URL

The Endpoint URL for the calendar events API is: GET /users/{id | userPrincipalName}/calendar/events e.g. https://graph.microsoft.com/v1.0/users/jon@cloudnueva.com/calendar/events

Postman Screenshots

The screenshot below shows what the Parameters section looks like in Postman.

  • MS Graph APIs allow you to provide filters via the $filter parameter and to select which columns you want to be returned via the $select parameter.
  • I encourage you to use both of these parameters as they will help improve performance by keeping the size of the payload down.
  • Here is a link to the documentation. See my post for more details on improving performance when consuming REST services from APEX. Postman Parameters.png In this screenshot, we see the MS Graph API token being passed as a 'Bearer Token' Postman Authorization.png In this screenshot, we see the HTTP headers. The Prefer header indicates that I want the dates and times for calendar entries to come back in the 'Pacific Standard Time' timezone. If you do not provide a value, dates and times will be returned in the 'UTC' timezone. Postman Headers.png

Sample Response

Here is a link to a sample response that includes all available fields.

APEX Configuration

Now that we understand how the Graph API works, it is time to integrate it with APEX. We are going to use APEX REST Sources to do the heavy lifting. If you need more control, you can use APEX_WEB_SERVICE to get the JSON response and use PL/SQL to parse and process it.

This post will focus on creating a REST Data Source to get calendar entries for a user's default calendar.

Demo Application

Refer to the demo application available from my GitHub Repository for the complete code.❗When you install the demo application, you will be prompted with the following: Import APEX Application.png

  • Enter the client id and secret for your Microsoft AD App registration above
  • For 'MS Tenant Login URL', change the text <YOUR_MS_OFFICE_TENANT_ID> to your Microsoft Tenant ID.

You will also need to update the column option_value for the option_code = TENANT_ID in the table 'CN_MS_GRAPH_OPTS' to your MS Office 365 Tenant ID.

APEX Web Credential

If you do not already have a Web Credential created from the first post, you will need to create one now. ❗The Web Credential must refer to an MS Azure AD App registration with the Calendars.Read permission described above.

REST Data Source

Follow these steps to create a REST Data Source which fetches calendar entries for a given user's default calendar.

  • Login to APEX Application Builder
  • 🧭 Application > Shared Components > REST Data Sources > Click 'Create'
  • Select 'From Scratch' then click 'Next'
  • Enter the below details, including the MS Graph calendar events endpoint URL https://graph.microsoft.com/v1.0/users/:user_id/calendar/events.
    • Notice I included a variable :user_Id, which we will use the inject the id of the user whose calendar we want to view.
    • We can also provide the userPrincipalName in this variable. While creating the REST source I entered a default value of my email address.
    • Click 'Next' once you have entered these values Oracle APEX Rest Source Step 1
  • Click 'Next' twice more until you see the below page
    • Select the Web Credential you created in the previous steps
    • Enter the following token URL https://login.microsoftonline.com/<TENANT_ID>/oauth2/v2.0/token. Where <TENANT_ID> is your MS Office 365 Tenant ID
    • Click 'Create REST Source Manually'. ❗At this stage, clicking 'Discover' will not work Oracle APEX Rest Source Step 3
  • Select the newly created REST Source, then click the 'Edit Data Profile' button
  • Change the value of 'Row Selector' from items to value Oracle APEX Rest Source Step 4
  • In the same modal window, scroll down and click the 'Rediscover Data Profile' button Oracle APEX Rest Source Step 5
  • Click 'Replace Data Profile', then 'Apply Changes' Oracle APEX Rest Source Step 6
  • Click Edit Data Profile Again, then change the 'Name' field for the 'Selectors' end.dateTime and start.dateTime. Doing this will make finding these columns easier when we create the APEX Calendar Region Oracle APEX Rest Source Step 6
  • Finally, change the user_id parameter to be required, and add the two additional Parameters $filter and $select as shown in the below screenshot Oracle APEX Rest Source Step 7.png

That completes the setup of the REST Data Source.

APEX Calendar Region

The final step is to create a calendar region that uses the REST Data Source to fetch the calendar entries from MS Office.

  • 🧭 Login to APEX Application Builder and create a new blank page
  • Create a new Region per the below screenshot
    • Note the parameters $filter and user_id should be displayed automatically Oracle APEX Calendar Region Step 1
  • Enter the below details in the 'Attributes' section of the region Oracle APEX Calendar Region Step 2.png
  • Link the two REST Source Parameters to APEX Page Items
    • Create two page items P5_USER_ID and P5_START_DT
    • Link these page items to their respective parameters e.g. Oracle APEX Calendar Region Step 3
  • Add a 'Before Header' process to default values for these parameters Oracle APEX Calendar Region Step 4
  • The final page structure should look something like this Oracle APEX Calendar Region Step 5
  • Finally, run the page, and it should look something like this: Oracle APEX Calendar Region Step 6

Conclusion

In this post, I showed you how to integrate Office 365 calendar entries into an Oracle APEX Calendar region with just two lines of code. I encourage you to explore further and begin integrating Office 365 services into your APEX applications.

🔗 Read More