Load Microsoft 365 SharePoint List Data in Python

access_time 7 days ago visibility9 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 2 days 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

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 Azure

visibility 439
thumb_up 0
access_time 5 years ago

In my previous post ( https://kontext.tech/Blog/DotNetEssential/Archive/2014/12/31...

Entity Framework Core Code-First - Generate Covering Index with Columns Included

local_offer entity-framework local_offer asp.net core local_offer Azure local_offer C#

visibility 97
thumb_up 1
access_time 3 months ago

In SQL Server or some other relational databases, it is a very common requirement to create covering index with columns included in index pages beside the index key columns. With Entity Framework Core, you can also easily generate covering indexes using purely C# code. Scenario For ...

local_offer python

visibility 33
thumb_up 1
access_time 11 months ago

Different programming languages have different package management tools.

About column