Context
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
- Register SharePoint Add-in
- Assign Permission to Add-in
- Obtain ClientID@TenantID
- Use Postman to Get/Test Access Token and Credentials
- Create OAuth2 credentials in Cloud Integration Suite
- Create an OData API artifact in a Package
- Bind Data Source
- Configure OData Receiver
- Configure Message Mapping
- Deploy OData API
- Create Instance and Credentials
- Test Deployed OData API in Postman
- Create/Test OData Service Connection in SAC
Preparation:
1. Register SharePoint Add-in
- Go to https://<yourSharePointDomain>/sites/205434/_layouts/15/appregnew.aspx
- Click “Generate” for both Client id and Client Secret
- Give a friendly name
- Add Domain and Redirect URI
- 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
- Go to https://<yourSharePointDomain>/sites/205434/_layouts/15/appinv.aspx
- Copy Client Id into the App id filed
- Click “Lookup”
- 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.
<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>
- Click “Create”
- Select the target SharePoint List from the drop-down list
- Click ”Trust it”
3. Obtain ClientID@TenantID
- Go to Site Setting
- Select “Site app permission” as below
- 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”
- 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
|
- Click “Send”
- Copy access_token from the response body
- 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
- Navigate to Security Material
- Click “Create” then select “OAuth2 Client Credentials”
- Give a name, then Input the exact same credentials when getting access_token in Postman
- Click “Deploy”
6. Create an OData API artifact in a Package
- Click “Edit” -> Select “OData API”
- Select “Create Using Wizard” and give a meaningful name.
- Then Click “Create”
- Click “Eidt” -> Click “Import Model Wizard”
- Select “ODATA”, then import prepared EDMX file
- Only Select the target list with the target properties
- We can leave the Review and Finish EDMX Structure as default
- Click “Finish” then Click “Save”
7. Bind Data Source
- Select the ODATA as Data Source and lick on “link” button
- 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/
- Click “OK” then Click “Save”
8. Configure OData Receiver
- 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)
- Go to Processing Tab, change Content Type to JSON.
- Leave the rest settings as default
9. Configure Message Mapping
- Click on the Message Mapping component, then navigate to Processing Tab
- Click on the default mapping link
- Leave the Source and Target as default (don’t change the file here), then map the Parent node and child nodes.
- Click “Ok”
10. Deploy OData API
- Navigate to the below interface and save the setting. Then click “Deploy”
- Go to the Manage Integration Content
- Refresh the status, then we will get the Deployed OData API endpoint.
- Copy WuTestList2OData EndPoint:
11. Create Instance and Credentials
- Go to the subaccount where we create the OData API
- Navigate to Instance and Subscriptions
- Click “Create” on the top right corner
- Input information as below
- Click “Next”, leave the information as default
- Click “Next” to preview information
- Click “Create”
- Then we will see the Instance was created and we will create a service key within this instance
- Click “Create Service Key” and give a meaningful name in the dialog.
- 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
- 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
- Use deloyed OData API URL in Postman. GET https://.../odata/SAP/WUTESTLIST2ODATA;v=1/$metadata
- For Authorization, choose “Basic Auth” and input the credentials obtained from step 11-12
- Copy EntitySet name from <EntitySet Name="WuTestList2ItemSet" EntityType="S1.WuTestList2Item"/> in Response body
So my EntitySet name is WuTestList2ItemSet
- 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
- Select OData Services from the connection area in SAC. Give a meaningful name and input information in below table. And click "OK"
- Navigate to Model and select the newly created SharePoint list connection from the drop-down list
- Select the EntitySet
- Build OData Service Query and click “Create”.
- 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