PowerShell try/catch Is Not Catching Your Error

You wrapped something in try/catch, it failed, and the catch block never ran. The error printed in red, the script carried on, and whatever you put in the catch was ignored.

This is not a bug and it is not you. PowerShell has two kinds of error and try/catch only sees one of them.

Everything below was run on PowerShell 7.5.8.

The demonstration

try {
    Get-Item 'C:\does\not\exist\nothing.txt'
    "...execution continued past the failing line"
} catch {
    "CAUGHT: $($_.Exception.Message)"
}
Get-Item:
Line |
   6 |      Get-Item $missing
     |      ~~~~~~~~~~~~~~~~~
     | Cannot find path 'C:\does\not\exist\nothing.txt' because it does not exist.
...execution continued past the failing line

The error was written out. The catch block did not run. The line after it did.

Now the same thing with four extra words:

try {
    Get-Item 'C:\does\not\exist\nothing.txt' -ErrorAction Stop
} catch {
    "CAUGHT: $($_.Exception.Message)"
}
CAUGHT: Cannot find path 'C:\does\not\exist\nothing.txt' because it does not exist.

Why

Most cmdlet errors are non-terminating. The cmdlet reports a problem and PowerShell carries on, because when you pipe a thousand items into something you usually want the other 999 to be processed.

try/catch only reacts to terminating errors.

-ErrorAction Stop promotes a non-terminating error to a terminating one for that call. That is the whole trick, and it is why so much published PowerShell has try/catch blocks in it that do nothing at all.

Write-Error is not throw

Worth knowing if you are writing your own functions:

try { Write-Error 'this is a Write-Error' } catch { "caught" }
try { throw 'this is a throw' }             catch { "caught: $($_.Exception.Message)" }
Write-Error: this is a Write-Error
caught: this is a throw

Write-Error raises a non-terminating error and is not caught. throw is terminating and is.

So if you write a function that reports failure with Write-Error, nobody calling it can catch that failure unless they also pass -ErrorAction Stop. Use throw when you mean stop.

Turning it on for a whole script

Rather than putting -ErrorAction Stop on every line:

$ErrorActionPreference = 'Stop'
CAUGHT without -ErrorAction on the cmdlet

Put that at the top of a script and every cmdlet error becomes terminating. For an admin script that changes things, this is almost always what you want – stopping on the first problem beats carrying on and doing half the job.

The important thing to note is that it is scoped. Set it inside a function and it applies there, not to the caller. That is useful, and it also means setting it in your profile does not protect your scripts.

Catching one kind of error and not others

A bare catch catches everything, including the failures you had not thought about. You can be specific:

try {
    Get-Item $path -ErrorAction Stop
} catch [System.Management.Automation.ItemNotFoundException] {
    "matched the typed catch: file not found"
} catch {
    "fell through to the generic catch"
}
matched the typed catch: file not found

To find the type name for something you are actually hitting, let it fail once and ask:

$Error[0].Exception.GetType().FullName
$Error[0].FullyQualifiedErrorId
$Error[0].CategoryInfo.Category
System.Management.Automation.ItemNotFoundException
PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand
ObjectNotFound

FullyQualifiedErrorId is the precise one. It names both the problem and the cmdlet that raised it, which is what you want when two different cmdlets can produce the same exception type.

Collecting errors without stopping

Sometimes you want the loop to finish and a list of what went wrong:

Get-Item $path -ErrorAction SilentlyContinue -ErrorVariable itemErr

$itemErr.Count
$itemErr[0].Exception.Message
1
Cannot find path 'C:\does\not\exist\nothing.txt' because it does not exist.

Use +itemErr with a plus sign to append across a loop rather than overwrite each time.

The one that catches everybody: native commands

try/catch does not work on robocopy, git, msiexec or anything else that is not a cmdlet.

try {
    cmd.exe /c "exit 7"
    "no exception was raised"
} catch {
    "caught"
}
no exception was raised

External programs do not raise PowerShell exceptions. They set an exit code, and you have to check it yourself:

cmd.exe /c "exit 7"
$?              # False
$LASTEXITCODE   # 7

Both measured immediately after the call – and that matters, because $? reflects the last thing that happened. Put a Write-Host between the command and the check and you are reading the result of the Write-Host. I made exactly that mistake while testing this post.

So for anything external:

robocopy $source $dest /E
if ($LASTEXITCODE -ge 8) { throw "robocopy failed with $LASTEXITCODE" }

Note the -ge 8. Robocopy uses 0-7 for various kinds of success, so testing -ne 0 would fail every time it copied something. Check what your tool’s exit codes actually mean rather than assuming zero is the only good one.

PowerShell 7 can do this for you

There is a setting for it, and it is off by default:

$PSNativeCommandUseErrorActionPreference   # False

Turn it on, with $ErrorActionPreference set to Stop:

$PSNativeCommandUseErrorActionPreference = $true
$ErrorActionPreference = 'Stop'

try { cmd.exe /c "exit 7" } catch { "caught = true" }
caught = true

A non-zero exit code now raises a terminating error you can catch like anything else. Worth knowing, and worth being careful with – anything that uses non-zero exit codes for non-failures, robocopy included, will start throwing at you.

finally

try {
    Get-Item $path -ErrorAction Stop
} catch {
    "catch ran"
} finally {
    "finally ran"
}
catch ran
finally ran

Runs whether or not anything failed. Use it to disconnect, release, or clean up temporary files – the things that should happen regardless.

What I actually put in a script

$ErrorActionPreference = 'Stop'

try {
    # everything that changes something
}
catch {
    Write-Error "Failed at $($_.InvocationInfo.ScriptLineNumber): $($_.Exception.Message)"
    throw
}
finally {
    Disconnect-PnPOnline -ErrorAction SilentlyContinue
}

$_.InvocationInfo.ScriptLineNumber tells you where it broke, which the message on its own does not. And the bare throw at the end re-raises the original error rather than swallowing it, so whatever called your script still finds out it failed.

Catching an error and then carrying on quietly is worse than not catching it. At least the red text was honest 🙂

PowerShell: Running Scripts Is Disabled on This System

You try to run a script and PowerShell refuses:

File C:\Scripts\demo.ps1 cannot be loaded because running scripts is disabled
on this system. For more information, see about_Execution_Policies at
https:/go.microsoft.com/fwlink/?LinkID=135170.
    + CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnauthorizedAccess

Or this one, which is a different problem with the same cause:

File C:\Scripts\demo.ps1 cannot be loaded. The file C:\Scripts\demo.ps1 is not
digitally signed. You cannot run this script on the current system.

Every answer you will find says to run Set-ExecutionPolicy. That is usually right and often does not work, because there are three separate things that produce these messages and only one of them is fixed that way.

First, what execution policy is not

It is not a security boundary. Microsoft say so themselves in the documentation. Anyone who can run PowerShell can bypass it in about four keystrokes, and I will show you how further down.

It exists to stop you double-clicking something by accident. Treat it as a seatbelt, not a lock.

The five scopes, and which one is winning

This is the part that catches people. There is not one execution policy, there are five, and they override each other in a fixed order:

MachinePolicy  ->  UserPolicy  ->  Process  ->  CurrentUser  ->  LocalMachine

Highest wins. So Get-ExecutionPolicy on its own tells you the effective answer but not which scope produced it. Always ask for the list:

Get-ExecutionPolicy -List

Here is my own machine:

        Scope ExecutionPolicy
        ----- ---------------
MachinePolicy       Undefined
   UserPolicy       Undefined
      Process          Bypass
  CurrentUser       Undefined
 LocalMachine    RemoteSigned

The effective policy is Bypass, not RemoteSigned. Something set it at Process scope for this session, and Process beats LocalMachine.

The important thing to note is that MachinePolicy and UserPolicy come from Group Policy. If either of those is set, nothing you type will change anything, and you will spend an afternoon finding out. Check those two first.

Fix 1 – set it for yourself only

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

-Scope CurrentUser does not need administrator rights, and it does not change the machine for anybody else. On a shared or managed machine that matters.

RemoteSigned is the sensible setting: scripts you wrote locally run, scripts that came from somewhere else need a signature. It is the default on Windows Server for that reason.

Fix 2 – bypass it for one command

If you cannot change the policy, or do not want to:

powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\Scripts\demo.ps1

This changes nothing permanently. It applies to that one process and then it is gone.

I ran the same blocked script three ways to show the difference:

-ExecutionPolicy Restricted    "running scripts is disabled on this system"
-ExecutionPolicy AllSigned     "is not digitally signed"
-ExecutionPolicy Bypass        the script ran

Which is also the demonstration that execution policy is not security. Four extra words on the command line and the “blocked” script runs.

Fix 3 – the one nobody mentions

Here is the case that makes people think the policy is broken.

Your policy is RemoteSigned. You wrote a script locally, it runs fine. A colleague emails you one, or you download it, and it refuses – with the “not digitally signed” message rather than the “disabled on this system” one.

The policy is correct. The file is marked.

Windows adds a hidden alternate data stream to anything that arrives from the internet or an email client. You can see it:

Get-Item .\demo.ps1 -Stream *
Stream          Length
------          ------
:$DATA              30
Zone.Identifier     26

That Zone.Identifier stream is the mark of the web, and RemoteSigned is doing exactly what it says – treating the file as remote.

Remove it:

Unblock-File -Path .\demo.ps1

Same script, same policy, straight afterwards:

the script ran

Note that Unblock-File only removes the mark. It does not weaken your policy and it does not affect any other file. It is the right fix for this case, and changing the execution policy to work around it is the wrong one.

For a folder of scripts:

Get-ChildItem .\Scripts\*.ps1 -Recurse | Unblock-File

Fix 4 – you fixed the wrong PowerShell

This one cost me time, so it is worth knowing.

Windows PowerShell 5.1 and PowerShell 7 keep their execution policies in completely separate places. Setting it in one does nothing for the other.

They are different registry keys:

Windows PowerShell 5.1  HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell
PowerShell 7 (Core)     HKLM\SOFTWARE\Microsoft\PowerShellCore\ShellIds\Microsoft.PowerShell

On my machine 5.1 has RemoteSigned set at both LocalMachine and CurrentUser, and the PowerShell 7 keys do not exist at all.

So if you opened the blue window, ran Set-ExecutionPolicy, and then went back to the black window and got the same error – that is why. Run $PSVersionTable.PSVersion in whichever one is refusing, and fix that one.

The order I would check them in

  1. Get-ExecutionPolicy -List – find which scope is actually winning
  2. If MachinePolicy or UserPolicy is set, stop. That is Group Policy and it is not your decision
  3. Get-Item .\script.ps1 -Stream * – if there is a Zone.Identifier, Unblock-File is your fix
  4. Check which PowerShell you are in
  5. Then, and only then, Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Four of those five cost you nothing and change nothing. Reaching for Set-ExecutionPolicy first is how people end up with an unrestricted machine and a script that still does not run 🙂

Find a SharePoint List’s Internal Column Names with PowerShell

You write a script against a SharePoint list, ask for a column by the name you can see in the browser, and get nothing back. No error, just an empty value.

The column is called Project Status on screen. It is not called that underneath.

Run against a real list on PnP.PowerShell 2.12.0.

Every column has two names

The Title is the label in the browser. Anyone can change it and it can contain anything.

The InternalName is the key. It is set once, when the column is created, and it never changes again.

Here is the difference in one line:

$item = Get-PnPListItem -List 'Projects' -Id 1

$item['ProjectStatus']    # internal name
$item['Project Status']   # display name
By internal name 'ProjectStatus' : 'Complete'
By display name 'Project Status' : ''

An empty string, not an error. That is why this is so easy to lose an hour to – the script runs, it just quietly does nothing.

Getting the list

The obvious command gives you far more than you want:

Get-PnPField -List 'Projects'

On a list where I had created exactly five columns:

Total fields          : 90
Hidden                : 61
Inherited from base   : 83
Actually yours        : 5

Ninety. So filter it down to the ones you actually made:

Get-PnPField -List 'Projects' |
    Where-Object { -not $_.FromBaseType -and -not $_.Hidden } |
    Select-Object Title, InternalName, TypeAsString |
    Format-Table -AutoSize
Title          InternalName  TypeAsString
-----          ------------  ------------
Project Status ProjectStatus Choice
Project Owner  ProjectOwner  User
Due Date       DueDate       DateTime
Budget         Budget        Currency
Notes          Notes         Note

FromBaseType is the useful one. It is true for everything SharePoint gave you and false for everything you added.

What happens to spaces

Those internal names are clean because they were created by script. Columns created in the browser are not, because SharePoint derives the internal name from whatever you typed.

I made one with a space on purpose:

Add-PnPField -List 'Projects' -DisplayName 'Demo Review Date' `
             -InternalName 'Demo Review Date' -Type DateTime
Title        : Demo Review Date
InternalName : Demo_x0020_Review_x0020_Date
StaticName   : Demo_x0020_Review_x0020_Date

Every space becomes _x0020_. A column called “Date of Last Review” ends up as Date_x0020_of_x0020_Last_x0020_Review, and that is what your script has to use.

This is also why you should create columns with a short name and rename them afterwards, which brings us to the thing that actually catches people.

Renaming does not change the internal name

I renamed that column and looked again:

Set-PnPField -List 'Projects' -Identity 'Demo Review Date' -Values @{ Title = 'Renamed Review Date' }
Title        : Renamed Review Date
InternalName : Demo_x0020_Review_x0020_Date

The label changed. The key did not, and it never will.

So a column that says Review Date today might be Temp_x0020_Column underneath because of what somebody typed two years ago. You cannot work out the internal name by looking at the browser. You have to ask.

The practical version of that: create the column with the internal name you want, then rename it to whatever reads well. Give it ReviewDate first, then change the label to “Date of Last Review”. You get a clean key and a readable heading.

Built-in columns are worse

Some of the ones you use most have internal names with no relationship to the label at all:

Title        InternalName TypeAsString
-----        ------------ ------------
Attachments  Attachments  Attachments
Created By   Author       User
Content Type ContentType  Computed
Created      Created      DateTime
Modified By  Editor       User
Name         FileLeafRef  File
URL Path     FileRef      Lookup
ID           ID           Counter
Modified     Modified     DateTime
Title        Title        Text

Created By is Author. Modified By is Editor. Those two are the ones everybody hits, and there is nothing on screen that hints at it.

In a document library it gets stranger still:

Title        InternalName    TypeAsString
-----        ------------    ------------
Created By   Author          User
Type         DocIcon         Computed
Modified By  Editor          User
File Size    File_x0020_Size Lookup
Name         FileLeafRef     File
URL Path     FileRef         Lookup

A file’s Name is FileLeafRef. Its path is FileRef. And File Size carries the _x0020_ encoding as a built-in, which tells you Microsoft named it in a browser too.

One thing that will not work at all

I tried to make a column called Cost & Time-Frame to see what an ampersand does:

An error occurred while parsing EntityName. Line 1, position 32.

Not an encoded name – a failure. PnP builds field XML underneath, and a raw ampersand breaks the parse before SharePoint ever sees it. The error mentions XML parsing and does not mention your column, which is not helpful when you are three lines into a provisioning script.

Note that this is a script limitation. The browser will happily create that column and encode it for you. So this is one of the few cases where the UI does something PowerShell will not.

The line worth keeping

Get-PnPField -List 'YourList' |
    Where-Object { -not $_.FromBaseType -and -not $_.Hidden } |
    Select-Object Title, InternalName, TypeAsString |
    Sort-Object Title

Run it before you write anything against a list you did not create yourself. Thirty seconds, and it saves the empty-string hour 🙂

What Copilot Chat (Basic) Can and Cannot Do

In this post, I am going to show you what you actually get on Copilot Chat (Basic) – the tier you have without buying a Microsoft 365 Copilot license – and exactly where it stops.

I went looking because I wanted to build an agent over some SharePoint documents and could not. Everything below is from my own tenant, on an account with no Copilot license.

Where to check what tier you are on

Open Copilot Chat and look at the bottom left, under your name. Mine says Copilot Chat (Basic).

Worth checking before you conclude anything is broken. A lot of “Copilot cannot see my files” questions are this, and the interface does not otherwise tell you.

Microsoft 365 Copilot Chat showing the Copilot Chat (Basic) tier label under the account name in the bottom left corner

What works

Chat itself. Full conversational Copilot, grounded on the web.

Creating documents, charts and code. Agent Builder shows Word, Excel and PowerPoint capabilities enabled by default, alongside image generation.

Prompt Lab, behind the at the end of the suggestion chips under the message box.

Organization prompts. This one surprised me, and it is the most useful thing in this post.

Prompts published by an admin in the Microsoft 365 admin center appear in Prompt Lab on a Basic account, labelled Promoted by your organization. I had assumed they were a licensed feature. They are not.

So if you are rolling out Copilot to a mixed estate where some people are licensed and some are not, your published prompts still reach everybody. That is a genuinely useful thing to know before you decide who needs a license.

Where it stops – work data

Basic cannot see your Microsoft 365 content. Not SharePoint, not OneDrive, not email.

I put four invented policy documents into a SharePoint library, waited for them to index, and asked a question whose answer is in one of them:

What is the accommodation limit in London?

The document says 180 pounds. What I got back was a request to clarify, followed by an explanation of Airbnb short-let limits, employer allowances in general, university accommodation and Local Housing Allowance.

All from the web. It never touched the document. It did not say it could not reach my files – it simply answered a different question well.

That is the thing to watch for. It does not fail visibly. It gives you a competent, confident, entirely irrelevant answer, and if you did not know your file was there you would not necessarily notice.

Where it stops – agents

You can open Agent Builder from New agent in the left navigation. The configuration screen is all there – instructions, capabilities, suggested prompts.

Then look at the Knowledge section. On Basic it offers exactly three things:

  • Add specific websites
  • Search all websites
  • Only use specified sources

That is the entire list. There is no SharePoint option, no OneDrive, no Teams, no email. The picker does not appear greyed out with an upgrade prompt beside it. It is simply not there.

Agent Builder Knowledge section on an unlicensed account, offering only Add specific websites, Search all websites and Only use specified sources - no SharePoint, OneDrive or Teams

So an agent on Basic is a web agent. You can point it at documentation sites and it will answer from them. You cannot point it at your own content, which for most internal use cases is the entire reason you wanted an agent.

I have not tested whether the Create button completes, or whether website grounding actually works once an agent exists. I will update this when I have. What I can say for certain is what the Knowledge section offers, because that is the wall you hit first.

What this means if you are deciding on licenses

The split is cleaner than the marketing suggests. Basic gives you a good general assistant. A license gives you an assistant that knows about your organization.

Which means the question is not “who wants Copilot”, it is who needs it grounded on our own content. Somebody drafting emails and summarizing pasted text is well served by Basic. Somebody asking “what did we agree with this supplier” is not, and no amount of prompt engineering closes that gap.

Two practical consequences:

Publish your organization prompts regardless of licensing. They reach everyone, so they are the cheapest thing you can do to make Copilot useful across the whole organization.

Expect the confident wrong answer. The failure mode on Basic is not an error message, it is a plausible answer from the open web. If you are running a pilot with a mix of licensed and unlicensed users, they are not having the same experience, and the unlicensed ones may not realize it.

A note on names, because they keep changing. What my tenant calls Copilot Chat (Basic) has been through several names, and Microsoft’s own documentation does not always use the one your interface shows. Go by what is printed under your account, not by what a comparison table calls it ?

Update SharePoint List Items from a CSV with PowerShell

In this post, I am going to show you how to update SharePoint list items from a CSV, and the three ways it goes wrong – two of which happen silently. Full script at the end.

Run against a real list on PnP.PowerShell 2.12.0.

A CSV has no types

That is the whole problem in one sentence. Export a list and read it back:

$row = Import-Csv .\projects.csv | Select-Object -First 1
foreach ($p in $row.PSObject.Properties) {
    "{0,-10} {1,-18} value: {2}" -f $p.Name, $p.Value.GetType().Name, $p.Value
}
Id         String             value: 1
Title      String             value: Project 01
Status     String             value: Not Started
DueDate    String             value: 7/28/2026 5:13:57 PM
Budget     String             value: 1000
Notes      String             value: Seeded row 1 for PowerShell examples.

Everything is a String. The list on the other side has Choice, DateTime, Currency and Note columns. Handing one to the other is where it falls apart.

The version everybody writes first

$rows = Import-Csv .\changes.csv

# Build the lookup ONCE. Searching the list per row is the usual reason
# these scripts take hours instead of seconds.
$lookup = @{}
Get-PnPListItem -List 'Projects' -PageSize 500 | ForEach-Object {
    $lookup[[string]$_['Title']] = $_.Id
}

foreach ($row in $rows) {
    $id = $lookup[$row.Title]
    if (-not $id) { Write-Host "no match for '$($row.Title)'"; continue }

    Set-PnPListItem -List 'Projects' -Identity $id -Values @{
        ProjectStatus = $row.Status
        DueDate       = $row.DueDate
        Budget        = $row.Budget
    }
}

I fed it seven rows, four good and three broken on purpose:

updated Project 01
updated Project 02
updated Project 03
updated Project 04
FAILED Project 05: The string was not recognized as a valid DateTime.
no match for 'Project 99'
updated Project 06

Looks like it mostly worked. It did not.

Failure one: an invalid Choice value is written anyway

Row four set the status to On Hold. That is not one of the four choices on that column. Look at what happened:

Project 04 status is now: 'On Hold'

Valid choices are:
Not Started
In Progress
Blocked
Complete

SharePoint does not validate Choice values on write. It took a value that was never an option, stored it, and said nothing. It reported success.

The column now holds something no dropdown in the interface can produce. Your filtered views miss it, your Power Automate conditions do not match it, and nothing anywhere flags it. Do this to two thousand rows and you will find out months later.

You have to check it yourself:

$field = Get-PnPField -List 'Projects' -Identity 'ProjectStatus'
if ($value -notin @($field.Choices)) {
    # reject it - SharePoint will not
}

Failure two: an empty cell deletes data

Row six had an empty Budget. Not zero – empty. Afterwards:

Title      Status    DueDate              Budget
-----      ------    -------              ------
Project 06 Complete  7/1/2026 12:00:00 AM

The Budget is gone. It was 6000 before.

An empty cell is treated as “clear this field”, not “leave this alone”. Which is defensible, but it is the opposite of what most people mean when they send a spreadsheet with a few columns filled in. If your CSV only carries the columns you meant to change, every blank cell in it is a deletion.

Both behaviours are reasonable, so decide which one you want rather than finding out.

Failure three: one bad cell loses the whole row

Row five had not a date in the DueDate column, and it threw. That part is fine – loud is good.

What is not fine is that Status and Budget on that row were not written either. The update is one call per item, so a single unusable value throws all of it away. Project 05 kept its old status, its old date and its old budget.

Validate before sending and the good values survive:

[5] Project 05: ProjectStatus=Complete, Budget=5500

DueDate is skipped and reported, the other two go through.

The whole script

Reads the list’s real schema, validates every value against its actual field type, batches the writes, and supports -WhatIf:

[CmdletBinding(SupportsShouldProcess)]
param(
    [Parameter(Mandatory)] [string]    $SiteUrl,
    [Parameter(Mandatory)] [string]    $ClientId,
    [Parameter(Mandatory)] [string]    $List,
    [Parameter(Mandatory)] [string]    $CsvPath,
    [string]    $KeyColumn = 'Title',
    [hashtable] $ColumnMap = @{},
    [switch]    $ClearEmptyValues
)

$ErrorActionPreference = 'Stop'
if (-not (Test-Path $CsvPath)) { throw "CSV not found: $CsvPath" }

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

$rows = @(Import-Csv -Path $CsvPath)
if ($rows.Count -eq 0) { throw "CSV is empty." }
Write-Host "Rows in CSV : $($rows.Count)" -ForegroundColor Cyan

# Read the real field types rather than assuming the CSV headers mean anything.
$fields = @{}
foreach ($f in Get-PnPField -List $List) { $fields[$f.InternalName] = $f }

$csvColumns = $rows[0].PSObject.Properties.Name | Where-Object { $_ -ne $KeyColumn }

$targets = @{}
foreach ($col in $csvColumns) {
    $internal = if ($ColumnMap.ContainsKey($col)) { $ColumnMap[$col] } else { $col }
    if (-not $fields.ContainsKey($internal)) {
        Write-Warning "CSV column '$col' maps to '$internal', which is not a field on '$List'. Ignored."
        continue
    }
    $targets[$col] = $fields[$internal]
}
if ($targets.Count -eq 0) { throw "No CSV columns matched fields on '$List'." }

Write-Host "Mapped columns:" -ForegroundColor Cyan
foreach ($col in $targets.Keys | Sort-Object) {
    "  {0,-14} -> {1,-18} ({2})" -f $col, $targets[$col].InternalName, $targets[$col].TypeAsString
}

# One pass to index the list. Searching per row is what makes these slow.
Write-Host "`nBuilding lookup..." -ForegroundColor Cyan
$lookup = @{}
$duplicates = [System.Collections.Generic.List[string]]::new()
foreach ($item in Get-PnPListItem -List $List -PageSize 500) {
    $key = [string]$item[$KeyColumn]
    if ([string]::IsNullOrWhiteSpace($key)) { continue }
    if ($lookup.ContainsKey($key)) { $duplicates.Add($key); continue }
    $lookup[$key] = $item.Id
}
Write-Host "  $($lookup.Count) item(s) indexed on '$KeyColumn'"
if ($duplicates.Count) {
    Write-Warning "$($duplicates.Count) duplicate key(s) - only the first of each was indexed."
}

function Convert-Value {
    param($Raw, $Field)
    $type = $Field.TypeAsString

    if ([string]::IsNullOrWhiteSpace($Raw)) {
        if ($ClearEmptyValues) { return @{ Ok = $true; Value = $null } }
        return @{ Ok = $false; Reason = 'empty (skipped)' }
    }

    switch -Regex ($type) {
        '^Choice$|^MultiChoice$' {
            # The check SharePoint does not do for you.
            $valid = @($Field.Choices)
            if ($Raw -notin $valid) {
                return @{ Ok = $false; Reason = "'$Raw' is not a valid choice (allowed: $($valid -join ', '))" }
            }
            return @{ Ok = $true; Value = $Raw }
        }
        '^DateTime$' {
            $parsed = [datetime]::MinValue
            if (-not [datetime]::TryParse($Raw, [ref]$parsed)) {
                return @{ Ok = $false; Reason = "'$Raw' is not a date" }
            }
            return @{ Ok = $true; Value = $parsed }
        }
        '^Number$|^Currency$' {
            $parsed = 0.0
            if (-not [double]::TryParse($Raw, [ref]$parsed)) {
                return @{ Ok = $false; Reason = "'$Raw' is not a number" }
            }
            return @{ Ok = $true; Value = $parsed }
        }
        '^Boolean$' {
            if ($Raw -in @('1','true','True','yes','Yes')) { return @{ Ok = $true; Value = $true } }
            if ($Raw -in @('0','false','False','no','No')) { return @{ Ok = $true; Value = $false } }
            return @{ Ok = $false; Reason = "'$Raw' is not a yes/no value" }
        }
        default { return @{ Ok = $true; Value = $Raw } }
    }
}

$plan     = [System.Collections.Generic.List[object]]::new()
$problems = [System.Collections.Generic.List[object]]::new()

foreach ($row in $rows) {
    $key = [string]$row.$KeyColumn

    if (-not $lookup.ContainsKey($key)) {
        $problems.Add([pscustomobject]@{ Key = $key; Column = '(row)'; Reason = 'no matching item' })
        continue
    }

    $values = @{}
    foreach ($col in $targets.Keys) {
        $result = Convert-Value $row.$col $targets[$col]
        if ($result.Ok) {
            $values[$targets[$col].InternalName] = $result.Value
        } elseif ($result.Reason -ne 'empty (skipped)') {
            $problems.Add([pscustomobject]@{ Key = $key; Column = $col; Reason = $result.Reason })
        }
    }

    if ($values.Count) {
        $plan.Add([pscustomobject]@{ Key = $key; Id = $lookup[$key]; Values = $values })
    }
}

Write-Host "`nTo update : $($plan.Count) item(s)" -ForegroundColor Green
Write-Host "Problems  : $($problems.Count)"
if ($problems.Count) { $problems | Format-Table Key, Column, Reason -AutoSize }

if ($plan.Count -eq 0) { Write-Host "Nothing to do."; Disconnect-PnPOnline; return }

if ($WhatIfPreference) {
    Write-Host "`n-WhatIf: nothing was written. Planned changes:" -ForegroundColor Cyan
    foreach ($p in $plan | Select-Object -First 10) {
        "  [$($p.Id)] $($p.Key): " + (($p.Values.GetEnumerator() | ForEach-Object { "$($_.Key)=$($_.Value)" }) -join ', ')
    }
    if ($plan.Count -gt 10) { "  ... and $($plan.Count - 10) more" }
    Disconnect-PnPOnline
    return
}

if ($PSCmdlet.ShouldProcess("$($plan.Count) items in '$List'", 'Update')) {
    $sw = [System.Diagnostics.Stopwatch]::StartNew()
    $batch = New-PnPBatch
    foreach ($p in $plan) {
        Set-PnPListItem -List $List -Identity $p.Id -Values $p.Values -Batch $batch | Out-Null
    }
    Invoke-PnPBatch -Batch $batch
    $sw.Stop()
    Write-Host "`nUpdated $($plan.Count) item(s) in $([math]::Round($sw.Elapsed.TotalSeconds,1))s" -ForegroundColor Green
}

Disconnect-PnPOnline

Run it dry first

.\Update-ListFromCsv.ps1 `
    -SiteUrl "https://contoso.sharepoint.com/sites/Demo" `
    -ClientId "11111111-2222-3333-4444-555555555555" `
    -List "Projects" -CsvPath .\changes.csv `
    -ColumnMap @{ Status = 'ProjectStatus' } `
    -WhatIf
Mapped columns:
  Budget         -> Budget             (Currency)
  DueDate        -> DueDate            (DateTime)
  Status         -> ProjectStatus      (Choice)

Building lookup...
  25 item(s) indexed on 'Title'

To update : 6 item(s)
Problems  : 3

Key        Column  Reason
---        ------  ------
Project 04 Status  'On Hold' is not a valid choice (allowed: Not Started, In Progress, Blocked, Complete)
Project 05 DueDate 'not a date' is not a date
Project 99 (row)   no matching item

-WhatIf: nothing was written. Planned changes:
  [4] Project 04: Budget=4400, DueDate=09/01/2026 00:00:00
  [5] Project 05: ProjectStatus=Complete, Budget=5500

Note rows 4 and 5. The bad cell is dropped and reported, the good ones still go through. The naive version threw all of row 5 away.

Then run it for real:

Updated 6 item(s) in 4.5s

The -WhatIf run is the one that matters. Thirty seconds of reading a problem list beats finding out in six months that a Choice column is full of values that were never options 🙂

Report Who Has Access to a SharePoint Site with PowerShell

In this post, I am going to show you how to report everyone who has access to a SharePoint site – and why the two obvious ways of doing it are both wrong, in opposite directions. Full script at the end.

Run against real sites on PnP.PowerShell 2.12.0.

Access arrives by five routes

  • SharePoint groups – Owners, Members, Visitors and any custom ones
  • A direct grant to one person on the site itself
  • The Microsoft 365 group behind a group-connected site
  • Sharing links
  • An Everyone or Everyone except external users claim

Most scripts read the first one. That is why sites look tidy and are not.

SharePoint groups

foreach ($group in Get-PnPGroup) {
    $permissions = (Get-PnPGroupPermissions -Identity $group.Title | ForEach-Object { $_.Name }) -join ', '
    "$($group.Title) [$permissions]"
    Get-PnPGroupMember -Group $group.Title | ForEach-Object { "    $($_.Title)" }
}
Demo Members [Edit]
Demo Owners [Full Control]
    VJ
Demo Visitors [Read]

Get-PnPGroupPermissions is the tidy way to get the permission level. You can walk role assignments by hand, but there is no need for groups.

Direct grants

$web = Get-PnPWeb
$assignments = Get-PnPProperty -ClientObject $web -Property RoleAssignments

foreach ($ra in $assignments) {
    $member = Get-PnPProperty -ClientObject $ra -Property Member
    $roles  = Get-PnPProperty -ClientObject $ra -Property RoleDefinitionBindings
    "{0,-30} {1}" -f $member.Title, (($roles | ForEach-Object { $_.Name }) -join ', ')
}

This is the authoritative list of who holds permission on the site. Everything else either appears here or sits underneath something that does.

The Everyone problem, and two ways to get it wrong

This is the part worth reading twice, because both obvious approaches fail.

Wrong answer one – scanning the user list

Get-PnPUser | Where-Object { $_.Title -match '^Everyone' }
Title                          LoginName
-----                          ---------
Everyone                       c:0(.s|true
Everyone except external users c:0-.f|rolemanager|spo-grid-all-users/03889806-...

Both of them, on a completely private site. Get-PnPUser returns the User Information List – everyone the site has ever heard of – and SharePoint puts those principals there whether or not anything has been granted to them.

I checked the same site’s role assignments:

Principals with a role assignment on this web:
    Demo Owners
    Demo Visitors
    Demo Members

Everyone-type principal actually granted access: False

So a report built this way flags every site in the tenant as overshared. People stop reading it, and then it is worth nothing on the day it is right.

Wrong answer two – only checking role assignments

Having found that out, the obvious fix is to only trust role assignments on the web. That is what I did, and it is also wrong.

Here is a different site, a group-connected one:

Route                            Principal                      Type          Permission
-----                            ---------                      ----          ----------
via group: All Company Members   Everyone except external users SecurityGroup Edit

Everyone except external users is a member of the Members group, and that group has Edit. Everyone in the organisation can edit that site. And because the claim is not itself in the web’s role assignments, my “fixed” script reported Everyone claims: 0.

That is the failure that matters. The first version cries wolf. The second stays quiet while the whole company has write access.

The check that actually works

An Everyone claim counts if it holds a role assignment or is a member of a group that holds one. And match on the login name, not the title – the title is whatever your tenant renamed it to:

function Test-EveryoneClaim {
    param([string] $LoginName, [string] $Title)

    return $LoginName -match 'spo-grid-all-users' -or
           $LoginName -match 'c:0\(\.s\|true' -or
           $Title -match '^Everyone'
}

Adding the claim to the Members group is the normal way this happens. Somebody wants everyone in the company to be able to contribute, clicks two things, and it never shows up in an audit again.

Group-connected sites have a second membership list

A Teams or Microsoft 365 group site has SharePoint groups and a Microsoft 365 group, and they are not the same list:

$site = Get-PnPSite
Get-PnPProperty -ClientObject $site -Property GroupId

Get-PnPMicrosoft365GroupOwner  -Identity $site.GroupId
Get-PnPMicrosoft365GroupMember -Identity $site.GroupId

Miss this and your report is missing the actual members of every Teams-connected site. I wrote about the same split causing trouble in the site inventory post, where group sites report no owner at all.

Sharing links

Sharing a file creates a SharePoint group to hold whoever the link is for. I made one on a demo file to see what it looks like:

Add-PnPFileOrganizationalSharingLink -FileUrl "/sites/Demo/Project Documents/Reports/Monthly Report.txt" -ShareType View

And then listed the site’s groups:

Demo Members
Demo Owners
Demo Visitors
SharingLinks.042f4d62-d446-4d80-83d1-fdb40ce5956d.OrganizationView.7cf4060c-7919-4531-8d2e-0bc4b5ddd2d3

I had expected these to be hidden. They are not – it is right there in an ordinary Get-PnPGroup listing. The name is SharingLinks.<file guid>.<type>.<link id>.

Which is worse, in a way. It is not concealed, it just looks like machine noise, so anyone scanning a list of group names slides straight past it. Twenty of those in a site and nobody reads a single one.

Get-PnPGroup | Where-Object { $_.Title -like 'SharingLinks*' }

The ShareType values available are View, Review, Edit, Embed, BlocksDownload, CreateOnly, AddressBar and AdminDefault.

One warning if you go on to manage these. The three cmdlets do not agree with each other:

Add-PnPFileOrganizationalSharingLink   -FileUrl                 (no -Identity)
Get-PnPFileSharingLink                 -Identity or -FileUrl    (-FileUrl obsolete)
Remove-PnPFileSharingLink              -FileUrl AND -Identity   (-Identity = the LINK)

So -FileUrl is deprecated on one, required on another, and -Identity means the file on two of them and the link on the third. Check with Get-Command <name> -Syntax before writing anything – it cost me four attempts.

The whole script

Everything above in one place, with a CSV option:

[CmdletBinding()]
param(
    [Parameter(Mandatory)] [string] $SiteUrl,
    [Parameter(Mandatory)] [string] $ClientId,
    [string] $OutputCsv
)

$ErrorActionPreference = 'Stop'
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientId -Interactive

$access = [System.Collections.Generic.List[object]]::new()

function Add-Access {
    param($Route, $Principal, $Type, $Permission, $Detail = '')
    $access.Add([pscustomobject]@{
        Route = $Route; Principal = $Principal; Type = $Type
        Permission = $Permission; Detail = $Detail
    })
}

function Test-EveryoneClaim {
    param([string] $LoginName, [string] $Title)
    return $LoginName -match 'spo-grid-all-users' -or
           $LoginName -match 'c:0\(\.s\|true' -or
           $Title -match '^Everyone'
}

Write-Host "Reading site..." -ForegroundColor Cyan
$web = Get-PnPWeb
$assignments = Get-PnPProperty -ClientObject $web -Property RoleAssignments

foreach ($ra in $assignments) {
    $member = Get-PnPProperty -ClientObject $ra -Property Member
    $roles  = Get-PnPProperty -ClientObject $ra -Property RoleDefinitionBindings
    $levels = ($roles | ForEach-Object { $_.Name }) -join ', '

    $route = if (Test-EveryoneClaim $member.LoginName $member.Title) { 'EVERYONE CLAIM' }
             elseif ($member.PrincipalType -eq 'SharePointGroup') { 'SharePoint group' }
             else { 'Direct grant' }

    Add-Access $route $member.Title $member.PrincipalType $levels $member.LoginName
}

Write-Host "Expanding groups..." -ForegroundColor Cyan

foreach ($group in Get-PnPGroup) {
    $isSharingLink = $group.Title -like 'SharingLinks*'

    $permissions = try {
        (Get-PnPGroupPermissions -Identity $group.Title | ForEach-Object { $_.Name }) -join ', '
    } catch { '(none on this web)' }

    $members = @(Get-PnPGroupMember -Group $group.Title -ErrorAction SilentlyContinue)

    if ($isSharingLink) {
        $parts = $group.Title -split '\.'
        $linkType = if ($parts.Count -ge 3) { $parts[2] } else { 'unknown' }
        if ($members.Count -eq 0) {
            Add-Access 'SHARING LINK' '(link with no named recipients)' 'SharingLink' $permissions $linkType
        }
        foreach ($m in $members) {
            Add-Access 'SHARING LINK' $m.Title $m.PrincipalType $permissions "$linkType | $($m.LoginName)"
        }
        continue
    }

    foreach ($m in $members) {
        # The claim usually gets in as a group MEMBER, not a direct grant.
        $route = if (Test-EveryoneClaim $m.LoginName $m.Title) { 'EVERYONE CLAIM' }
                 else { "via group: $($group.Title)" }
        $detail = if ($route -eq 'EVERYONE CLAIM') {
            "inside group '$($group.Title)' | $($m.LoginName)"
        } else { $m.LoginName }

        Add-Access $route $m.Title $m.PrincipalType $permissions $detail
    }
}

$site = Get-PnPSite
$null = Get-PnPProperty -ClientObject $site -Property GroupId

if ($site.GroupId -and $site.GroupId -ne [guid]::Empty) {
    Write-Host "Group-connected site - reading Microsoft 365 group..." -ForegroundColor Cyan
    try {
        foreach ($o in Get-PnPMicrosoft365GroupOwner -Identity $site.GroupId -ErrorAction Stop) {
            Add-Access 'M365 group owner' $o.DisplayName 'User' 'Owner' $o.UserPrincipalName
        }
        foreach ($m in Get-PnPMicrosoft365GroupMember -Identity $site.GroupId -ErrorAction Stop) {
            Add-Access 'M365 group member' $m.DisplayName 'User' 'Member' $m.UserPrincipalName
        }
    }
    catch { Write-Warning "Could not read the Microsoft 365 group: $($_.Exception.Message)" }
}

foreach ($u in Get-PnPUser) {
    if ($u.LoginName -match '#ext#' -or $u.IsShareByEmailGuestUser) {
        Add-Access 'EXTERNAL USER' $u.Title 'Guest' '(see routes above)' $u.LoginName
    }
}

$everyone  = @($access | Where-Object Route -eq 'EVERYONE CLAIM')
$links     = @($access | Where-Object Route -eq 'SHARING LINK')
$externals = @($access | Where-Object Route -eq 'EXTERNAL USER')

Write-Host ""
Write-Host "Site           : $($web.Title)"
Write-Host "Access entries : $($access.Count)"
Write-Host "Sharing links  : $($links.Count)"
Write-Host "External users : $($externals.Count)"
Write-Host "Everyone claims: $($everyone.Count)"

if ($everyone.Count) {
    Write-Warning "This site grants access to an Everyone claim. Anyone in the organisation can reach it."
    foreach ($e in $everyone) {
        Write-Host "  $($e.Principal) - $($e.Permission) - $($e.Detail)" -ForegroundColor Red
    }
}

if ($OutputCsv) {
    $access | Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8
    Write-Host "`nWritten to $OutputCsv" -ForegroundColor Green
} else {
    $access | Sort-Object Route, Principal | Format-Table Route, Principal, Type, Permission -AutoSize
}

Disconnect-PnPOnline

On a private site:

Site           : PowerShell Demo
Access entries : 4
Sharing links  : 0
External users : 0
Everyone claims: 0

And on the one that is not:

Site           : All Company
Access entries : 9
Everyone claims: 1

WARNING: This site grants access to an Everyone claim. Anyone in the organisation can reach it.
  Everyone except external users - Edit - inside group 'All Company Members'

That last line is the one you want. Not just that a claim exists, but which group it is sitting in, because that is where you go to remove it 🙂

List Every SharePoint Site with Owner, Storage and Last Activity

In this post, I am going to show you how to list every site in your tenant with its owner, storage and last activity – and why the owner column is harder than it looks. Full script at the end.

Run against a real tenant of 37 sites on PnP.PowerShell 2.12.0.

The basic call

Connect-PnPOnline -Url "https://contoso-admin.sharepoint.com" -ClientId "1111...-5555" -Interactive
Get-PnPTenantSite
Sites returned : 37
Took           : 1.5s

Note the -admin URL. Tenant-wide cmdlets want the admin centre, not a site.

Two things that are not obvious

OneDrive is excluded. Personal sites are site collections too, but you only get them if you ask:

Without -IncludeOneDriveSites : 37
With    -IncludeOneDriveSites : 42

Five here. In a real organisation it is one per person, so a 200 site tenant becomes 5,000. Know which number you want before you run a report on it.

-Detailed did nothing. I compared the property sets:

Get-PnPTenantSite            1.5s, 91 properties
Get-PnPTenantSite -Detailed  0.7s, 91 properties - no difference

Not one extra property. Plenty of guides tell you to reach for -Detailed; on this version there is nothing to reach for. Check it on your own version before you pay for it.

91 properties

Far more than the four everyone shows. The ones I use:

Url  Title  Owner  Template  GroupId
StorageUsageCurrent  StorageQuota  LastContentModifiedDate
SharingCapability  LockState  SensitivityLabel  IsHubSite  WebsCount

There are newer ones worth knowing about too – ArchiveStatus, RestrictedAccessControl, RestrictContentOrgWideSearch, EnableAutoExpirationVersionTrim, ExpireVersionsAfterDays. Print the lot with:

Get-PnPTenantSite | Select-Object -First 1 | Get-Member -MemberType Properties

StorageUsageCurrent and StorageQuota are both in MB. A quota of 1048576 is 1TB.

The owner problem

This is the part worth reading. There are four owner-looking properties. Here is how many of my 37 sites had each one populated:

Owner          : 15
OwnerName      : 0
OwnerEmail     : 0
OwnerLoginName : 0

Three of them are empty on every single site. Not sparse – zero. If you build a report on OwnerEmail you get a column of blanks and nothing tells you it did not work.

And the one that does work is only populated on 15 of 37. Grouped by template, the blanks look like this:

Count Template
----- --------
   15 GROUP#0
    1 EHS#1
    1 POINTPUBLISHINGHUB#0
    1 RedirectSite#0
    ...

Every group-connected site has a blank Owner. No exceptions. Which makes sense once you think about it – a Teams or Microsoft 365 group site is owned by the group, not by a site collection administrator, so there is nothing for SharePoint to put in that field.

The consequence is worth being blunt about. A “find sites with no owner” script that checks Owner reported 22 ownerless sites on my tenant. The real answer is 7. Fifteen of them were group sites with perfectly good owners in the group.

Getting the real owner

For group-connected sites, ask the group. The site object gives you the GroupId:

$owners = Get-PnPMicrosoft365GroupOwner -Identity $site.GroupId
($owners | ForEach-Object { $_.UserPrincipalName }) -join '; '
allcompany : admin@contoso.onmicrosoft.com
cd         : admin@contoso.onmicrosoft.com
demohr     : admin@contoso.onmicrosoft.com

The important thing to note is the cost. That is a separate call per group, and I measured 1557ms each. Fifteen groups is 23 seconds. Five hundred groups is thirteen minutes on top of everything else, so make it optional rather than something your report always does.

What does not work

The obvious alternative is to ask each site for its administrators:

Connect-PnPOnline -Url "https://contoso.sharepoint.com/sites/Example" -ClientId "..." -Interactive
Get-PnPSiteCollectionAdmin
Attempted to perform an unauthorized operation.

I am a tenant administrator. That is not the same as being a site collection administrator on every site, and SharePoint does not pretend otherwise. To read a site’s administrators you have to be one, which means adding yourself first – and that is a change to the site, on a report you thought was read-only.

Run it from the admin connection instead and it answers a different question entirely:

Title                 LoginName
-----                 ---------
Company Administrator c:0t.c|tenant|90a53517-cc9a-405c-a4cb-...

That is the admin centre’s own administrators, not the site’s.

So there is no cheap way to get a site collection administrator for a site you do not already administer. Group owners are the practical answer for group sites, and Owner is the practical answer for the rest.

The whole script

[CmdletBinding()]
param(
    [Parameter(Mandatory)] [string] $TenantAdminUrl,
    [Parameter(Mandatory)] [string] $ClientId,
    [string] $OutputCsv,
    [switch] $ResolveGroupOwners,
    [switch] $IncludeOneDrive,
    [int]    $StaleDays = 90
)

$ErrorActionPreference = 'Stop'
Connect-PnPOnline -Url $TenantAdminUrl -ClientId $ClientId -Interactive

Write-Host "Enumerating sites..." -ForegroundColor Cyan
$sites = if ($IncludeOneDrive) { Get-PnPTenantSite -IncludeOneDriveSites } else { Get-PnPTenantSite }
Write-Host "  $($sites.Count) site(s)" -ForegroundColor Green

$cutoff  = (Get-Date).AddDays(-$StaleDays)
$results = [System.Collections.Generic.List[object]]::new()
$number  = 0

foreach ($site in $sites) {
    $number++
    Write-Progress -Activity 'Building site inventory' `
                   -Status "$number of $($sites.Count): $($site.Url)" `
                   -PercentComplete (($number / $sites.Count) * 100)

    $isGroupSite = $site.GroupId -and $site.GroupId -ne [guid]::Empty

    $owner = if ($site.Owner) {
        $site.Owner
    }
    elseif ($isGroupSite -and $ResolveGroupOwners) {
        try {
            $groupOwners = Get-PnPMicrosoft365GroupOwner -Identity $site.GroupId -ErrorAction Stop
            ($groupOwners | ForEach-Object { $_.UserPrincipalName }) -join '; '
        }
        catch { "(group lookup failed: $($_.Exception.Message))" }
    }
    elseif ($isGroupSite) {
        '(group-connected - rerun with -ResolveGroupOwners)'
    }
    else {
        '(none recorded)'
    }

    $results.Add([pscustomobject]@{
        Url             = $site.Url
        Title           = $site.Title
        Owner           = $owner
        GroupConnected  = $isGroupSite
        Template        = $site.Template
        StorageUsedMB   = [math]::Round($site.StorageUsageCurrent, 1)
        StorageQuotaMB  = $site.StorageQuota
        PercentUsed     = if ($site.StorageQuota -gt 0) {
                              [math]::Round(($site.StorageUsageCurrent / $site.StorageQuota) * 100, 1)
                          } else { 0 }
        LastModified    = $site.LastContentModifiedDate
        Stale           = $site.LastContentModifiedDate -lt $cutoff
        Sharing         = $site.SharingCapability
        SensitivityLabel= $site.SensitivityLabel
        LockState       = $site.LockState
    })
}

Write-Progress -Activity 'Building site inventory' -Completed

$stale   = @($results | Where-Object Stale)
$noOwner = @($results | Where-Object { $_.Owner -like '(none*' })
$totalGB = [math]::Round(($results | Measure-Object StorageUsedMB -Sum).Sum / 1024, 2)

Write-Host ""
Write-Host "Sites            : $($results.Count)"
Write-Host "Group-connected  : $(@($results | Where-Object GroupConnected).Count)"
Write-Host "Storage used     : $totalGB GB"
Write-Host "Not modified $StaleDays d : $($stale.Count)"
Write-Host "No owner recorded: $($noOwner.Count)"

if (-not $ResolveGroupOwners -and @($results | Where-Object GroupConnected).Count -gt 0) {
    Write-Warning "Group-connected sites have no owner shown. Rerun with -ResolveGroupOwners (about 1.5s each)."
}

if ($OutputCsv) {
    $results | Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8
    Write-Host "`nWritten to $OutputCsv" -ForegroundColor Green
} else {
    $results | Sort-Object StorageUsedMB -Descending |
        Format-Table Url, Owner, StorageUsedMB, LastModified, Stale -AutoSize
}

Disconnect-PnPOnline

Save it as Get-SiteInventory.ps1 and run it:

.\Get-SiteInventory.ps1 `
    -TenantAdminUrl "https://contoso-admin.sharepoint.com" `
    -ClientId "11111111-2222-3333-4444-555555555555" `
    -ResolveGroupOwners `
    -OutputCsv .\sites.csv
Enumerating sites...
  37 site(s)

Sites             : 37
Group-connected   : 15
Storage used      : 1.06 GB
Not modified 90 d : 1
No owner recorded : 7

Written to .\sites.csv

Without -ResolveGroupOwners it says (group-connected – rerun with -ResolveGroupOwners) in the owner column rather than leaving it blank. A blank there reads as “this site has no owner”, and on my tenant that would have been wrong fifteen times out of twenty-two 🙂

Bulk Updates in PnP PowerShell: Batching, Throttling and Retry

In this post, I am going to show you why a PnP PowerShell loop that works fine on twenty items takes an hour on two thousand, what batching does about it, and where batching turns out not to help much.

All numbers below are from one run against a real site on PnP.PowerShell 2.12.0. Your times will differ, the ratios will not.

The problem, measured

Adding 200 items the obvious way:

1..200 | ForEach-Object {
    Add-PnPListItem -List 'Bulk Test' -Values @{
        Title  = "Single $_"
        Ref    = "S-$('{0:D4}' -f $_)"
        Amount = $_
    }
}
Individual: 242.9s for 200 items
Per item  : 1214ms

1.2 seconds per item. Every one of those is a separate round trip – a request out, a wait, a response back.

That number is worth sitting with. Two hundred items is four minutes. Two thousand is forty. Ten thousand is two hours, assuming nothing throttles you, which at ten thousand separate requests it will.

Reading is cheaper – I measured about 250ms per call in the permissions post – but writing costs about five times that.

Batching

You build up the operations, then send them together:

$batch = New-PnPBatch

1..200 | ForEach-Object {
    Add-PnPListItem -List 'Bulk Test' -Values @{
        Title  = "Batched $_"
        Ref    = "B-$('{0:D4}' -f $_)"
        Amount = $_
    } -Batch $batch
}

Invoke-PnPBatch -Batch $batch
Batched   : 12.8s for 200 items
Per item  : 64ms

Same 200 items, 19 times faster. Four minutes becomes thirteen seconds.

The important thing to note is that nothing happens until Invoke-PnPBatch. If your script throws before it reaches that line, none of the work is done. That is usually what you want, but it does mean a partial failure looks like nothing happened at all.

Where it does not help nearly as much

Same test, updating existing items instead of adding new ones:

Updating one at a time : 285.0s for 200 items
Updating in a batch    :  89.7s for 200 items

3.2 times faster, not nineteen.

I am not going to pretend I know exactly why, but the shape of it makes sense: an update has to identify the item first, and that work does not disappear just because the calls are bundled. A batched update still costs about 450ms an item against 64ms for a batched insert.

So batching is worth doing for updates – three times faster is three times faster – but if you have read somewhere that batching makes everything twenty times quicker, that is inserts you are reading about.

Not everything can be batched

This surprised me. Ask the module:

Get-Command -Module PnP.PowerShell |
    Where-Object { $_.Parameters.Keys -contains 'Batch' } |
    Select-Object -ExpandProperty Name
Add-PnPGroupMember
Add-PnPListItem
Invoke-PnPBatch
Invoke-PnPSPRestMethod
Publish-PnPSyntexModel
Remove-PnPField
Remove-PnPListItem
Request-PnPSyntexClassifyAndExtract
Set-PnPListItem
Unpublish-PnPSyntexModel

Ten cmdlets on 2.12.0, and three of them are Syntex. In practice batching means list items, group members and fields. Everything else – creating sites, uploading files, changing permissions – is one call at a time and no amount of wishing changes it.

Run that command against your own version rather than trusting this list. It has grown over releases and will keep growing.

Throttling

Push too hard and SharePoint returns 429 Too Many Requests, sometimes 503. This is not a fault, it is the service protecting itself, and the correct response is to wait exactly as long as it tells you to.

The response carries a Retry-After header with a number of seconds. Honour it. Retrying immediately, or backing off on a guess, is what turns brief throttling into a long block.

function Invoke-WithRetry {
    param([scriptblock] $Action, [int] $MaxAttempts = 5)

    for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
        try { return & $Action }
        catch {
            $response = $_.Exception.Response
            $status   = $response.StatusCode.value__

            if ($status -ne 429 -and $status -ne 503) { throw }
            if ($attempt -eq $MaxAttempts) { throw }

            $wait = $response.Headers['Retry-After']
            if (-not $wait) { $wait = [math]::Pow(2, $attempt) }

            Write-Warning "Throttled. Waiting $wait seconds (attempt $attempt of $MaxAttempts)."
            Start-Sleep -Seconds $wait
        }
    }
}

Used like this:

Invoke-WithRetry { Invoke-PnPBatch -Batch $batch }

Being straight with you: I could not make SharePoint throttle me on demand to test this, so what you are looking at is the shape rather than something I watched recover. The parts that matter are that it only retries on 429 and 503 and rethrows everything else, that it gives up rather than looping forever, and that it reads Retry-After before falling back to its own backoff.

Do not retry on every error. A 404 will never succeed no matter how many times you ask.

What I actually do

  • Batch anything that supports it. Nineteen times on inserts is not an optimisation, it is the difference between a script you can run and one you cannot.
  • Split very large batches. One batch of 20,000 operations is a single enormous request. A few thousand at a time is easier on everyone and gives you somewhere to resume from.
  • Wrap the invoke, not the loop. Retry Invoke-PnPBatch, not each Add-PnPListItem – the individual calls are not doing any work yet.
  • Print progress. Anything over about thirty seconds needs to say something, or you will assume it has hung and kill it.

And time it on a small list before you point it at a big one. Twelve seconds against 242 is the kind of thing you want to find out on 200 items, not on 20,000 🙂

Find Broken Permission Inheritance in SharePoint with PowerShell

In this post, I am going to show you how to find everything in a SharePoint site that does not inherit its permissions, and who can actually get at it. There is a full script at the end if you just want to run something.

Broken inheritance is where oversharing hides. A site can look tidy at the top and still have one folder shared with everyone, because somebody clicked Share eighteen months ago. Nothing in the interface shows you all of them at once.

Run against a real site on PnP.PowerShell 2.12.0. Output below is what came back.

Permissions break at four levels

Site, list, folder and item. You have to check all four, and the checks are different at each.

The site

$web = Get-PnPWeb
Get-PnPProperty -ClientObject $web -Property HasUniqueRoleAssignments
$web.HasUniqueRoleAssignments
True

Do not panic at that. The top web of a site collection has nothing above it to inherit from, so it always reports True. It only means something on a subsite.

The important thing to note is Get-PnPProperty. Most permission properties are not loaded when you fetch an object – you have to ask for them separately. Skip that line and HasUniqueRoleAssignments comes back empty rather than throwing, which is worse, because your report quietly finds nothing.

Lists and libraries

Get-PnPList | Where-Object { -not $_.Hidden } | ForEach-Object {
    [pscustomobject]@{
        List   = $_.Title
        Unique = Get-PnPProperty -ClientObject $_ -Property HasUniqueRoleAssignments
    }
}
List              Unique
----              ------
Departments        False
Documents          False
Events             False
Project Documents  False
Projects           False
Site Pages         False
Style Library      False

Items, and why this is the slow part

Get-PnPListItem -List 'Projects' -PageSize 500 | ForEach-Object {
    $unique = Get-PnPProperty -ClientObject $_ -Property HasUniqueRoleAssignments
    if ($unique) { [pscustomobject]@{ Id = $_.Id; Title = $_['Title'] } }
}
Id Title
-- -----
 3 Project 03

That took 6.2 seconds for 25 items.

Each item is a separate round trip to the server. There is no bulk way to ask. So a 10,000 item library is roughly forty minutes, and a large tenant is an overnight job. That is not a fault in the script, it is how the API works, and it is why you should scope these reports to a site rather than pointing them at everything and hoping.

Folders, and the one that breaks your script

The obvious way to check folders looks like this, and it does not work:

Get-PnPFolderItem -FolderSiteRelativeUrl 'Project Documents' -ItemType Folder | ForEach-Object {
    $folderItem = Get-PnPProperty -ClientObject $_ -Property ListItemAllFields
    Get-PnPProperty -ClientObject $folderItem -Property HasUniqueRoleAssignments
}
Object reference not set to an instance of an object on server.
The object is associated with property ListItemAllFields.

The culprit is Forms. Every document library has one, SharePoint made it, it holds the library’s form pages, and it is not backed by a list item at all. So asking it about ListItemAllFields returns nothing and the next line falls over.

Note the error does not tell you which folder caused it. You get an object reference exception on a folder you did not create and never see in the browser.

Ask the list for its folders instead:

Get-PnPListItem -List 'Project Documents' -PageSize 500 |
    Where-Object { $_.FileSystemObjectType -eq 'Folder' } |
    ForEach-Object {
        [pscustomobject]@{
            Folder = $_['FileLeafRef']
            Unique = Get-PnPProperty -ClientObject $_ -Property HasUniqueRoleAssignments
        }
    }
Folder    Unique
------    ------
Contracts  False
Reports    False
Archive     True
2024       False
2025       False

Forms never appears, because it is not a list item. Problem gone rather than worked around.

Who actually has access

Knowing something is broken is half of it. You want to know who that let in:

Get-PnPListItemPermission -List 'Projects' -Identity 3
HasUniqueRoleAssignments Permissions
------------------------ -----------
                    True {PowerShell Demo Owners, PowerShell Demo Visitors, PowerShell Demo Members}

For the permission levels as well as the names, walk the role assignments:

$item = Get-PnPListItem -List 'Projects' -Id 3
$assignments = Get-PnPProperty -ClientObject $item -Property RoleAssignments

$rows = foreach ($ra in $assignments) {
    $member = Get-PnPProperty -ClientObject $ra -Property Member
    $roles  = Get-PnPProperty -ClientObject $ra -Property RoleDefinitionBindings
    [pscustomobject]@{
        Principal = $member.Title
        Type      = $member.PrincipalType
        Roles     = ($roles | ForEach-Object { $_.Name }) -join ', '
    }
}
$rows | Format-Table -AutoSize
Principal                           Type Roles
---------                           ---- -----
PowerShell Demo Owners   SharePointGroup Full Control
PowerShell Demo Visitors SharePointGroup Read
PowerShell Demo Members  SharePointGroup Edit

The one to watch there is $rows = foreach. A foreach statement cannot be piped – only the ForEach-Object cmdlet can. Put a pipe straight after the closing brace and you get “An empty pipe element is not allowed”, which does not obviously point at the mistake you made.

A warning about fixing what you find

While writing this I tried to break inheritance on purpose to have something to detect:

Set-PnPListItemPermission -List 'Projects' -Identity 3 -InheritPermissions:$false

It reported success. It did nothing at all.

-InheritPermissions restores inheritance. There is no switch that breaks it, and passing $false is a silent no-op. If you write a script to lock something down that way, you get a green light and no change. Use the CSOM method instead:

$item = Get-PnPListItem -List 'Projects' -Id 3
$item.BreakRoleInheritance($true, $false)
Invoke-PnPQuery

First argument keeps the existing permissions so you do not lock yourself out, second clears sub-scopes.

The whole script

Parameters instead of hardcoded URLs, progress while it runs, and a CSV if you want one:

[CmdletBinding()]
param(
    [Parameter(Mandatory)] [string] $SiteUrl,
    [Parameter(Mandatory)] [string] $ClientId,
    [string] $OutputCsv,
    [int]    $MaxItems = 5000
)

$ErrorActionPreference = 'Stop'
Connect-PnPOnline -Url $SiteUrl -ClientId $ClientId -Interactive

$results = [System.Collections.Generic.List[object]]::new()

function Get-AccessSummary {
    param($ClientObject)
    try {
        $assignments = Get-PnPProperty -ClientObject $ClientObject -Property RoleAssignments
        $parts = foreach ($ra in $assignments) {
            $member = Get-PnPProperty -ClientObject $ra -Property Member
            $roles  = Get-PnPProperty -ClientObject $ra -Property RoleDefinitionBindings
            "$($member.Title) [$(($roles | ForEach-Object { $_.Name }) -join '/')]"
        }
        return ($parts -join '; ')
    }
    catch { return "could not read: $($_.Exception.Message)" }
}

Write-Host "Checking site..." -ForegroundColor Cyan
$web = Get-PnPWeb
$null = Get-PnPProperty -ClientObject $web -Property HasUniqueRoleAssignments
if ($web.HasUniqueRoleAssignments) {
    $results.Add([pscustomobject]@{
        Scope = 'Site'; Name = $web.Title
        Path = $web.ServerRelativeUrl; Access = Get-AccessSummary $web
    })
}

$lists = Get-PnPList | Where-Object { -not $_.Hidden }
$listNumber = 0

foreach ($list in $lists) {
    $listNumber++
    Write-Progress -Activity 'Scanning for broken inheritance' `
                   -Status "$($list.Title) ($listNumber of $($lists.Count))" `
                   -PercentComplete (($listNumber / $lists.Count) * 100)

    $null = Get-PnPProperty -ClientObject $list -Property HasUniqueRoleAssignments
    if ($list.HasUniqueRoleAssignments) {
        $results.Add([pscustomobject]@{
            Scope = 'List'; Name = $list.Title
            Path = $list.RootFolder.ServerRelativeUrl; Access = Get-AccessSummary $list
        })
    }

    if ($list.ItemCount -eq 0) { continue }
    if ($list.ItemCount -gt $MaxItems) {
        Write-Warning "Skipping items in '$($list.Title)' - $($list.ItemCount) exceeds -MaxItems $MaxItems."
        continue
    }

    # Asking the list for its items also gives us folders, and avoids
    # Get-PnPFolderItem returning the system Forms folder, which has no
    # backing list item and throws when you ask about its permissions.
    foreach ($item in Get-PnPListItem -List $list -PageSize 500) {
        $null = Get-PnPProperty -ClientObject $item -Property HasUniqueRoleAssignments
        if (-not $item.HasUniqueRoleAssignments) { continue }

        $isFolder = $item.FileSystemObjectType -eq 'Folder'
        $results.Add([pscustomobject]@{
            Scope  = if ($isFolder) { 'Folder' } else { 'Item' }
            Name   = if ($isFolder) { $item['FileLeafRef'] } else { $item['Title'] }
            Path   = $item['FileRef']
            Access = Get-AccessSummary $item
        })
    }
}

Write-Progress -Activity 'Scanning for broken inheritance' -Completed

if ($results.Count -eq 0) {
    Write-Host "`nNothing found. Everything in this site inherits its permissions." -ForegroundColor Green
    Disconnect-PnPOnline
    return
}

Write-Host "`nFound $($results.Count) object(s) with unique permissions.`n" -ForegroundColor Yellow

if ($OutputCsv) {
    $results | Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8
    Write-Host "Written to $OutputCsv" -ForegroundColor Green
} else {
    $results | Format-Table Scope, Name, Path -AutoSize
    $results | Format-List Scope, Name, Access
}

Disconnect-PnPOnline

Save it as Get-BrokenInheritance.ps1 and run it:

.\Get-BrokenInheritance.ps1 `
    -SiteUrl "https://contoso.sharepoint.com/sites/Demo" `
    -ClientId "11111111-2222-3333-4444-555555555555" `
    -OutputCsv .\permissions.csv
Checking site...

Found 3 object(s) with unique permissions.

Scope  Name              Path
-----  ----              ----
Site   PowerShell Demo   /sites/Demo
Folder Archive           /sites/Demo/Project Documents/Archive
Item   Project 03        /sites/Demo/Lists/Projects/3_.000

Scope  : Folder
Name   : Archive
Access : Demo Owners [Full Control]; Demo Visitors [Read]; Demo Members [Edit]

If a site is clean it tells you so rather than printing an empty table and leaving you wondering whether it worked or you mistyped the URL 🙂

Read SharePoint Lists, Items and Files with PnP PowerShell

In this post, I am going to show you how to read lists, items and files with PnP PowerShell, and the handful of things that come back looking nothing like what you expected.

All of it was run against a real site on PnP.PowerShell 2.12.0. The output below is what my machine printed.

Listing lists

The first command everyone runs:

Get-PnPList | Measure-Object | Select-Object Count
Count
-----
   19

Nineteen, on a site where I had created three. The rest are SharePoint’s own. So you filter out the hidden ones:

Get-PnPList | Where-Object { -not $_.Hidden } | Select-Object Title, ItemCount, BaseTemplate
Title             ItemCount BaseTemplate
-----             --------- ------------
Departments               4          100
Documents                 0          101
Events                    0          106
Form Templates            0          101
Project Documents         9          101
Projects                 25          100
Site Pages                1          119
Style Library             0          101

Down to eight, but still only three of those are mine. Documents, Events, Form Templates, Site Pages and Style Library are all created by SharePoint and are not hidden.

There is no flag for “lists a human made”. If you want that, filter on what you know – by title, or by BaseTemplate where 100 is a generic list and 101 is a document library.

Reading one item

$item = Get-PnPListItem -List 'Projects' -Id 1
$item.FieldValues

A list with five columns of mine returned forty-five fields. Alongside Title, Budget and ProjectStatus you get ComplianceAssetId, MetaInfo, owshiddenversion, ScopeId, SMTotalFileStreamSize, WorkflowVersion and a couple of dozen more.

To read a value, index into the item with the internal name:

$item['Title']
$item['ProjectStatus']
$item['Budget']
Project 01
Not Started
1000

The important thing to note is that internal names are not display names. A column shown as Project Status can have an internal name of ProjectStatus, or Project_x0020_Status if it was created through the browser with a space in the name. Check before you guess:

Get-PnPField -List 'Projects' | Select-Object Title, InternalName

Not every field is a string

This is where exports go wrong. Look at what comes back for the built-in Author field:

Author   Microsoft.SharePoint.Client.FieldUserValue
Editor   Microsoft.SharePoint.Client.FieldUserValue

Person fields, lookup fields and taxonomy fields are objects. Pipe one straight into a CSV and that is the text you get in the file. You want the property off it:

$item['Author'].LookupValue    # display name
$item['Author'].Email
$item['Author'].LookupId

Same shape for lookups – LookupValue for the text, LookupId for the ID.

Ask for less

By default you pull every one of those forty-five fields for every item. Ask only for what you need:

Get-PnPListItem -List 'Projects' -Fields 'Title','ProjectStatus' -PageSize 500

And filter on the server rather than dragging everything down and filtering locally:

$caml = "<View><Query><Where><Eq><FieldRef Name='ProjectStatus'/><Value Type='Text'>Blocked</Value></Eq></Where></Query></View>"
Get-PnPListItem -List 'Projects' -Query $caml

I timed both against my 25 items. Filtering locally took 998ms, the CAML query 913ms. Barely a difference, and I am not going to pretend otherwise.

It matters at scale. Past 5,000 items the list view threshold turns the lazy version from slow into an outright error, and by then you are rewriting the script under pressure. Get in the habit while your lists are small.

Files, and the folder nobody asks for

Listing a library looks fine:

Get-PnPFolderItem -FolderSiteRelativeUrl 'Project Documents'
Name      Type
----      ----
Archive   Folder
Contracts Folder
Forms     Folder
Reports   Folder

I created three of those. Forms is SharePoint’s, and it is in every library on every site.

Which becomes a real problem the moment you go recursive:

Get-PnPFolderItem -FolderSiteRelativeUrl 'Project Documents' -ItemType File -Recursive
Old Contract.txt        /Project Documents/Archive/2024/Old Contract.txt
Statement of Work.txt   /Project Documents/Contracts/Statement of Work.txt
AllItems.aspx           /Project Documents/Forms/AllItems.aspx
Combine.aspx            /Project Documents/Forms/Combine.aspx
DispForm.aspx           /Project Documents/Forms/DispForm.aspx
EditForm.aspx           /Project Documents/Forms/EditForm.aspx
repair.aspx             /Project Documents/Forms/repair.aspx
template.dotx           /Project Documents/Forms/template.dotx
Thumbnails.aspx         /Project Documents/Forms/Thumbnails.aspx
Upload.aspx             /Project Documents/Forms/Upload.aspx
Monthly Report.txt      /Project Documents/Reports/Monthly Report.txt
Versioned Document.txt  /Project Documents/Reports/Versioned Document.txt

Twelve files, of which four are documents. The other eight are the library’s own form pages. If you are writing a report of “every file in this library”, or worse a script that deletes or moves things, filter Forms out:

Get-PnPFolderItem -FolderSiteRelativeUrl 'Project Documents' -ItemType File -Recursive |
    Where-Object { $_.ServerRelativeUrl -notmatch '/Forms/' }

File metadata without downloading the file

Get-PnPFile -Url "/sites/Demo/Project Documents/Reports/Versioned Document.txt" -AsListItem

-AsListItem gives you the metadata only. Without it you are pulling the bytes down, which you rarely want if all you needed was the modified date.

Version history is one shorter than you think

Get-PnPFileVersion -Url "/sites/Demo/Project Documents/Reports/Versioned Document.txt"
VersionLabel Created              Size
------------ -------              ----
1.0          7/21/2026 5:17:06 PM   30
...
11.0         7/21/2026 5:17:17 PM   31

Eleven versions. But the file itself reports 12.0 as its current version.

Get-PnPFileVersion returns the previous versions, not the current one. So if you are counting versions to work out storage, or deciding what to trim, remember the live copy is not in that list and cannot be deleted from it 🙂