Synchronizing SharePoint Online Site Users with a SharePoint List Using PnP PowerShell

Introduction

Understanding who has access to a SharePoint Online site is fundamental for governance, auditing, migration planning, security reviews, and workflow automation. While exporting users to a CSV file is useful for one-time reporting, organizations often require a persistent repository that can be consumed by other Microsoft 365 services such as Power Automate, Power BI, SharePoint dashboards, or custom SPFx solutions.

This article presents a complete PowerShell solution that synchronizes the SharePoint User Information List with a custom SharePoint list. Unlike a traditional export, the solution automatically creates the destination list, provisions all required columns, validates users against Microsoft Entra ID, and keeps the information synchronized over time.

The result is a reusable governance dataset that remains inside SharePoint itself, making it immediately available to the entire Microsoft 365 ecosystem.

Why Store Users in a SharePoint List?

Although Get-PnPUser provides an easy way to retrieve users from a site, its output exists only during script execution.

Persisting this information inside SharePoint provides several advantages:

Centralized governance repository
Native integration with Power Automate
Easy reporting through Power BI
Searchable user inventory
Historical synchronization
No dependency on CSV files
Data accessible from SPFx, REST and Microsoft Graph

Instead of generating temporary reports, the script builds a reusable data source.

Solution Overview

The synchronization process follows these steps:

Verify that PnP PowerShell is installed.
Connect to SharePoint Online using Interactive Authentication.
Validate the connection.
Create the SharePoint list if it does not already exist.
Create all required columns automatically.
Read all users registered in the SharePoint User Information List.
Validate each account against Microsoft Entra ID.
Create new records when necessary.
Update existing records without creating duplicates.
Produce a synchronization summary.

This design allows the script to be executed repeatedly without recreating the list or generating duplicate entries.

Automatic List Provisioning

One of the major advantages of the solution is that no manual preparation is required.

If the destination list does not exist, the script automatically creates it and provisions every required column.

This allows administrators to execute the script on any SharePoint site without performing any manual configuration beforehand.

SharePoint Columns

The script creates the following columns.

Column	Purpose
Title	SharePoint default title
DisplayName	User display name
UserEmail	User email address
LoginName	SharePoint claims login
PrincipalType	User or security principal type
IsSiteAdmin	Indicates whether the user is a Site Collection Administrator
UserExists	Indicates whether the account still exists in Microsoft Entra ID
AccountEnabled	Indicates whether the Entra ID account is enabled
SiteTitle	Current SharePoint site title
SiteUrl	Current SharePoint site URL
LastSeen	Last synchronization date
ExtractionDate	Date of the current synchronization

These columns provide enough information for governance, reporting, automation and auditing scenarios.

User Synchronization

Rather than deleting and recreating records every time the script runs, the solution performs a synchronization.

Each SharePoint user is identified by its LoginName.

If the LoginName already exists in the destination list, the existing item is updated.

If the LoginName does not exist, a new list item is created.

This approach prevents duplicate records and allows the SharePoint list to become the authoritative inventory of site users.

Microsoft Entra ID Validation

One of the most valuable features of this solution is the validation performed against Microsoft Entra ID.

The script uses:

Get-PnPEntraIDUser

to determine whether the account still exists.

This information is stored in the UserExists column.

Additionally, the script stores the value of AccountEnabled, allowing administrators to distinguish between:

Active users
Disabled accounts
Accounts removed from the tenant

This capability is particularly useful during migrations and periodic security reviews.

Historical Synchronization

Because existing items are updated rather than deleted, the SharePoint list gradually becomes a historical repository.

Every synchronization updates:

LastSeen
ExtractionDate
Site information
Account status

This information can later be analyzed to identify inactive users, disabled accounts, or orphaned permissions.

Power Automate Integration

One of the primary motivations for storing the data in SharePoint is automation.

Power Automate can easily monitor the list and perform actions such as:

Notify site owners when disabled users are detected.
Send periodic governance reports.
Trigger approval workflows.
Create Microsoft Teams notifications.
Generate compliance dashboards.
Synchronize data with external systems.

Since the data is already stored in SharePoint, no additional connectors or file parsing are required.

Power BI Integration

The SharePoint list can also serve as a Power BI data source.

Typical reports include:

Users per site
Site administrators
Disabled accounts
Users no longer found in Microsoft Entra ID
Principal type distribution
Governance dashboards
Tenant-wide inventories

Unlike CSV files, the SharePoint list remains continuously available for scheduled refreshes.

Benefits of the Solution

Compared to a traditional CSV export, this synchronization approach provides several advantages.

Automatic list provisioning
Automatic column provisioning
Duplicate prevention
User validation against Microsoft Entra ID
Historical synchronization
Native Microsoft 365 integration
Power Automate ready
Power BI ready
SPFx ready
Governance ready

Future Enhancements

This solution establishes the foundation for a complete SharePoint governance toolkit.

Future versions may include:

SharePoint Groups
Permission Levels
Microsoft 365 Groups
Security Groups
Azure AD Roles
Site Collection Administrators
Library Permissions
Folder Permissions
Item-Level Permissions
Incremental synchronization
Multi-site inventory
Tenant-wide reporting

Conclusion

This synchronization script goes far beyond a simple export utility. It creates a living repository of SharePoint site users that can be reused throughout the Microsoft 365 ecosystem. By combining PnP PowerShell, SharePoint lists, and Microsoft Entra ID validation, administrators gain a reliable and extensible foundation for governance, reporting, automation, and security analysis.

As the first synchronization component in a broader administration toolkit, this solution demonstrates how native Microsoft 365 services can be combined to build scalable governance processes without relying on external databases or third-party tools.