CRUD Operations on a SharePoint List using Python


By:   |   Updated: 2021-10-28   |   Comments (1)   |   Related: More > Python


Problem

SharePoint lists, places to store organized information but not files, are a powerful tool to gather information, track status, and organize teams (Thorpe, 2021). SharePoint Lists are also simple to set up and manage. As a result, business users can manage and consume data by themselves. The SharePoint lists and Power Apps have a tight integration. However, they may still need IT professionals to help them to import raw data into SharePoint lists. Several tools and methods can upload data to a SharePoint list. As IT professionals, it is our nature to automate the data operations using a computer program. Python, described as a glue language, is the right candidate to implement the program (Zhou, 2020). IT professionals may ask recommendations on which Python library provides, create, read, update, and delete (CRUD) operations on a SharePoint list.

Solution

We recommend the third-party Python package "SharePlum," which provides an easy way to work with the SharePoint list and allows programmers to write clean Python code (Rollins, 2020). We create a fictitious project to demonstrate the CRUD operations on a SharePoint list. Edgewood Solutions maintain the SQL Server community website MSSQLTips.com. Talented IT professionals worldwide write articles to share their knowledge. We can find these authors through this hyperlink: https://www.mssqltips.com/sql-server-mssqltips-authors. The CTO at Edgewood Solutions wants to use a SharePoint list to manage author information and keep the information updated. The project uses a Python program to extract information from the web page and load data into the SharePoint list.

This project needs a SharePoint Online environment to perform the SharePoint list operations. Suppose some IT professionals do not have an environment for development. In that case, the "Microsoft 365 Developer Program" provides them a free SharePoint Online environment (Zhou, 2021). We start with a brief introduction to the SharePoint list. The introduction explores how we manually create, edit, and delete list items. Next, we use a Python program to automate the manual process. The Python package "SharePlum" provides functions to perform the CRUD operations. We cover these functions with sample code. We also use the Beautiful Soup library (Crummy, 2020) to pull the author information data on the MSSQLTips site. In the end, we provide a Python program that can regularly update the SharePoint list according to the information on the site. Therefore, the article comprises two parts, and the audience who already know the SharePoint list can skip the first part:

1 – Introducing the SharePoint List

2 – Performing CRUD Operations on the SharePoint List

We tested all the Python scripts used in this article with Microsoft Visual Studio Community 2022 Preview 4.1 and Python 3.9 (64-bit) on Windows 10 Home 10.0 <X64>. In addition, the Python web scraper extracts the author information from the website MSSQLTips.com. We use the SharePoint Online environment provided by the "Microsoft 365 Developer Program during development and testing." The web browser "Opera" is used to access the SharePoint list page. After the program loads all data to the SharePoint list, the list page should look like Figure 1.

 mssqltips authors list output

Figure 1 The SharePoint List Page

1 – Introducing the SharePoint List

We can often break down SharePoint content into three categories: pages, lists, and libraries. A SharePoint list is a collection of data used to organize information in a tabular structure. A list has three components: item, field, and view (Rathore, 2017). Many features provided by the SharePoint list help store data, track information, organize work and manage workflows. Some core features include versioning, workflows, filtering, search, and check-out/check-in (Helpmecoder, 2020). It is noteworthy that the SharePoint list landing page may have two types of interfaces: Classic and Modern. The classic interface looks like the desktop office application, while the modern one looks like OneDrive for business and other Office 365 apps (Concilla, 2020). The screenshots in this article illustrate the modern interface.

1.1 Create a SharePoint List

A SharePoint site provides several ways to create a SharePoint list. For example, we can create a SharePoint list from a blank, an Excel spreadsheet, or an existing list. SharePoint also provides some list templates. When selecting a template, we create a new list with all the same columns in the template. Since we want to create a list to manage MSSQLTips author information, we start from scratch and add custom columns by ourselves. The list should include these columns: "Name," "Experience," "Author Since," "Tip Contributions," and "Status." The columns "Name" and "Experience" contain text data, while the columns "Author Since" and "Tip Contributions" contain integers. The "Status" column is different from others, and it only takes two values, either "Active" or "Inactive."

Suppose we have already created a SharePoint communication site named "MSSQLTips." In that case, the home page should look like Figure 2. When using different site templates, the home page may have a different appearance. The home page should have the "+ New" button. We use the following step-by-step procedure to create a SharePoint list.

create sharepoint list

Figure 2 The Landing Page of the SharePoint Communication Site

1.2.1 Click on the "New" button to expand the drop-down menu, as shown in Figure 3.

create sharepoint list

Figure 3 The Drop-down Menu When Clicking on the "New" Button

1.2.2 In the drop-down menu, select the "List" item to bring up a pop-up window, as shown in Figure 4.

create sharepoint list

Figure 4 Select an Option to Create a List

1.2.3 In the pop-up window, click on the "Blank list" button to bring up a form. Next, we fill out the form, as shown in Figure 5.

create sharepoint list

Figure 5 The Form to Name the List

1.2.4 Click on the "Create" button to create the list. The landing page of the new SharePoint list should look like Figure 6.

create sharepoint list

Figure 6 The Landing Page of the New SharePoint List

When we create a SharePoint list, by default, the list includes a column named "Title." However, our project does not need this column. The SharePoint list does not allow us to change this column’s data type or delete the column (Swain, 2021). Zelfond concludes with three ways to use the "Title" column, and he also pointed out, "Do not hide a Title field from the SharePoint lists!" (Zelfond, 2018). Wade also made a comparison between the repurposing title and hiding the title (Wade, 2019). If we hide the "Title" column, some functionalities are no longer available to us. Therefore, we decide to rename the "Title" column to "Name."

1.2.5 On the top-right corner, click on the "Settings" icon to open the setting panel, as shown in Figure 7.

create sharepoint list

Figure 7 The Settings Panel

1.2.6 On the setting panel, click on the "List settings" link to land on the settings page, as shown in Figure 8.

create sharepoint list

Figure 8 The List Settings

1.2.7 In the "Columns" section, click on the "Title" link to edit the "Title" column. First, we change the column name to "Name," as shown in Figure 9. Next, we click on the "OK" button to complete the name change.

create sharepoint list

Figure 9 Change the Column Name

1.2.8 On the left panel, click on the "MSSQLTips Authors" menu item to land on the list page. We observe that the first column is "Name." Click on the "Add column" button to expand the drop-down list, as shown in Figure 10.

create sharepoint list

Figure 10 Select a Column Type

1.2.9 In the drop-down list, select the option "Multiple lines of text." Next, a form to create a column appears. First, we fill out the form according to Figure 11. Then, click on the "Save" button to create the "Experience" column.

create sharepoint list

Figure 11 The Form to Create a Column

1.2.10 We use the same process in Step 1.2.9 to create columns "Author Since" and "Tip Contributions." These two columns should select "Number" as their data type. The numerical value does not have any decimal place and does not use thousands separator. We fill out the form according to Figure 12.

create sharepoint list

Figure 12 The Form to Create an Integer Column

1.2.11 Click on the "Add column" button and select "Choice" from the drop-down list. A form to create a column appears. We fill out the form according to Figure 13. Next, click on the "Save" button to create the "Status" column.

create sharepoint list

Figure 13 Create a Categorical Column

At the end of this step, we should have an empty list, and its landing page should look like Figure 14.

create sharepoint list

Figure 14 The Empty List

1.2 Manage List Items

We see two buttons on the list landing page: "+ New" and "Edit in grid view." These two buttons give us two ways to manage a list. When using the button "Edit in grid view," we can manage multiple items. However, for simplicity, we manage a single list item one at a time. We use the website "fakenamegenerator.com" to generate a fake author name "Neil D. Lacasse" and use the website "lipsum.com" to generate some dummy text. We assume the author started in 2018 and has contributed 32 tips. Table 1 shows the sample data.

Name Neil D. Lacasse
Experience Curabitur quam massa, malesuada nec iaculis sed, dictum vitae nunc. Etiam enim nulla, condimentum a est sed, pellentesque rhoncus enim. Nunc bibendum mollis justo, ut euismod sapien elementum eget. Suspendisse maximus, mauris et volutpat imperdiet, nibh neque semper erat, nec suscipit ipsum justo a erat. Fusce in ultricies est. Nullam eget mattis massa, non rhoncus eros. Sed dignissim nisi id posuere placerat. Aliquam erat volutpat.
Author Since 2018
Tip Contributions 32

Table 1 Same Data for a List Item

1.2.1 Add a list item

On the SharePoint list page, click on the "+ New" button to open the new item form. We then enter the information for the list item according to Figure 15.

add list item to sharepoint list

Figure 15 The Form to Add a New List Item

Click on the "Save" button to create the new item. We then land on the SharePoint list page, as shown in Figure 16. Since we did not enter the "Status" column value, the field used the default value.

sharepoint list

Figure 16 The SharePoint List Page with One List Item

1.2.2 Edit a list item

When we move the mouse over the list item, the three dots icon appears beside the author’s name. Click on the icon to open the drop-down menu, as shown in Figure 17.

edit list item to sharepoint list

Figure 17 The Drop-Down Menu to Manage a List Item

We select the "Edit" menu item to bring up the list item form. The form allows us to modify the values of fields. For example, we change the value of the "Status" field to "Inactive," as shown in Figure 18.

edit list item to sharepoint list

Figure 18 Modify the Value of a Field

Click on the "Save" button to apply the change. Figure 19 illustrates the modified list item.

sharepoint list

Figure 19 The SharePoint List Page with One Modified List Item

1.2.3 Delete a List Item

Figure 17 illustrates the drop-down menu used to manage the selected list item. We can select the "Delete" menu item to delete the selected list item. Since deleting an item may be permanent and we might not be able to recover it, a confirmation window shows up, like Figure 20. By the way, depending on the site configuration, we may be able to recover the deleted items in the Recycle Bin.

delete list item to sharepoint list

Figure 20 The Confirmation Window for Deletion

Click on the "Delete" button. We delete the list item, and the SharePoint list page should look like Figure 14, in which there is no list item.

2 – Performing CRUD Operations on the SharePoint List

We created a SharePoint list named "MSSQLTips Author" on the SharePoint communication site "MSSQLTips." Then, we manually added a list item to the list. We also practiced editing and deleting the item. The manual process is not efficient when we manage a large number of list items. We created that project that required us to automatically import all authors on the MSSQLTips site to the SharePoint list and update the list. To use a Python program to implement the project requirements, we create a Python application project using Visual 2022 Preview. We add a virtual environment to this project using this requirement.txt:

beautifulsoup4==4.10.0
certifi==2021.5.30
cffi==1.14.6
charset-normalizer==2.0.6
cryptography==35.0.0
idna==3.2
lxml==4.6.3
ntlm-auth==1.5.0
pip==21.2.3
pycparser==2.20
requests==2.26.0
requests-ntlm==1.1.0
requests-toolbelt==0.9.1
setuptools==57.4.0
SharePlum==0.5.1
soupsieve==2.2.1
urllib3==1.26.7

To invoke the functions defined in these libraries, we use the following code snippet to import the modules to the Python program. The code snippet also defines some constants. For the sake of simplicity, Python scripts in subsequent sections do not include this code snippet. However, we must include it for testing user-defined functions.

import sys 
import requests 
from bs4 import BeautifulSoup 
from shareplum.site import Version 
from shareplum import Site, Office365 
  
AUTHOR_PAGE_URL = 'https://www.mssqltips.com/sql-server-mssqltips-authors/' 
SHAREPOINT_URL = 'https://trainingcenter2021.sharepoint.com' 
SHAREPOINT_SITE = 'https://trainingcenter2021.sharepoint.com/sites/MSSQLTips' 
SHAREPOINT_LIST = 'MSSQLTips Authors' 
USERNAME = '******' 
PASSWORD = '******' 

2.1 Connect to the SharePoint List

The "SharePlum" package uses the Office365 class to obtain a login token from Microsoft’s login servers. It then sends the cookie to the SharePoint site for authentication. If the authentication process is successful, we can obtain a SharePoint Site instance. However, we receive a "None" object if there is a problem with authenticating the user. The following Python user-defined function implements this authentication process:

def authenticate(sp_url, sp_site, user_name, password): 
    """ 
    Takes a SharePoint url, site url, username and password to access the SharePoint site. 
    Returns a SharePoint Site instance if passing the authentication, returns None otherwise. 
    """ 
    site = None 
    try: 
        authcookie = Office365(SHAREPOINT_URL, username=USERNAME, password=PASSWORD).GetCookies() 
        site = Site(SHAREPOINT_SITE, version=Version.v365, authcookie=authcookie) 
    except: 
        # We should log the specific type of error occurred. 
        print('Failed to connect to SP site: {}'.format(sys.exc_info()[1])) 
    return site 
  
# Test the function 
sp_site = authenticate(SHAREPOINT_URL,SHAREPOINT_SITE,USERNAME,PASSWORD) 

After obtaining a valid SharePoint Site instance, we invoke this instance's "List" method to create an instance of the SharePoint list. The List instance represents a SharePoint list web service. We can work with SharePoint lists, content types, list items, and files through the web service. The following function creates a connection to the web service:

def get_sp_list(sp_site, sp_list_name): 
    """ 
    Takes a SharePoint Site instance and invoke the "List" method of the instance. 
    Returns a SharePoint List instance. 
    """ 
    sp_list = None 
    try: 
        sp_list = sp_site.List(sp_list_name) 
    except: 
        # We should log the specific type of error occurred. 
        print('Failed to connect to SP list: {}'.format(sys.exc_info()[1])) 
    return sp_list 
  
# Test the function 
sp_list = get_sp_list(sp_site, SHAREPOINT_LIST) 

2.2 Create List Items

We can upload data to the SharePoint list through the List instance. The data should be in a list of Python dictionaries, as demonstrated in the following code block. The keys of the dictionaries are the column names in the SharePoint list. The value types should match in the two places. If a key is missing in a dictionary, the corresponding column uses the default value.

def create_fake_data(): 
    """ 
    Returns a list of dictionaries for testing. 
    """ 
    emp1 = { 
        'Name':'Neil D. Lacasse', 
        'Experience':""" 
Curabitur quam massa, malesuada nec iaculis sed, dictum vitae nunc. Etiam enim nulla, condimentum a est sed, pellentesque rhoncus enim. 
Nunc bibendum mollis justo, ut euismod sapien elementum eget. Suspendisse maximus, mauris et volutpat imperdiet, nibh neque semper erat, nec suscipit ipsum justo a erat. 
Fusce in ultricies est. Nullam eget mattis massa, non rhoncus eros. Sed dignissim nisi id posuere placerat. Aliquam erat volutpat. 
        """, 
        'Author Since':2018, 
        'Tip Contributions':32 
        } 
    emp2 = { 
        'Name':'Ray A. Fix', 
        'Experience':'Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.', 
        'Author Since':2020, 
        'Tip Contributions':17 
        } 
    emp3 = { 
        'Name':'Caroline L. Tobin', 
        'Experience':'Nullam dignissim porttitor leo, fringilla elementum diam facilisis sed.', 
        'Author Since':2020, 
        'Tip Contributions':16 
        } 
    emp4 = { 
        'Name':'Dante M. Bradshaw', 
        'Experience':'Vestibulum efficitur nisl non lectus aliquam interdum.', 
        'Author Since':2021, 
        'Tip Contributions':10 
        } 
    emp5 = { 
        'Name':'Jesse M. Pope', 
        'Experience':'Sed finibus risus pharetra tellus tempus malesuada.', 
        'Author Since':2021, 
        'Tip Contributions':12 
        } 
    return [emp1, emp2,emp3,emp4,emp5] 
  
# Test the function 
web_list_items = create_fake_data() 

The SharePoint List instance provides a method "UpdateListItems()." We can use this method to create, update and delete SharePoint list items. When we pass the value "New" to the function, the function adds the data to the SharePoint list. We write a user-defined function, shown as follows, to upload data to the SharePoint list. The user-defined function takes a list of dictionaries. If the process works successfully, we can immediately see the list items on the SharePoint list page, looking like Figure 21.

def create_list_items(sp_list, new_items): 
    """ 
    Takes a SharePoint List instance and a list of disctoraries. 
    The keys in the disctorary should match the list column. 
    """ 
    if len(new_items) > 0: 
        try: 
            sp_list.UpdateListItems(data=new_items, kind='New') 
        except: 
            # We should log the specific type of error occurred. 
            print('Failed to upload new list items: {}'.format(sys.exc_info()[1])) 
  
# Test the function 
create_list_items(sp_list, web_list_items) 
sharepoint list

Figure 21 The SharePoint List Page with Five new Items

2.3 Read List Items

To keep the SharePoint list items updated, we need to download these items first. We then compare the list items to the data extracted from the web page. Based on the comparison, we determine how to operate the SharePoint list. For example, if the web page has new authors, we should add data to the SharePoint list. When the MSSQLTips site updates the author information, we should update the list items. We should remove authors from the list if the authors are not on the site. We write the following user-defined function to read list items from a SharePoint list.

def download_list_items(sp_list, view_name=None, fields=None, query=None, row_limit=0): 
    """ 
    Takes a SharePoint List instance, view_name, fields, query, and row_limit. 
    The rowlimit defaulted to 0 (unlimited) 
    Returns a list of dictionaries if the call succeeds; return a None object otherwise. 
    """ 
    sp_list_items = None 
    try: 
        sp_list_items = sp_list.GetListItems(view_name=view_name, fields=fields, query=query, row_limit=row_limit) 
    except: 
        # We should log the specific type of error occurred. 
        print('Failed to download list items {}'.format(sys.exc_info()[1])) 
        raise SystemExit('Failed to download list items {}'.format(sys.exc_info()[1])) 
    return sp_list_items 
  
# Test the function 
sp_items_all = download_list_items(sp_list) 

2.3.1 Read All Data

When we pass only a SharePoint List instance to the user-defined function as we did in the previous test, the function returns all columns, including metadata. Table 2 presents a complete list of columns. The value in the "Name" column is unexpected. We also see that the two numeric columns have one decimal place. Unless we are using the "ID" column for updating and deleting, we do not use other metadata in this project.

Name '19;#19_.000'
Experience 'Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.'
Author Since 2020.0
Tip Contributions 17.0
Status 'Active'
Approval Status '0'
Level '1'
ID '19'
Unique Id '19;#{BB5A18D6-B19A-470D-A2F0-BFFDA206C323}'
owshiddenversion '1'
Item Type '19;#0'
Created datetime.datetime(2021, 10, 2, 10, 15, 31)
Effective Permissions Mask '0x7ffffffffffbffff'
Modified datetime.datetime(2021, 10, 2, 10, 15, 31)
ScopeId '19;#{BF5D62A7-6817-4022-850B-691F45365697}'
URL Path '19;#sites/MSSQLTips/Lists/MSSQLTips Authors/19_.000'
Property Bag '19;#'

Table 2 A SharePoint List Item Including Metadata

2.3.2 Read Data from a View

SharePoint uses views to organize list items. We can use features like filtering and sorting to create custom views to show data in different ways. The default view is "All Items." When we pass a view name to the function, as shown in the following line of code, we obtain all list items shown in the view. For example, Table 3 shows an item downloaded from the default view. The "Name" column has meaningful values. Nevertheless, we need the "ID" column for update and deletion.

sp_items_view = download_list_items(sp_list, view_name="All Items") 
Name 'Ray A. Fix'
Experience 'Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.'
Author Since 2020.0
Tip Contributions 17.0
Status 'Active'

Table 3 An Item Download from the Default View

2.3.3 Read Selected Fields

We need the "ID" column for updating and deleting. However, the default view does not include the column. It is not convenient to ask the SharePoint users to add the "ID" column to the view or create a new view for us. In some cases, we may not need all columns in a view. To download data in desired columns, we can pass column names to the function, as shown in the following line of code:

sp_items_fields = download_list_items(sp_list,view_name="All Items",fields=["ID","Name"]) 
# An item should look like {'ID': '19', 'Name': 'Ray A. Fix'} 

2.3.4 Read Filtered Data

Sometimes, we may not need a complete list of items. Like a "Where" clause in SQL query, we also can add a "Where" clause to the web service call. We can construct a "Where" clause according to the SharePlum documents and pass it to the user-defined function, as shown in the following code:

query = {'Where': [('Eq', "Name", "Neil D. Lacasse")]} 
sp_items_filter_Neil = download_list_items(sp_list, view_name="All Items", query=query) 

The function only returns Neil's information. Besides the comparison operator "Eq," the SharePlum documents provide many other operators shown in Table 4 (Rollins, 2020).

Operator Name
Eq Equals
Neq Not Equal To
Geq Greater Than or Equal To
Gt Greater Than
Leq Less Than or Equal To
Lt Less Than
IsNull Value Is Null
IsNotNull Value is not Null
BeginsWith Begins With Text
Contains Contains Text

Table 4 SharePlum Comparison Operators

We also can use "AND," "OR," or both to construct multiple conditions. For example, we want to download information for authors whose first name is "Neil," or authors who started in 2021 and have contributed more than ten tips. The following filter can download the author information that meets our requirements:

query =  {"Where": ["Or", ("BeginsWith", "Name", "Neil "), 
                    "And", ("Eq", "Author Since", "2021"), 
                           ("Gt", "Tip Contributions", "10")]} 
sp_items_filter_multi = download_list_items(sp_list, view_name="All Items", query=query) 
# Returns two authors 

2.4 Update List Items

We used the method "UpdateListItems()" to create SharePoint list items. We also can invoke the function to update list items. When performing the update operations, we should include the "ID" column in the source data. We write a user-defined function, shown as follows, to update a SharePoint list item. To test the function, we first download an item from the SharePoint list. We then change the data. Next, we invoke the user-defined function to update the SharePoint list. We can immediately see changes on the SharePoint list page, as shown in Figure 22.

def update_list_items(sp_list, update_items): 
    """ 
    Takes a SharePoint List instance and data to update the SharePoint list. 
    The data should have the "ID" column. 
    """ 
    if len(update_items) > 0: 
        try: 
            sp_list.UpdateListItems(data=update_items, kind='Update') 
        except: 
            # We should log the specific type of error occurred. 
            print('Failed to update the SharePoint list itemst: {}'.format(sys.exc_info()[1])) 
  
# Test the function 
query = {'Where': [('Eq', 'Name', 'Neil D. Lacasse')]} 
sp_items_filter_Neil = download_list_items(sp_list, fields=['ID','Tip Contributions'], query=query) 
sp_items_filter_Neil[0]['Tip Contributions'] = 48 
update_list_items(sp_list, sp_items_filter_Neil) 
sharepoint list

Figure 22 The Updated List Item

2.5 Delete List Items

We can use the method "UpdateListItems()" to delete list items as well. However, we should pass to the function a list of IDs rather than a list of dictionaries. We write a user-defined function, shown as the follows, to delete a SharePoint list item. To test the function, we first download an item from the SharePoint list. We then get the value of the "ID" column and construct a list with one element. Next, we invoke the user-defined function to delete the item from the SharePoint list. We can immediately see changes on the SharePoint list page, as shown in Figure 23.

def detele_list_items(sp_list, item_id_list): 
    """ 
    Takes a SharePoint List instance and a list of ID values. 
    """ 
    if len(item_id_list) > 0: 
        try: 
            sp_list.UpdateListItems(data=item_id_list, kind='Delete') 
        except: 
            # We should log the specific type of error occurred. 
            print('Failed to delete list items: {}'.format(sys.exc_info()[1])) 
  
# Test the function 
query = {'Where': [('Eq', 'Name', 'Neil D. Lacasse')]} 
sp_items_filter_Neil = download_list_items(sp_list, fields=['ID'], query=query) 
detele_list_items(sp_list, [sp_items_filter_Neil[0]['ID']])			
sharepoint list

Figure 23 The SharePoint List after One Item Was Removed

3 – The Complete Source Code

We write a Python program to implement the project requirements. The Python program extracts author information, such as name, experience, author since, and tip contribution from the MSSQLTips site. The program also downloads all list items from the SharePoint list and makes a comparison. If new authors join the MSSQLTips team, we create new list items for these authors. We update list items if there is any change. We delete the list items when the site removes authors. We covered all core user-defined functions. To make the process work, we also write some helper functions. By and large, the program includes these eight steps:

  1. Read the web page content.
  2. Use an instance of the BeautifulSoup class to parse the web content.
  3. Connect to the SharePoint list.
  4. Download all SharePoint list items.
  5. Compare the data extracted from the web page to the data downloaded from the SharePoint list.
  6. Create new authors on the SharePoint list if applicable.
  7. Update authors if there is any change on the web page.
  8. Delete authors if the site removed them.

Click here for the complete code. To run the program, we should assign correct values to these constant variables: SHAREPOINT_URL, SHAREPOINT_SITE, SHAREPOINT_LIST, USERNAME, and PASSWORD.

Summary

We can consider a SharePoint list to be a collection of data, like an Excel spreadsheet. First, the article briefly covered creating a SharePoint list and managing items on the list through a SharePoint site. The steps demonstrated that the SharePoint list is convenient to set up and easy to use. Then, to demonstrate how to manage the list items automatically, we created a fictitious project. We used a SharePoint list to manage MSSQLTips author information and keep the information updated.

Next, we showed how to use the Python package "SharePlum" to connect the SharePoint list and performed CRUD operations. The article provided user-defined functions to implement each operation. When creating SharePoint list items, we should arrange source data into a list of dictionaries. The keys of the dictionaries should correspond to the column names in the SharePoint list, and the values should contain the actual data. We explored several ways to download desired data from the SharePoint list. When updating the SharePoint list items, we should include the "ID" values in the list of dictionaries. We should provide a list of "ID" values to delete corresponding list items.

In the end, the article provided a complete source code. The source code implemented eight steps that automatically perform CRUD operations based on the author information on the MSSQLTips site. We used the BeautifulSoup package to parse the web content. The source code also included some helper functions that found new authors, updated authors, and deleted authors. Thus, when running the Python script regularly, we can keep the SharePoint list updated.

Reference

Concilla, S. S. (2020). 2020-04-10 - SharePoint Online - List Basics. https://youtu.be/VX40l39UdmY.

Crummy. (2020). Beautiful Soup. https://www.crummy.com/software/BeautifulSoup.

Helpmecoder. (2020). Microsoft Sharepoint Lists - Complete Beginner Tutorial. https://youtu.be/GFR-BAUFoVo.

Hristov, H. (2021). What is Microsoft Power Apps. https://www.mssqltips.com/sqlservertip/6774/what-is-power-apps/.

Rathore, B. (2017). What is a SharePoint list (Create a List in SharePoint Online). https://www.spguides.com/sharepoint-list.

Rollins, J. (2020). SharePlum 0.5.1, https://pypi.org/project/SharePlum.

Schloemmer, E. (2020). The power of SharePoint lists: 5 reasons why they are better than spreadsheets. https://www.contentformula.com/blog/sharepoint-lists-5-reasons-why-they-are-better-than-spreadsheets.

Swain, R. (2021). SharePoint list delete title column or SharePoint list title column remove. https://www.enjoysharepoint.com/remove-sharepoint-online-title-column.

Thorpe, C. (2021). Using SharePoint Online to Manage your Business: SharePoint Lists. https://www.amtevolve.com/using-sharepoint-online-to-manage-your-business-sharepoint-lists.

Wade, M. (2019). Removing the Title field from a SharePoint list. https://medium.com/jumpto365/removing-the-title-field-from-a-sharepoint-list-d58283fbbe67.

Zelfond, G. (2018). SharePoint list delete title column or SharePoint list title column remove 3 ways to use the Title field in a SharePoint list or library. https://sharepointmaven.com/3-ways-to-use-the-title-field-in-sharepoint-list-or-library.

Zhou, N. (2020). Learning Python in Visual Studio 2019. https://www.mssqltips.com/sqlservertip/6442/learning-python-in-visual-studio-2019.

Zhou, N. (2021). File Handling in a SharePoint Document Library Using Python. https://www.mssqltips.com/sqlservertip/6799/file-handling-sharepoint-document-library-python.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Nai Biao Zhou Nai Biao Zhou is a Senior Software Developer with 20+ years of experience in software development, specializing in Data Warehousing, Business Intelligence, Data Mining and solution architecture design.

View all my tips


Article Last Updated: 2021-10-28

Comments For This Article




Tuesday, November 2, 2021 - 10:55:40 AM - Rinaldo Paulino de Souza Back To Top (89396)
Excelent post!
Congratulations... and thank you for share!


download














get free sql tips
agree to terms