Tip: Check out the MS Graph Explorer to try out the MS Graph APIs.
Azure Active Directory ‘App registrations’
For example, here is a list of Applications Registrations we have at JMJ Cloud.
API permissions allow users of the ‘App registration’ to perform certain actions with Office 365 services. Here is an example of ‘API permissions’ which are used by one of our internal APEX applications to automatically process inbound email.
The below permissions are utilized for an App registration that is used purely for Authenticating users into our internal applications. These are ‘Delegated permissions’ and are specific to each logged in user. This means that you can only perform actions in Office 365 on behalf of the logged in user.
When you create an App registration, Microsoft generates a ‘Application (client) ID’ and you can then generate one or more ‘Client secrets’. The combination of a ‘Application (client) ID’ and a ‘Client secret’ is used by APEX to get a token which is required to call the MS Graph APIs. APEX has you covered here because it automatically keeps track of tokens (and their expirations) and fetches a new one when it needs to.
Securing ‘App registrations’
Once someone has the ‘Application (client) ID’ and a ‘Client secret’, they can effectively perform any actions allowed in the ‘API permissions’ section. It is a good idea to create separate App registrations for each Office 365 service you want to access (SharePoint, Email, Sign-On etc.). This way if the credentials for one App registration were to get into the wrong hands, the damage is limited to that one service.
Once you have created the appropriate App registrations in Azure AD, you can get started configuring APEX to integrate with Office 365.
Sign-On to APEX with MS Azure AD
Here is a screen shot of an example APEX Authentication Scheme used for authenticating users using their Office 365 Credentials.
A key part of the above Authentication scheme is the ‘Credential Store’ entry. BTW, ‘Credential Store’ was renamed to ‘Web Credentials’ in APEX 20.2 so you may see me use them interchangeably in this post. In the context of Office 365, Web Credentials store the Client ID and Client Secret that APEX uses to link your Authentication scheme to a specific Azure AD ‘App registration’.
When using a third-party authentication provider, you need to be extra careful. If you only create an Authentication scheme, you are essentially giving anyone with an Office 365 account the ability to sign-in to your application. You need to also include APEX Authorization scheme(s) which makes(s) sure users are allowed to access your application and also define what they are allowed to do if they are allowed in.
SharePoint & OneDrive
- Uploading and downloading file Attachments to and from SharePoint in your APEX Application. In this scenario you control the entire interaction with SharePoint from APEX.
- Storing Oracle e-Business Suite attachments (instead of bloating your database by storing them in the database). In this scenario you intercept EBS and store files in SharePoint instead of the FND_LOBS table. Files appear to the user as an attachment and they are taken to SharePoint when they want to download a file.
Azure AD App registration
You will need to start by creating an App registration in Azure AD and make sure you assign the ‘Sites.ReadWrite.All’ permission. This permission will allow you full access to traverse the folder structure, create folders, upload, and download files etc.
APEX Web Credentials
As with sign-on, you will need to create Web Credentials that provide APEX the information it needs to access the ‘App registration’ and authenticate you so you can call the Graph APIs.
The MS Graph APIs you use for SharePoint and OneDrive are pretty much the same. There is an extra step in SharePoint to get from the SharePoint Site to the Documents folder (Drive) for the SharePoint Site. Once you have the Drive ID, the steps for traversing folders, uploading, and downloading files are the same for both. MS Graph APIs allow you to do the following:
- List Files and Folders
- Create and Delete Folders
- Upload and Delete Files
- Get a File
Calling MS Graph APIs from APEX
Interacting with SharePoint & OneDrive files from APEX is trickier than setting up single sign-on, but not much.
You have a couple of options; one is to use APEX ‘REST Data Sources’ and the other is to call the apex_web_service PL/SQL APIs. Creating a ‘REST Data Source’ is the best option, especially for data that is flat (e.g., a list of folders or a list of emails). One key advantage is that ‘REST Data Sources’ can be used as data sources declaratively throughout APEX (IRs, IGs, Charts, LOVs etc.).
If the payload from Microsoft contains embedded arrays, then you can use apex_web_service to get the JSON then parse the JSON and traverse the arrays yourself.
The function below shows an example of using apex_web_service to call an MS Graph API. The function fetches a file from SharePoint into a BLOB in PL/SQL. Developers do not need to worry about fetching tokens etc. from Microsoft. APEX uses the referenced Credential Store (identified by ‘p_credential_static_id’) to automatically get a token from Microsoft (if it needs to). The token is then automatically passed to the Graph API.
Example Use Cases
- APEX ticketing system - Allow users to create and update tickets in your ticketing system by sending email to an email account dedicated to processing tickets. Use APEX 20.2 Automations functionality to poll the Inbox every five minutes and create/update the appropriate tickets.
- Information requests - Allow users to send an email to an email account dedicated to handling requests for information from your ERP system. Use APEX automations to poll the account and fetch requested data from your ERP and respond with an email containing the requested information.
Note: Performing actions in your system based on the receipt of an email should be done with great care. You must verify the email sender, check that they are an authorized user of your application and that they have access to perform the requested action.
Azure AD App registration
You will need to create an App registration in Azure AD and make sure you assign the ‘Mail.Read’ permission. This permission will allow you to read email from any inbox.
APEX Web Credentials
The APEX Web Credential will look very similar to the one you created for SharePoint and OneDrive, except you will need to use the client and client secret from the App registration you created for handling email.
Email Graph APIs
There are MS Graph APIs that allow you to do the following with email:
- Get a list of emails
- See who sent the email, who was on the CC list etc.
- Get the full body of an email
- Get email attachments
- Follow email ‘conversations’
- Send email
MS Graph API Considerations
Make Use of API Query Parameters
There are several query parameters that can be used to influence the response you get from MS Graph APIs. Here is a screen shot showing a call to the GET email API where we are passing a number of these parameters.
- $select allows you to just select the fields you need. By selecting just, the fields you need, you can dramatically reduce the size of the response payload and the time it will take you to parse it.
- $filter allows you to filter the data set that is returned. In this case we are fetching only emails received since ‘2020-09-01T11:32:44Z’. This can also dramatically reduce the size of the response payload returned.
- @orderby allows you to pre-sort the data returned by the API.
- @top allows you to limit the response to the top X records. This is another mechanism you can use to limit the size of the response.
Utilize Delta Queries
Delta Query enables applications to discover newly created, updated, or deleted entities without performing a full read of the target resource with every request. This can be especially useful for things like polling for new emails created since the last time you checked.
Understand Limits and Throttling
Microsoft does impose certain limits and will throttle access if you exceed them.
For example, at time of writing, the Outlook mail API limits are as follows:
- 10,000 API requests in a 10-minute period
- 4 concurrent requests
- 15 megabytes (MB) upload (PATCH, POST, PUT) in a 30 second period
One More Thing
Jon Dixon Co-Founder JMJ Cloud