Lewis and Laura vs. Fabric Notebooks and Microsoft Graph: Part 1

This week my friend Laura GB told me about some fun she was having with Python in Fabric Notebooks, working to access Excel table data for processing in a set of logic. There were some screen shares, some 'ah what if we do this' and 'YES! it works!' and now we're sharing the fun with you all!

In this post, find out how me and Laura made Fabric Notebooks and Microsoft Graph work together like good friends to enable processing of 👀 Excel 👀 table data in Microsoft Fabric notebooks!

Let's put our Excel workbook in a suitable place

The first thing we need to do to achieve accessing Excel data from Python in a Fabric notebook, is to store that workbook in SharePoint. So very simply, I have an Excel workbook with a simple table in it, and I'm going to save that in a document library in a SharePoint site.

Handling authentication

Next we need to make considerations for authentication. One of the requirements Laura told me about was the fact that this notebook was going to run on a scheduled basis, and it'd be late at night. This means we won't have someone actively signing in to anything, and this is an application only scenario.

We're going to create an app registration in Microsoft Entra ID that we can use for authentication here. Laura will be able to then use this to give the notebook the ability to login or authenticate, using client credentials. Laura will use the client id, tenant id, and client secret to authenticate.

Handling authorisation

We handled authentication, but that doesn't mean our notebook will be able to access anything yet. Now we need to add the Sites.Selected application permission to our app registration, and we need to give admin consent for that permission.

We don't want to apply access to all sites in the tenant, and most organisations won't hand this permission out, if they know the buttons they should be clicking anyway 😸

The Sites.Selected permission allows us to give an application access to specific SharePoint sites, without needing to use a user, and delegated permissions.

Providing the app with access to the SharePoint site

The next thing we need to do is provide access to the SharePoint site using PowerShell. We can use the Microsoft Graph PowerShell SDK to execute the cmdlets we need to achieve this. We cannot provide an application with permissions to a specific SharePoint site in the GUI.

Lewis is thrilled at this part, because the Microsoft Graph PowerShell SDK is cross-platform! Yay for us Mac friends :)

Laura >> sigh 👀

Let's run the following syntax which should return a table highlighting the permissions have been assigned.

Connect-MgGraph -Scopes "Sites.FullControl.All"
$Site = Get-MgSite -SiteId "<site-id-goes-here>"
New-MgSitePermission -SiteId $Site.Id -Roles "write" -GrantedToIdentities @{application = @{id = "<app-id-goes-here>"; displayName = "<app-display-name-goes-here>"}}

The additional thing we can do to check the permissions were assigned for the app, are to look in the site collection app permissions.

There I can see my app registration.

Now, Laura will be able to access the contents of the Excel workbook we put in SharePoint, from Python in her Microsoft Fabric notebook. Over to you, Laura!

Stay tuned for the next part in this series, on a hat full of data, where Laura shares how we access our Excel workbook from a notebook using Graph API, and how we then process the data in Python.