Use PowerShell PNP to Create an Alphabetical Directory of Folders in SharePoint

I’m in the process of reorganizing a document library and wanted to store all of the documents in alphabetical folders. Yes, I’m using metadata, but I’ve passed the magic 5,000 item threshold and want to rearrange the library and leverage a rich search experience.

So, using PowerShell, how do you create a bunch of folders going from A to Z?

$siteURL = "https://sharepointed.sharepoint.com/sites/parent/child"

$conn = Connect-PnPOnline -Url $siteURL -Credentials (Get-Credential) -ReturnConnection

try{
(65..(65+25)).ForEach({     
$xy = [char]$_    
Add-PnPFolder -Name $xy -Folder "/mylibrary" -Connection $conn
})
}

catch{ Write-host -f Red "Error:" $_.Exception.Message}

More information about creating folders using ASCII:
https://devblogs.microsoft.com/scripting/use-powershell-and-ascii-to-create-folders-with-letters/

Get Files From a Folder Using PNP

How do you get all of the files from a folder in SharePoint using PowerShell PNP?

  $devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials $userCredential -ReturnConnection

    $folderName = "/Shared Documents/myfolder/anotherfolder"

    $folderItems = Get-PnPFolderItem -FolderSiteRelativeUrl $folderName -Connection $devConn

    foreach($item in $folderItems)
    {
        Write-Host $item.Name
    }

    Write-Host "done"

You could also use a search query with a path filter to get the files, just depends on your needs.

Example of using the Get-PnPListItem cmdlet with the FolderServerRelativeUrl parameter.

$devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials $userCredential -ReturnConnection

    $folderName = "/sites/spdev2/bw2/Shared Documents/myfolder/anotherfolder"

    $folderItems = Get-PnPListItem -List "Shared Documents" -FolderServerRelativeUrl $folderName -Connection $devConn 

    foreach($item in $folderItems)
    {
        Write-Host $item
    }
    

Get-PnPSearchCrawlLog Filter to a List or Library

Using the Get-PnpSearchCrawlLog cmdlet wanted to filter the returned result set to a specific list. Before you begin, you’ll want to make sure you have access to the Crawl Log: https://yourSite-admin.sharepoint.com/_layouts/15/searchadmin/crawllogreadpermission.aspx

Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/food" -Credentials (Get-Credential)

$logs = Get-PnPSearchCrawlLog  -filter "https://sharepointed.sharepoint.com/sites/food/Lists/tacos/"

foreach($l in $logs)
{
    Write-Host "    "
    Write-Host $l.Url
    Write-Host $l.ItemId
    Write-Host $l.LogLevel
    Write-Host $l.CrawlTime
}

This will filter the returned results to a specific list. Note: when using Connect-PnPOnline I use my email address and App Password. App Password can be created/found here: https://account.activedirectory.windowsazure.com/AppPasswords.aspx

Get-PnPSearchCrawlLog details: https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/get-pnpsearchcrawllog?view=sharepoint-ps

The Web application at X could not be found.

Error: The Web application at https://sharepoint.sharepointed.com could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

I created a .net console app to update some stuff in SharePoint.  When executing the .exe file with a new service account, I received the above error.

First I tried granting Shell access to the content db that I was working with but that didn’t solve the problem.

#powershell
$cDb = Get-SPContentDatabase -site "https://taco.sharepointed.com/"
Add-SPShellAdmin -UserName "domain\userAccount -database $cDb

Running the same command without the database switch fixed my problem.

#powershell
Add-SPShellAdmin -UserName "domain\userAccount"

Use PowerShell to Execute SharePoint Search Queries

In this example, I’m narrowing my search to one library and a search term.

At a high level, the script is searching the FoodSite for the word GoodTaco.

cls

function Query-SPSearch {
	param(
		[Parameter(Mandatory=$true)][String]$WebApplicationPath,
		[Parameter(Mandatory=$true)][String]$KeywordQuery,
		[Parameter()][Int32]$Count = 10
	)

	$QueryXml = @"

<QueryPacket xmlns="urn:Microsoft.Search.Query" >
    <Query>
        <Context>
            <QueryText type="STRING">$KeywordQuery</QueryText>
        </Context>
        <Range>
            <Count>$Count</Count>
        </Range>    
        <IncludeSpecialTermResults>false</IncludeSpecialTermResults>
        <PreQuerySuggestions>false</PreQuerySuggestions>
        <HighlightQuerySuggestions>false</HighlightQuerySuggestions>
        <IncludeRelevantResults>true</IncludeRelevantResults>
        <IncludeHighConfidenceResults>false</IncludeHighConfidenceResults>
    </Query>
</QueryPacket>
"@
	$ServicePath = "/_vti_bin/search.asmx"
	$SearchWS = New-WebServiceProxy -Uri ($WebApplicationPath + $ServicePath) -UseDefaultCredential
	$Results = $SearchWS.QueryEx( $QueryXml )
	# we excluded all other result sets, but just in case get the one we want:
	$Results.Tables["RelevantResults"]
}

Query-SPSearch -WebApplicationPath "https://sharepointed.com/sites/foodsite" -KeywordQuery "GoodTaco AND path:https://sharepointed.com/sites/foodsite/tacos" -Count 20 | Format-Table Title, Author, Path

Searching SharePoint Using PowerShell

In this example, I needed to search a farm for every site under a managed path. BUT, the sites I’m searching for were built using a 3rd part tool and would not correctly appear in the search results.  The problem was related to having Trim Duplicates enabled by default.  Easy fix… Set your search property trim duplicates = false.

$site = Get-SPSite "https://sharepointed.com"

$keywordQuery = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($site)<br>$queryText = "ContentClass:STS_Site AND Path:https://sharepointed.com/TACOS/*"

$keywordQuery.QueryText = $queryText<br>$keywordQuery.TrimDuplicates = $false 
$searchExec = New-Object Microsoft.Office.Server.Search.Query.SearchExecutor<br>$searchResults = $searchExec.ExecuteQuery($keywordQuery)

Write-Host "'r'n"
$table = $searchResults.Table
Write-Host $table.Length" Results Found" -BackgroundColor "Green" -ForegroundColor "Black"
$table | select Title, Path, IsDocument

The search results will display all sites that have Taco as its managed path. If you are not retrieving the results you expect, try switching TrimDuplicates = $false .

Email address is incorrect for user in SharePoint

In the process of migrating from SharePoint 2010 to 2016 and ran into a small problem.

When trying to get the email property from the SPUser class, it returned a value of domain\userName. Clearly, this is not correct and caused some other issues.

Sample code

$web = Get-SPWeb "https://webapp.taco/toppings/cheese"
$userEnsure = $web.EnsureUser("domain\yourNameHere")
write-host $userEnsure.Email

Running this returned domain\yourNameHere, when it should have returned yourname@domain.com.

Navigate to Central Admin, then cruise over to your User Profile Service. Once there, run a full synchronization.
Profile Service –> Synchronization –> Start Profile Synchronization –> Start Full Synchronization

Run the PowerShell script again and it will return the correct data.

Same idea as above but using the SharePoint ClientContext.

            using (ClientContext clientContext = new ClientContext("https://webapp.taco/toppings/cheese"))
            {
                Web web = clientContext.Web;

                clientContext.Load(web);
                clientContext.Load(web.CurrentUser);
                clientContext.ExecuteQuery();

                var userEmail = web.CurrentUser.Email;
           }

SharePoint listdata.svc Returns Error – FIXED

With SharePoint 2016 and 2013:
If you try to access listdata.svc you receive an error This page can’t be displayed or Sorry, something went wrong.
SharePoint Designer you try to open Lists and Libraries and receive a message of There are no items to show in this view.

The root problem is that the Farm is missing a feature. In SPD, if you click on All Files, Lists, then click on each list and click the Preview in Browser button (ribbon). You will sooner or later find the problem list. From there, you can remove the list or find the problem feature and unhook it.

Basic script to find the problem list in SharePoint 2013 and 2016:

function Get-WebPage([string]$url)
{
	$pageContents = ""
	
	try
	{
		$wc = new-object net.webclient;
		$wc.credentials = [System.Net.CredentialCache]::DefaultCredentials;
		$pageContents = $wc.DownloadString($url);
		$wc.Dispose();
	}
	catch{}
    return $pageContents;
}

$webX = Get-SPWeb "https://yourSpWebUrl"

foreach($list in $webX.Lists)
{
	$listUrl = $list.ParentWeb.Url + "/" + $list.RootFolder.Url
	
	$xo = Get-WebPage -url $listUrl 

	if($xo -like "*Sorry, something went wrong*")
	{
		Write-Host $listUrl
	}	
}

Make Your PowerShell Script Environment Aware

In place of hard-coding URLs for each environment, I decided to make a single script that is environmentally aware. Why? Cuts down on the number of scripts that have to be supported for a single development cycle. To make this more dynamic, you could move this to a function script and reference it from all your scripts.

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
	Add-PsSnapin Microsoft.SharePoint.PowerShell
}

#get config database server
$ConfigDB = Get-SPDatabase | where-Object{$_.Type -eq "Configuration Database"}
$serverName = $ConfigDB.Server.Displayname

#replace this with the web app you want to target.  taco, burrito, nacho...
$webApp = "taco"

#set variable equal to the environment url
$siteURL = switch ($serverName.ToLower())
{
	"dev_db" {"http://$webApp.sharepointed.com/"}
	"test_db" {"http://test$webApp.sharepointed.com/"}
	"build_db" {"http://build$webApp.sharepointed.com/"}
	"prod_db" {"http://$webApp.sharepointed.com/"}
}

Same as above, but using a wildcard in the switch statement.

$siteURL = switch -Wildcard ($serverName.ToLower())
{
	"*dev*" {"http://$webApp.sharepointed.com/"}
	"*test*" {"http://test$webApp.sharepointed.com/"}
	"*build*" {"http://build$webApp.sharepointed.com/"}
	"*prod*" {"http://$webApp.sharepointed.com/"}
}

Make sure to check $serverName = $ConfigDB.Server.Displayname
This might need to be replaced with $ConfigDB.Displayname

Use PowerShell to get all your SQL databases and their size

Quick script to get all the databases on a server, then output to a CSV file.

In this example, all I needed was the database name and its size.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YourSQLServer"
$dbs=$s.Databases
$dbs | SELECT Name, Size | Export-Csv c:\test.txt

Other properties:

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

Credit for the base script:
Edwin M Sarmiento