How to Duplicate a SharePoint List Using PnP PowerShell

When working with SharePoint, you might find yourself in situations where you need to create a duplicate of an existing list. This could be for backup purposes, testing, or simply as a template for new content. Fortunately, using PnP PowerShell, you can automate this process in just a few easy steps.

In this blog post, I'll walk you through how to create a duplicate of a SharePoint list, including its structure and items, using a simple PowerShell script with PnP PowerShell commands.

What is PnP PowerShell?

PnP PowerShell is a powerful library of cmdlets that helps you interact with SharePoint and Office 365. It allows you to manage SharePoint Online or SharePoint Server environments from the command line, automating common tasks and simplifying your SharePoint administration.

Prerequisites:

Before we dive into the script, make sure you have the following:

1. PnP PowerShell module installed. If you haven't installed it yet, you can do so by running this command:

   Install-Module -Name PnP.PowerShell -Force -AllowClobber
   

2. Permissions: Ensure you have appropriate permissions to access the source list and create new lists on the SharePoint site.

---

Steps to Duplicate a SharePoint List Using PnP PowerShell:

1. Connect to Your SharePoint Site

The first step is to connect to your SharePoint site where the source list is located. This can be done using the Connect-PnPOnline cmdlet.

Connect-PnPOnline -Url "https://yoursharepointsiteurl" -UseWebLogin

Replace "https://yoursharepointsiteurl" with your actual SharePoint site URL. You will be prompted to log in if necessary.

2. Get the Source List

Next, retrieve the list you want to duplicate. We use the Get-PnPList cmdlet for this.

$sourceList = Get-PnPList -Identity "Source List Name"

Make sure to replace "Source List Name" with the name of the list you wish to copy.

3. Create the New Duplicate List

Now, let's create a new list that will serve as the duplicate of the source list. The new list will have the same template as the original.

New-PnPList -Title "New Duplicate List" -Template $sourceList.Template -Description "Duplicate of the Source List" -OnQuickLaunch $false

In this command:

• "New Duplicate List" is the title of the new list.
• The template is copied from the source list to ensure the new list has the same layout.
• The -OnQuickLaunch $false option means the list won't be added to the Quick Launch bar, but you can adjust this to your preference.

4. Copy Columns (Fields) from the Source List

SharePoint lists often have custom columns (fields) that you may want to duplicate. This step ensures that the new list will have the same columns as the original.

$sourceFields = Get-PnPField -List $sourceList
foreach ($field in $sourceFields) {
    Add-PnPField -List "New Duplicate List" -DisplayName $field.Title -InternalName $field.InternalName -Type $field.TypeAsString
}

This loop retrieves all fields from the source list and adds them to the new list.

5. Copy Items from the Source List to the New List

Next, you'll copy all items (rows of data) from the source list to the new list. This ensures that the new list will contain the same data as the original.

$sourceItems = Get-PnPListItem -List $sourceList
foreach ($item in $sourceItems) {
    Add-PnPListItem -List "New Duplicate List" -Values $item.FieldValues
}

This loop retrieves each item from the source list and adds it to the new list, preserving the field values.

6. Disconnect from the SharePoint Site

Finally, after the operation is complete, disconnect from the SharePoint site.

Disconnect-PnPOnline

---

Full Script Overview

Here is the complete script for duplicating a SharePoint list:

# Connect to SharePoint site
Connect-PnPOnline -Url "https://yoursharepointsiteurl" -UseWebLogin

# Get the source list
$sourceList = Get-PnPList -Identity "Source List Name"

# Create a new duplicate list
New-PnPList -Title "New Duplicate List" -Template $sourceList.Template -Description "Duplicate of the Source List" -OnQuickLaunch $false

# Copy columns (fields) to the new list
$sourceFields = Get-PnPField -List $sourceList
foreach ($field in $sourceFields) {
    Add-PnPField -List "New Duplicate List" -DisplayName $field.Title -InternalName $field.InternalName -Type $field.TypeAsString
}

# Copy items from the source list to the new list
$sourceItems = Get-PnPListItem -List $sourceList
foreach ($item in $sourceItems) {
    Add-PnPListItem -List "New Duplicate List" -Values $item.FieldValues
}

# Disconnect from SharePoint site
Disconnect-PnPOnline

---

Conclusion

Duplicating a SharePoint list with PnP PowerShell is a simple and efficient process. With just a few commands, you can easily create an identical list, including its structure and content. This method is particularly useful for bulk operations or for backing up data without having to manually recreate everything.

Have any questions or need further customization for your specific SharePoint environment? Feel free to leave a comment below!

Happy scripting! 😊

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!

Clear Approval History from Data verse Approval table

To Automate Delete Approval history we need to delete the records data from Approval History Data verse table. To Delete Approvals History there are 2 approaches

1.    1. Using Data verse Actions in Power automate which is premium connector.

1.    2. Using HTTP Action in Flow which is also a premium connector.

Using Data Verse Connector

Step 1: 

Step 2: