Loading Excel file from Sharepoint with Excel Online connector
Good day,
for our customer we want to get data from an Excel file which is stored in their Sharepoint. We've used the documentation from this article.
The authentication is successful and we are getting some results but not the Excel files. If we test it with our own (Victa) Sharepoint then we see many results and also Excel files.
Below the screenshots from Victa and the second from our customer.
Results loading from our own Victa Sharepoint (same connector and settings (entra id > app permissions)Same Excel Online connector but configured for the customer in their own Sharepoint.
We cant figure out the difference because we use the same setup (clone) of the Excel Connector. Does the user who is authenticating the connector within TimeXtender need more rights on the Sharepoint side? We already gave for testing the account Owner rights and also added many API Permissions to the app registration.
Hopefully somebody has an idea.
Page 1 / 1
Hi,
if you rApp Registration permissions require Admin consent, have you already granted those? You can check this in the Azure Portal in the App Registration details in Entra. There is also a footnote in Microsoft's documentation that says something like “mixing Delegation and Application permissions may result in difficult to diagnose errors” so try to stay away from mixing both types in your API permissions.
A few points that might help you:
Please check if the API permissions are granted. I had a client where the API permission were set, but the Excel files weren’t showing. The descriptions state that granting isn’t required for some permissions. However we explicitly granted all permissions we did see the Excel files.
When using the permission sites.selected, I think you have to give the app registration access to the site via the following grant command: https://pnp.github.io/powershell/cmdlets/Grant-PnPAzureADAppSitePermission.html . Running this requires Powershell and I think the user running this needs to be admin on the site. The benefit of doing this is that limiting the access to a specific site prevents having access to all Sharepoint sites in the tennant which will make your cybersecurity team very happy. ;-)
Hi @vw-victa
You can sometimes see it more specifically if you turn on logging while synchronizing. It does GRAPH API calls, so you should be able to locate the URI that is sent to retrieve the file and try it out in Postman, that may explain why.
Besides that I would agree with Rory, it is likely APP permissions. This is also largely dependent on what grant type you are using for this.
Thank you for the answers, will test it today!
I accidentality pressed “Solved”, but I didn't test it yet. Sorry!
We solved it. Not sure exactly what did the trick but these were the steps we took (using ODX Server):
-Leave the Sharepoint URL empty
-Turn on logging on the synchronization
-We retrieved this URL from the logging: ***-my.sharepoint.com/personal/***
-We were able to put a few Excel files over here
-We were able to connect to these files in TX
-In Postman we connected to Graph API and did a few calls
-We changed the URL to our sharepoint page: https://***.sharepoint.com/sites/***
