How to consume Excel (or other) file stored on secured SharePoint Site
Create the WebService Bearer connection in xMart
- Click the Add button to create a new connection under the WebService Bearer provider (referred to “MySharepointConn” for this example)
- Make sure you are logged in with the account authorized on the target Sharepoint file.
- 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. - Insert the “scope” JSON property as displayed (
"scope":"https://worldhealthorg.sharepoint.com/.default offline_access",
). - 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
- 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).