SharePoint PowerShell Links

Connect-SPOService problems see also Microsoft.SharePoint.Client.SharePointOnlineCredentials - cannot find type

If, when you run:

Connect-SPOService -Url https://galaxyFarFarAway-admin.sharepoint.com

you might get:

Connect-SPOService: Could not load type 'System.Security.Cryptography.SHA256Cng' from assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

in which case, uninstall the module (See import module problems)

if you still get:

Connect-SPOService: Method not found: 'Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationResult Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext.AcquireToken(System.String, System.String, System.Uri, Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior)'.

This has to do with MFA not being dealt with right in this module. You can try:

Connect-SPOService -Url https://palpatine-admin.sharepoint.com -Credential (Get-Credential)

But this only gets:

Connect-SPOService: The remote server returned an error: (400) Bad Request.

I only get this error with PS 7.2.1; PS 5.1.19041.1320 seems to work just fine.

copy files from a SharePoint folder to a AzzureBlob folder

count files in a site, broken down by library

sometimes libraries have file counts that exceed the 5000-file limit. So other means fail. But this works.

First, download a getSPOobjectWithRestModule.psm1 module. Then import:

import-module C:\PowerShell\MSModules\getSPOobjectWithRestModule.psm1 -Verbose

Read in your password as you type it into a variable:

$Password = Read-Host -AsSecureString

Now, specify a Url and site or list (object) in the following command

Get-SPOObject -Username "[email protected]" -password $Password -Url "https://yourTenant.sharepoint.com/someSite" -Object "lists" | select title, itemcount

create site, see site, create

–D–

deleted sites, listsites, list deleted

–E–

external users, list

You can only list 50 at a time and they're in alphabetical order. So, a "position" is like a screen full of results that you'd get in the GUI.

Get-SPOExternalUser -Position 0 -PageSize 50

For just one user:

Get-SPOExternalUser -Position 0 -PageSize 50 -Filter bob

Filter only seems to work on display name.

–F–

files, list all in a library beginning with a string

This finds all files (including subdirectories) beginning with "someSubString" in the "someLibrary" under the "someSubSite" sub-site.

$web = Get-PnPWeb -Identity someSubSite
$query = @"
    <View Scope='RecursiveAll'>
        <Query>
            <Where>
                <BeginsWith>
                    <FieldRef Name="FileLeafRef"/>" + "<Value Type="Note">someSubString</Value>
                </BeginsWith>
            </Where>
        </Query>
    </View>
"@
$$Items = Get-PnPListItem -List someLibrary -Web $web -Query $query
foreach($item in $items){
    $item.FieldValues.FileLeafRef
}

–G–

Get-SPWeb

if you get the "Not recognized"

Get-SPWeb : The term 'Get-SPWeb' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

from here, Get-SPWeb cmdlet is not for SharePoint Online. It is only for SharePoint on-premise versions

groups, list all users with each users' groups

Note: Get-SPUser is a little misleading in that it returns all user or security group accounts. With a name like "Get-SPOUser", you'd think that it'd only get users and not also groups.

$SPusers = Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All | ft DisplayName, Groups -AutoSize

This query takes a while, so I usually stuff the results into a variable so I can run other commands on it if necessary. Running:

$SPusers | ft

or, better yet:

$SPusers | ogv

You might see a bunch of "users" that look like

c:0u.c|tenant|026297b403004915085f0ff42d0925d1783f5b34058ce868f2e99e6ad2db2283
Guest Contributor

which can be:

groups created by users sharing out files
AAD Guest

groups, list with members

$siteURL = "https://yourtenant.sharepoint.com"
$SPgroups = Get-SPOSiteGroup -Site $siteURL
foreach ($SPgroup in $SPgroups)
{
Write-Host $SPgroup.Title -ForegroundColor "Yellow"
Get-SPOSiteGroup -Site $siteURL -Group $SPgroup.Title| Select-Object -ExpandProperty Users
Write-Host
}

Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, users Export-Csv 'SharePointGroups.csv'

But the problem with the above is that users don't display right. That is, the users column ends up looking like System.Collections.Generic.List`1[System.String]. So try this instead:

Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, {%{"$($_.users)"}} | Export-Csv 'SharePointGroups2.csv'

Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, @{e={$_.users};name='users'} | Export-Csv 'SharePointGroups3.csv'

It's useful to be able to find all groups with no members (anonymous shares). Since it takes so long to gather the groups, maybe put them in a variable first:

$SPgroups = Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/

Then find those with no members and which start with "SharingLinks."

$SPgroups | ?{($_.users.Count -eq 0) -and ($_.title -like "SharingLinks.*")} | select title

Get a count:

($SPgroups | ?{($_.users.Count -eq 0) -and ($_.title -like "SharingLinks.*")}).count #131

groups, which groups is a user a member of?

Get-SPOUser -site https://yourtenant.sharepoint.com -loginname someuser@yourdomain.com | fl Groups

guest users, list - see external users, list

–H–

–I–

import module problems

if when you try to import module

Import-Module Microsoft.Online.SharePoint.PowerShell

Delete SharePoint Online Client Assemblies from: C:\Windows\Microsoft.NET\assembly\GAC_MSIL, remove all Folders starting with name: Microsoft.SharePoint*

Then uninstall:

Uninstall-Module -Name Microsoft.Online.SharePoint.PowerShell

For me, this step failed:

PackageManagement\Uninstall-Package : No match was found for the specified search criteria and module names 'Microsoft.Online.SharePoint.PowerShell'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:2194 char:21
+ ... $null = PackageManagement\Uninstall-Package @PSBoundParameters
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Microsoft.Power...ninstallPackage:UninstallPackage) [Uninstall-Package], Exception
+ FullyQualifiedErrorId : NoMatchFound,Microsoft.PowerShell.PackageManagement.Cmdlets.UninstallPackage

And when I check to see if still installed:

Get-Module -Name Microsoft.Online.SharePoint.PowerShell -ListAvailable | Select Name,Version

It was still installed. But at least Connect-SPOService worked after that.

and when I try to import module

Import-Module Microsoft.Online.SharePoint.PowerShell

I now get

WARNING: The names of some imported commands from the module 'Microsoft.Online.SharePoint.PowerShell' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.

which is normal

inheritance broken for a list (or not) - see lists in a sites, what type and whether inherited permission or inheritance broken

install module - see module, install.

Make sure you've downloaded the SharePoint Online Client Components SDK

if you don't include the two Add-Type commands, the command populating $Credentials will fail.

this lists name and item count

this also assumes you don't want to see "generic" libraries like "DocumentLibrary", "Form Templates", "Site Assets", "Site Pages", "Style Library"

$User = "[email protected]"
$PassWord = ConvertTo-SecureString -String "topSecret" -AsPlainText -Force
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$Credentials =New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User, $PassWord)
$SiteURL = "https://madhatter.sharepoint.com/sites/whiteRabbit"
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
$Web = $Ctx.web #Get the web and Its subsites from given URL
$Ctx.Load($Web)
$Ctx.Load($Web.Lists)
$Ctx.Load($web.Webs)
$Ctx.executeQuery()
$Lists = $Web.Lists | ? {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $False -and $_.Title -ne "Form Templates" -and $_.Title -ne "Site Assets" -and $_.Title -ne "Site Pages" -and $_.Title -ne"Style Library"}
$Lists | select Title, Itemcount

list deleted sites, see sites, list deleted

lists in a site - see libraries in a site

lists in a site, what type and whether inherited permission or inheritance broken

what type and
whether inherited permission or inheritance broken

Quick way for just libraries with broken permission:

$web = Get-PnPWeb -Identity someSubSite
$list = Get-PnPList -web $web -Includes SchemaXml | ? {$_.SchemaXML -match "SharedWithDetails"}

more detail:

Function Invoke-LoadMethod() {
    param(
       [Microsoft.SharePoint.Client.ClientObject]$Object = $(throw "Please provide a Client Object"),
       [string]$PropertyName
    )
       $ctx = $Object.Context
       $load = [Microsoft.SharePoint.Client.ClientContext].GetMethod("Load")
       $type = $Object.GetType()
       $clientLoad = $load.MakeGenericMethod($type)
       $Parameter = [System.Linq.Expressions.Expression]::Parameter(($type), $type.Name)
       $Expression = [System.Linq.Expressions.Expression]::Lambda(
                [System.Linq.Expressions.Expression]::Convert(
                    [System.Linq.Expressions.Expression]::PropertyOrField($Parameter,$PropertyName),
                    [System.Object]
                ),
                $($Parameter)
       )
       $ExpressionArray = [System.Array]::CreateInstance($Expression.GetType(), 1)
       $ExpressionArray.SetValue($Expression, 0)
       $clientLoad.Invoke($ctx,@($Object,$ExpressionArray))
    }

# Initialize client context
$siteUrl = 'https://yourdomain.sharepoint.com/'
$username = '[email protected]'
$password = 'topSecret'
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username,$securePassword)
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$clientContext.Credentials = $credentials
$Web = $clientContext.Web;
$clientContext.Load($Web)
$clientContext.ExecuteQuery()
Write-Host $siteUrl -ForegroundColor Yellow;
$Lists = $Web.Lists
$clientContext.Load($Lists)
$clientContext.ExecuteQuery()

#Iterate through each list in a site
$ListCount = 0
$ListsCount = $lists.Count
ForEach($List in $Lists)
{
    $ListCount++
    #Get the List Name
    $BaseInfo = "'$($List.Title)' (list $ListCount of $ListsCount), List type is"
    Invoke-LoadMethod -Object $List -PropertyName "HasUniqueRoleAssignments"
    $clientContext.ExecuteQuery()
    if($List.HasUniqueRoleAssignments -eq $true)
    {
        $inherited = " broken"
        $inheritedColor = "red"
    }
    else {
        $inherited = " inherited"
        $inheritedColor = "green"
    }
    if($List.BaseType -eq "GenericList")
    {
        $ListTypeColor = "Cyan"
    }
    else {
        $ListTypeColor = "Magenta"
    }
    Write-host $BaseInfo -NoNewline; Write-host " $($List.BaseType)," -foreground $ListTypeColor -NoNewline; `
        Write-Host $inherited -foreground $inheritedColor -NoNewline; Write-Host " permission"
}

There's a way to get around including that Invoke-LoadMethod function up top. Instead of invoking

Invoke-LoadMethod -Object $List -PropertyName "HasUniqueRoleAssignments"

use this instead

Load-CSOMProperties -object $item -propertyNames @("HasUniqueRoleAssignments")

This acts as a "wrapper" to replicate what you would do with a lambda expression in C#. But in order for this to work, you have to download Load-CSOMProperties.ps1 and make it available in the same path or specify a path.

–M–

Microsoft.SharePoint.Client.SharePointOnlineCredentials - cannot find type

When you attempt the following:

$SiteURL = "https://krypton.sharepoint.com/sites/krypton"
$DirectoryRelativeURL = "/sites/krypton/Jor-El"
$User = "[email protected]"
$PWord = ConvertTo-SecureString -String "GreenKryptonite" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

You get:

New-Object : Cannot find type [Microsoft.SharePoint.Client.SharePointOnlineCredentials]: verify that the assembly containing this type is loaded.
At line:1 char:16
+ ... edentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCr ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Start by:

[AppDomain]::CurrentDomain.GetAssemblies() | ? {$_.location -like "*SharePoint*"}

And among all the other stuff, you should see something like:

GAC Version Location
--- ------- --------
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.PowerShell.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Migration.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Publishing.dll
False v4.0.30319 C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.PowerShell.ResourceManager.dll

if instead this returns no rows, check whether it's installed

modules, check if installed

Get-Module -Name Microsoft.Online.SharePoint.PowerShell -ListAvailable | Select Name,Version

if this returns nothing, install module.

module, install

Install-Module -Name Microsoft.Online.SharePoint.PowerShell

if you get

PackageManagement\Install-Package : Unable to find module providers (PowerShellGet).
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:1809 char:21
+ ... $null = PackageManagement\Install-Package @PSBoundParameters
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (Microsoft.Power....InstallPackage:InstallPackage)
[Install-Package], Exception + FullyQualifiedErrorId : UnknownProviders,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage

problems

Although some suggest deleting or renaming Modules in C:\Program Files\WindowsPowerShell\Modules\ I haven't found that to work.

–N–

–O–

OneDrive, gain admin privelege over all users'

$SiteCollAdmin = "[email protected]"

#Get all OneDrive for Business Site collections
$OneDriveSites = Get-SPOSite -Template "SPSPERS" -Limit All -IncludePersonalSite $True
Write-Host -f Yellow "Total Number of OneDrive Sites Found: "$OneDriveSites.count

#Add Site Collection Admin to each OneDrive
$i = 0
foreach ($Site in $OneDriveSites) {
$i++
Write-Host "$i of $($OneDriveSites.Count): Add Site Collection Admin to: "$Site.URL -f Yellow
Set-SPOUser -Site $Site.Url -LoginName $SiteCollAdmin -IsSiteCollectionAdmin $True
}

OneDrive, list all users'

Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like '-my.sharepoint.com/personal/'" | Select URL, Owner, StorageQuota, StorageUsageCurrent, LastContentModifiedDate | Export-Csv "onedrive-info.csv" -NoTypeInformation

–P–

PnPOnline - a module you can download here or here that helps generate list of subsites

The easiest way to get it to actually work:

Install-Module SharePointPnPPowerShellOnline

returns

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "No"): y

now that it's installed, connect to a site (how to get $credentials not covered here)

Connect-PnPOnline -Url https://yourTenant.sharepoint.com/someSite -Credentials $credentials

invoke it all by itself to list subsites:

Get-PnPSubWebs

returns:

Title         ServerRelativeUrl  Id
-----         -----------------  --
Accounting    /accounting        d856f80a-d979-444d-b0ba-af64f492efe2
Finance       /finance           f3dad18f-0ab7-4d35-8d02-fdd4fe89683c
someOtherSite /someOtherSite     ecb27c05-99ab-45b0-9bac-943624872d61

to get sub-subsites:

Get-PnPSubWebs -Recurse

retrieve only the sub-subsites for one subsite (i.e.: Subsite X)

Get-PnPSubWebs -Web "/subsiteX" -Recurse

versions availalble:

Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name,Version | Sort-Object Version -Descending

update:

Update-Module SharePointPnPPowerShell*

methods and properties

Get-PnPSubWebs | gm

view all cmdlets

Get-Command -Module *PnP*

–Q–

–R–

recently modified files, find

This recursively finds files in a library newer than 4 days (green) or 7 days (yellow). Although probably not as useful, can also be tweaked to find folders.

$SiteUrl = "https://yourTenant.sharepoint.com/someSite"
$ListName = "someLibrary"
$UserName = "[email protected]"
$Password = "topSecret"

$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)

#Bind to Site Collection
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$ctx.Credentials = $Creds

$list = $ctx.Web.Lists.GetByTitle($listName)
$query = New-Object Microsoft.SharePoint.Client.CamlQuery

# Collaborative Application Markup Language (CAML)
# for files, set to 0; for folders, set to 1
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <Where>
        <Eq>
            <FieldRef Name="FSObjType" /><Value Type="Integer">0</Value>
        </Eq>
        </Where>
    </Query>
</View>
"@

$query.ViewXml = $qCommand
$items = $list.GetItems($query)
$ctx.Load($items)
$ctx.ExecuteQuery()
Write-host "Total Items Found:"$items.Count
foreach($item in $items)
{
    if ($item["Modified"] -gt (get-date).AddDays(-4)) # last 4 days � can be adjusted
    { # ["FileLeafRef"] for just the name without the directory
        Write-Host -ForegroundColor green $item["FileRef"] - "Last Modified Time: " $item["Modified"]
    }
    elseif ($item["Modified"] -gt (get-date).AddDays(-7)) # last 7 days
    {
        Write-Host -ForegroundColor yellow $item["FileRef"] - "Last Modified Time: " $item["Modified"]
    }
}

–S–

site, create

This command seems to make a whole new site collection, not just a new site

New-SPOSite -Url https://yourtenant.sharepoint.com/sites/SomeNewSite -Owner someuser@yourtenant.onmicrosoft.com -Title " Some New Site" -StorageQuota 100

site, delete from recycle bin

Assume you've already deleted the site but now you need to remove from the recycle bin

Remove-SPODeletedSite -Identity https://yourtenant.sharepoint.com/sites/SiteToBeRemoved

sites, list

Get-SPOSite -Limit all | select Title, GroupID, url | sort Title | ogv

this doesn't necessarily list sites you'd expect - which are the subsites under any one of these main sites. I think this lists only top-level sites. And a lot of those seem like they're:

personal sites
sites created in Outlook
sites created with Planner

sites, list deleted

Get-SPODeletedSite

sub-sites in a site

see also PnPOnline - especially to recurse to sub-subsites.

sub-sites are known as "webs" in SharePoint parlance

$siteURL = "https://yourTenant.sharepoint.com"
$username = "[email protected]"
$password = "topSecret"
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials ($username, $(convertto-securestring $Password -asplaintext -force))
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$ctx.Credentials = $credentials
$Web = $ctx.Web
$sites = $Web.Webs
$ctx.Load($Web)
$ctx.Load($sites)
$ctx.ExecuteQuery()
"Site Name: $($Web.Title), Site count: $($Sites.Count)"
foreach ($site in $sites)
{
    $ctx.Load($site)
    $ctx.ExecuteQuery()
    Write-Host $site.Url "-" $site.Title
}

–T–

–U–

upload files to SharePoint

users, list all users - see also external users, list

Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All

this query takes a while, and it returns a bunch of users that look like

c:0u.c|tenant|026297b403004915085f0ff42d0925d1783f5b34058ce868f2e99e6ad2db2283
Guest Contributor

Because this command takes so long, if I need to work with this collection, I usually store into a variable:

$SPusers = Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All | ft DisplayName, Groups -AutoSize

$SPusers = Get-SPOUser -site https://yourTenant.sharepoint.com -Limit All

followed by

$SPusers | ogv