With the increasing demands of the SAC users who want to connect to SharePoint List for data visualization and data analysis, I figured out a solution for SAC import connectivity against SharePoint Online List items via BTP Cloud Integration. To achieve it, I deployed an OData API Artifact to retrieve items from SharePoint List without Open Connectors and map entity framework. Compare to the method with Open Connectors, it simplified workflows and we don’t need to configure EDMX or XSD files manually. This blog is to provide End to End connectivity guidance from SharePoint List Add-in Registration to SAC Connection Set up through SAP Integration Suite. Currently, we are not able to use the OAuth2 Client Credentials authentication method directly in SAC against SharePoint Online List as there is one more key-value pair (resource: 00000003-0000-0ff1-ce00-000000000000/<yourSharePointDomain>@TenantID) is required by SharePoint Online when getting the access_token and SAC didn’t provide a place to input this value in the connection dialog. Hence, the below method can be treated as a workaround without modifying the SharePoint OAuth issuer.

Table of Contents

1. Register SharePoint Add-in
2. Assign Permission to Add-in
3. Obtain ClientID@TenantID
4. Use Postman to Get/Test Access Token and Credentials
5. Create OAuth2 credentials in Cloud Integration Suite
6. Create an OData API artifact in a Package
7. Bind Data Source
8. Configure OData Receiver
9. Configure Message Mapping
10. Deploy OData API
11. Create Instance and Credentials
12. Test Deployed OData API in Postman
13. Create/Test OData Service Connection in SAC

Preparation:

• Found OData Service Endpoint URL of SharePoint Online subsite https://<yourSharePointDomain>/sites/205434/_vti_bin/listdata.svc/
• Make sure we get list items from above URL by adding the list name at the end of the URL https://<yourSharePointDomain>/sites/205434/_vti_bin/listdata.svc/WuTestList2
• Save xml file from https://<yourSharePointDomain>/sites/205434/_vti_bin/listdata.svc/$metadata
• Copy the XML file and past it, then rename the file extension of the copied one to .edmx

1. Register SharePoint Add-in

1. Go to https://<yourSharePointDomain>/sites/205434/_layouts/15/appregnew.aspx
2. Click “Generate” for both Client id and Client Secret
3. Give a friendly name
4. Add Domain and Redirect URI
5. Click “Create” and note down the information
   

   Client Id:            5d57…8bac Client Secret:    bbc7…TTA= Title:      WuTestList2App App Domain:      localhost Redirect URI:      https://localhost

2. Assign Permission to Add-in

1. Go to https://<yourSharePointDomain>/sites/205434/_layouts/15/appinv.aspx
2. Copy Client Id into the App id filed
3. Click “Lookup”
4. Copy below XML to Permission Request XML. Please also refer to learn.microsoft.com “Table 2. SharePoint add-in permission scope URIs and available rights” And narrow down the permission according to the organization’s security policy. In this example, I will copy all of them to provide insights into how it impacts permissions.

5. <AppPermissionRequests AllowAppOnlyPolicy="true">
   
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list"
       Right="FullControl" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list" Right="Manage" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list" Right="Read" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list" Right="Write" />
   
     <AppPermissionRequest Scope="https://sharepoint/content/tenant" Right="FullControl" />
     <AppPermissionRequest Scope="https://sharepoint/content/tenant" Right="Manage" />
     <AppPermissionRequest Scope="https://sharepoint/content/tenant" Right="Read" />
     <AppPermissionRequest Scope="https://sharepoint/content/tenant" Right="Write" />
   
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="Manage" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="Read" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="Write" />
   
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Manage" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Read" />
     <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Write" />
   
   </AppPermissionRequests>
   

6. Click “Create”
7. Select the target SharePoint List from the drop-down list
8. Click ”Trust it”

3. Obtain ClientID@TenantID

1. Go to Site Setting
2. Select “Site app permission” as below
3. Find the App and Note down the Clientid@Tenantid (we will use it later). Clientid@Tenantid is
   5d57…8bac@42f7…1af7

4. Use Postman to Get/Test Access Token and Credentials

Please also refer to code2care.org “How to access SharePoint Online data using Postman-REST API and Bearer token”

1. POST https://accounts.accesscontrol.windows.net/[Tenant ID]/tokens/OAuth/2  ([Tenant ID] = Tenantid obtained in step 3-3)
   

   Add Request Header	Content-Type:application/x-www-form-urlencoded
   Add Request Body	Select: x-www-form-urlencoded grant_type:client_credentials client_id:Clientid@Tenantid (obtained in step 3-3) client_secret: Client Secret (obtained in step 1-5) resource: 00000003-0000-0ff1-ce00-000000000000/<yourSharePointDomain>@Tenantid
   Example	grant_type:client_credentials client_id:5d57…8bac@42f7…1af7 client_secret:bbc7…TTA= resource:00000003-0000-0ff1-ce00-000000000000/<yourSharePointDomain>@42f7…1af7

2. Click “Send”
3. Copy access_token from the response body
4. GET http://<yourSharePointDomain>/sites/205434/_api/web/lists/GetByTitle(‘WuTestList2’)/items
   

   Add Request Headers

   Authorization:Bearer (paste above access_token) Accept:application/json;odata=verbose

If it success, it will return the whole list items with metadata

5. Create OAuth2 credentials in Cloud Integration Suite

1. Navigate to Security Material
2. Click “Create” then select “OAuth2 Client Credentials”
3. Give a name, then Input the exact same credentials when getting access_token in Postman
4. Click “Deploy”

6. Create an OData API artifact in a Package

1. Click “Edit” -> Select “OData API”
2. Select “Create Using Wizard” and give a meaningful name.
3. Then Click “Create”
4. Click “Eidt” -> Click “Import Model Wizard”
5. Select “ODATA”, then import prepared EDMX file
6. Only Select the target list with the target properties
7. We can leave the Review and Finish EDMX Structure as default
8. Click “Finish” then Click “Save”

7. Bind Data Source

1. Select the ODATA as Data Source and lick on “link” button
2. Leave the “Upload OData Model” as default because we have uploaded in the beginning and don’t need to change it, then select the Entity Sets from the list. Then put the data service end point https://<yourSharePointDomain>/sites/205434/_vti_bin/listdata.svc/
3. Click “OK” then Click “Save”

8. Configure OData Receiver

1. Select “OData Receiver”. In Connection Tab, change the Authentication to OAuth2 client credentials and input credentials name: WuTestList2Credentials (we created in step 5-3 for getting the access_token)
2. Go to Processing Tab, change Content Type to JSON.
3. Leave the rest settings as default

9. Configure Message Mapping

1. Click on the Message Mapping component, then navigate to Processing Tab
2. Click on the default mapping link
3. Leave the Source and Target as default (don’t change the file here), then map the Parent node and child nodes.
4. Click “Ok”

10. Deploy OData API

1. Navigate to the below interface and save the setting. Then click “Deploy”
2. Go to the Manage Integration Content
3. Refresh the status, then we will get the Deployed OData API endpoint.
4. Copy WuTestList2OData EndPoint:

11. Create Instance and Credentials

1. Go to the subaccount where we create the OData API
2. Navigate to Instance and Subscriptions
3. Click “Create” on the top right corner
4. Input information as below
5. Click “Next”, leave the information as default
6. Click “Next” to preview information
7. 
8. Click “Create”
9. Then we will see the Instance was created and we will create a service key within this instance
10. Click “Create Service Key” and give a meaningful name in the dialog.
11. Leave the rest of the setting as default then click “Create”. Then we will see the Service key in as the below screen. Click “…” to view the credentials in this key
12. Then we see all credentials in the dialog. Note down the clientid and clientsecret. We will use them in Postman and SAC. And I will call them as Instance-clientid and instance-clientsecret in the following steps to avoid mistaking with SharePoint add-in client id and client secret

12. Test Deployed OData API in Postman

1. Use deloyed OData API URL in Postman. GET https://…/odata/SAP/WUTESTLIST2ODATA;v=1/$metadata
2. For Authorization, choose “Basic Auth” and input the credentials obtained from step 11-12
3. Copy EntitySet name from <EntitySet Name=”WuTestList2ItemSet” EntityType=”S1.WuTestList2Item”/> in Response body
   So my EntitySet name is WuTestList2ItemSet
4. Change GET URL to https://…/odata/SAP/WUTESTLIST2ODATA;v=1/WuTestList2ItemSet
   At the same time, we can update the list items in SharePoint and test the updates in response body

13. Create/Test OData Service Connection in SAC

1. Select OData Services from the connection area in SAC. Give a meaningful name and input information in below table. And click “OK”
   

   Data Service URL	Deployed OData API URL https://…/gw/odata/SAP/WUTESTLIST2ODATA;v=1
   Authentication Type	Basic Authentication
   User Name	Instance-clientid
   Password	Instance-clientsecret

   

2. Navigate to Model and select the newly created SharePoint list connection from the drop-down list
3. Select the EntitySet
4. Build OData Service Query and click “Create”.
5. If the connection and OData API integration flow are correct. Then data is imported into SAC successfully. We can also schedule the import activities as the screenshot below.

Summary

As described in the context, this solution fits SharePoint Online and it is a one-time configuration. But please note that

• Client secrets for SharePoint Add-ins that are registered by using the AppRegNew.aspx page expire after one year. Please refer to “Replace an expiring client secret in a SharePoint Add-in” For further maintenance on the client secret in the Security Material section.
• As for more operations for read/write operations, please refer to Saranya Baskaran‘s Blog “OData API in SAP Cloud Integration for Beginners “
• For further details on OData V2 Receiver Adapter, please refer to SAP Help Portal ”Configure the OData V2 Receiver Adapter “
• Fur further details on OData Sender Adapter, please refer to SAP Help Portal “Configure the OData Sender Adapter“