How to consume Excel (or other) file stored on secured SharePoint Site

Create the WebService Bearer connection in xMart

  1. Click the Add button to create a new connection under the WebService Bearer provider (referred to “MySharepointConn” for this example)
  2. Make sure you are logged in with the account authorized on the target Sharepoint file.
  3. Click on the Open the authorization_code helper popup button. If it prompts you to accept the permission, accept it. If you have already given the permission, the popup will open and then immediately close.
  4. Insert the “scope” JSON property as displayed ("scope":"https://worldhealthorg.sharepoint.com/.default offline_access",). image
  5. Click on Current Azure AD button and set Token URL endpoint to https://login.microsoftonline.com/f610c0b7-bd24-4b39-810b-3dc280afb590/oauth2/v2.0/token
  6. Click Save button to save the connection.

Acquiring a specific excel file/table

Now that a token has been acquired, a url change is needed to use the Sharepoint API.

Example file original URL: https://worldhealthorg.sharepoint.com/:x:/r/sites/EMRCOVIDResponse/Shared%20Documents/COVID-19%20Vaccine/Countries%20update%20on%20COVID%2019%20vaccination%20tracking.xlsx

Transformed API URL: (note the repeated /sites/MySite and the relative path file as parameter of function getfilebyserverrelativeurl) https://worldhealthorg.sharepoint.com/sites/EMRCOVIDResponse/_api/web/getfilebyserverrelativeurl('/sites/EMRCOVIDResponse/Shared%20Documents/COVID-19%20Vaccine/Countries%20update%20on%20COVID%2019%20vaccination%20tracking.xlsx')/$value

Reference: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-folders-and-files-with-rest

    <GetWebService Url="https://worldhealthorg.sharepoint.com/sites/EMRCOVIDResponse/_api/web/getfilebyserverrelativeurl('/sites/EMRCOVIDResponse/Shared%20Documents/COVID-19%20Vaccine/Countries%20update%20on%20COVID%2019%20vaccination%20tracking.xlsx')/$value" ConnectionName="MySharepointConn">
      <GetExcel TrimValues="true" TableName="Vaccine update" RemoveBlankRows="true" StartingRow="4" ColumnSuffixIfDuplicate="UPTAKE" />
    </GetWebService>

That’s it! The origin can be loaded (in debug mode if possible).

Useful resources

SharePoint REST API docs

GetWebService command