Working with Python and Microsoft’s Graph API to pull data from an Excel workbook (located in a SharePoint)
Introduction
In this article, I will walk through how to pull data from an Excel workbook (located in a SharePoint) using Python and Microsoft’s Graph API. General knowledge of Python, Azure, and APIs is nice, but not required. I will be providing code samples (from Microsoft documentation) and other articles that helped me out.
The main reason I’m writing this is because I was tasked with this exact thing at work and had to piece it together. Hopefully, this makes it easier for someone else.
We will be creating a system account that will hit an endpoint and return some data. A user will not need to log in.
What you will need
- Python 3+ installed
- Access to your Azure portal with Admin rights
- Virtual environment with venv (optional)
Create the application in Azure portal
- First, log into your Azure portal with your Admin account (if you are building this for work, you may need to get in contact with IT or someone with an Azure Admin account)
- Next, search for and select Azure Active Directory
- Under Manage, select App registrations > New registration
- Enter a name for your application, select Single Tenant, and hit Register. Copy the Application ID and Tenant ID
- Under Manage, select Certificates & secrets
- Under Client secrets, select New client secret, enter a name, and then select Add. Copy the secret value
- Under Manage, select API Permissions > Add a permission. Select Microsoft Graph
- Select Application permissions. This is the key to creating the system account (e.g. user-less account)
- Since the Excel document I need to access is located on a SharePoint site, under the Sites node, select Sites.Read.All (if you’d like provide access to a specific SharePoint rather than all, select Sites.Selected. See this article for more details)
Once you’ve completed all these steps, you should have:
- Tenant ID
- Application (client) ID
- Secret (value)
Building the Python application
For the Python application, we can recycle this example from Microsoft. Go ahead and download the repo to your local machine, unzip, and “cd” into the directory. We will be working in the “1-Call-MsGraph-WithSecret” folder.
This script will basically get a token, then use it to make authenticated API calls.
Create a virtual environment with venv (if you haven’t already):
python3 -m venv envIf you're on a Windows, activate it using:
env\Scripts\activate.batIf you're on a Mac, activate it using:
source env/bin/activateInstall the “requirements.txt”:
pip install -r requirements.txtThen, open “parameters.json” in your favorite code editor and update the JSON values to your own values for authority, client_id, and secret. For the endpoint, use the following template:
https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items/{item-id}/driveitem/workbook/worksheets/{id | name}/range(address=’G2:G38‘)
You may need to do some digging to get all of your specific values, so a good resource is the Graph Explorer from Microsoft.
Running the Python application
Once you’ve got everything together, you can run the script with the following command:
py confidential_client_secret_sample.py parameters.json