Load Microsoft 365 SharePoint List Data in Python

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

Prerequisites

• 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:

• Android
• Angular
• ASP.NET
• iOS
• Javascript
• Node.js
• Java
• PHP
• Python
• Ruby

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(
    client_id=client_id,
    client_credential=client_secret,
    authority=authority)

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

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

if not result:
    print(
        "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"])

Access token is ***

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))

else:
    print(result.get("error"))
    print(result.get("error_description"))
    print(result.get("correlation_id"))

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"
      }
...

Summary

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. 

References

• Overview of Microsoft Graph
• SharePoint sites and content API overview