PowerShell Automation for Smart SharePoint List Migration

Migrating data between SharePoint environments is one of those tasks that looks simple on paper—but in practice, it’s easy to get tangled in duplicates, mismatched fields, or incomplete moves. With many organizations moving from on-premises SharePoint Server to SharePoint Online, automation becomes key to a smooth transition.

This guide walks through a PowerShell script that not only moves list items from an on-premises SharePoint list to the cloud but also checks for duplicates before copying. Let’s break it down.

Why Automate the Migration?

Traditional migrations often copy everything from the source list to the destination list—whether or not those items already exist in the target. The result? Duplicates, clutter, and a messy data clean-up later.

This script was designed to solve that. It brings intelligence into the migration process by checking for duplicates first, then deciding whether to move or just delete the source item. The decision is based on four fields that uniquely identify a record:

• First Name
• Last Name
• Email
• Phone Number

What the Script Does

Here’s a simple explanation of the workflow:

1. Connects to the source SharePoint on-premises site and retrieves all list items.
2. Connects to the destination SharePoint Online site using secure App-only authentication.
3. For each item in the source:
   • Checks if an item with the same name, email, and phone already exists in the destination list.
   • If it exists → deletes the source item (no need to copy).
   • If it doesn’t exist → copies it to the destination and then deletes the source record.
4. Displays progress messages along the way for visibility.

This approach ensures you migrate only unique items while keeping your lists synchronized and clean.

The Tools Involved

• PowerShell — the engine driving the automation.
• SharePoint Server Object Model (Get-SPWeb) — used for accessing items from on-premises SharePoint.
• PnP PowerShell — used for connecting to SharePoint Online and adding list items.

Together, they bridge your on-premises and cloud environments with minimal manual effort.

Example PowerShell Script (with Dummy Values)

# ============================================
# SMART SHAREPOINT LIST MIGRATION SCRIPT
# ============================================

# --- Source (On-Premises) ---
$sourceWebURL = "https://sp2019.contoso.local/sites/Finance"
$sourceListName = "ExpenseClaims"
$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]
$spSourceItems = $spSourceList.GetItems()

# --- Destination (SharePoint Online) ---
$SiteURL = "https://contoso.sharepoint.com/sites/FinancePortal"
$ListName = "ExpenseClaims_Archive"
$AppId = "00000000-aaaa-bbbb-cccc-111111111111"
$AppSecret = "xyz123fakeappsecret987654321"
Connect-PnPOnline -Url $SiteURL -AppId $AppId -AppSecret $AppSecret
Get-PnPContext

# --- Process Each Item ---
foreach ($spSourceItem in $spSourceItems) {

    $firstName = $spSourceItem['First_x0020_Name']
    $lastName  = $spSourceItem['Last_x0020_Name']
    $email     = $spSourceItem['Email']
    $phone     = $spSourceItem['Phone_x0020_Number']

    # --- Check for Duplicate in Destination ---
    $existingItem = Get-PnPListItem -List $ListName -PageSize 2000 -Query @"
        <View>
            <Query>
                <Where>
                    <And>
                        <And>
                            <Eq><FieldRef Name='First_x0020_Name' /><Value Type='Text'>$firstName</Value></Eq>
                            <Eq><FieldRef Name='Last_x0020_Name' /><Value Type='Text'>$lastName</Value></Eq>
                        </And>
                        <And>
                            <Eq><FieldRef Name='Email' /><Value Type='Text'>$email</Value></Eq>
                            <Eq><FieldRef Name='Phone_x0020_Number' /><Value Type='Text'>$phone</Value></Eq>
                        </And>
                    </And>
                </Where>
            </Query>
        </View>
"@

    if ($existingItem.Count -gt 0) {
        Write-Host "Duplicate found for $firstName $lastName ($email) — deleting from source only." -ForegroundColor Yellow
        $spSourceItem.Delete()
        continue
    }

    # --- Copy to Destination ---
    Add-PnPListItem -List $ListName -Values @{
        "Title" = $firstName
        "LastName" = $lastName
        "Email" = $email
        "Phone" = $phone
        "Comments" = $spSourceItem['Comments']
        "MeetingDate" = $spSourceItem['Meeting_x0020_Date']
        "Region" = $spSourceItem['Region']
        "Processed" = $spSourceItem['Status']
    }

    Write-Host "Copied new item for $firstName $lastName ($email)." -ForegroundColor Green

    # --- Delete from Source After Copy ---
    $spSourceItem.Delete()
    Write-Host "Deleted source item for $firstName $lastName ($email)." -ForegroundColor Cyan
}

Write-Host "Migration completed successfully!" -ForegroundColor White -BackgroundColor DarkGreen

Step-by-Step Summary

1. Source Connection – The script connects to the on-premises SharePoint site and fetches all items from the specified list.
2. Destination Connection – Uses Connect-PnPOnline to authenticate with SharePoint Online via App ID and Secret.
3. Duplicate Checking – For each item, it searches the destination list for matching data based on four identifying fields.
4. Conditional Logic –
   • If a duplicate is found → the item is deleted from the source only.
   • If not found → the item is copied to SharePoint Online and then removed from the source.
5. Logging – Console messages display what’s being copied, skipped, or deleted in real time.

Why This Approach Works

• Prevents duplicates in your target list.
• Keeps data synchronized between on-premises and SharePoint Online.
• Supports incremental migration—you can run it multiple times without re-importing the same records.
• Cleans up automatically after successful copy, leaving no leftover data.

Important Considerations

• The script deletes items from the source list after migration. Always test in a non-production environment first.
• To perform a dry run, simply comment out the $spSourceItem.Delete() lines to verify results without removing anything.
• Ensure your field internal names match between both lists.
• Large lists may require pagination or indexing for better performance.

Potential Enhancements

This script forms a strong foundation for more advanced migration scenarios. Here are a few ideas to build upon it:

• Export results or logs to a CSV file.
• Handle attachments and lookup fields.
• Add date filters to migrate only recent entries.
• Introduce a “Test Mode” switch to simulate the run safely.

Final Thoughts

SharePoint migrations don’t have to be painful. By using PowerShell with smart checks, you can move your data confidently, ensure uniqueness, and maintain a clean, cloud-ready environment.

This particular script shows how automation and logic can work together to make SharePoint migrations faster, safer, and smarter.

---

Written by: Keyur Pandya
LinkedIn: https://www.linkedin.com/in/keyurbpandya/