Getting List Items from a SharePoint List using PnP PowerShell

Retrieving list items from a SharePoint Online list is a common requirement when working with SharePoint data. PnP PowerShell provides powerful commands to fetch, filter, and export list items efficiently.

This guide will cover:
How to connect to SharePoint Online
How to retrieve list items from a SharePoint list
How to filter and export data

Prerequisites

Before proceeding, ensure you have:
PnP PowerShell installed
Admin or Site Owner permissions
The SharePoint site URL and list name

Step 1: Install and Import PnP PowerShell

If you haven’t installed PnP PowerShell, install it using:

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

Then, import the module:

Import-Module PnP.PowerShell

PnP PowerShell is ready!

Step 2: Connect to SharePoint Online

To retrieve list items, first, connect to the SharePoint site:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

🔹 Replace "yourtenant" with your tenant name
🔹 Replace "yoursite" with the site name where the list exists

Connected successfully!

Step 3: Retrieve All Items from a SharePoint List

To fetch all list items, use:

# Get all items from a list
Get-PnPListItem -List "Employee Directory"

This retrieves all items from the “Employee Directory” list.

Step 4: Retrieve Specific Columns

You can fetch only required columns instead of the entire item:

# Get specific columns
Get-PnPListItem -List "Employee Directory" -Fields "Title", "Department", "JoiningDate"

This fetches Title, Department, and Joining Date fields only.

Step 5: Filtering List Items

You can filter results using CAML Queries or Where-Object.

1️⃣ Using Where-Object

# Get employees from HR department
Get-PnPListItem -List "Employee Directory" | Where-Object { $_["Department"] -eq "HR" }

This retrieves only employees from the HR department.

2️⃣ Using CAML Query (Efficient for large lists)

# Filter list items using CAML Query
$camlQuery = @"
<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='Department' />
                <Value Type='Text'>HR</Value>
            </Eq>
        </Where>
    </Query>
</View>
"@

Get-PnPListItem -List "Employee Directory" -Query $camlQuery

This retrieves items faster for large lists.

Step 6: Export List Items to a CSV File

To export list data for reports or analysis:

# Export list items to CSV
$items = Get-PnPListItem -List "Employee Directory" | Select Title, Department, JoiningDate
$items | Export-Csv -Path "C:\SharePointListItems.csv" -NoTypeInformation

This saves the list data as a CSV file.

Step 7: Retrieve Items with Pagination

If a list has thousands of records, use pagination:

# Get items in batches
Get-PnPListItem -List "Employee Directory" -PageSize 100

This fetches 100 items per request, optimizing performance.

Common Errors & Troubleshooting

Error	Cause	Solution
`Access Denied`	Insufficient permissions	Ensure you have Read or Admin rights
`Cannot find list 'Employee Directory'`	List name is incorrect	Use `Get-PnPList` to check the list name
`Column not found`	Column name is incorrect	Use `Get-PnPField -List "Employee Directory"` to verify column names
`PnP PowerShell module not found`	PnP PowerShell is not installed	Run `Import-Module PnP.PowerShell`