Load Microsoft 365 SharePoint List Data in Python

access_time 3 months ago visibility414 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

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:

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

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

info Last modified by Raymond at 3 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

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer pyspark local_offer spark-2-x local_offer python local_offer spark-dataframe

visibility 4447
thumb_up 0
access_time 12 months ago

This articles show you how to convert a Python dictionary list to a Spark DataFrame. The code snippets runs on Spark 2.x environments. The input data (dictionary list looks like the following): data = [{"Category": 'Category A', 'ItemID': 1, 'Amount': 12.40}, {"Category": 'Category B' ...

local_offer python local_offer SQL Server local_offer python-database

visibility 2914
thumb_up 0
access_time 10 months ago

Python JayDeBeApi module allows you to connect from Python to databases using Java JDBC drivers.

local_offer linux local_offer Azure

visibility 464
thumb_up 0
access_time 6 years ago

Creating virtual machine in windows azure is quite straightforward. After logged into the management portal ( www.azure.com ), you can create one VM within 4 steps. In the tab 'Virtual Machines', click the link button 'CREATE A VIRTUAL MACHINE' to start. Click 'From Gallery' in the popup ...

About column