Connection to sharepoint site to get a custom file and then use it in data model

Hello,

It is possible, you'll need to use so-called API graph from Microsoft and Python (Celonis ML workbench).

Steps to perform:

1) Register APP in Azure:

https://www.sharepointpals.com/post/how-to-generate-app-id-secret-key-to-access-sharepoint-online-through-console-application-using-access-token-part-1/\\

https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs

2) Use Tenant ID, APP secret, APP ID, client ID to get bearer authorization token

(Online found code - there's a lot of different ways to authorize, that one works - at least for me)

app = msal.ConfidentialClientApplication(

   client_id        = client_id,

   client_credential = client_secret,

   authority        = f"https://login.microsoftonline.com/{tenant_id}")

scopes = ["https://graph.microsoft.com/.default"]

# Obtain bearer token from MS Graph

authorization_token = None

result = app.acquire_token_for_client(scopes = scopes)

authorization_token = 'Bearer ' + result['access_token']

authorization_token

3) Get sharepoint site ID

4) Get sharepoint drive ID

5) Search for specific file ID by filename

6) Use previously stored information to get the file (drive-item) and use workbook manipulation functions. You've got few options:

A) getting data from range (to have dynamic range you can use formula in excel located in "tech sheet", first get used-range from "tech sheet" and then use that to get data needed from "data sheets")

Link to excel formula

B) getting data from table (much easier) - then you need just to mentioned table name/number in workbook and you'll get data for whole table range

7) Push Data-frame to Celonis EMS as new table

More details on steps connection steps can be found here.

Please note that code mentioned in above URL needs lot of changes in order to make it work.

Most helpful post about file structure/navigation is this one, however if you'll use app options, you cannot use commands like "shared documents" or "/me/" as you're not "normal" user (human-like).

Usual template for getting f.e. file ID is:

FullURL = ApiEndpoint+SiteURL+DriveId

FullURL = URL + "/root/search(q='{" + file_name + "}')"

   response = requests.get(FullURL, headers={'Content-Type': 'application/json', 'SdkVersion': 'postman-graph/v1.0', 'Authorization': authorization_token})

   print(response.json())

Please upvote if I've helped.

Best Regards,

Mateusz Dudek