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

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=("******@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 = "******@test.com"
smtp_password = "password"

sender = "******@test.com"
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