Load Microsoft 365 SharePoint List Data in Python
access_time 6 months ago languageEnglish

Load Microsoft 365 SharePoint List Data in Python

visibility 1,372 comment 0

A Microsoft SharePoint list is a collection of data can be shared with team members or people who you give access to. It is commonly used to capture commonly maintained master data from manual inputs. This article summarizes steps to create a SharePoint list and then load the data in the list via Microsoft Graph Python SDK (msal).

*Python logo is a registered trademark of PSF; Microsoft Graph image from https://docs.microsoft.com/en-us/graph/images/microsoft-graph.png


  • A Microsoft 365 or Office 365 SharePoint site that you have access to. 
  • Python 3.x environment.

I am using Microsoft 365 SharePoint for this tutorial. 

Create a list

1) Go to a SharePoint site that you have access. 

2) Click '+ New' button and select List from the dropdown list:

3) Input details for the list:

  • Name: TestList
  • Description: Teat SharePoint List

4) Click button 'Create' button.

5) Navigate to the page of the created list:

6) Click 'Add column' to add a few columns as the following screenshot shows:

*ID column is a system column. 

7) Click '+ New' button to add a few sample records to the list.

8) The list now looks like the following screenshot:

There are 3 sample records added. 

Now let's use Microsoft Graph APIs to load the created SharePoint list.

About Microsoft Graph API

Microsoft Graph is the gateway to data and intelligence on Microsoft 365. It provides a unified programmability model and APIs to access data on Microsoft 365, Windows 10, and Enterprise Mobility + Security. For this tutorial, we will use the authentication and SharePoint related APIs.

Microsoft Graph SDKs are currently available for the following languages and platforms:

Install Python packages

Install the following Python packages which are required for complete this tutorial.

1) Run the command below to install Microsoft Authentication Library (MSAL) for Python:

pip install msal

Register an application on Azure

Go to Azure portal to register an application where client ID and secret can be generated. Refer to Microsoft Docs page for more details: Register an application with the Microsoft identity platform.

The main steps are:

1) Register an application.

2) Add a client secret:

3) Setup read user detail permission:

Add a permission -> Microsoft Graph -> Application Permissions -> User.Read.All:

4) Setup API permission for read SharePoint:

Add a permission -> Microsoft Graph -> Application Permissions -> Sites.Read.All:

Retrieve access token

Run the following code to authenticate and to retrieve access token:

import msal
import json
import logging

import requests

client_id = '***'
client_secret = '***'
tenant_id = '***'
authority = f"https://login.microsoftonline.com/{tenant_id}"

app = msal.ConfidentialClientApplication(

scopes = ["https://graph.microsoft.com/.default"]

result = None
result = app.acquire_token_silent(scopes, account=None)

if not result:
"No suitable token exists in cache. Let's get a new one from Azure Active Directory.") result = app.acquire_token_for_client(scopes=scopes) if "access_token" in result: print("Access token is " + result["access_token"])
Ensure you can see the following output:
Access token is ***
warning Remember to change client_id, client_secret, tenant_id accordingly. 

Load data from SharePoint list

With access token, we can now load SharePoint list data by calling the corresponded Graph API.

List all sites

Call endpoint https://graph.microsoft.com/v1.0/sites to list all the SharePoint site:

if "access_token" in result:
    # Calling graph using the access token
    graph_data = requests.get('https://graph.microsoft.com/v1.0/sites/',
                              headers={'Authorization': 'Bearer ' + result['access_token']},).json()
    print("Graph API call result: %s" % json.dumps(graph_data, indent=2))


The output looks like the following:

      "createdDateTime": "2020-09-14T10:22:32Z",
      "id": "******",
      "lastModifiedDateTime": "2020-09-19T13:24:07.6340084Z",
      "name": "Kontext",
      "displayName": "Kontext",
      "webUrl": "https://***.sharepoint.com/sites/***",
      "sharepointIds": {
        "siteId": "***",
        "siteUrl": "https://***.sharepoint.com/sites/***",
        "tenantId": "***",
        "webId": "***"
      "siteCollection": {
        "hostname": "***.sharepoint.com"
      "root": {}

Site ID will be used in the following request.

Retrieve all the SharePoint list under a site

With site_id, we can retrieve all the lists under that site:

if "access_token" in result:
    # List all the SharePoint list
    site_id = '***'
    graph_data = requests.get(f'https://graph.microsoft.com/v1.0/sites/{site_id}/lists',
                              headers={'Authorization': 'Bearer ' + result['access_token']},).json()
    print("SharePoint list: %s" % json.dumps(graph_data, indent=2))

Sample output:

SharePoint list: {
  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('***')/lists",
  "value": [
      "@odata.etag": "\"***,17\"",
      "createdDateTime": "2020-09-14T11:17:44Z",
      "description": "Test SharePoint List",
      "eTag": "\"***,17\"",
      "id": "***",
      "lastModifiedDateTime": "2020-09-19T00:46:16Z",
      "name": "TestList",
      "webUrl": "https://***.sharepoint.com/sites/***/Lists/TestList",
      "displayName": "TestList",
      "createdBy": {
        "user": {
          "email": "***@kontext.tech",
          "id": "***",
          "displayName": "Kontext"
      "lastModifiedBy": {
        "user": {
          "email": "***@kontext.tech",
          "id": "***",
          "displayName": "Kontext"
      "parentReference": {
        "siteId": "***.sharepoint.com,***,***"
      "list": {
        "contentTypesEnabled": false,
        "hidden": false,
        "template": "genericList"

Load list items 

Now we can use site_id and id of the SharePoint List to retrieve all the items in that list.

The following are some endpoints that can be used to retrieve list items:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items
GET https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?expand=fields
GET https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?expand=fields(select=Column1,Column2)

Code snippet:

if "access_token" in result:

    # Load all list items
    site_id = '***'
    list_id = '***'
    endpoint = f'https://graph.microsoft.com/v1.0/sites/{site_id}/lists/{list_id}/items?expand=fields(select=id,TestBoolColumn,NumberColumn,TestDateColumn,TestextColumn)'
    graph_data = requests.get(endpoint,
                              headers={'Authorization': 'Bearer ' + result['access_token']},).json()
    print("SharePoint list items: %s" % json.dumps(graph_data, indent=2))

Sample output:

"fields": {
        "@odata.etag": "\"***,1\"",
        "TestBoolColumn": true,
        "TestDateColumn": "2020-09-14T07:00:00Z",
        "NumberColumn": 300.0,
        "id": "3"


This tutorial provides the examples to load SharePoint data. You can use similar approach to load all the other data incl. Emails, OneDrive documents, Contacts, Teams, etc. 


info Last modified by Raymond 6 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to contribute on Kontext to help others?

Learn more

More from Kontext