How to Add More Than 5000 Items to a SharePoint Online List Using PnP PowerShell

Whenever I need to test list view thresholds, indexing, filtering, or performance in SharePoint Online, the first thing I need is a list with a lot of data in it. And by “a lot”, I mean more than 5,000 items, because that is the magic number where SharePoint starts behaving differently.

In this tutorial, I will show you exactly how I create a SharePoint Online list from scratch using PnP PowerShell and then fill it with 5,000+ items. I will share multiple methods (one simple, one fast, and one CSV-driven), and I will explain when I use each one.

By the end of this article, you will have a working, copy-paste-ready script that adds thousands of items into a SharePoint Online list without your PowerShell window sitting there for an hour.

What You Need Before You Start

Before running any of these scripts, here is what I make sure I have in place:

• PnP.PowerShell module installed. If you have not installed it yet, run this once:

Install-Module PnP.PowerShell -Scope CurrentUser -Force

• An Entra ID (Azure AD) App Registration Client ID. Since PnP PowerShell 2.x, you must pass your own -ClientId. In my examples, I use 655a839c-8659-4229-9fd3-2824ae3c537b.
• Site Collection Admin or at least Contribute/Manage Lists permission on the target site.
• PowerShell 7.x — I strongly recommend it. PnP.PowerShell 2.x and above only supports PowerShell 7, and it also handles the emoji characters in my choice values far better than Windows PowerShell 5.1.

One quick note about the emojis. Because my choice column uses characters like 🟢 and 🔴, I always save my .ps1 file as UTF-8 with BOM. If you skip this, you will end up with garbage characters like 🟢 in your list, and then you will spend an hour wondering what went wrong. Trust me on this one.

The SharePoint List Structure I Am Building

Here is the SharePoint Online list I will create, called Project Tracker:

Column Name	Internal Name	Column Type	Details
Title	Title	Single line of text	Default column
Project Status	ProjectStatus	Choice	🟢 Not Started, 🔵 In Progress, 🟠 On Hold, 🟣 Completed, 🔴 Cancelled
Project Manager	ProjectManager	Person or Group	People only
Priority	Priority	Choice	High, Medium, Low
Start Date	StartDate	Date and Time	Date only
Due Date	DueDate	Date and Time	Date only

Notice that I keep the internal names without spaces. This is intentional. If I create a column named “Project Status” through the SharePoint UI, the internal name becomes Project_x0020_Status, which is painful to work with in scripts. By creating columns via PnP PowerShell with a clean internal name, my scripts stay readable.

Step 1 – Create the SharePoint Online List Using PnP PowerShell

This first script creates the list and all six columns. It is safe to re-run, because I check whether the list and each field already exist before creating them.

# ===============================================================
# Create the "Project Tracker" list in SharePoint Online
# ===============================================================

$ErrorActionPreference = "Stop"

$ClientID = "655a839c-8659-4229-9fd3-2824ae3c537b"
$SiteUrl  = "https://tsinfotechnologies.sharepoint.com/sites/SPGuides/"
$ListName = "Project Tracker"

# Connect interactively (supports MFA)
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

try {
    # -----------------------
    # Create the list
    # -----------------------
    $List = Get-PnPList -Identity $ListName -ErrorAction SilentlyContinue

    if ($null -eq $List) {
        $List = New-PnPList -Title $ListName -Template GenericList -OnQuickLaunch
        Write-Host "Created list: $ListName" -ForegroundColor Green
    }
    else {
        Write-Host "List already exists: $ListName" -ForegroundColor Yellow
    }

    # Helper so I do not create the same field twice
    function Add-FieldIfMissing {
        param (
            [string]$ListTitle,
            [string]$InternalName,
            [scriptblock]$CreateAction
        )

        $Existing = Get-PnPField -List $ListTitle -Identity $InternalName -ErrorAction SilentlyContinue

        if ($null -eq $Existing) {
            & $CreateAction
            Write-Host "Created field: $InternalName" -ForegroundColor Green
        }
        else {
            Write-Host "Field already exists: $InternalName" -ForegroundColor Yellow
        }
    }

    # -----------------------
    # Project Status (Choice)
    # -----------------------
    Add-FieldIfMissing -ListTitle $ListName -InternalName "ProjectStatus" -CreateAction {
        Add-PnPField -List $ListName `
                     -DisplayName "Project Status" `
                     -InternalName "ProjectStatus" `
                     -Type Choice `
                     -Choices "🟢 Not Started", "🔵 In Progress", "🟠 On Hold", "🟣 Completed", "🔴 Cancelled" `
                     -AddToDefaultView | Out-Null

        Set-PnPField -List $ListName -Identity "ProjectStatus" -Values @{ DefaultValue = "🟢 Not Started" }
    }

    # -----------------------
    # Project Manager (Person)
    # -----------------------
    Add-FieldIfMissing -ListTitle $ListName -InternalName "ProjectManager" -CreateAction {
        Add-PnPField -List $ListName `
                     -DisplayName "Project Manager" `
                     -InternalName "ProjectManager" `
                     -Type User `
                     -AddToDefaultView | Out-Null
    }

    # -----------------------
    # Priority (Choice)
    # -----------------------
    Add-FieldIfMissing -ListTitle $ListName -InternalName "Priority" -CreateAction {
        Add-PnPField -List $ListName `
                     -DisplayName "Priority" `
                     -InternalName "Priority" `
                     -Type Choice `
                     -Choices "High", "Medium", "Low" `
                     -AddToDefaultView | Out-Null

        Set-PnPField -List $ListName -Identity "Priority" -Values @{ DefaultValue = "Medium" }
    }

    # -----------------------
    # Start Date (Date only)
    # -----------------------
    Add-FieldIfMissing -ListTitle $ListName -InternalName "StartDate" -CreateAction {
        Add-PnPField -List $ListName `
                     -DisplayName "Start Date" `
                     -InternalName "StartDate" `
                     -Type DateTime `
                     -AddToDefaultView | Out-Null

        Set-PnPField -List $ListName -Identity "StartDate" -Values @{ DisplayFormat = 0 }  # 0 = Date only
    }

    # -----------------------
    # Due Date (Date only)
    # -----------------------
    Add-FieldIfMissing -ListTitle $ListName -InternalName "DueDate" -CreateAction {
        Add-PnPField -List $ListName `
                     -DisplayName "Due Date" `
                     -InternalName "DueDate" `
                     -Type DateTime `
                     -AddToDefaultView | Out-Null

        Set-PnPField -List $ListName -Identity "DueDate" -Values @{ DisplayFormat = 0 }  # 0 = Date only
    }

    Write-Host "`nList '$ListName' is ready." -ForegroundColor Cyan
    Get-PnPField -List $ListName |
        Where-Object { -not $_.Hidden } |
        Select-Object Title, InternalName, TypeDisplayName |
        Format-Table -AutoSize
}
catch {
    Write-Host "Script failed: $($_.Exception.Message)" -ForegroundColor Red
}
finally {
    Disconnect-PnPOnline -ErrorAction SilentlyContinue
}

The last few lines print out the internal names of every column. I always run that before writing any data script, because guessing internal names is the number one reason bulk insert scripts fail.

Check out Add SharePoint List Fields From Excel Using PnP PowerShell

Step 2 – Index the Columns Before You Add 5000+ Items

This is the step almost everybody skips, and then complains that their SharePoint list is unusable.

Once a list crosses 5,000 items, SharePoint will not let you add an index on a column anymore in many scenarios, and filtering on non-indexed columns throws the dreaded list view threshold error. So I add my indexes now, while the list is empty and indexing takes a fraction of a second.

Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

# Index the columns I know I will filter or sort on later
Add-PnPFieldIndex -List $ListName -Field "ProjectStatus"
Add-PnPFieldIndex -List $ListName -Field "Priority"
Add-PnPFieldIndex -List $ListName -Field "StartDate"
Add-PnPFieldIndex -List $ListName -Field "DueDate"
Add-PnPFieldIndex -List $ListName -Field "ProjectManager"

Write-Host "Indexes created." -ForegroundColor Green
Disconnect-PnPOnline

Keep in mind SharePoint allows a maximum of 20 indexed columns per list, so index only what you will actually filter on.

Check out Create Indexed Columns in SharePoint Using Power Automate

Method 1 – Add Items One by One Using Add-PnPListItem

This is the simplest and most readable approach. Each item is a separate HTTP call to SharePoint. I use this method when I am adding a few hundred items, or when I need per-item error handling and want to know exactly which record failed.

Here is my complete script, now including the Due Date column:

# ===============================================================
# METHOD 1: Add items one at a time using Add-PnPListItem
# ===============================================================

$ClientID  = "655a839c-8659-4229-443-3333333333333"
$SiteUrl   = "https://tsinfotechnologies.sharepoint.com/sites/SPGuides"
$ListName  = "Project Tracker"
$ItemCount = 20

# Connect interactively; supports MFA
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

# Confirm the list exists
$List = Get-PnPList -Identity $ListName -ErrorAction SilentlyContinue

if ($null -eq $List) {
    throw "List '$ListName' was not found at: $SiteUrl"
}

# Confirm actual internal names before running:
# Get-PnPField -List $ListName | Select-Object Title, InternalName, TypeDisplayName

$ProjectStatuses = @(
    "🟢 Not Started",
    "🔵 In Progress",
    "🟠 On Hold",
    "🟣 Completed",
    "🔴 Cancelled"
)

$Priorities = @(
    "High",
    "Medium",
    "Low"
)

# Email addresses of the allowed Project Managers
$ProjectManagerEmails = @(
    "user1@tsinfot.onmicrosoft.com",
    "user2@tsinfotec.onmicrosoft.com",
    "bijay@tsinfot.onmicrosoft.com",
    "preeti@tsinfo.onmicrosoft.com",
    "asit@tsinfote.onmicrosoft.com"
)

# Get all users known to this SharePoint site
$Users = Get-PnPUser

# Select only the specified Project Managers
$ProjectManagers = $Users | Where-Object {
    $_.Email -in $ProjectManagerEmails
}

# Make sure all required Project Managers were found
if ($null -eq $ProjectManagers -or $ProjectManagers.Count -eq 0) {
    throw "None of the specified Project Managers were found in the SharePoint site."
}

# Display the Project Managers that will be used
Write-Host "`nAvailable Project Managers:" -ForegroundColor Cyan

$ProjectManagers |
    Select-Object Id, Title, LoginName, Email |
    Format-Table -AutoSize

$ProjectPrefixes = @(
    "SharePoint",
    "Power Platform",
    "Copilot",
    "Teams",
    "Intranet",
    "Migration",
    "Automation",
    "CRM"
)

$ProjectTypes = @(
    "Implementation",
    "Modernization",
    "Migration",
    "Integration",
    "Portal",
    "Dashboard",
    "Optimization",
    "Rollout"
)

# Create random Project Tracker items
for ($i = 1; $i -le $ItemCount; $i++) {

    $ProjectTitle = "{0} {1} {2:D3}" -f `
        ($ProjectPrefixes | Get-Random), `
        ($ProjectTypes | Get-Random), `
        $i

    $ProjectStatus = $ProjectStatuses | Get-Random

    # Select a random SharePoint user from the filtered Project Managers
    $RandomProjectManager = Get-Random -InputObject $ProjectManagers

    # Use the actual SharePoint LoginName
    $ProjectManagerLoginName = $RandomProjectManager.LoginName

    $Priority = $Priorities | Get-Random

    # Random start date between 180 days ago and 90 days from today
    $StartDate = (Get-Date).AddDays((Get-Random -Minimum -180 -Maximum 91)).Date

    # Due Date is always 15 to 120 days after the Start Date
    $DueDate = $StartDate.AddDays((Get-Random -Minimum 15 -Maximum 121))

    $ItemValues = @{
        "Title"          = $ProjectTitle
        "ProjectStatus"  = $ProjectStatus
        "ProjectManager" = $ProjectManagerLoginName
        "Priority"       = $Priority
        "StartDate"      = $StartDate.ToString("yyyy-MM-dd")
        "DueDate"        = $DueDate.ToString("yyyy-MM-dd")
    }

    try {
        Add-PnPListItem -List $ListName -Values $ItemValues -ErrorAction Stop | Out-Null

        Write-Host "Added $i of $($ItemCount): $ProjectTitle | Project Manager: $($RandomProjectManager.Title)" -ForegroundColor Green
    }
    catch {
        Write-Warning "Failed to add item $($i): $($_.Exception.Message)"
    }
}

Disconnect-PnPOnline

Write-Host "Completed: attempted to add $ItemCount items to '$ListName'." -ForegroundColor Cyan

Notice two things I changed from a typical script:

1. I calculate $DueDate from $StartDate, so the data actually makes logical sense. A Due Date before a Start Date would make my test data useless for filtering demos.
2. I pass dates as yyyy-MM-dd strings. This avoids regional/locale surprises where a [DateTime] object gets serialized in a format SharePoint interprets differently.

The honest downside: this method makes one round trip per item. On my tenant, that is roughly 1 to 2 items per second. For 5,000 items, you are looking at somewhere between 45 minutes and 2 hours. That is why I rarely use it beyond a few hundred records.

I executed the above PowerShell script, and you can see the exact output in the screenshot below:

If I open the SharePoint Online list, you can see it has added items like in the screenshot below:

Check out Delete All Items from a SharePoint List Using PnP PowerShell

Method 2 – Add Items in Bulk Using PnP Batching (My Recommended Method)

This is the method I use for anything over a few hundred items. Instead of sending one request per item, I queue the items into a batch object and then push up to 100 operations to SharePoint in a single request.

The speed difference is not small. In my testing, batching added 5,000 items in roughly 3 to 5 minutes, compared to well over an hour with Method 1.

Here is my complete batching script, with the Due Date column included and the item count set to 5,000:

# ===============================================================
# METHOD 2: Add 5000+ items to a SharePoint Online list
#           using PnP batching (fast)
# ===============================================================

# Install PnP.PowerShell once if it is not already installed:
# Install-Module PnP.PowerShell -Scope CurrentUser -Force

$ErrorActionPreference = "Stop"

# -----------------------
# Configuration
# -----------------------
$ClientID  = "655a839c-8659-4229-55444-rrrrrrrrrrrrreeee"
$SiteUrl   = "https://tsinfotechnologies.sharepoint.com/sites/SPGuides"
$ListName  = "Project Tracker"
$ItemCount = 5000
$BatchSize = 100

# -----------------------
# Connect to SharePoint
# -----------------------
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

$Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()

try {
    # Verify that the list exists
    $List = Get-PnPList -Identity $ListName -ErrorAction Stop
    Write-Host "Connected successfully to: $SiteUrl" -ForegroundColor Green
    Write-Host "Target list: $($List.Title)" -ForegroundColor Green

    # ------------------------------------------------------------
    # IMPORTANT:
    # These must be the SharePoint INTERNAL names of the columns.
    # Update them only if Get-PnPField returns different names.
    # ------------------------------------------------------------
    $TitleField          = "Title"
    $ProjectStatusField  = "ProjectStatus"
    $ProjectManagerField = "ProjectManager"
    $PriorityField       = "Priority"
    $StartDateField      = "StartDate"
    $DueDateField        = "DueDate"

    # Random values for Choice columns
    $ProjectStatuses = @(
        "🟢 Not Started",
        "🔵 In Progress",
        "🟠 On Hold",
        "🟣 Completed",
        "🔴 Cancelled"
    )

    $Priorities = @(
        "High",
        "Medium",
        "Low"
    )

    # Person or Group column values
    $ProjectManagerEmails = @(
        "user1@tsinfotec.onmicrosoft.com",
        "user2@tsinfotech.onmicrosoft.com",
        "bijay@tsinfotech.onmicrosoft.com",
        "preeti@tsinfote.onmicrosoft.com",
        "asit@tsinfotec.onmicrosoft.com"
    )

    # ------------------------------------------------------------
    # Get all users known to this SharePoint site
    # ------------------------------------------------------------
    $Users = Get-PnPUser

    # Select only the specified Project Managers
    $ProjectManagers = $Users | Where-Object {
        $_.Email -in $ProjectManagerEmails
    }

    # Make sure at least one Project Manager was found
    if ($null -eq $ProjectManagers -or $ProjectManagers.Count -eq 0) {
        throw "None of the specified Project Managers were found in the SharePoint site."
    }

    # Display the Project Managers that will be used
    Write-Host "`nAvailable Project Managers:" -ForegroundColor Cyan

    $ProjectManagers |
        Select-Object Id, Title, LoginName, Email |
        Format-Table -AutoSize

    # Random data for the Title field
    $ProjectPrefixes = @(
        "SharePoint",
        "Power Platform",
        "Copilot",
        "Teams",
        "Intranet",
        "Migration",
        "Automation",
        "CRM"
    )

    $ProjectTypes = @(
        "Implementation",
        "Modernization",
        "Migration",
        "Integration",
        "Portal",
        "Dashboard",
        "Optimization",
        "Rollout"
    )

    # -----------------------
    # Queue and submit items
    # -----------------------
    $Batch = New-PnPBatch

    for ($i = 1; $i -le $ItemCount; $i++) {

        $ProjectTitle = "{0} {1} {2:D5}" -f `
            ($ProjectPrefixes | Get-Random), `
            ($ProjectTypes | Get-Random), `
            $i

        $ProjectStatus = $ProjectStatuses | Get-Random

        # Select a random SharePoint user from the filtered Project Managers
        $RandomProjectManager = Get-Random -InputObject $ProjectManagers

        # Use the actual SharePoint LoginName for the Person or Group field
        $ProjectManager = $RandomProjectManager.LoginName

        $Priority = $Priorities | Get-Random

        # Random start date: 365 days ago through 90 days ahead
        $StartDate = (Get-Date).AddDays(
            (Get-Random -Minimum -365 -Maximum 91)
        ).Date

        # Due Date always falls 15 to 180 days after the Start Date
        $DueDate = $StartDate.AddDays(
            (Get-Random -Minimum 15 -Maximum 181)
        )

        $ItemValues = @{
            $TitleField          = $ProjectTitle
            $ProjectStatusField  = $ProjectStatus
            $ProjectManagerField = $ProjectManager
            $PriorityField       = $Priority
            $StartDateField      = $StartDate.ToString("yyyy-MM-dd")
            $DueDateField        = $DueDate.ToString("yyyy-MM-dd")
        }

        # Queue the item; it is sent only when Invoke-PnPBatch runs.
        Add-PnPListItem -List $ListName -Values $ItemValues -Batch $Batch | Out-Null

        # Submit each full batch, and submit the final partial batch.
        if (($i % $BatchSize -eq 0) -or ($i -eq $ItemCount)) {

            Write-Host "Submitting batch ending at item $i of $ItemCount..." -ForegroundColor Cyan

            Invoke-PnPBatch -Batch $Batch -StopOnException

            $Percent = [math]::Round(($i / $ItemCount) * 100, 1)
            Write-Host "Batch submitted. Progress: $Percent% | Elapsed: $($Stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -ForegroundColor Green

            # Create a fresh batch for subsequent items.
            if ($i -lt $ItemCount) {
                $Batch = New-PnPBatch
            }
        }
    }

    $Stopwatch.Stop()
    Write-Host "`nSuccess: $ItemCount items were added to '$ListName'." -ForegroundColor Green
    Write-Host "Total time: $($Stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -ForegroundColor Green
}
catch {
    Write-Host "Script failed: $($_.Exception.Message)" -ForegroundColor Red
}
finally {
    Disconnect-PnPOnline -ErrorAction SilentlyContinue
    Write-Host "Disconnected from SharePoint Online." -ForegroundColor DarkGray
}

Why I Keep the Batch Size at 100

SharePoint’s $batch endpoint accepts a maximum of 100 change operations in a single request. If you set $BatchSize to 500, PnP will internally split it anyway, but you lose your progress visibility and error isolation. So 100 is the sweet spot, and it is what I always use.

Why I Create a Fresh Batch Every Time

Once you call Invoke-PnPBatch, that batch object is spent. If you keep reusing the same variable without calling New-PnPBatch again, you will either get errors or silently add nothing. That is why my script rebuilds the batch inside the loop.

About -StopOnException

I use -StopOnException because when I am generating test data, I want to know immediately if something is wrong with my field names or my choice values. If instead you want the script to push through and ignore individual failures, just remove that switch.

Check out Filter SharePoint List Items in SharePoint Framework (SPFx)

Method 3 – Bulk Import Items from a CSV File

The two methods above generate random data. But quite often, I already have real data sitting in an Excel or CSV file that I need to push into SharePoint. This is the method I use for actual migrations.

First, I prepare a CSV file named ProjectTracker.csv that looks like this:

Title,ProjectStatus,ProjectManager,Priority,StartDate,DueDate
SharePoint Migration 00001,🔵 In Progress,bijay@tsinfo.onmicrosoft.com,High,2026-01-15,2026-04-20
Copilot Rollout 00002,🟢 Not Started,preeti@tsinfote.onmicrosoft.com,Medium,2026-02-01,2026-06-10
Intranet Portal 00003,🟣 Completed,asit@tsin.onmicrosoft.com,Low,2025-09-05,2025-12-15

Then I run this script, which reads the CSV and pushes everything in batches:

# ===============================================================
# METHOD 3: Bulk import 5000+ items from a CSV file (batched)
# ===============================================================

$ErrorActionPreference = "Stop"

$ClientID  = "655a839c-8659-4229-96565-tytyrrrr"
$SiteUrl   = "https://tsinfotechnologies.sharepoint.com/sites/SPGuides"
$ListName  = "Project Tracker"
$CsvPath   = "C:\Temp\ProjectTracker.csv"
$BatchSize = 100

Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

$Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$FailedRows = New-Object System.Collections.Generic.List[object]

try {
    # Always read the CSV as UTF8 so the emoji choice values survive
    $Rows = Import-Csv -Path $CsvPath -Encoding UTF8
    $Total = $Rows.Count

    Write-Host "Loaded $Total rows from $CsvPath" -ForegroundColor Green

    # ------------------------------------------------------------
    # Get all users known to this SharePoint site
    # ------------------------------------------------------------
    $Users = Get-PnPUser

    # ------------------------------------------------------------
    # Create a lookup of Email -> SharePoint LoginName
    # This allows the CSV to contain email addresses while the
    # Person/Group column receives the actual SharePoint LoginName.
    # ------------------------------------------------------------
    $ProjectManagerLookup = @{}

    foreach ($User in $Users) {
        if ($User.Email) {
            $ProjectManagerLookup[$User.Email.ToLower()] = $User.LoginName
        }
    }

    Write-Host "Loaded $($ProjectManagerLookup.Count) SharePoint users for Project Manager lookup." -ForegroundColor Cyan

    # ------------------------------------------------------------
    # Verify all unique Project Managers from the CSV
    # ------------------------------------------------------------
    $UniqueManagers = $Rows.ProjectManager |
        Where-Object { $_ } |
        Sort-Object -Unique

    foreach ($Manager in $UniqueManagers) {

        $ManagerKey = $Manager.Trim().ToLower()

        if (-not $ProjectManagerLookup.ContainsKey($ManagerKey)) {
            Write-Warning "Project Manager '$Manager' was not found in the SharePoint site's users."
        }
    }

    $Batch = New-PnPBatch
    $Counter = 0

    foreach ($Row in $Rows) {

        $Counter++

        try {

            # ----------------------------------------------------
            # Resolve the CSV Project Manager email to the actual
            # SharePoint LoginName
            # ----------------------------------------------------
            $ManagerKey = $Row.ProjectManager.Trim().ToLower()

            if (-not $ProjectManagerLookup.ContainsKey($ManagerKey)) {
                throw "Project Manager '$($Row.ProjectManager)' was not found in the SharePoint site's users."
            }

            $ProjectManagerLoginName = $ProjectManagerLookup[$ManagerKey]

            $ItemValues = @{
                "Title"          = $Row.Title
                "ProjectStatus"  = $Row.ProjectStatus
                "ProjectManager" = $ProjectManagerLoginName
                "Priority"       = $Row.Priority
                "StartDate"      = ([datetime]$Row.StartDate).ToString("yyyy-MM-dd")
                "DueDate"        = ([datetime]$Row.DueDate).ToString("yyyy-MM-dd")
            }

            Add-PnPListItem -List $ListName -Values $ItemValues -Batch $Batch | Out-Null
        }
        catch {
            $FailedRows.Add([pscustomobject]@{
                RowNumber = $Counter
                Title     = $Row.Title
                Error     = $_.Exception.Message
            })
        }

        if (($Counter % $BatchSize -eq 0) -or ($Counter -eq $Total)) {

            Invoke-PnPBatch -Batch $Batch

            $Percent = [math]::Round(($Counter / $Total) * 100, 1)
            Write-Host "Imported $Counter of $Total ($Percent%) | Elapsed: $($Stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -ForegroundColor Green

            if ($Counter -lt $Total) {
                $Batch = New-PnPBatch
            }
        }
    }

    $Stopwatch.Stop()
    Write-Host "`nImport finished in $($Stopwatch.Elapsed.ToString('hh\:mm\:ss'))." -ForegroundColor Cyan

    if ($FailedRows.Count -gt 0) {
        $ErrorLog = "C:\Temp\ImportErrors.csv"
        $FailedRows | Export-Csv -Path $ErrorLog -NoTypeInformation -Encoding UTF8
        Write-Warning "$($FailedRows.Count) rows failed. See $ErrorLog"
    }
}
catch {
    Write-Host "Script failed: $($_.Exception.Message)" -ForegroundColor Red
}
finally {
    Disconnect-PnPOnline -ErrorAction SilentlyContinue
}

The thing I like most about this version is the error log. When you import 5,000 real rows, a handful will always fail because of a bad date, a typo in a choice value, or a user who left the company. Exporting those failures to ImportErrors.csv means I can fix and re-import just those rows instead of starting over.

How to Verify You Actually Crossed 5000 Items

Once the script finishes, I always verify the count rather than trusting the console output:

Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Interactive

$List = Get-PnPList -Identity $ListName
Write-Host "Total items in '$ListName': $($List.ItemCount)" -ForegroundColor Cyan

Disconnect-PnPOnline

If you want to actually retrieve items from a list that now exceeds the threshold, remember to use -PageSize:

$Items = Get-PnPListItem -List $ListName -PageSize 2000
Write-Host "Retrieved $($Items.Count) items."

Without -PageSize, you will hit the list view threshold error. With it, PnP pages through the list for you.

Read Display SharePoint List Items in SPFx Web Part

Common Errors and How I Fix Them

Here are the errors I run into most often when doing bulk inserts, and what actually fixes them.

“Column ‘ProjectStatus’ does not exist.” Your internal name is wrong. Run Get-PnPField -List $ListName | Select-Object Title, InternalName and use whatever comes back in the InternalName column. If you created the list through the UI, expect names like Project_x0020_Status.

Choice values show as blank or the item fails validation. Your string must match the choice value character for character, including the emoji and the space after it. Copy the value directly out of (Get-PnPField -List $ListName -Identity "ProjectStatus").Choices to be certain.

Emojis appear as 🟢. Save your script file as UTF-8 with BOM and run it in PowerShell 7. This fixes it every single time.

“The specified user could not be found” on the Person column. The account must exist in your tenant and be resolvable on that site. That is exactly why I run the New-PnPUser loop before inserting anything.

Throttling: HTTP 429 responses. PnP PowerShell already retries automatically, but if you are pushing tens of thousands of items you may still get throttled. My fix is to add a short pause between batches:

Start-Sleep -Milliseconds 500

I place that right after Invoke-PnPBatch. It costs a few extra minutes but it keeps the run stable.

Dates land one day off. Always pass dates as yyyy-MM-dd strings, as I do in every script above. Passing raw [DateTime] objects can shift by a day depending on the site’s regional time zone settings.

Which Method Should You Use?

Here is how I decide:

Scenario	Method I Use	Approximate Time for 5,000 Items
Under 200 items, need per-item error detail	Method 1 (one by one)	2 to 4 minutes per 200
Generating 5,000+ test items	Method 2 (batching)	3 to 5 minutes
Importing real data from Excel or CSV	Method 3 (CSV + batching)	4 to 7 minutes

In practice, I use Method 2 for almost everything. Method 1 is really only useful for small runs or for debugging why a specific record will not save.

Check out Create a Location Column in SharePoint List Using Power Automate REST API

A Few Best Practices I Always Follow

• Index your columns before you cross 5,000 items. This is the single most important step, and it is the one that cannot easily be undone later. Indexing an empty list takes a second. Indexing a 40,000-item list can fail outright.
• Test with 100 items first. I never point a script at 5,000 items on the first run. I set $ItemCount = 100, check the results in the browser, confirm the dates, the emojis, and the Person column all look right, and only then bump the number up.
• Add folders if you plan to go far beyond 5,000. If you are heading toward 100,000+ items, consider distributing items into folders or using a metadata-driven view structure. It keeps the default views responsive.
• Always use -PageSize when reading back. Any Get-PnPListItem call against a large list should include -PageSize 2000, otherwise you will hit the threshold error the moment you try to verify your work.
• Run heavy imports outside business hours. SharePoint throttles per tenant, not per script. If your colleagues are hammering the same tenant at 10 AM, your import will be slower and more likely to get 429 responses. I schedule big imports for evenings or weekends.
• Turn off versioning temporarily for very large imports. Every item you add creates a version entry. For a one-time import of tens of thousands of rows, I disable versioning first and re-enable it afterwards:

# Before the import
Set-PnPList -Identity $ListName -EnableVersioning $false

# After the import
Set-PnPList -Identity $ListName -EnableVersioning $true -MajorVersions 50

• Log everything to a file. When a script runs for several minutes, console output scrolls away. I wrap my runs with a transcript so I have a permanent record:

Start-Transcript -Path "C:\Temp\BulkImport_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"
# ... your script here ...
Stop-Transcript

• Use app-only authentication for unattended runs. Interactive login is fine when I am sitting at my desk, but for scheduled jobs I register a certificate-based app and connect with Connect-PnPOnline -Url $SiteUrl -ClientId $ClientID -Tenant "yourtenant.onmicrosoft.com" -CertificatePath "C:\Certs\PnP.pfx" -CertificatePassword $SecurePassword. No MFA prompt, no expired session halfway through a 30-minute import.
• Keep a cleanup script handy. Test data has a habit of outliving its usefulness. When I am done, I remove the items in batches too:

$Batch = New-PnPBatch
$Items = Get-PnPListItem -List $ListName -PageSize 2000 -Fields "ID"
$Counter = 0

foreach ($Item in $Items) {
    Remove-PnPListItem -List $ListName -Identity $Item.Id -Recycle -Batch $Batch
    $Counter++

    if ($Counter % 100 -eq 0) {
        Invoke-PnPBatch -Batch $Batch
        $Batch = New-PnPBatch
        Write-Host "Deleted $Counter items..." -ForegroundColor Yellow
    }
}

Invoke-PnPBatch -Batch $Batch
Write-Host "Cleanup complete. Deleted $Counter items." -ForegroundColor Green

Just remember that -Recycle sends everything to the Recycle Bin, which itself counts against your site storage. For genuinely large cleanups, I empty the recycle bin afterwards with Clear-PnPRecycleBinItem -All -Force.

Frequently Asked Questions

Conclusion

Adding more than 5,000 items to a SharePoint Online list is not difficult once you know the pattern. What trips people up is not the volume itself, it is the small details around it: wrong internal names, unindexed columns, mismatched choice values, and mangled emojis.

My honest recommendation is simple. Create the list with PnP PowerShell rather than the UI so you control the internal names. Index your filterable columns while the list is still empty. Then use the batching script from Method 2, because it turns an hour-long job into a five-minute one.

Start small with 100 items, confirm the data looks right in the browser, and only then scale up to 5,000 or 50,000. Every script in this tutorial is ready to copy, paste, and run once you swap in your own site URL, Client ID, and user accounts.

If you are building a demo environment, testing threshold behavior, or migrating real project data, these three methods will cover just about every scenario you run into.

You may also like:

• How to Get SharePoint Document Library Size Using PnP PowerShell
• Enable Sensitivity Labels For Microsoft 365 Groups & SharePoint Sites Using PowerShell
• Add Bulk Data from Excel File to SharePoint List Using PnP PowerShell