rene.klomp

Integrating with the Microsoft Graph API

Blog Post created by rene.klomp Employee on Jan 24, 2017

In this post I want to address how we can integrate Boomi with Microsoft Graph API using REST via the HTTP Connector with OAuth 2.0 authentication. In this example I will show how we can use the Graph API to add rows to an Excel Online spreadsheet on OneDrive.

 

What is the Microsoft Graph API?

From Microsoft:

Microsoft Graph exposes multiple APIs from Office 365 and other Microsoft cloud services through a single endpoint: https://graph.microsoft.com. Microsoft Graph simplifies queries that would otherwise be more complex.

 

To access a user's Microsoft data, your application must enable users to authenticate their identity and give their consent for the app to perform actions on their behalf.

The Microsoft Graph supports two authentication providers:

 

For our purpose we want to authenticate users with enterprise accounts so we will use Azure Active Directory (Azure AD) and the authentication provider, and the Authorization Code Grant Flow as the auth flow.

 

Let's get started!

 

Register Boomi with your AD tenant

First, you will need to register Boomi with your Azure Active Directory (Azure AD) tenant. This will give you an Application ID for your application (Boomi), as well as enable it to receive tokens.

 

  • Sign in to the Azure Portal.
  • Choose your Azure AD tenant by clicking on your account in the top right corner of the page:

 

 

  • In the left hand navigation pane, click on Azure Active Directory:

 

 

  • Click on 'App Registrations' and click on 'Add'.
  • Follow the prompts and create a new application. For this integration we choose 'Web app / API' as Application Type:

 

 

  • Once you've completed registration, Azure AD will assign your application a unique client identifier, the Application ID (Client ID). You will need this value in the next sections, so copy it from the application page.

 

 

  • When we now click on 'All settings' we get to see various settings. Here we can fill in our reply URL for Boomi and set 'Required permissions' and generate a Key (Client Secret).
  • Looking at the Properties we can find some of the things we have already seen in the previous screens but we can also upload a logo for our application:

 

 

  • Then looking at Reply URLs we can set the Reply URL (or Callback or Redirect URI). The redirect URI is where the service (i.e. MS Azure AD) will redirect the user after they authorize (or deny) your application (i.e. Boomi), and therefore the part of your application (i.e. Boomi) that will handle authorization codes or access tokens.
  • For Boomi the Reply URL is: https://platform.boomi.com/account/<ATOMSPHERE-ACCOUNT-ID>/oauth2/callback.

 

 

  • Next, we need to set the 'Required permissions' for the API we want to address so we can define the scope of what permissions we can request from our application (i.e. Boomi). There are basically two types of permissions here that we can set:

 

  • Application Permissions - Your client application (i.e. Boomi) needs to access the Web API (i.e. MS Graph API) directly as itself (no user context). This type of permission requires administrator consent and is also not available for Native client applications.
  • Delegated Permissions - Your client application (i.e. Boomi) needs to access the Web API (i.e. MS Graph API) as the signed-in user, but with access limited by the selected permission. This type of permission can be granted by a user unless the permission is configured as requiring administrator consent.

 

For this example we are adding rows to my Excel Online spreadsheet on MS OneDrive so Boomi needs Delegated Permissions to be able to write to my spreadsheet. In general it will depend on what you're trying to achieve what set of permissions you would want to choose here.

 

So clicking on 'Required permissions' and subsequently on Microsoft Graph (because this is the API we are planning on accessing) we get to see the 'Enable Access' pane to the right with both mentioned 'Application Permissions' and 'Delegated Permissions'. If Microsoft Graph does not show up, you can add it by clicking on the '+ Add' just below Required Permissions and you will be allowed to add additional API access by first selecting an API and then selecting its Permissions:

 

 

Now scrolling down we see the Delegated Permission called 'Have full access to user files' which is what we select:

 

 

Now this will allow the app ('Boomi') read, create, update and delete all files the signed-in user can access without requiring administrator's consent.

 

  • Finally we need to generate a Key (Client Secret) for Boomi that it will use as a symmetric key to authenticate to MS Azure.

 

Enter a Description for the Key and click the 'Duration' drop down, and select either a duration, e.g. 'In 1 year':

 

 

The right-most column will contain the key value, after you save the configuration changes. Be sure to come back to this section and copy it after you hit save, so you will have it for use within Boomi during authentication at run-time:

 

 

Now we are all set to go and configure Boomi!

 

Configure Boomi HTTP Client Connection

As said before, as an illustrating example we want to use MS Graph API to add rows to an Excel Online spreadsheet in Office 365 OneDrive. However, the main purpose of this blog is to outline how to integrate Boomi with MS Azure and Graph API so we won't spend a lot of time going into details about how to configure a process. The main focus will be on how to configure the HTTP Client Connector to be able to access the Graph API via REST.

 

The idea of the example process is that we query Salesforce and then append the output to an existing spreadsheet in Office 365. So an important step is adding a new HTTP Client Connector to our process. The first step in configuring this HTTP Client Connector is configuring its Connection settings.

 

  • Create a new HTTP Connector - just give it a name and choose OAuth 2.0 as Authentication Type:

 

 

  •  In the new screen enter all values needed as outlined in the screenshot below:

 

 

  • URL: https://graph.microsoft.com - "Microsoft Graph unified API endpoint"
  • Authentication type: OAuth 2.0
    • Grant Type: Authorization Code - we will be using the Authorization Code Grant Flow as the auth flow (also see the Introduction)
    • Client ID: the Application ID as taken from the App Registration in MS Azure
    • Client Secret: the (symmetric) Key as taken from the App Registration in MS Azure
    • Authorization Token URL: https://login.microsoftonline.com/common/oauth2/authorize - the production Azure AD authentication endpoint; where authorizing the App ('Boomi') starts
    • Scope: User.ReadWrite.All - these are the permissions we are going to request when authenticating; more specifically in our case this is the scope corresponding to the required permission that we configured while registering Boomi in MS Azure and that we need to be able to write to our Excel Online spreadsheet.

You can find more information about these permission scopes in the text and tables listed at: Microsoft Graph - Documentation - Permission scopes

Access Token URL: https://login.microsoftonline.com/common/oauth2/token - where we can exchange the access code retrieved from the authentication endpoint for an access token

 

Click on 'Save' and that's it for configuring!

 

Now we can actually request a token by clicking on the Access Token 'Generate...' button. The Generate OAuth Tokens dialog starts and asks you to authenticate.

 

 

 

Once signed in, you are prompted to accept the requested permissions ('Have full access to your files' as configured in the Graph API and 'Sign you in and read your profile' as was already setup as a required permission for the Windows Azure Active Directory API):

 

 

Once we have accepted this, we see we have received an Authorization Code as part of the flow.

 

 

We can now go back to the first tab where we will see that the generation of the Access Token was successful!

 

 

Also, the buttons at the bottom of this HTTP Client Connection configuration screen have changed as a sign of success. Here we are now able to regenerate our Access Token or completely reset it. The Access Token will be saved and used for future requests against MS Graph API:

 

 

Configure Boomi Process to add Rows to Excel Online Spreadsheet

The process that we will be using will be querying Salesforce for Leads and write that information at the end of our existing spreadsheet (workbook) on Office 365 OneDrive.

 

From Microsoft Graph - Documentation - Excel:

You can use Microsoft Graph to allow web and mobile applications to read and modify Excel workbooks stored in OneDrive, SharePoint, or other supported storage platforms. The Workbook (or Excel file) resource contains all the other Excel resources through relationships. You can access a workbook through the Drive API by identifying the location of the file in the URL. For example:

 

https://graph.microsoft.com/{version}/me/drive/items/{id}/workbook/
https://graph.microsoft.com/{version}/me/drive/root:/{item-path}:/workbook/

 

You can access a set of Excel objects (such as Table, Range, or Chart) by using standard REST APIs to perform create, read, update, and delete (CRUD) operations on the workbook. For example, https://graph.microsoft.com/{version}/me/drive/items/{id}/workbook/ returns a collection of worksheet objects that are part of the workbook.

 

We are not going to spend a whole lot of time talking about configuring a Boomi process but the actual process looks like this:

 

 

First, we query Salesforce (SFDC) for Lead data based on some filter. Then we map that SFDC data represented by an XML Profile to the data in our Excel Online Workbook represented by a JSON Profile - where the columns in our spreadsheet represent JSON properties as outlined in Microsoft Graph - Documentation - Excel:

 

 

After mapping our data we will call MS Graph API by using our newly configured HTTP Client Connector where we have been looking at configuring its HTTP Client Connection in great detail above. Finally, configuring its HTTP Client Operation, the most important piece here is the Resource Path which actually looks like:

 

v1.0/me/drive/root:/Leads.xlsx:/workbook/tables('Table1')/Rows

 

With that the configuration of the HTTP Client Operation looks fairly simple:

 

 

Now, when running our process (in a Test Case) we can inspect the data coming from Salesforce and after being mapped into JSON data, ready to be used in a call to MS Graph API:

 

 

And the actual addition of rows in my spreadsheet 'Leads.xlsx' on OneDrive:

 

Outcomes