How to read sharepoint excel with Notebooks using PySpark

The solution needs to be done by entering to azure portal, create an app, give permitions of reader in mivcrosfot graph and sharepoint.

From that app you will take tenant id, client secret, client id.

When that's done you can run the following code:

# Authentication details
tenant_id = "enter-your-tenant-id"
client_id = "enter-your-client-id"
client_secret = "enter-your-client-secret"
sharepoint_domain = "enter-your-sharepoint-domain"
site_name = "enter-your-site"
file_path = "/path/to/excel.xlsx"  # Corrected path

# Step 1: Get an access token using Microsoft Identity OAuth2.0
token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
token_data = {
    "grant_type": "client_credentials",
    "client_id": "client_id",
    "client_secret": "client_secret",
    "scope": "https://graph.microsoft.com/.default"
}

response = requests.post(token_url, data=token_data)
response.raise_for_status()  # Raise error if request fails
access_token = response.json().get("access_token")

# Debugging: Print token preview to ensure it's not empty
print(" Access Token Received:", access_token[:50], "...")

# Step 2: Retrieve the SharePoint Site ID
headers = {"Authorization": f"Bearer {access_token}"}
site_url = f"https://graph.microsoft.com/v1.0/sites/{sharepoint_domain}:/sites/{site_name}"
response = requests.get(site_url, headers=headers)
response.raise_for_status()
site_id = response.json()["id"]
print(f" Retrieved Site ID: {site_id}")

# Step 3: Retrieve the File Content from SharePoint using Graph API
file_url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drive/root:/{file_path}:/content"
response = requests.get(file_url, headers=headers)

if response.status_code != 200:
    raise Exception(f" Failed to download file: {response.text}")

print(" File successfully downloaded from SharePoint!")

# Step 4: Read the specific sheet "LH_Mapping" from the downloaded Excel file
xls = BytesIO(response.content)  # Convert the response content into an in-memory file
df = pd.read_excel(xls, sheet_name="sheet-name") # Trinitec Share Point PLUs
print(' Excel fetched succesfully!\n')