SAP To SharePoint Integration Via Microsoft Graph using SAP PO REST Adapter (No UDF’s for OAUTH)

Introduction: 

We will hereby explain how to connect to Microsoft Sharepoint Using Microsoft Graph.

This blog is entirely different from what you may have come across till now.

Technology is changing at a very rapid rate and the way you can access certain Systems on the network and their Authentication Mechanism is changing too….

I had a requirement to Integrate SharePoint with SAP PO and was clueless until i came across this blog from Dilip

https://blogs.sap.com/2018/08/26/integrate-sharepoint-using-sap-pi/#

A really nice blog which explains how to perform basic operations on SharePoint.

Problem: We rely on UDF’s for getting the accessToken via OAUTH 2.0.

SharePoint works on OAUTH 2.0 authentication mechanism where you generate a client id and client secret. You then get the accessToken from the Sharepoint Auth url.

https://accounts.accesscontrol.windows.net/{TenantID}/tokens/OAuth/2

The problem with this mechanism is that SAP PO REST Sender adapter does not support the OAUTH parameter “resource” which is mandatory for Sharepoint Authentication so we need to get AccessTokens Via UDF!!

Solution:

We have two ways to connect to Sharepoint

1. Via SharePoint API’s which require the steps to be followed mentioned in Dilip’s blog.
2. Via Microsoft Graph which is the STANDARD and recommended way of connecting to SharePoint.

Advantages of using Microsoft Graph:

a) You can use SAP PO Receiver Rest adapter for Oauth without having to worry about any udf’s for the access token and let SAP PO Take care of it. 

b) Really vast variety of options to query to Sharepoint for instance. I had to read an excel file from Sharepoint and SharePoint’s REST API’s were returning that file to me as a binary file!!! No other way.

I moved on to Microsoft Graph and i could now query the data in the excel sheet directly! No hassle of any Java Mappings or handling of large Binary files….you only get the rows coulmns you want!!!

c) Works on ODATA and really user friendly to work on!

d) provides you a product wide client id and client secret…it means that if you are using Sharepoint’s API directly the client ID and client secret you generate is only valid for a particular “site” on sharepoint and won’t work on any other site! But with the client id client secret generated for Microsoft Graph you can access the ENTIRE SHAREPOINT with the same Token!! That’s something amazing!!

I can keep going on but need to keep this blog short.

How do we do it!!

Step 1) Generate client-id Client Secret.

Follow the steps as is in this blog and you will have your client-id and client-secret

https://tsmatz.wordpress.com/2016/10/07/application-permission-with-v2-endpoint-and-microsoft-graph/

Step 2) Once you have the admin consent open Postman and try to see if you are able to get the access token.

do a POST to https://login.microsoftonline.com/{yourCompany}.onmicrosoft.com/oauth2/v2.0/token

for example if your sharepoint account is ABC.sharepoint.com then just replace yourCompany in the url by ABC

Content-Type: application/x-www-form-urlencoded

grant_type:client_credentials
client_id:YYYYYYYYYYY
client_secret:XXXXXXXX
scope:https://graph.microsoft.com/.default

Step 3) Import the Microsoft login and Microsoft Graph Certificates into your system

Microsoft Graph URL

https://graph.microsoft.com

Also there is a Microsoft Graph developer portal where you can login via your Company’s credentials and test your ODATA queries live!!

Now all you need to do is configure the REST Receiver Channel!!!

URL Tab

Here I am Querying a list for it’s Items. My Microsoft Graph URL Is

https://graph.microsoft.com/v1.0/sites/{YourCompany}.sharepoint.com,{site-id}/lists/{list-id}/items?filter=( (fields/JobNumber ne ‘1234’) )&expand=fields(select=Jobnumber,id)

and the result i get from a end to end postman test via SAP PO is

Request Mapping doesn’t matter in this case Response mapping is

Step 5) Understanding the ODATA query and how it was made is equally important and you won’t find much detail as to how to get these Site-id and list-id etc which can be really frustrating. Here is what i discovered via trial and error!

The query https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?filter=( (fields/JobNumber ne ‘1234’) )&expand=fields(select=Jobnumber,id)

has two main parts “site-id” and “list-id”

a) to get Site-id go to Microsoft Graph Developer portal

https://developer.microsoft.com/en-us/graph/graph-explorer#

Now SharePoint works on the concept of sites or lets say sub portals under the Share Point or pages in SharePoint where you can store files, images, or create “lists” now enter the query

https://graph.microsoft.com/v1.0/sites/{YourCompanyName}.sharepoint.com:/sites/{YourSharepointSite}/

you will get something like

This becomes your site-id

b) Now we need to get the list we are working on, and a sharepoint site can have multiple lists.

again run the query

https://graph.microsoft.com/v1.0/sites/{site-id}/lists/

this will give you the list of list id’s and you can pick up the list id and ready to go!!

Conclusion:  We learned how to query Sharepoint via Microsoft Graph(which in turn converts the ODATA query to Share Point’s REST API’s and you need not worry about the hundreds of API’s and functions).

Microsoft Graph and Sharepoint currently support ODATA 2.0 which has a wide array of functionalities.

Sample Microsoft Graph url’s for you which i built during some of my developments.

For Reading an Excel sheets row’s and column’s

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/items/{excelSheetFileID}/workbook/worksheets/Sheet1/UsedRange?$select=values

How to get Drives??

https://graph.microsoft.com/v1.0/sites/{site-id}/drives/

How to get Excel sheet ID??

https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/root/children

Reading binary file from Sharepoint via Microsoft Graph(this covers Image, Text File , excel sheet, virtually everything!!!!) and you get back a Binary version of the file.

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/root:/{FullFilename}:/content

Regards

Vinay