Get SharePoint List Column Details [Internal Name, Display Name, Data Type] Using PnP PowerShell

A few weeks before, I developed a Power Apps application for a client that takes data sources as a SharePoint list. There are almost six lists, and each list has more than 20 fields.

Due to some permission issues, we had to manually create all the lists and columns on the SharePoint site. So I had to fetch each SharePoint list column’s internal name and display name along with data type into an Excel file so that he could create it using it as a reference.

You can get the SharePoint Online list column names using PnP PowerShell, and it is also easy to export to an Excel file.

In this article, I will explain how to get SharePoint list columns’ internal and display names along with their data types using PnP PowerShell.

Get SharePoint List Column Internal Name, Display Name, Data Type Using PnP PowerShell

In the image below, you can see the Excel spreadsheet containing the internal name, display name, and data type of each column, which is created by users that are present in the provided SharePoint list.

Here, I’m filtering the columns, such as the default created columns, like Created, Modified by, Content type, ID, etc. So, these fields are excluded, and the remaining fields are included in the Excel.

Here is the PnP PowerShell script that fetches the user-created columns on the SharePoint list. Run this script on Visual Studio Code or Windows PowerShell ISE platforms.

Connect-PnPOnline -Url "https://{tenantname}.sharepoint.com/sites/{sitename}" -ClientId "Provide your client ID" -Interactive
$listName = "Travel Authorization"

# Get all columns from the SharePoint Online list
$Columns = Get-PnPField -List $listName
$ColumnDetails = @()

# iterate over the each column and get internal name, display name, and data type
foreach ($Column in $Columns ) {
    # Exclude hidden and system columns
    if (-not $field.Hidden -and -not $field.ReadOnlyField) {
        $ColumnDetails += New-Object PSObject -property @{
            "Internal Name" = $Column.InternalName
            "Display Name"  = $Column.Title
            "Data Type"     = $Column.TypeAsString
        }
    }
}

Install-Module -Name ImportExcel -Force

$ColumnDetails | Export-Excel -Path "D:\MyFolder\TravelAuthorization.xlsx" -AutoSize -TableName "Travel Authorization"

Here,

• For Connect-PnPOnline:
  • -URL = Provide your SharePoint site URL.
  • -ClientId =Provide your client ID; if you don’t have an ID, follow this article to create it.
• $listName = Provide the SharePoint list name.
• Get-PnPField = It fetches all fields from the current site.
  • -List $listName = Fetches fields from the provided list.
• $ColumnDetails = @(). It creates an empty array to store all the field details.

foreach ($Column in $Columns ) {
    # Exclude hidden and system columns
    if (-not $field.Hidden -and -not $field.ReadOnlyField) {
        $ColumnDetails += New-Object PSObject -property @{
            "Internal Name" = $Column.InternalName
            "Display Name"  = $Column.Title
            "Data Type"     = $Column.TypeAsString
        }
    }
}

Using the foreach loop, we’re iterating through each field present in the array $Columns. Then, if the field’s hidden property is not true and not the read-only field, then we’re including it in the $ColumnDetails custom object.

This $ColumnDetails object has three properties:

• Internal Name
• Display Name
• Data Type

The Export-Excel cmd will take the data present on the $ColumnDetails object and export it to Excel.

• -Path = Provide the path address where to store this Excel.
• -AutoSize = Optional, takes the auto size.
• -TableName = Provide the table name,

Save the changes and make sure to provide the site URL, list name, and client ID, then run the script. It will create an Excel with column names like in the above image.

I hope you understand how to get the SharePoint list column’s details like internal name, display name, and data type using PnP PowerShell. Do let me know in the comment below if you have any questions.

Also, you may like:

• Download All Files From A SharePoint Online Document Library Using PnP PowerShell
• SharePoint List Items CRUD Operations Using PnP PowerShell
• Customizing SharePoint List Fields with PnP PowerShell