Automating SharePoint List Updates with PnP PowerShell and Excel Integration

In this post, we will walk through a PowerShell script that automates updating SharePoint list items using PnP PowerShell commands, including reading data from an Excel file to add new items to a secondary SharePoint list. This is useful for syncing or updating information across SharePoint lists, saving time and reducing manual effort.

Prerequisites

Before diving into the script, ensure you have the following:

1. PnP PowerShell Module: You can install this via PowerShell by running Install-Module -Name "PnP.PowerShell" -Force -AllowClobber.
2. Access to SharePoint Online Site: Ensure you have the necessary permissions to read and update SharePoint lists.
3. Excel File: An Excel file with the data you want to import into the SharePoint list. The script uses the Import-Excel command, which requires the ImportExcel PowerShell module. You can install it using Install-Module -Name ImportExcel.

Script Overview

This script connects to a SharePoint Online site, retrieves items from a Locations List, and for each item, checks if certain conditions are met (in this case, skipping items with specific IDs). If the conditions are not met, it reads an Excel file and adds new items to another SharePoint list called Shifts List.

Here’s a breakdown of the script:

$SiteUrl = "https://[Domain]/sites/SiteCollectionName"

$ListName = "Locations List"

# Connect to the SharePoint site

Connect-PnPOnline -Url $SiteUrl -UseWebLogin 

# Retrieve items from the Locations List

$ListItems = Get-PnPListItem -List $ListName -Fields "Title" -PageSize 2000

# Loop through each item in the Locations List

foreach($ListItem in $ListItems)

{

    Write-Host "ID:" $ListItem["ID"]

    Write-Host "Location Name:" $ListItem["Title"]

    

    # Skip processing for specific items

    if ($ListItem["ID"] -eq "15" -or $ListItem["ID"] -eq "8") {

        Write-Host "ID matches, skipping execution."

        #return  # This would skip the rest of the script if uncommented

    }

    else {

        # Import data from the Excel file

        $data = Import-Excel -Path "C:\FilePath.xlsx"

        

        # Name of the list to add items to

        $ListNameShift = "Shifts List"

        

        # Add records to the Shifts List

        try {

            ForEach($record in $data) {

                Write-Host "Adding record $($ListItem["Title"])"

                

                # Add items to the Shifts List, mapping Excel data to SharePoint fields

                Add-PnPListItem -List $ListNameShift -Values @{

                    "Title" = $($record.'Title')

                    "Location" = $($ListItem["ID"])

                    "SORT_ORDER" = $($record.'SORT_ORDER')

                    "ACTIVE_FLAG" = $($record.'ACTIVE_FLAG')

                }

            }

        } catch {

            Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red

        }

    }

}

Step-by-Step Explanation

1. Connect to SharePoint Site: The script begins by defining the SharePoint site URL and list names. Then, it uses the Connect-PnPOnline command to authenticate and connect to the SharePoint site using the -UseWebLogin option. This method prompts for credentials if needed.

2. Retrieve Items from the Locations List: Using Get-PnPListItem, the script fetches all items from the Locations List. It specifies to fetch the "Title" field and limits the number of items fetched to 2000 for performance reasons.

3. Loop Through List Items: For each item in the Locations List, the script:

   • Displays the ID and Title of the list item.
   • Checks if the item’s ID matches certain values (15 and 8 in this case). If it matches, it skips the rest of the loop and moves to the next item. You can adjust this condition to suit your needs.

4. Read Excel Data: If the ID does not match the skip condition, the script imports data from an Excel file located at C:\FilePath.xlsx. Each row in the Excel file contains data that will be added to the Shifts List.

5. Add Items to the Shifts List: The script iterates through the Excel data and adds each record to the Shifts List. The Add-PnPListItem cmdlet is used to create a new item in the list, mapping the relevant fields (e.g., Title, SORT_ORDER, ACTIVE_FLAG) from the Excel file to the SharePoint list columns.

6. Error Handling: If there are any errors during the process (e.g., issues with the Excel file or SharePoint list), the script will catch the error and display an appropriate message.

Conclusion

This script demonstrates how to automate the process of updating a SharePoint list based on data from another list and an Excel file. By leveraging the PnP PowerShell module, you can streamline SharePoint list management tasks, making it easier to keep your data in sync without manual intervention. Whether you're dealing with large datasets or need to automate repetitive tasks, PowerShell provides a powerful toolset for SharePoint administration.

Let me know if you need help customizing this script for your specific use case or if you have any questions!