alt-fabric-m365

Python helpers for automation in Microsoft Fabric notebooks, from Archway Learning Trust (ALT).

Up to 0.5.0 this package was called fabric_python_helper. See Moving from fabric_python_helper.

The package has a module for each Microsoft 365 service, plus auth, which they share:

• alt_fabric_m365.auth gets app-only access tokens for Microsoft APIs (Microsoft Graph, Power BI, Fabric, OneLake and others) as a service principal, using a certificate held in Azure Key Vault. Key Vault signs each request, so the certificate's private key never leaves the key vault.
• alt_fabric_m365.exchange finds emails in an Exchange Online mailbox, downloads their attachments, deletes them and sends emails, using Microsoft Graph. Up to 0.6.x this module was called graph_api, see Upgrading to 0.7.0.
• alt_fabric_m365.sharepoint manages SharePoint document libraries and their permissions through Entra ID security groups: creating libraries, keeping group membership in sync, navigation links and site sharing settings.

Use a Fabric workspace identity wherever Fabric supports one. This package is for Microsoft 365 work it can't do, such as Exchange mailboxes and SharePoint, see Why Exchange and SharePoint need an app registration.

The package is built for Fabric notebooks. KeyVaultCertificateCredential uses notebookutils to call Key Vault as the identity running the notebook, so it only works in Fabric (or Synapse) notebooks. The package itself can be imported anywhere.

Contents

• Installation
• Quick start
• Authentication
  • Use a workspace identity where you can
  • Why Exchange and SharePoint need an app registration
  • How certificate authentication works
  • Setting up a certificate
  • Using the credential
  • Scopes
  • Using a client secret instead
  • Renewing the certificate
• Emails
  • Giving the app access to a mailbox
  • Creating an Emails instance
  • Finding an email
  • Downloading attachments
  • Deleting an email
  • Sending an email
  • Full example
• SharePoint libraries
  • What it manages
  • Giving the app access
  • Creating a SharePointLibraryManager
  • Operations
  • The action log
  • Safety features
  • Using it in your own setting
  • Example notebook
• Troubleshooting
• Upgrading to 0.7.0
• Moving from fabric_python_helper
• Upgrading from 0.3.x
• Building and publishing
• Licence

Installation

The package is published on PyPI as alt-fabric-m365 and imported as alt_fabric_m365. Its dependencies, azure-core, pandas and requests, are already in the Fabric runtime.

In a Fabric environment (use this for scheduled notebooks and pipelines):

1. Open the environment your notebooks use.
2. Under Public libraries, select Add from PyPI, enter alt-fabric-m365 and choose a version, for example 0.7.0.
3. Select Publish. Notebooks pick up the new version when their next session starts.

In a single notebook session (useful for testing a new version):

%pip install alt-fabric-m365==0.7.0

Always pin a version. 0.4.0 has breaking changes from 0.3.x, see Upgrading from 0.3.x. 0.5.0 adds the sharepoint module and doesn't change anything that was already there. 0.6.0 renames the package from fabric_python_helper to alt_fabric_m365 and changes nothing else, see Moving from fabric_python_helper. 0.6.1 only updates the documentation. 0.7.0 renames the graph_api module to exchange, and the old name still works with a warning, see Upgrading to 0.7.0.

Quick start

Read the latest report email from a mailbox, save its CSV attachment to a lakehouse and delete the email:

from alt_fabric_m365.auth import KeyVaultCertificateCredential
from alt_fabric_m365.exchange import Emails

credential = KeyVaultCertificateCredential(
    tenant_id="<tenant-id>",
    client_id="<app-registration-client-id>",
    akv_url="https://<key-vault-name>.vault.azure.net/",
    certificate_name="<certificate-name>",
)

mailbox = Emails(credential, "reports@contoso.com")

message_id = mailbox.search_message_by_subject_and_sender("Daily Sales Report", "noreply@supplier.com")

csv_files = [(attachment_id, file_name)
             for attachment_id, file_name in mailbox.get_attachment_ids_and_names(message_id)
             if file_name.lower().endswith(".csv")]
if not csv_files:
    raise RuntimeError("The latest Daily Sales Report email has no CSV attachment.")

attachment_id, file_name = csv_files[0]
csv_text = mailbox.download_attachment(message_id, attachment_id)

lakehouse = "abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<lakehouse>.Lakehouse"
notebookutils.fs.put(f"{lakehouse}/Files/Reports/{file_name}", csv_text, overwrite=True)

mailbox.delete_email(message_id)

Before this works you need to set up a certificate and give the app access to the mailbox.

Authentication

Use a workspace identity where you can

A workspace identity is a service principal that Fabric creates for a workspace and manages for you. Fabric holds its credentials and rotates them itself, so there's no secret or certificate to store, renew or leak. When something in Fabric can authenticate as the workspace identity, use it rather than an app registration of your own, including the one this package uses.

A workspace identity can be used for:

Use	How
OneLake shortcuts to Azure Data Lake Storage Gen2	Choose Workspace identity as the shortcut's authentication method, and give the identity a role such as Storage Blob Data Reader on the storage account.
Firewall-enabled storage accounts	Trusted workspace access lets shortcuts, pipelines and semantic models reach storage that only allows selected networks. Needs a purchased Fabric capacity (F SKU).
Pipelines	Connections for the Copy, Lookup and GetMetadata activities, for sources such as ADLS Gen2, Blob Storage and Azure SQL.
Semantic models (import mode)	A cloud connection using the workspace identity, chosen in the model's Gateway and cloud connections settings.
Dataflows Gen2 (CI/CD)	The workspace identity as a connector's authentication method.
Reading SharePoint Online lists	The SharePoint Online list connector in Dataflows Gen2, pipelines (Copy and Lookup) and copy jobs. Read only.
Notebooks run from a pipeline	Set the Notebook activity's connection to the workspace identity. The notebook then runs as it, so notebookutils.credentials.getToken() returns its tokens for OneLake and Azure Storage (storage), Power BI and Fabric (pbi), Key Vault (keyvault) and Kusto (kusto), and notebookutils.credentials.getSecret() reads Key Vault as it.
Azure resources that accept Microsoft Entra ID	Give the identity a role on the resource, as for any service principal: Key Vault, storage accounts, Azure SQL and so on. Its service principal has the same name as the workspace.

The list of connectors that support it grows, so check it before setting up an app registration.

Things to know before relying on one:

• Only a workspace admin can create it, and not in a My workspace. Deleting the identity, or permanently deleting the workspace, breaks everything that uses it, and a deleted identity can't be restored.
• Admins, members and contributors of the workspace can use it in connections, and the identity that runs a pipeline using it must hold one of those roles. Treat those roles as access to everything the workspace identity can reach.
• Don't change its app registration or enterprise application in Entra ID, other than adding API permissions; Fabric may revert other changes, and they can stop it working.
• If a Conditional Access policy for workload identities covers all service principals, exclude workspace identities from it, or they won't work.
• It can't be used across tenants or with on-premises data gateway connections.

Why Exchange and SharePoint need an app registration

This package's Emails and SharePointLibraryManager call Microsoft Graph and the SharePoint REST API. Those calls can't be made as a workspace identity, so they authenticate as an app registration you create, using KeyVaultCertificateCredential:

• Your code can't get a Graph or SharePoint token as a workspace identity. Fabric only uses the identity's tokens inside the features in the table above, and notebookutils.credentials.getToken() only supports the storage, pbi, keyvault and kusto audiences. Fabric keeps the identity's credentials, so you can't request a token for https://graph.microsoft.com or https://<tenant>.sharepoint.com with them yourself. Granting it Graph permissions or an Exchange role wouldn't help, as nothing can use them.

• Fabric's connectors only cover reading data. The Exchange Online connector only signs in as a user. The SharePoint Online list connector does accept the workspace identity, so use it if you only need to copy list data into Fabric. Neither can read and delete emails, send them, create libraries, manage groups or change permissions, which is what this package does.

• SharePoint only accepts app-only tokens obtained with a certificate. That needs a certificate registered on the app, which you can't add to a workspace identity, and client secrets are rejected.

• The permissions stay separate from the workspace identity's. Mailbox and SharePoint access are powerful, so it's better held by an app registration that does only this job, where it can be reviewed and revoked on its own. How far it reaches depends on what you grant:

  • Mail access is limited to the mailboxes in its Exchange role assignment (see Giving the app access to a mailbox).
  • SharePoint access is limited to chosen sites only with Sites.Selected. Sites.FullControl.All covers every site.
  • SharePointLibraryManager also needs Group.ReadWrite.All, GroupMember.ReadWrite.All and User.Read.All, which apply to the whole tenant (see Giving the app access).

  Sign on the key in Key Vault controls who can use the certificate this package signs with. It doesn't cover any other certificate or client secret on the app registration, which can also authenticate as the app, so remove those you don't need.

The two work together. Run the notebook as the workspace identity, and give the workspace identity permission to sign with the certificate in Key Vault. Key Vault is one of the audiences the workspace identity can get tokens for, so KeyVaultCertificateCredential uses it to sign as the app, and the app is what Exchange and SharePoint see. The workspace identity still does everything else in the notebook, such as writing to OneLake.

Anyone who can run code as the workspace identity can then act as the app too, so keep notebooks that use the app in a workspace whose admins, members and contributors you'd trust with its mailboxes and sites.

Task	Authenticate as
Read or write OneLake, a lakehouse or Azure Storage	The workspace identity
Call the Power BI or Fabric REST APIs, or Kusto	The workspace identity
Copy SharePoint Online list data into Fabric	The workspace identity, through the SharePoint Online list connector
Read secrets or sign with keys in Key Vault	The workspace identity
Read, delete or send email (Emails)	An app registration, with KeyVaultCertificateCredential
Manage SharePoint libraries and their groups (SharePointLibraryManager)	An app registration, with KeyVaultCertificateCredential (a certificate is required)
Call any other Microsoft Graph or SharePoint API	An app registration, with KeyVaultCertificateCredential

How certificate authentication works

KeyVaultCertificateCredential authenticates as an app registration (service principal) using the OAuth 2.0 client credentials flow with a certificate:

1. It reads the public part of the certificate from Key Vault.
2. It builds a client assertion: a JSON Web Token (JWT) that is valid for 10 minutes, identifies the app and names the certificate by its SHA-256 thumbprint.
3. Key Vault signs the assertion with the certificate's private key (RSASSA-PSS, PS256). The private key never leaves the key vault.
4. The signed assertion is exchanged with Microsoft Entra ID for an access token.
5. Access tokens are cached per scope and renewed when they have less than 5 minutes left, so the credential can be used for long-running notebooks.

Two identities are involved, and each needs different permissions:

Identity	What it is	What it needs
The identity running the notebook	You, when running interactively. When a pipeline runs the notebook, whatever the notebook activity runs as, for example the workspace identity.	Permission to read the certificate and sign with its key in Key Vault.
The app registration	The service principal the access tokens are issued to.	Access to the API being called, for example access to a mailbox in Exchange.

Anyone who can sign with the key can act as the app, so treat the Key Vault Sign permission as you would the app's password.

Setting up a certificate

These steps use the Azure CLI, for example in Azure Cloud Shell. Each step also describes the portal equivalent.

1. Create an app registration

In the Microsoft Entra admin center, go to App registrations > New registration. Choose Single tenant and leave the redirect URI empty. Note the Application (client) ID and Directory (tenant) ID.

Only add API permissions if the API you're calling needs them, see Scopes. For mailboxes, don't add any: access is granted in Exchange instead.

2. Create the certificate in Key Vault with a non-exportable private key

Save this policy as policy.json:

{
  "issuerParameters": { "name": "Self" },
  "keyProperties": { "exportable": false, "keyType": "RSA", "keySize": 2048, "reuseKey": false },
  "secretProperties": { "contentType": "application/x-pkcs12" },
  "x509CertificateProperties": {
    "subject": "CN=fabric-mailbox-app",
    "validityInMonths": 12,
    "keyUsage": ["digitalSignature"]
  },
  "lifetimeActions": [
    { "trigger": { "daysBeforeExpiry": 30 }, "action": { "actionType": "EmailContacts" } }
  ]
}

Then create the certificate:

az keyvault certificate create --vault-name <key-vault-name> --name <certificate-name> --policy @policy.json

"exportable": false means nobody, including Key Vault administrators, can download the private key. The lifetime action emails the vault's certificate contacts 30 days before expiry. Add yourself as a contact if you aren't one already:

az keyvault certificate contact add --vault-name <key-vault-name> --email <you@contoso.com>

In the portal: Key Vault > Certificates > Generate/Import, then under Advanced Policy Configuration set Exportable Private Key to No.

3. Upload the public certificate to the app registration

az keyvault certificate download --vault-name <key-vault-name> --name <certificate-name> --file cert.pem --encoding PEM
az ad app credential reset --id <client-id> --cert "@cert.pem" --append

--append keeps the app's existing credentials. In the portal: download the certificate in CER format from its version in Key Vault, then in the app registration go to Certificates & secrets > Certificates > Upload certificate.

4. Let the identity running the notebook use the certificate

With Azure role-based access control (the default for new key vaults), assign two roles scoped to just this certificate and its key. <object-id> is the object ID of the user, group or service principal that runs the notebooks. For a workspace identity, use the object ID of its service principal in Entra ID, which has the same name as the workspace.

vault_id=$(az keyvault show --name <key-vault-name> --query id --output tsv)

az role assignment create --assignee <object-id> --role "Key Vault Reader" \
    --scope "$vault_id/certificates/<certificate-name>"

az role assignment create --assignee <object-id> --role "Key Vault Crypto User" \
    --scope "$vault_id/keys/<certificate-name>"

If the key vault uses access policies instead:

az keyvault set-policy --name <key-vault-name> --object-id <object-id> \
    --certificate-permissions get --key-permissions sign

Avoid broader roles such as Key Vault Certificate User, Key Vault Secrets User or Key Vault Administrator. They aren't needed, and on an exportable certificate they allow reading the private key.

5. Give the app access to the API you're calling

See the Scopes table, and Giving the app access to a mailbox for email.

Using the credential

from alt_fabric_m365.auth import KeyVaultCertificateCredential, Scopes

credential = KeyVaultCertificateCredential(
    tenant_id="<tenant-id>",
    client_id="<client-id>",
    akv_url="https://<key-vault-name>.vault.azure.net/",
    certificate_name="<certificate-name>",
)

Parameter	Required	Description
tenant_id	Yes	Directory (tenant) ID.
client_id	Yes	Application (client) ID of the app registration.
akv_url	Yes	Key Vault URL, https://<key-vault-name>.vault.azure.net/. Anything that isn't an https:// Key Vault URL raises ValueError. Only the Azure public cloud is supported, so sovereign cloud vaults (such as vault.azure.cn or vault.usgovcloudapi.net) are rejected.
certificate_name	Yes	Name of the certificate in Key Vault.
scope	No	Default scope for get_access_token(). Not needed if you always pass a scope, or only use the credential with semantic link or Azure SDK clients.
certificate_version	No	Certificate version to sign with. Defaults to the latest version. See Renewing the certificate.

No calls are made until a token is requested.

Calling an API directly

get_access_token(scope) returns a token string. Call it before each request; it returns the cached token until it's close to expiring.

import requests

token = credential.get_access_token(Scopes.FABRIC)
response = requests.get(
    "https://api.fabric.microsoft.com/v1/workspaces",
    headers={"Authorization": f"Bearer {token}"},
)
workspaces = response.json()["value"]

If you mostly call one API, set it as the default scope:

graph = KeyVaultCertificateCredential(tenant_id, client_id, akv_url, certificate_name, scope=Scopes.GRAPH)
token = graph.get_access_token()

Semantic link (sempy)

The credential implements the Azure SDK TokenCredential interface (get_token), so semantic link functions accept it through their credential parameter. This needs semantic link 0.12 or later. If your runtime has an older version, add semantic-link to the environment or run %pip install -U semantic-link.

import sempy.fabric as fabric

fabric.refresh_dataset("Sales", workspace="Finance", credential=credential)

To use it for every semantic link call in a block:

from fabric.analytics.environment.credentials import SetFabricAnalyticsDefaultTokenCredentials

with SetFabricAnalyticsDefaultTokenCredentials(credential):
    items = fabric.list_items(workspace="Finance")

Some semantic link functions don't support service principals, for example execute_tmsl, evaluate_measure and read_table(mode="rest"). Service principals also can't access anyone's "My workspace".

Azure SDK clients

Any Azure SDK client that takes a credential works too. For example, reading OneLake as the app:

from azure.storage.filedatalake import DataLakeServiceClient

onelake = DataLakeServiceClient("https://onelake.dfs.fabric.microsoft.com", credential=credential)
workspace = onelake.get_file_system_client("<workspace-name>")

for path in workspace.get_paths("<lakehouse-name>.Lakehouse/Files"):
    print(path.name)

If a client passes claims to get_token (for example after a Continuous Access Evaluation challenge), a new token is requested rather than the cached one returned. Asking for a token in a different tenant_id from the credential's raises ValueError.

Scopes

Tokens are requested with the client credentials flow, so a scope is always an API's resource URI followed by /.default. /.default means "every permission this app has been granted on that API". Apps can't request individual permissions such as Mail.Read.

Entra ID issues a token whether or not the app has access to the API. If the API then returns 401 or 403, check the last column.

Constant	Scope	API	How to give the app access
Scopes.GRAPH	https://graph.microsoft.com/.default	Microsoft Graph	Application permissions on the app registration, with admin consent. For mailboxes, use Exchange RBAC instead.
Scopes.POWER_BI	https://analysis.windows.net/powerbi/api/.default	Power BI REST API	Add the service principal to the workspace, and allow service principals to use the APIs in the Fabric admin portal (Tenant settings > Developer settings).
Scopes.FABRIC	https://api.fabric.microsoft.com/.default	Fabric REST API	As for Power BI.
Scopes.STORAGE	https://storage.azure.com/.default	Azure Storage and OneLake	OneLake: a workspace role or item permissions. Storage accounts: an Azure role such as Storage Blob Data Reader.
Scopes.KEY_VAULT	https://vault.azure.net/.default	Azure Key Vault	An Azure role or access policy on the key vault.
Scopes.SQL	https://database.windows.net/.default	Azure SQL, Fabric warehouses, SQL analytics endpoints and SQL databases	Fabric: a workspace role or item permissions. Azure SQL: CREATE USER [<app-name>] FROM EXTERNAL PROVIDER.
Scopes.KUSTO	https://api.kusto.windows.net/.default	Azure Data Explorer, Fabric Eventhouse and KQL databases	A workspace role, or a database role such as .add database <db> viewers ('aadapp=<client-id>;<tenant-id>').
Scopes.AZURE_MANAGEMENT	https://management.azure.com/.default	Azure Resource Manager	An Azure role on the subscription, resource group or resource.
Scopes.LOG_ANALYTICS	https://api.loganalytics.io/.default	Azure Monitor Log Analytics	Log Analytics Reader on the Log Analytics workspace.
(none)	https://<tenant>.sharepoint.com/.default	SharePoint REST API	SharePoint application permissions, ideally Sites.Selected. SharePoint only allows app-only access with a certificate; client secrets are rejected.
(none)	https://<org>.crm11.dynamics.com/.default	Dataverse	Use your environment URL. Add the app as an application user in the Power Platform admin center.

One credential can get tokens for any number of these APIs.

When you don't need this package: notebookutils.credentials.getToken() already returns tokens for the identity running the notebook, for the storage, pbi, keyvault and kusto audiences. For those APIs, prefer the workspace identity, see Use a workspace identity where you can. Use this package when you need another API (such as Graph or SharePoint) or need to act as a separate app.

Using a client secret instead

Certificates are recommended, but Emails and anything else in this package that takes a credential, except SharePointLibraryManager, also accepts azure.identity.ClientSecretCredential, which is already in the Fabric runtime:

from azure.identity import ClientSecretCredential
from alt_fabric_m365.exchange import Emails

client_secret = notebookutils.credentials.getSecret("https://<key-vault-name>.vault.azure.net/", "<secret-name>")
credential = ClientSecretCredential("<tenant-id>", "<client-id>", client_secret)

mailbox = Emails(credential, "reports@contoso.com")

The identity running the notebook needs Get on the secret. Compared with a certificate:

• The secret is read into the notebook's memory, whereas the certificate's private key never leaves Key Vault.
• Client secrets expire after at most 2 years and have to be copied into Key Vault by hand when they're renewed.
• SharePoint's REST API rejects client secrets for app-only access.

Renewing the certificate

By default the credential signs with the latest version of the certificate, and Entra ID only accepts certificates that have been uploaded to the app registration. When you renew:

1. Create a new version by running the same az keyvault certificate create command as before. From this moment the credential signs with the new version.
2. Straight away, download the new version and upload it with az ad app credential reset ... --append, as in step 3 of the setup.
3. Once notebooks are running successfully, remove the old certificate from the app registration.

To avoid any failures between steps 1 and 2, first pin the current version with certificate_version="<current-version>", renew and upload, then remove the pin.

Emails

alt_fabric_m365.exchange.Emails works with mailboxes using Microsoft Graph, authenticating as the app rather than as a user.

Giving the app access to a mailbox

Use RBAC for Applications in Exchange Online to give the app access to specific mailboxes only. Run these as an Exchange administrator (Exchange Administrator role, or the Organization Management role group), using the ExchangeOnlineManagement PowerShell module:

Connect-ExchangeOnline -UserPrincipalName <admin@contoso.com>

# From Entra ID > Enterprise applications (not App registrations)
$appId    = "<application-id>"
$objectId = "<object-id>"

# 1. Register the app's service principal with Exchange
New-ServicePrincipal -AppId $appId -ObjectId $objectId -DisplayName "<app-name>"

# 2. Create a scope containing only the mailbox. Matching on GUID keeps working if the address changes.
$mbx = Get-Mailbox -Identity "reports@contoso.com"
New-ManagementScope -Name "<app-name> mailboxes" -RecipientRestrictionFilter "GUID -eq '$($mbx.GUID)'"

# 3. Assign the role, limited to that scope. Use "Application Mail.Read" instead if the app never deletes emails.
New-ManagementRoleAssignment -App $objectId -Role "Application Mail.ReadWrite" -CustomResourceScope "<app-name> mailboxes"

# Only if the app needs to send email
New-ManagementRoleAssignment -App $objectId -Role "Application Mail.Send" -CustomResourceScope "<app-name> mailboxes"

# 4. Check: the mailbox should show InScope = True, any other mailbox False
Test-ServicePrincipalAuthorization -Identity $objectId -Resource "reports@contoso.com" | Format-Table
Test-ServicePrincipalAuthorization -Identity $objectId -Resource "someone.else@contoso.com" | Format-Table

To cover several mailboxes, use a filter such as MemberOfGroup -eq '<distinguished name of a mail-enabled security group>' and add the mailboxes to the group.

Test-ServicePrincipalAuthorization reflects changes immediately, but Graph can take from 30 minutes to 2 hours to apply them.

Don't add Mail.Read, Mail.ReadWrite or Mail.Send as application permissions on the app registration. Those apply to every mailbox in the tenant, and they add to the Exchange role assignment rather than being limited by it.

Roles needed by each method:

Method	Exchange role
search_message_by_subject_and_sender, get_attachment_ids_and_names, download_attachment	Application Mail.Read or Application Mail.ReadWrite
delete_email	Application Mail.ReadWrite
send_email	Application Mail.Send

Creating an Emails instance

from alt_fabric_m365.exchange import Emails

mailbox = Emails(credential, "reports@contoso.com")

Parameter	Description
credential	A KeyVaultCertificateCredential, a ClientSecretCredential, or any other credential with a get_token method.
mailbox	Optional. Email address of the default mailbox to use.

A token is requested from the credential for each call, and the credential caches it, so one instance can be used for the whole notebook.

Every method also takes an optional shared_mailbox_email argument to work with a different mailbox for that call. The app needs access to that mailbox too. If you always pass shared_mailbox_email, you can leave out mailbox, as in Emails(credential). A call with neither raises ValueError.

Finding an email

message_id = mailbox.search_message_by_subject_and_sender(
    subject="Daily Sales Report",
    sender_email="noreply@supplier.com",
)

Parameter	Default	Description
subject		The exact subject to match.
sender_email		The sender's email address.
only_search_inbox	True	Search only the Inbox. Set to False to search every folder.
only_return_latest	True	Return only the ID of the most recent match. Set to False to return every match.
shared_mailbox_email	None	Search a different mailbox.

With only_return_latest=True it returns the message ID as a string. With False it returns a list of messages, newest first, each a dictionary with id, subject, from, receivedDateTime, parentFolderId and hasAttachments.

If nothing matches it raises MessageNotFoundError, with the message No matching messages found. Use this to handle emails that don't always arrive:

from alt_fabric_m365.exchange import MessageNotFoundError

try:
    message_id = mailbox.search_message_by_subject_and_sender("Weekly Exceptions", "noreply@supplier.com")
except MessageNotFoundError:
    message_id = None
    print("No exceptions report this week.")

Processing every matching email, oldest first:

messages = mailbox.search_message_by_subject_and_sender(
    "Daily Sales Report", "noreply@supplier.com", only_return_latest=False
)

for message in reversed(messages):
    print(message["receivedDateTime"], message["id"])

Downloading attachments

get_attachment_ids_and_names returns a list of (attachment_id, file_name) tuples, one for each file attached to the email:

attachments = mailbox.get_attachment_ids_and_names(message_id)
# [('AAMkAD...', 'sales.csv'), ('AAMkAD...', 'summary.xlsx')]

Inline images, such as logos in an email signature, are left out unless you pass include_inline=True. Attached emails and links to cloud files are always left out, as they can't be downloaded. An empty list means the email has nothing to download, so check for it rather than taking the first item.

download_attachment returns the attachment's content. By default it's decoded as UTF-8 text, which suits CSV and text files:

for attachment_id, file_name in attachments:
    if file_name.endswith(".csv"):
        csv_text = mailbox.download_attachment(message_id, attachment_id)
        notebookutils.fs.put(f"{lakehouse}/Files/Sales/{file_name}", csv_text, overwrite=True)

Use encoding for text in another encoding, for example encoding="cp1252" for some Windows exports.

For binary files such as Excel workbooks or PDFs, set is_binary=True to get bytes:

import io
import pandas as pd

content = mailbox.download_attachment(message_id, attachment_id, is_binary=True)

# Read an Excel workbook directly
df = pd.read_excel(io.BytesIO(content))

# Or save it to the notebook's default lakehouse
with open(f"/lakehouse/default/Files/Sales/{file_name}", "wb") as f:
    f.write(content)

Only file attachments can be downloaded. Passing the ID of an attached email or a link to a cloud file (from somewhere other than get_attachment_ids_and_names) raises an exception.

Deleting an email

mailbox.delete_email(message_id)

Returns True, or raises an exception if the email couldn't be deleted. Delete emails after processing them, so the next run doesn't pick them up again.

Sending an email

mailbox.send_email(
    subject="Sales load complete",
    content="Today's sales file was loaded successfully.\n\nRows loaded: 1,204",
    email_addresses=["finance-team@contoso.com", "someone@contoso.com"],
)

The email is sent from the mailbox as plain text and saved in its Sent Items. This needs the Application Mail.Send role. Use shared_mailbox_email to send from a different mailbox.

Full example

A notebook that loads three daily reports from a shared mailbox, run from a pipeline. Missing reports are skipped rather than failing the run, and an email without a CSV attachment is left in the mailbox for someone to look at.

# Parameters cell
TENANT_ID = "<tenant-id>"
CLIENT_ID = "<client-id>"
KEY_VAULT = "https://<key-vault-name>.vault.azure.net/"
CERTIFICATE_NAME = "<certificate-name>"
MAILBOX = "reports@contoso.com"
SENDER = "noreply@supplier.com"
LAKEHOUSE = "abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<lakehouse>.Lakehouse"

from alt_fabric_m365.auth import KeyVaultCertificateCredential
from alt_fabric_m365.exchange import Emails, MessageNotFoundError

credential = KeyVaultCertificateCredential(TENANT_ID, CLIENT_ID, KEY_VAULT, CERTIFICATE_NAME)
mailbox = Emails(credential, MAILBOX)

reports = {
    "Daily Change Report": "Files/ServiceDesk/changes.csv",
    "Merged Request Report": "Files/ServiceDesk/merged.csv",
    "Deleted Tickets": "Files/ServiceDesk/deleted.csv",
}

for subject, path in reports.items():
    try:
        message_id = mailbox.search_message_by_subject_and_sender(subject, SENDER)
    except MessageNotFoundError:
        print(f"No email for '{subject}', skipping.")
        continue

    csv_files = [(attachment_id, file_name)
                 for attachment_id, file_name in mailbox.get_attachment_ids_and_names(message_id)
                 if file_name.lower().endswith(".csv")]
    if not csv_files:
        print(f"No CSV attachment on '{subject}', skipping.")
        continue

    attachment_id, file_name = csv_files[0]
    content = mailbox.download_attachment(message_id, attachment_id)
    notebookutils.fs.put(f"{LAKEHOUSE}/{path}", content, overwrite=True)
    mailbox.delete_email(message_id)
    print(f"Loaded '{subject}' ({file_name}).")

SharePoint libraries

alt_fabric_m365.sharepoint.SharePointLibraryManager manages SharePoint document libraries and their permissions through Entra ID security groups. Each operation takes a pandas DataFrame describing the libraries or memberships you want, makes SharePoint match it, and returns an action log of what it did.

What it manages

For each library (a row with a site_url, a library_code and a group_prefix):

• The library. Created with the code as its URL slug (/sites/<site>/<code>), then given its display name. The slug never changes, so renaming a library only changes its display name. It is given its own permissions, not inherited from the site.
• Security groups, named {group_prefix}-{library_code}-{suffix} (or {group_prefix}-{site}-{library_code}-{suffix} with include_site_name=True). Names are how the manager finds existing groups, so don't change the naming after rollout.
  • Auto-Read, Auto-Contribute and Auto-Owner are owned by the automation. Their membership is synced from your data and they are emptied when the library is unlinked.
  • Manual-Read and Manual-Contribute are optional, hand-curated groups. They are granted access like the Auto groups, but their membership is never changed.
  • Group names use the library code, so they stay the same when the library's display name changes. create_libraries updates the groups' descriptions, which name the library, to match. Names only change if the code itself changes, through rename_libraries with old_library_code.
  • The app registration is made owner of every group it creates, and an existing group is only granted access or synced if the app owns it (see Safety features). Pass app_owned=False for groups people own, such as a leadership team's groups owned by the headteacher.
• Navigation (optional). A link to the library in the site's quick launch, under a heading such as "Document Libraries".
• Provenance (optional). source_system and source_id stored in the library's property bag, invisible to users, so a library can be traced back to the record that created it.
• Site sharing (optional). Turns off "Allow members to share", "Allow access requests" and "Allow members to invite others" on each site, so access only comes from the groups.

Giving the app access

The app registration needs these application permissions, with admin consent:

API	Permission	Used for
Microsoft Graph	Sites.FullControl.All, or Sites.Selected with full control on each site	Finding sites, creating and renaming libraries
Microsoft Graph	Group.ReadWrite.All and GroupMember.ReadWrite.All	Creating and renaming groups, syncing membership
Microsoft Graph	User.Read.All	Resolving users by UPN, mail or proxy address
Microsoft Graph	Files.ReadWrite.All	Only for OneDrive shortcuts (create_onedrive_shortcuts=True)
SharePoint	Sites.FullControl.All, or Sites.Selected with full control on each site	Permissions, property bag, navigation and sharing settings

SharePoint only accepts app-only tokens obtained with a certificate, so use KeyVaultCertificateCredential, not a client secret. Set up the certificate as in Setting up a certificate, uploading it to this app registration. The identity running the notebook (for a pipeline, whatever the notebook activity runs as) needs Get on the certificate and Sign on its key.

Creating a SharePointLibraryManager

from alt_fabric_m365.auth import KeyVaultCertificateCredential
from alt_fabric_m365.sharepoint import SharePointLibraryManager

credential = KeyVaultCertificateCredential(
    "<tenant-id>", "<client-id>", "https://<key-vault-name>.vault.azure.net/", "<certificate-name>",
)

mgr = SharePointLibraryManager(
    credential,
    sharepoint_hostname="contoso.sharepoint.com",
    dry_run=True,                   # log what would change, change nothing
    max_workers=4,                  # libraries processed in parallel
    include_site_name=False,        # see group naming above; don't change after rollout
    add_to_navigation=True,
    navigation_label="Document Libraries",
    max_removals_per_group=10,      # see Safety features
)

Create the credential without a scope: the manager asks it for a Graph token and a SharePoint token (https://<sharepoint_hostname>/.default), and the credential caches each until shortly before it expires. The constructor gets both tokens straight away, so wrong credentials or permissions fail here rather than part way through. dry_run, max_workers, include_site_name, add_to_navigation, navigation_label, create_onedrive_shortcuts, max_removals_per_group and allow_mass_removal are defaults that each operation can override, for example mgr.create_libraries(df, dry_run=False).

Operations

Method	What it does	DataFrame columns
create_libraries(df)	Ensures each library exists with its own permissions, its groups, metadata and navigation link. Safe to re-run: existing libraries and groups are reused, and a changed display name is applied.	site_url, library_code, library_display_name, group_prefix. Optional: auto_groups (default True), manual_read, manual_contribute, app_owned (default True), source_system, source_id
update_memberships(df, libraries_df=None, nested_groups_df=None)	Makes each library's Auto groups contain exactly the users (and nested groups) given. Users are matched by UPN, then mail, then proxy addresses, so aliased domains work. Libraries in libraries_df with no rows in df have their Auto groups emptied.	site_url, library_code, user_principal_name, permission (read, contribute or owner), group_prefix
unlink_libraries(df)	For libraries removed at source: renames to {code}_UNLINKED, empties the Auto groups, keeps the Manual groups, removes the navigation link. Nothing is deleted.	site_url, library_code, group_prefix
retire_moved_libraries(df)	For a library whose site changed: renames the copy on the old site to {code}_MOVED and removes the groups' access to it. With new_site_url, waits until the library exists there (retire_deferred until then).	site_url (the old site), library_code, group_prefix. Optional: new_site_url
rename_libraries(df)	Sets new display names and, when old_library_code is given, renames the groups to the new code.	site_url, library_code, library_display_name, group_prefix. Optional: old_library_code
grant_group_access(df)	Grants existing groups a permission level on existing libraries, for example a leadership group on every library. Additive: never removes access.	site_url, library_code, group_name, permission
enforce_site_sharing_lockdown(site_urls)	Turns off member sharing, access requests and member invites on each site, and checks the change stuck.	A list of site URLs
set_library_metadata(df) / get_library_metadata(df)	Writes or reads source_system and source_id in each library's property bag.	site_url, library_code, and for set source_system and/or source_id

Read-only helpers: mgr.group_name(group_prefix, site_url, library_code, suffix) returns a group name exactly as the operations build it, and mgr.get_navigation_link(site_url, library_code) and mgr.get_site_sharing_settings(site_url) read back what is on the site.

import pandas as pd

site = "https://contoso.sharepoint.com/sites/Academy"

libraries = pd.DataFrame({
    "site_url": [site, site],
    "library_code": ["MATH", "ENG"],
    "library_display_name": ["Maths", "English"],
    "group_prefix": "ACA-Sharepoint",
    "manual_read": True,
})
results = mgr.create_libraries(libraries)

members = pd.DataFrame({
    "site_url": site,
    "library_code": ["MATH", "MATH", "ENG"],
    "user_principal_name": ["jo@contoso.com", "sam@contoso.com", "alex@contoso.com"],
    "permission": "contribute",
    "group_prefix": "ACA-Sharepoint",
})
results = mgr.update_memberships(members, libraries_df=libraries)

The action log

Every operation returns a DataFrame with the columns site_url, document_library, group, user, action, permission and status, one row per thing it did or (in a dry run) would do. Problems are rows too, so one bad library, user or site never stops the others. Where nothing needed changing, the row says so (already_member, already_assigned), and nothing is written. Two sets of action names help decide whether a run succeeded:

• FATAL_ACTIONS: error, add_failed, remove_failed. Something failed and should be retried, so fail the run.
• WARNING_ACTIONS: for example user_not_found, library_not_found and removal_guardrail_triggered. Problems with the data that failing the run wouldn't fix.

from alt_fabric_m365.sharepoint import FATAL_ACTIONS, WARNING_ACTIONS

warnings = results[results["action"].isin(WARNING_ACTIONS)]
failures = results[results["action"].isin(FATAL_ACTIONS)]
if not failures.empty:
    raise RuntimeError(f"{len(failures)} failed action(s)")

Safety features

• Dry run. With dry_run=True nothing is changed; the log shows what would be, with actions ending (dry run).
• Removal guardrail. If syncing a group would remove more than max_removals_per_group members, its removals are skipped and logged as removal_guardrail_triggered (additions still happen). Re-run with allow_mass_removal=True once you've checked the data.
• Incomplete data never drives removals. If any user (or nested group) for a role can't be resolved, removals of that kind from that role's group are skipped (removals_skipped), as the "stale" member may be the one that failed to resolve.
• Manual groups are never synced or emptied, and service principals and devices in Auto groups are left in place.
• No guessing between duplicates. If two Entra groups share a name the manager would use, it stops for that library rather than pick one. Library codes that would produce the same group names (such as PE-KS3 and PEKS3, or codes differing only in case) are skipped with group_name_collision, including when the other library was created by an earlier run. A library is only ever found by its URL, so a code is never matched to another library that merely has that title (library_name_conflict).
• Groups must be the app's. Group names are predictable, so a group someone else creates with the name a library would use could otherwise be handed that library's access. The app is made owner of the groups it creates, and before it grants or syncs an existing group it checks the app is an owner: a group with no owner (as earlier versions created them) has the app added and is logged as group_owner_added; a group owned only by others is skipped with group_not_owned. The app's object ID is read from its Graph token, or can be passed as service_principal_id. Groups created with app_owned=False aren't owned or checked, and grant_group_access grants the groups it's given by name.
• Creating a group, navigation link or OneDrive shortcut is never repeated after a failure that may have succeeded (a 5xx or timeout), so a retry can't make a duplicate. The next run finds what was created.
• Only document libraries count as libraries. An ordinary list whose URL ends in the code (/Lists/ENG) is never adopted, though its title still counts for library_name_conflict.
• A moved library keeps working until its replacement exists. Given new_site_url, retire_moved_libraries leaves the old library alone (retire_deferred) until the library exists on the new site.
• Permission levels are found by type, not by name, so sites in another language, or with renamed levels, work too.
• Tokens only go to your tenant. Every site_url must be https:// on sharepoint_hostname, and redirects to another host or to plain http are refused. A redirect that can't be followed is an error, never a success.
• Throttling and transient errors are retried, honouring Retry-After, with exponential backoff.

Using it in your own setting

The manager only works with DataFrames, so it doesn't mind where the data comes from: a school's faculties, a company's departments, project teams or anything else that needs a library with its own access. This section suggests a set of tables to drive it from, the tables to record what it did, and the order to run the operations in. The example notebook is one setting built this way.

Input tables

Keep the desired state in a few lakehouse tables, maintained by whatever feeds them (an MIS or HR extract, a hand-maintained table, a dataflow). Names and the extra columns used only by the flow are suggestions; the columns the manager reads are fixed.

sharepoint_libraries: one row per library, holding its current state.

Column	Type	Required	Meaning
library_id	string	Yes	Stable ID from the source system. Stored on the library as source_id, so a library can be traced back to its record.
site_url	string	Yes	Full URL of the site the library lives on, https://<tenant>.sharepoint.com/sites/<site>.
library_code	string	Yes	Short code, used as the library's URL and in its group names (for example MATH). Never change it after the library is created; rename with library_display_name instead.
library_display_name	string	Yes	Name shown in SharePoint (for example Mathematics). Can change; the next create pass applies it.
group_prefix	string	Yes	Start of the group names, for example ACA-Sharepoint. Use one prefix per site.
auto_groups	boolean	No (True)	Create the synced Auto-Read and Auto-Contribute groups.
manual_read, manual_contribute	boolean	No (False)	Create hand-curated Manual-Read / Manual-Contribute groups.
app_owned	boolean	No (True)	The app owns the groups. False for groups a person owns and curates.
previous_site_url	string	Flow only	The site the library was on before a move, or null.
deleted	boolean	Flow only	True once the library has been removed at source. Keep the row, so the unlink pass sees it.
last_changed_ts	timestamp	Flow only	When the row last changed. Drives the incremental passes.

sharepoint_library_members: one row per person per library. The full desired membership, not changes.

Column	Type	Required	Meaning
library_id	string	Yes	Joins to sharepoint_libraries.
user_principal_name	string	Yes	The person's sign-in name or email. Aliases and secondary addresses are resolved.
permission	string	Yes	read, contribute or owner.

sharepoint_library_nested_groups (optional): existing Entra ID security groups to nest in a library's Auto groups, such as an all-staff group.

Column	Type	Required	Meaning
library_id	string	Yes	Joins to sharepoint_libraries.
member_group	string	Yes	The group's object ID, or its exact display name.
permission	string	Yes	read, contribute or owner.

sharepoint_library_grants (optional): existing groups that should hold a permission on a library without being synced, such as a leadership team on every library.

Column	Type	Required	Meaning
library_id	string	Yes	Joins to sharepoint_libraries.
group_name	string	Yes	The group's exact display name.
permission	string	Yes	read, contribute or owner.

Output tables

sharepoint_action_log: every operation's action log, appended. The seven action log columns (site_url, document_library, group, user, action, permission, status, all strings), plus:

Column	Type	Meaning
run_id	string	Identifies the run, to find everything it did.
operation	string	Which operation logged the row, for example update_memberships.
dry_run	boolean	Whether the run was a dry run.
logged_at	timestamp	When the row was written.

sharepoint_sync_state: the watermark for the incremental passes.

Column	Type	Meaning
process	string	Names the flow, for example sharepoint_libraries. Include the rollout scope in it if you filter the inputs, so a wider scope starts a fresh watermark.
last_processed_ts	timestamp	The newest last_changed_ts a clean run has processed.
updated_at	timestamp	When it was last advanced.

Optionally, also write the user_not_found rows to a table of their own (run_id, site_url, library_code, user_principal_name, reason, logged_at), so whoever owns the source data can fix the addresses.

Recommended flow

Run the operations in this order, from a pipeline, with a dry run first whenever the inputs or the package version change:

1. Read the libraries table, apply any scope, then read the watermark, and the newest last_changed_ts as an upper bound. Only rows between the two are "changed" in this run. Rows that land while it runs are left for the next one.
2. Lock down site sharing on every live site (full pass). It's independent of the rest, and corrects settings changed by hand.
3. Create changed, not-deleted libraries (incremental). This also applies renames and new metadata. It runs before the grant and membership passes, which need the libraries to exist.
4. Unlink changed, deleted libraries (incremental).
5. Retire libraries whose site changed (incremental, on previous_site_url). This runs after the create pass, and with new_site_url it only retires a library once its replacement exists on the new site, so a failed create never leaves the library without working access (retire_deferred until then).
6. Grant the extra groups on every live library (full pass). Additive, so it also restores access removed by hand.
7. Sync memberships for every live library (full pass), passing all live libraries as libraries_df so a library whose last member left has its groups emptied.
8. Save each operation's action log straight after it runs, so a later failure doesn't lose it.
9. Fail the run if any row's action is in FATAL_ACTIONS. Show the WARNING_ACTIONS rows, but don't fail on them: they're data problems that failing can't fix.
10. Advance the watermark to the upper bound, only after a clean run that wasn't a dry run. A failed run leaves it alone, so its changes are retried.

Every operation is safe to re-run, so a failed run is fixed by running it again.

# Parameters cell
DRY_RUN = True
SCHEMA = "<workspace>.<lakehouse>.<schema>"
TENANT_ID = "<tenant-id>"
CLIENT_ID = "<client-id>"
KEY_VAULT = "https://<key-vault-name>.vault.azure.net/"
CERTIFICATE_NAME = "<certificate-name>"
SHAREPOINT_HOSTNAME = "<tenant>.sharepoint.com"

import uuid
from datetime import datetime, timezone

import pandas as pd

from alt_fabric_m365.auth import KeyVaultCertificateCredential
from alt_fabric_m365.sharepoint import FATAL_ACTIONS, WARNING_ACTIONS, SharePointLibraryManager

credential = KeyVaultCertificateCredential(TENANT_ID, CLIENT_ID, KEY_VAULT, CERTIFICATE_NAME)
mgr = SharePointLibraryManager(
    credential, SHAREPOINT_HOSTNAME,
    dry_run=DRY_RUN, max_workers=4, include_site_name=False, add_to_navigation=True,
)

RUN_ID = str(uuid.uuid4())
PROCESS = "sharepoint_libraries"
logs = []


def save(operation, log):
    """Step 8: keep the log for the gate, and append it to the audit table."""
    logs.append(log)
    if not log.empty:
        rows = log.assign(run_id=RUN_ID, operation=operation, dry_run=DRY_RUN, logged_at=datetime.now(timezone.utc))
        spark.createDataFrame(rows).write.format("delta").mode("append").saveAsTable(f"{SCHEMA}.sharepoint_action_log")


# Step 1: what changed since the last clean run, up to now
libraries = spark.table(f"{SCHEMA}.sharepoint_libraries").toPandas()
# Setting-specific: filter the rows in scope here (see "Adding setting-specific steps")

# The watermark is read after any scope is applied, as the scope is part of its key
state = spark.sql(f"SELECT last_processed_ts FROM {SCHEMA}.sharepoint_sync_state WHERE process = '{PROCESS}'").collect()
since = state[0]["last_processed_ts"] if state else datetime(1900, 1, 1)
until = libraries["last_changed_ts"].max() if not libraries.empty else since
changed = libraries[(libraries["last_changed_ts"] > since) & (libraries["last_changed_ts"] <= until)]
live = libraries[~libraries["deleted"]].assign(source_id=lambda df: df["library_id"])

save("site_sharing_lockdown", mgr.enforce_site_sharing_lockdown(live["site_url"]))                          # step 2
save("create_libraries", mgr.create_libraries(live[live["library_id"].isin(changed["library_id"])]))        # step 3
save("unlink_libraries", mgr.unlink_libraries(changed[changed["deleted"]]))                                  # step 4

# Step 5: new_site_url makes retire wait until the library exists on its new site (not for deleted ones)
moved = changed[changed["previous_site_url"].notna() & (changed["previous_site_url"] != changed["site_url"])]
save("retire_moved_libraries", mgr.retire_moved_libraries(moved.assign(
    site_url=moved["previous_site_url"], new_site_url=moved["site_url"].where(~moved["deleted"]),
)))

# Steps 6 and 7 read the optional tables too; leave out any you don't use
keys = live[["library_id", "site_url", "library_code", "group_prefix"]]
grants = spark.table(f"{SCHEMA}.sharepoint_library_grants").toPandas().merge(keys, on="library_id")
save("grant_group_access", mgr.grant_group_access(grants))                                                  # step 6

members = spark.table(f"{SCHEMA}.sharepoint_library_members").toPandas().merge(keys, on="library_id")
nested = spark.table(f"{SCHEMA}.sharepoint_library_nested_groups").toPandas().merge(keys, on="library_id")
save("update_memberships", mgr.update_memberships(members, libraries_df=keys, nested_groups_df=nested))      # step 7

# Setting-specific: extra passes go here, before the gate (see "Adding setting-specific steps")

# Step 9: fail on failed actions, show warnings
results = pd.concat(logs, ignore_index=True)
display(results[results["action"].isin(WARNING_ACTIONS)])
failed = results[results["action"].isin(FATAL_ACTIONS)]
if not failed.empty:
    raise RuntimeError(f"{len(failed)} failed action(s), see sharepoint_action_log where run_id = '{RUN_ID}'")

# Step 10: advance the watermark after a clean live run
if not DRY_RUN and not changed.empty:
    spark.sql(f"""
        MERGE INTO {SCHEMA}.sharepoint_sync_state AS t
        USING (SELECT '{PROCESS}' AS process, TIMESTAMP'{until:%Y-%m-%d %H:%M:%S.%f}' AS last_processed_ts) AS s
        ON t.process = s.process
        WHEN MATCHED THEN UPDATE SET t.last_processed_ts = s.last_processed_ts, t.updated_at = current_timestamp()
        WHEN NOT MATCHED THEN INSERT (process, last_processed_ts, updated_at)
            VALUES (s.process, s.last_processed_ts, current_timestamp())
    """)

Adding setting-specific steps

The skeleton is deliberately generic. Most settings add a few rules of their own, at the two places marked in it. These are the patterns the example notebook uses for its school faculties; swap in your own names.

Scope and exclusions, straight after reading the libraries table. Everything after it works on the filtered rows, so a filtered-out library is neither created nor synced. Include the scope in the watermark's process key, so widening it starts a fresh watermark and the newly included rows are processed:

# Roll out a few sites at a time, let the source opt rows out, and keep the
# leadership library's code (below) for that library alone
SITES_IN_SCOPE = {"https://<tenant>.sharepoint.com/sites/Academy"}
LEADERSHIP_CODE = "SLT"

libraries = libraries[
    libraries["site_url"].isin(SITES_IN_SCOPE)
    & ~libraries.get("exclude", pd.Series(False, index=libraries.index)).astype("boolean").fillna(False)
    & (libraries["library_code"].map(sanitise_for_group_name).str.upper() != LEADERSHIP_CODE)
]
PROCESS = f"sharepoint_libraries|{','.join(sorted(SITES_IN_SCOPE))}"

This needs from alt_fabric_m365.sharepoint import sanitise_for_group_name. The code check compares sanitised codes, because that's how they appear in group names: S-L-T would otherwise share the leadership library's groups. The example notebook does the same filtering in Spark SQL instead, in its eligible() predicate, and also reports the excluded rows as warnings so they don't go unnoticed.

A leadership library on each site (the example notebook's SLT library), before the gate. It's governed only by hand-curated Manual groups that a person (the headteacher) owns, so app_owned=False, and it has no Auto groups. A second pass grants those groups access to every library on their site:

# One leadership library per site, with Manual groups only, owned by a person
leadership = live[["site_url", "group_prefix"]].drop_duplicates().assign(
    library_code=LEADERSHIP_CODE, library_display_name="Senior Leadership",
    auto_groups=False, manual_read=True, manual_contribute=True, app_owned=False,
)
save("create_leadership_libraries", mgr.create_libraries(leadership))

# Its groups get Contribute and Read on every library on their site. Names come
# from mgr.group_name, so they always match the groups create_libraries made.
leadership_grants = pd.DataFrame(
    [
        {
            "site_url": row.site_url,
            "library_code": row.library_code,
            "group_name": mgr.group_name(row.group_prefix, row.site_url, LEADERSHIP_CODE, suffix),
            "permission": permission,
        }
        for row in keys.itertuples(index=False)
        for suffix, permission in (("Manual-Contribute", "contribute"), ("Manual-Read", "read"))
    ],
    columns=["site_url", "library_code", "group_name", "permission"],
)
save("grant_leadership_access", mgr.grant_group_access(leadership_grants))

The leadership library isn't in sharepoint_libraries, so the membership sync never touches its groups, and the grant pass is additive, so a library that's later unlinked keeps the leadership access. If two academies could share a site, give each its own code or skip the shared site, as create_libraries keeps only the first of two rows for the same library.

Other rules the example notebook adds, which need no new operations:

• Group prefix from the source. It builds group_prefix as academy_code || '-Sharepoint' in SQL, so each academy's groups are named after it.
• Nested groups from flags. Instead of a nested-groups table, it builds the rows from two flag columns and a group column on the faculty list (all_staff_read, all_staff_contribute, all_staff_security_group), one row per flag that's set.
• Unresolved users with context. It joins the user_not_found rows back to the source table, so each carries the academy and subject, which helps whoever fixes the address in the source system.

Things to decide once, before the first live run, as changing them later orphans what was created:

• Group naming. group_prefix and include_site_name make up every group name, and names are how the manager finds its groups. Use one prefix per site, so two sites never share a library's groups.
• Library codes. A library's code is its URL and part of its group names. Treat it as permanent, and use library_display_name for renames. Codes that differ only in punctuation or case (PE-KS3, peks3) would share groups, so they're skipped with group_name_collision.
• Who owns the groups. Leave app_owned on for groups the automation manages. Turn it off for groups a person owns and curates, and give those to grant_group_access rather than the membership sync.
• Scope. If you roll out a few sites at a time, filter the input DataFrames and include the filter in the watermark's process key. Widening the scope then starts a fresh watermark, so the newly included rows are processed.

Outside Fabric, KeyVaultCertificateCredential isn't available (it needs notebookutils). Pass any credential with a get_token method that uses a certificate, such as azure.identity.CertificateCredential, and run the same flow with the input tables read however suits.

Example notebook

examples/Manage Faculty Sharepoint Libraries.Notebook is a complete notebook, run from a pipeline, that drives every operation from curated lakehouse tables: incremental create, unlink and retire using a watermark, full membership and access passes, an audit table and a run outcome gate. docs/faculty-sharepoint-notebook.md describes it cell by cell, and how it was moved onto this package.

Troubleshooting

Error	Likely cause
Failed to get certificate ... Status code: 403, Forbidden	The identity running the notebook can't read the certificate. Assign Key Vault Reader on the certificate, or the certificate Get access policy.
Failed to get certificate ... Status code: 404	The certificate name or Key Vault URL is wrong.
ValueError when creating KeyVaultCertificateCredential	akv_url isn't an https:// Key Vault URL, such as https://<key-vault-name>.vault.azure.net/.
Failed to sign client assertion ... Forbidden ... keys/sign/action	The identity running the notebook can't sign with the key. Assign Key Vault Crypto User on the key, or the key Sign access policy.
AADSTS700027 (certificate not registered on application)	The certificate version used to sign hasn't been uploaded to the app registration. This usually happens after a renewal, see Renewing the certificate.
AADSTS700016 (application not found in the directory)	The client ID or tenant ID is wrong.
AADSTS7000215 (invalid client secret)	Client secret only: the secret is wrong or has expired.
Graph 403 with ErrorAccessDenied	The app has no Exchange role for this mailbox. Check Test-ServicePrincipalAuthorization, or wait up to 2 hours after a change.
Graph 404 for the mailbox	The mailbox address is wrong, or the mailbox doesn't exist.
ValueError from an Emails method	No mailbox was given: pass one to Emails(credential, mailbox) or shared_mailbox_email to the call.
MessageNotFoundError: No matching messages found.	No email in the Inbox matches the exact subject and sender. Check both, or try only_search_inbox=False.
get_attachment_ids_and_names returns an empty list	The email has no file attachments. Inline images are left out unless you pass include_inline=True, and attached emails and cloud links are always left out.
Attachment ... is a #microsoft.graph.itemAttachment	download_attachment was given the ID of an attached email, not a file.
ImportError: notebookutils is not available. KeyVaultCertificateCredential must be used in a Fabric notebook.	The code is running outside Fabric. KeyVaultCertificateCredential only works in Fabric (or Synapse) notebooks.
SharePoint 401 with Unsupported app only token	The token came from a client secret. SharePoint needs a certificate, see Giving the app access.
ValueError: site_url '...' is not on https://...	A site_url isn't on the sharepoint_hostname the manager was created with, or uses http://.
error row: Multiple Entra groups share displayName	Two groups have the name the manager uses for a library. Delete or rename the duplicate.
user_not_found rows	The address matches no user's UPN, mail or proxy addresses. Fix it in the source data; the next run picks the user up.
removal_guardrail_triggered row	A sync would remove more members than max_removals_per_group. Check the input data, then re-run with allow_mass_removal=True.
library_not_found row	The library doesn't exist on the site, for example it was deleted by hand. Recreate it with create_libraries or remove it from the input. When unlinking, its Auto groups are still emptied.
group_not_owned row	A group with the name the manager uses is owned by someone, but not by the app registration, so it wasn't used. Check who created it: if it's legitimate, add the app's service principal as an owner; otherwise delete it.
retire_deferred row	The library hasn't been created on its new site yet (for example the create failed), so the old one was left working. It's retired on a later run once the new one exists.
library_name_conflict row	Another library on the site already has the code as its title, so the library can't be created. Rename that library's title.

Upgrading to 0.7.0

0.7.0 gives each part of the package the same layout: a folder per area, with everything you need importable from the folder's name. The only name that changes is graph_api, which only ever held the email code, so it's now exchange, alongside sharepoint:

Up to 0.6.x	From 0.7.0
from alt_fabric_m365.graph_api import Emails, MessageNotFoundError	from alt_fabric_m365.exchange import Emails, MessageNotFoundError
from alt_fabric_m365.auth import KeyVaultCertificateCredential, Scopes	Unchanged
from alt_fabric_m365.sharepoint import SharePointLibraryManager	Unchanged
from alt_fabric_m365 import Emails, KeyVaultCertificateCredential, ...	Unchanged

Nothing else changes, and alt_fabric_m365.graph_api still works: using it shows a FutureWarning asking you to change the import. It will be removed in a later release, so change graph_api to exchange in your notebooks when you next edit them.

0.7.0 also adds the licence to the package's metadata, so PyPI shows it.

If an environment still has the fabric_python_helper 0.6.0 forwarding release, remove it first: it requires alt-fabric-m365 0.6, so it can't be installed alongside 0.7.0. See Moving from fabric_python_helper.

Moving from fabric_python_helper

0.6.0 renames the package. Its code is the same as 0.5.0's.

	Up to 0.5.0	From 0.6.0
PyPI project	fabric-python-helper	alt-fabric-m365
Import	fabric_python_helper	alt_fabric_m365

The two import names are different, so both packages can be installed in the same environment. That lets you move notebooks over one at a time:

1. In each Fabric environment, add alt-fabric-m365 0.7.0 under Public libraries, keep fabric_python_helper 0.5.0 for now, and publish the environment.

2. In each notebook, change fabric_python_helper to alt_fabric_m365 in every import, and graph_api to exchange, and run it:

   from alt_fabric_m365.auth import KeyVaultCertificateCredential
   from alt_fabric_m365.exchange import Emails
   

3. Once no notebook in the workspace imports fabric_python_helper, remove it from the environment and publish it again.

Environments pinned to fabric_python_helper 0.5.0 or earlier keep working, and those versions stay on PyPI. There's one more release under the old name, fabric_python_helper 0.6.0, built from legacy/. It contains no code of its own: it installs alt-fabric-m365 0.6 and makes fabric_python_helper an alias for it, so old imports work but show a FutureWarning. There will be no later releases under the old name.

Upgrading from 0.3.x

0.4.0 removes the delegated (signed-in user) authentication, which stored a user's refresh token in Key Vault, and the pbi_admin module.

The examples below use the package's current names, alt_fabric_m365 and its exchange module (0.7.0 or later). On 0.6.x, import Emails from alt_fabric_m365.graph_api, and on 0.4.x or 0.5.x from fabric_python_helper.graph_api.

Emails

Before:

import fabric_python_helper.graph_api as gr

email_account = gr.Emails(tennant_id, client_id, akv_url, refresh_secret_name)
email_account.connect()

After:

from alt_fabric_m365.auth import KeyVaultCertificateCredential
from alt_fabric_m365.exchange import Emails

tenant_id = "<tenant-id>"
client_id = "<client-id>"
akv_url = "https://<key-vault-name>.vault.azure.net/"
certificate_name = "<certificate-name>"
shared_mailbox = "reports@contoso.com"

credential = KeyVaultCertificateCredential(tenant_id, client_id, akv_url, certificate_name)
email_account = Emails(credential, shared_mailbox)

The other method calls, including shared_mailbox_email=, are unchanged. connect(), get_initial_tokens() and get_user() have been removed.

Notebooks that didn't pass shared_mailbox_email read the signed-in user's own mailbox. They now need the mailbox passed to Emails, or shared_mailbox_email passed to every call, and the app needs access to it in Exchange.

Two behaviour changes to check:

• search_message_by_subject_and_sender raises MessageNotFoundError when nothing matches. It's a subclass of Exception with the same message, so existing handling still works, but except MessageNotFoundError: is clearer than comparing str(e).
• get_attachment_ids_and_names now only returns file attachments. Inline images (such as signature logos) are left out unless you pass include_inline=True, and attached emails and cloud links are always left out. Code that took the first attachment by position should pick it by name instead.

Once every notebook has moved over, delete the refresh token secret from Key Vault and disable Allow public client flows on the old app registration (or delete it).

pbi_admin

Removed	Use instead
AccessTokens().get_token_as_fabric_notebook_owner()	notebookutils.credentials.getToken("pbi")
AccessTokens().get_token_as_service_principal(...)	KeyVaultCertificateCredential(...).get_access_token(Scopes.POWER_BI), or ClientSecretCredential
SemanticModels(...).refresh_semantic_model()	The pipeline Semantic model refresh activity, sempy_labs.refresh_semantic_model(), or sempy.fabric.refresh_dataset()
Dataflows(...).refresh_dataflow()	The pipeline Dataflow activity

Dependencies: msal is no longer required, and azure-core is.

Building and publishing

Running the tests

The tests mock Microsoft Entra ID, Key Vault, Graph and SharePoint, and replace notebookutils and Spark, so they run anywhere without credentials. From the repository root:

python -m pip install -e .[test]
python -m pytest
python -m pyflakes alt_fabric_m365 legacy tests setup.py

The Tests workflow runs the same checks on Python 3.10, 3.11 and 3.12, and builds the package, for every pull request into main and every push to main. It doesn't publish anything.

Requiring the tests to pass before merging

On its own, the Tests workflow only reports a result: GitHub still lets a pull request with failing tests be merged. To block that, add a ruleset on main once the workflow has run at least once (GitHub only lists a check after it has reported):

1. On GitHub, go to Settings > Rules > Rulesets > New ruleset > New branch ruleset.
2. Name it, for example Protect main, and set Enforcement status to Active.
3. Under Target branches, select Add target > Include default branch.
4. Turn on Require a pull request before merging.
5. Turn on Require status checks to pass, select Add checks, and add test (3.10), test (3.11) and test (3.12).
6. Turn on Block force pushes, then select Create.

If the Python versions in tests.yml change, update the required checks to match, or pull requests will wait for a check that never runs.

What they cover:

File	Covers
test_auth.py	KeyVaultCertificateCredential: token requests and caching, Key Vault signing, the client assertion
test_exchange.py	Emails, and the deprecated graph_api name
test_sharepoint_http.py	The retry wrapper: throttling, Retry-After, server and connection errors, redirects
test_sharepoint_helpers.py	Group naming and collisions, input validation, user and group resolution, the property bag, navigation URLs
test_sharepoint_manager.py	Every SharePointLibraryManager operation end to end, including re-runs, dry runs, guardrails and failures, and authenticating with KeyVaultCertificateCredential
test_readme_flow.py	Runs the recommended flow example against the suggested input tables (a dry run, a live run, a re-run and a deletion), and again with the setting-specific steps inserted
test_faculty_notebook.py	Runs the example notebook cell by cell, with Spark SQL on SQLite and the real KeyVaultCertificateCredential
test_packaging.py	The version, public names and that the README's examples are valid Python

The SharePoint tests run against tests/sharepoint_fake.py, an in-memory fake of the Graph and SharePoint endpoints the manager calls, which keeps state so a test can check what an operation changed. Add --cov=alt_fabric_m365 to see coverage.

The tests can't show that the real services accept the requests, so test a new version in Fabric before releasing it (see Testing a wheel in Fabric). Get a token, find a known email and list its attachments. For the SharePoint manager, run the example notebook with RUN_SMOKE_TEST = True, first with DRY_RUN = True.

Versioning

The version number is only set in alt_fabric_m365/__init__.py, as __version__="0.7.0". setup.py reads it from there, so there's nothing else to keep in sync. Check it with python setup.py --version.

Versions follow semantic versioning. While the major version is 0, increase the minor version (0.4.0 to 0.5.0) for breaking changes, and the patch version (0.4.0 to 0.4.1) for fixes. PyPI never accepts the same version twice, even if it's deleted.

Building a wheel locally

From the repository root, in PowerShell:

python -m pip install --upgrade build twine
Remove-Item dist -Recurse -Force -ErrorAction SilentlyContinue
python -m build
python -m twine check dist/*

This creates two files in dist/:

• alt_fabric_m365-0.7.0-py3-none-any.whl, the wheel
• alt_fabric_m365-0.7.0.tar.gz, the source distribution

twine check confirms the package metadata and this README will render on PyPI. Clearing dist/ first stops old versions being uploaded by mistake.

If pip can't verify PyPI's certificate on your network, create C:\ProgramData\pip\pip.ini containing:

[global]
trusted-host = files.pythonhosted.org
               pypi.org

Testing a wheel in Fabric before publishing

Upload the .whl file to a lakehouse's Files folder, then in a notebook attached to that lakehouse:

%pip install /lakehouse/default/Files/alt_fabric_m365-0.7.0-py3-none-any.whl

Or add it to an environment under Custom libraries > Upload.

Publishing to PyPI with a GitHub release

This is the normal route. Merging to main never publishes anything; only publishing a GitHub release does. The Release workflow then:

1. Runs the tests.
2. Checks the release tag matches __version__ (tag v0.4.1 for __version__="0.4.1"), and stops if it doesn't.
3. Builds the package and runs twine check.
4. Publishes it to PyPI.
5. Attaches the wheel and source distribution to the GitHub release.

To release:

1. Update __version__ in alt_fabric_m365/__init__.py, merge it into main through a pull request.
2. On GitHub, go to Releases > Draft a new release.
3. Create a tag matching the version, for example v0.4.1, targeting main.
4. Add release notes, then select Publish release.
5. Check the workflow run under Actions, and that the new version appears on PyPI.

If a step fails, nothing after it runs. PyPI never accepts the same version twice, so a release whose version is already on PyPI fails at the publish step rather than silently uploading nothing.

How to recover depends on where the run failed. Check the Publish to PyPI step in the run, and whether the version is on PyPI:

• Before publishing (tests, tag check or build failed, nothing on PyPI): delete the GitHub release and its tag, fix the problem, and publish the release again.
• After publishing (the version is on PyPI, but attaching files to the release failed): don't delete the release, as publishing it again would fail at the PyPI step. Open the run under Actions and select Re-run failed jobs. Only the failed job runs again, using the files already built.
• A published version is broken: it can't be replaced. Fix it, increase the patch version (for example 0.4.1 to 0.4.2) and release that. Optionally yank the broken version on PyPI so pip stops choosing it.

Trusted Publishing setup

The workflow uploads to PyPI with Trusted Publishing, so no PyPI API token is stored in GitHub. PyPI only accepts uploads from this repository's release.yml workflow running in the pypi environment. Set it up once:

1. On PyPI, open the alt-fabric-m365 project, then Manage > Publishing > Add a new publisher > GitHub. Enter owner ArchwayTrust, repository Insights-Fabric-PythonHelper, workflow release.yml and environment pypi. Before the project's first release, when it doesn't exist yet, add a pending publisher instead: go to Your account > Publishing, and enter the same details with PyPI project name alt-fabric-m365. The first release then creates the project.
2. On GitHub, go to Settings > Environments > New environment and create pypi. Then, in the environment:
   • Under Deployment branches and tags, choose Selected branches and tags, select Add deployment branch or tag rule, set Ref type to Tag and enter the pattern v*. Only release tags can then publish, even if the workflow is started some other way.
   • Optionally, turn on Required reviewers and add the people who can approve a release, so each upload to PyPI waits for one of them.
3. After the first release published this way, delete the old PYPI_API_TOKEN repository secret and revoke that token on PyPI.

Publishing to PyPI manually

After building and checking the wheel as above:

python -m twine upload dist/*

When prompted, enter __token__ as the username and your PyPI API token (starting pypi-) as the password.

To try a release on TestPyPI first (this needs a separate TestPyPI account and token):

python -m twine upload --repository testpypi dist/*

%pip install -i https://test.pypi.org/simple/ --extra-index-url https://pypi.org/simple/ alt-fabric-m365==0.7.0

Publishing the fabric_python_helper forwarding release

This is needed once, for 0.6.0, after alt-fabric-m365 0.6.0 is on PyPI (the forwarding release depends on it). legacy/ holds the final release of the old name, fabric_python_helper 0.6.0, which only forwards to alt_fabric_m365. The Tests workflow builds and checks it, but the Release workflow doesn't publish it, so upload it by hand:

1. On PyPI, open the fabric-python-helper project, go to Manage > Settings > API tokens, and create a token scoped to that project only.

2. Build, check and upload it:

   python -m build legacy
   python -m twine check --strict legacy/dist/*
   python -m twine upload legacy/dist/*
   

   Enter __token__ as the username and the token as the password.

3. Delete the token on PyPI.

4. Check that %pip install fabric_python_helper==0.6.0 in a notebook installs alt-fabric-m365 too, and that import fabric_python_helper shows the FutureWarning.

5. In the fabric-python-helper project's settings on PyPI, select Archive project. Archiving tells visitors the project won't be updated; every version stays installable. Don't delete the project or yank its versions, as environments pinned to them would stop working.

After publishing

Update the version in each Fabric environment that uses the package (Public libraries), and publish the environment.

Release checklist

1. Update __version__ in alt_fabric_m365/__init__.py.
2. Update this README, including Upgrading for breaking changes.
3. Build, run the tests and twine check, and test the wheel in Fabric.
4. Merge into main through a pull request, once the Tests workflow passes.
5. Publish a GitHub release tagged v<version>.
6. Confirm the Release workflow succeeded and the version is on PyPI.
7. Update and publish the Fabric environments.

Licence

BSD 3-Clause, copyright Archway Learning Trust. See LICENSE. The software is provided "as is", without warranty, and the licence excludes liability for any damage arising from its use.