Add SharePoint List Fields From Excel Using PnP PowerShell

When I build Power Apps solutions for clients, SharePoint lists are almost always part of the setup. And most of the time, these lists require many fields. Sometimes 30. Sometimes 40+. Manually creating them was a big waste of time.

So I started using PnP PowerShell to automate this.
One script -> Run it once -> SharePoint lists created -> all fields added -> Huge time saver.

But there was a problem. All field details, such as internal name, display name, and data type, were hard-coded in the PowerShell script. If a client wanted to add a new field, update an existing one, or delete something, they had to touch the script. And let’s be honest, most clients don’t want to edit PowerShell code. That quickly became a bottleneck.

To fix this, I changed the approach.

Instead of hardcoding field details in PowerShell, I moved everything to Excel. Each row represents a field. Name, type, settings, everything lives in a simple Excel file. Now the PowerShell script just reads the Excel file and creates the fields one by one in the SharePoint list.

This way, clients only update Excel.
No PowerShell changes. No risk. Much simpler.

In this tutorial, I’ll show you how to add fields to a SharePoint list from Excel using PnP PowerShell, step by step. If you work with Power Apps, SharePoint, or automation, this approach will save you a lot of time.

Create a SharePoint List & Add Fields From Excel [PnP PowerShell]

Before we discuss the PnP PowerShell script, first, we need to make an Excel sheet that contains the SharePoint list fields data, such as:

• Display Name
• Internal Name
• Data Type
• Is Mandatory
• Choices

Now, the Excel is ready; let’s understand the PowerShell script:

Understand the PnP PowerShell Script

The script will first create a list with the provided name. Then, it fetches each field from Excel and adds it to the SharePoint list based on the data type. It will also update the Required property and add choice values for the choice fields.

$siteUrl = "https://<tenant name>.sharepoint.com/sites/HumanResourcesDepartment"
$excelFilePath = "D:\ListColumns\Event Registrations.xlsx"
$listName = "EventRegistrationsList"
$ListDisplayName = "Event Registrations List"
$ClientID = "Proivde your client id"
function Get-ColumnsFromExcel {
    param (
        [string]$excelFilePath
    )

    $excelData = Import-Excel -Path $excelFilePath

    # SharePoint Field Type Mapping
    $FieldTypeMapping = @{
        "Single line of text"     = "Text"
        "Multiple lines of text"  = "Note"
        "Number"                  = "Number"
        "Currency"                = "Currency"
        "Yes/No"                  = "Boolean"
        "Choice"                  = "Choice"
        "Multi Choice"            = "MultiChoice"
        "Date and Time"           = "DateTime"
        "Hyperlink or Picture"    = "URL"
        "Person or Group"         = "User"
        "Lookup"                  = "Lookup"
    }

    $columns = @()

    foreach ($row in $excelData) {
        if (![string]::IsNullOrEmpty($row.'Display Name') -and (![string]::IsNullOrEmpty($row.'Internal Name'))) {

            $mappedType = $FieldTypeMapping[$row.'Data Type']

            if (-not $mappedType) {
                Write-Host "Invalid Data Type found in Excel: $($row.'Data Type')" -ForegroundColor Red
                continue
            }

            $column = @{
                DisplayName = $row.'Display Name'
                InternalName = $row.'Internal Name'
                Type = $mappedType
                Choices = if ($mappedType -eq "Choice" -or $mappedType -eq "MultiChoice") { [string[]]($row.Choices -split '\s*,\s*') } else { @() }
                Required = if ($row.'Is Mandatory' -eq "TRUE" -or $row.'Is Mandatory' -eq "Yes") { $true } else { $false }
            }

            $columns += New-Object -TypeName PSObject -Property $column
        }
        else {
            Write-Host "Skipping Empty Column in Excel..." -ForegroundColor Yellow
        }
    }

    return $columns
}

# Function to Create List and Columns from Excel Data
function Create-ListFromExcel {
    param (
        [string]$listName,
        [string]$excelFilePath,
        [string]$ListDisplayName
    )

    $columns = Get-ColumnsFromExcel -excelFilePath $excelFilePath
    $list = Get-PnPList | Where-Object { $_.Title -eq $ListDisplayName }

    # Create List if not exists
    if (-not $list) {
        New-PnPList -Title $ListDisplayName -Template GenericList -OnQuickLaunch
        Write-Host "List '$ListDisplayName' created!" -ForegroundColor Green

    
    # Create Columns
    foreach ($column in $columns) {
        Write-Host "Creating Field: $($column.DisplayName) of Type $($column.Type)" -ForegroundColor Cyan

        if (($column.Type -eq "Choice" -or $column.Type -eq "MultiChoice") -and $column.Choices.Count -gt 0) {
            Add-PnPField -List $ListDisplayName -DisplayName $column.DisplayName -InternalName $column.InternalName -Type $column.Type -AddToDefaultView -ErrorAction SilentlyContinue
            Set-PnPField -List $ListDisplayName -Identity $column.InternalName -Values @{Choices = [string[]]$column.Choices}
        }
        
        else {
            Add-PnPField -List $ListDisplayName -DisplayName $column.DisplayName -InternalName $column.InternalName -Type $column.Type -AddToDefaultView -ErrorAction SilentlyContinue
        }

        if ($column.Required -eq $true) {
            Set-PnPField -List $ListDisplayName -Identity $column.InternalName -Values @{Required = $true}
        }
    }

    Write-Host "All columns added successfully!" -ForegroundColor Green
    }
    else {
        Write-Host "List '$ListDisplayName' already exists!" -ForegroundColor Yellow
    }

}

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

Create-ListFromExcel -listName $listName -excelFilePath $excelFilePath -ListDisplayName $ListDisplayName

1. Update the parameters below in the script:

• $siteUrl = Provide the SharePoint site URL where you need to create the list.
• $excelFilePath = Provide the Excel file path.
• $listName = Provide the list internal name.
• $ListDisplayName = Provide list display name.
• $ClientID = Provide your Microsoft Entra ID registered application ID.

2. The Get-ColumnsFromExcel function takes Excel as input and then performs the following actions:

• $excelData = In this variable, we’re storing the Excel file data.
• $columns = @() Created an empty array to store each field’s data in an array of