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:

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. Click “Create”
  8. Then we will see the Instance was created and we will create a service key within this instance
  9. Click “Create Service Key” and give a meaningful name in the dialog.
  10. 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
  11. 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

Sara Sampaio

Sara Sampaio

Author Since: March 10, 2022

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x