How to read Excel in Sharepoint in Azure Functions and send email in Azure Functions using Python

Question

Hello all,

I want to read Excel data in SharePoint as df using Python. Currently, I am test my code in my local Workspace. But always get error. I am wondering if I need to deploy first then test it.

And do anyone know the code of reading Excel from SharePoint as df and send email using Python in Azure Function? Thank you so much.

Kind Regards,

Olivia

Answer 1

Hi @Olivia Chen,

You can leverage an Azure Function with a configured managed identity to access a SharePoint document library. To do so:

1. Enable a system-assigned managed identity for Azure Function, you can follow the steps mentioned in https://learn.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=portal%2Cdotnet#add-a-system-assigned-identity. This will create an app registration in your tenant.
2. Grant that identity the necessary permissions to access the SharePoint document library. In Entra Id, search for the app registration, select API Permissions > SharePoint > and select the permission type and roles needed. The following PowerShell script does the same thing.
3. Use the Microsoft Graph API to access the document library in your Function app. You can use the requests Python module to make the request. This uses the local function itself to retrieve it's identity token that you use to set the HTTP Authorization header in the request to SharePoint.

import requests
import pandas as pd

# Replace <tenant-id>, <site-id>, <document-library-id>, and <file-name> with your values
url = f"https://graph.microsoft.com/v1.0/sites/<tenant-id>:/sites/<site-id>:/drives/<document-library-id>/root:/{file-name}:/workbook/tables('Sheet1')/rows"

# Get an access token using the managed identity of the Azure Function
identity_endpoint = "http://169.254.169.254/metadata/identity/oauth2/token"
identity_header = {"Metadata": "true"}
identity_params = {
    "resource": "https://graph.microsoft.com/",
    "api-version": "2018-02-01"
}
identity_response = requests.get(identity_endpoint, headers=identity_header, params=identity_params)
access_token = identity_response.json()["access_token"]

# Make a GET request to the Microsoft Graph API to read the Excel file as a pandas dataframe
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}
response = requests.get(url, headers=headers)
data = response.json()["value"]
df = pd.DataFrame(data)

I did come across the following blog post, https://learningbydoing.cloud/blog/connecting-to-sharepoint-online-using-managed-identity-with-granular-access-permissions/, which someone did using a logic app. I think a logic app would be better in this case, but it depends on your decision making that led you to a function app in the first place. Some users did run into issues, https://learn.microsoft.com/en-us/answers/questions/1196213/how-to-grant-permission-to-azure-managed-identity, but still worth checking out.

Answer 2

Thanks for posting your question in the Microsoft Q&A forum.

To complete this action, follow these steps:

1. Install the necessary packages such as pandas, openpyxl, requests, and smtplib.
2. Use the requests library to authenticate with SharePoint and download the Excel file. Then, use pandas to read the Excel data into a DataFrame.
3. You can use the smtplib library to send emails in Python.
4. Make sure to include the required Python libraries (openpyxl, requests, ...) in your function app's requirements.txt file.

import requests
import smtplib
from email.mime.text import MIMEText
from openpyxl import load_workbook

site_url = "https://test.sharepoint.com/sites/mysite"
file_url = f"{site_url}/_api/web/GetFileByServerRelativePath(decodedurl='/sites/mysite/Shared Documents/myexcelfile.xlsx')/\$value"

response = requests.get(file_url, auth=("user@test.com", "password"))
with open("temp.xlsx", "wb") as f:
    f.write(response.content)
workbook = load_workbook("temp.xlsx")
worksheet = workbook.active
data = worksheet.values

smtp_server = "smtp_server"
smtp_port = 25
smtp_username = "user@test.com"
smtp_password = "password"

sender = "sender@test.com"
receiver = "receiver@test.com"
subject = "Subject"
body = "Email body"

msg = MIMEText(body)
msg['Subject'] = subject
msg['From'] = sender
msg['To'] = receiver

with smtplib.SMTP(smtp_server, smtp_port) as server:
    server.starttls()
    server.login(smtp_username, smtp_password)
    server.send_message(msg)

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful