Monday, December 9, 2019

The possibly best way to return value from function in PowerShell

"Return" from function is tricky in PowerShell.

By default, PowerShell functions work so that when executed, they write to the $Result array all the values that were displayed during the function’s operation.

This makes "Return" useless.

How should we handle "return value" of function then? I don't want to use global variable, so, reference variable seems the only choice, as it chooses the correct data type automatically.

Below are some sample code. Please let me know if you have better idea!

  • Sample code 1
Function TestFunctionReturnValue{
    $ParaReturnObject.Value = $false
    if ($Para1){
        $ParaReturnObject.Value  = $true

$testResult1 = $null
$testResult2 = $null

TestFunctionReturnValue "hello" ([ref]$testResult1)
TestFunctionReturnValue "" ([ref]$testResult2)

Write-Host "testResult1 = $testResult1"
Write-Host "testResult2 = $testResult2"


  • Sample code 2
Function TestFunctionReturnValue2{
    $ParaReturnObject.Value = 100
    if ($Para1){
        $ParaReturnObject.Value  = 200

$testResult1 = $null
$testResult2 = $null

TestFunctionReturnValue2 "hello" ([ref]$testResult1)
TestFunctionReturnValue2 "" ([ref]$testResult2)

Write-Host "testResult1 = $testResult1"
Write-Host "testResult2 = $testResult2"



Friday, May 31, 2019

(fixed) Import-Module: The specified module was not loaded because no valid module file was found in any module directory

I tried to load module 'CredentialManager' through the script below:

Import-Module CredentialManager -DisableNameChecking

And got error:

Import-Module: The specified module "CredentialManager" was not loaded because no valid module file was found in any module directory

Then I ran "Get-InstalledModule" to check it.

No problem, the module was installed. Then why I cannot import it?

$env:PSModulePath shows:

C:\Users\username\AppData\Local\Apps\SharePointPnPPowerShellOnline;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell
\v1.0\Modules;C:\Program Files\SharePoint Online Management Shell\

C:\Program Files\WindowsPowerShell\Modules" is not there.

I can fix the problem by adding this path to PSModulePath environment variable. But, I believe the better way is to re-install "CredentialManager".

Uninstall-Module CredentialManager
Install-Module CredentialManager

After the re-installation, this module appeared in "C:\Program Files\WindowsPowerShell\Modules"


Thursday, May 30, 2019

When to use folder in SharePoint list/library?

Just read a nice article. I agree with Joanne Klein, but I only see two practical reasons to use folder.

1. Permission control.
2. Too many items in one list/library.

So, if there is choices, go for metadata!

Office 365 is too complicated!

At the moment, I can see 23 Apps under Office 365.

How can we train thousands of users to understand when to use which one?

In my opinion, we only need three apps, and Dynamics 365 should not be part of Office 365.

1. Synchronous communication: Online chatting service(Teams)
2. Asynchronous communication: Email(Outlook)
3. Information management: SharePoint

Most of those apps should join SharePoint. They are:

Calendar,Delve, Excel, Flow, Forms, OneDrive, OneNote, People, Planner, PowerBI, PowerApps, PowerPoint, Stream, Sway, Tasks, To-Do, Video, Word, Yammer

So, why it is so complicated? For more subscription fees?

Friday, May 17, 2019

Microsoft Flow - Send Email - You don’t have permissions to send emails on behalf of . Contact to check shared mailbox permissions and gain access.

When sending email from Microsoft Flow, I got the error below:

"You don’t have permissions to send emails on behalf of . Contact  to check shared mailbox permissions and gain access."

Click "Send an email" action and get the detailed error message:

"You are not authorized to send mail on behalf of the specified sending account."

Some people suggests, For the Shared Mailbox, you just need to make sure that you or whoever/whatever account will execute the flow will have Send As permissions to it. Then just set the From parameter of the function to the email address of the shared mailbox

It may work. But the easier option is to leave "From (Send as)" field blank.

Friday, April 12, 2019

Quickly delete list items in batch, through CSOM and PowerShell

"Manage Site Content and Structure" in SharePoint Online had gone. So, we cannot manually delete the items easily, anymore.

If there is less than 5000 items in the list, things are much easier: we can save the list template, then delete the list, and rebuild it through the list template. But, if there are more than 10,000 items, it's time consuming.

Here is a simple solution: Using PowerShell to load all items into an array variable, then delete the items one by one, and then commit the requests in batch.

During my test, to delete 48622 items from one list, it took around 6300 seconds. So it's around 7.7 items per second.

If you know any way to improve the performance, please let me know.

Below is the major part of the PowerShell script.

$i = 0
$iItemId = 0

$web = $ctx.Web
$lists =$web.Lists
$listTarget = $lists.GetByTitle($strListName)


$viewFields = ""
$caml = "$viewFields1000"
$caml += '
'$position = $null
$allItems = @()

    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ViewXml = $caml
    $camlQuery.ListItemCollectionPosition = $position

    $listItems = $listTarget.getItems($camlQuery)

    $position = $listItems.ListItemCollectionPosition
    $allItems += $listItems
Until($position -eq $null)

if ($allItems.Count -lt 1){
    Write-Host "$(__FILE__), No item needs to be deleted. caml=$caml"
    return 0

$iItemCount = $allItems.Count
$progressBarTitle = "$(__FILE__), Scan list '$strListName', iItemCount=$iItemCount, deleteSubFolders=$deleteSubFolders, DaysOfDataToKeep=$DaysOfDataToKeep, ReadOnly=$ReadOnly"
foreach($item in $allItems){
    Write-Progress -Activity $progressBarTitle -PercentComplete (($i/$iItemCount)*100) -Status "Working"
    $iItemId = $item.Id
    if($item.FileSystemObjectType -eq "File" -or ($item.FileSystemObjectType -eq "Folder" -and $deleteSubFolders)){
        $listTarget.getitembyid($iItemId).Recycle() > $null
        Write-Host "Invalid FileSystemObjectType, item(FSObjType: $($item.FileSystemObjectType), FileRef: $($item.FieldValues.FileRef))."
    if ($i % 1000 -eq 0){
    Write-Verbose "item(FSObjType: $($item.FieldValues.FSObjType), FileRef: $($item.FieldValues.FileRef)) is deleted."

Friday, April 5, 2019

PowerApps - save list item into list folder

We know that PowerApps doesn't support folder in SharePoint list. And Workflow 2013 doesn't support it either. As they are both triggered from remote PowerApps or Workflow server, I believe they both use REST API to communicate with SharePoint Online. And the REST API does not have complete support for folders.

So we have to use workflow to "move" the saved item into folder.

Lucky Workflow 2010 is still there, so we can use it to create list item in folder. However, we cannot create a list item from workflow 2010 instance, if the workflow is triggered by item creation.

The only choice is List Workflow 2013 + Site Workflow 2010.

When item is saved into list root folder by PowerApps form, it triggers the list workflow 2013 instance, which change the primary key field (to avoid the conflict with the forthcoming new item in the folder), then start the site workflow 2010 instance.

Pass ItemId to the site workflow parameter list.

In the site workflow, check to create folder if necessary, then create the item in the folder (specify folder name in the folder field), then delete the original item in the root folder.

The List Workflow 2013 instance will not be triggered if the item is created by workflow 2010 instance.

All these steps will be executed in less than 30 seconds during my test, so users need a little bit patience to see the result in the relevant folder.