How to trigger Power Automate during debug, test and production stage, to prevent infinite loop, with SharePoint item events

Some post recommends to set up a extra column to apply the control. It definitely works, but it needs extra column and extra actions.

Here is something much easier.

1. Define at least three dedicated user accounts

For example, tester1@company.com, developer1@company.com, PowerAutomateService@company.com

The first two accounts are for tester and developer. Power Automate runs under PowerAutomateService@company.com.

2. Change the settings of the trigger conditions

During debugging:

@equals(triggerOutputs()?['body/Editor/Email'],'developer1@company.com')

During testing:

@equals(triggerOutputs()?['body/Editor/Email'],'tester1@company.com')

During production:

@not(equals(triggerOutputs()?['body/Editor/Email'],'PowerAutomateService@company.com'))

PS: It needs three extra user subscriptions in this case, but hopefully our company doesn't mind.

Resolved - cannot create list/document library/post/link/App from home page in SharePoint Online?

Normally we can create list/document library/post/link/App directly from home page, as the screenshot below shows.

However, sometimes, these links are missing.

 

 

What happened? Not sure. But you can reactivate the site feature "Wiki Page Home Page" to recover those links.

ps: During my test, no content in the home page is lost.

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!

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.

Done.

Resolved - PowerShell script only works in Console, but not in ISE?

I'd like to collect workflow information from SPO. But the script below only works in Console, not in ISE.

$WfServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ctx,$Web)

The error message: Cannot find an overload for "WorkflowServicesManager" and the argument count: "2"

Below is how I did the trouble shooting.

1. Compare the PowerShell environment version information.

$PSVersionTable shows everything is same in Console and ISE.

2. Make sure the class exist in both environment.

$ClassName = "Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager"
if ($ClassName -as [type]) {
"$ClassName is already loaded"
}
else{
"$ClassName is not loaded"
}

Yes, the class is loaded.

3. Check how many relevant assemblies are loaded.

[appdomain]::currentdomain.getassemblies() | ?{$_.CodeBase -match "WorkflowServices"}

Here I noticed that ISE loaded a PnP assembly which also contains "Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager"

Obviously this is the root cause.

I think this is caused by old version PnP, so just removed/uninstalled these assemblies first.

Uninstall-Module SharePointPnPPowerShellOnline -AllVersions -Confirm

Then reinstalled the latest version.

Done.

SharePoint Online Data Access Performance test - CSOM and PowerShell / C#

Two years ago, I did a similar test on SharePoint 2016. That's through server-side API.

Now, it's time to test SharePoint Online through client side CSOM + PowerShell.

SharePoint Online as access threshold for one user, which make it hard to test "multiple thread" performance.

However, we still can test "ExecuteQuery for each item" and "ExecuteQuery for a batch of items".

I assume the SharePoint Online host center is quite close to where I am, as the network latency is less than 3 ms for tenant 1, and less than 15 ms for tenant 2.

* The test is based on two different tenants and 100 list items.
* The unit in the table below is "items per second".
* There is no "Bulk commit" for Delete, Recycle and Retrieve.
* 5 single-line text fields in the test list.

Action	Single, tenant 1	Single, tenant 2	Bulk, tenant 1	Bulk, tenant 2
Insert	7.1	6.6	14.1	12.7
Update	8.6	7.1	21.3	13.5
Delete	5.8	6.3	10.4	10.4
Retrieve	11.9	13.3	1000	1000

For single item "insert", it takes around 140 ms.

The performance is around 25% of SharePoint 2016 (local server). This is disappointing, even if we exclude the 15 ms consumed on the traffic.

So, it's fast for "read-only" access on SharePoint Online, but pretty slow for any "write" access.

[update, 2018-12-07]

PowerShell script is slower than C#, so I did some other test by C#

Run the C# code remotely

Action	Single, test 1	Single, test 2	Bulk, test 1	Bulk, test 2
Insert	6.90	6.09	7.79	12.91
Update	7.94	6.42	19.49	17.25
Delete	6.01	6.00	5.78	6.34
Recycle	6.46	6.59	6.37	7.10
Retrieve	1307.85	1183.34	807.41	746.23

Run the C# code from Azure API Apps

Action	Single, test 1	Single, test 2	Bulk, test 1	Bulk, test 2
Insert	8.17	7.14	7.14	12.35
Update	10.63	8.16	8.16	14.93
Delete	7.69	6.41	6.41	7.12
Recycle	9.08	6.76	6.76	6.68
Retrieve	2133.05	1600.18	1600.18	1280.42

Not much difference.

"Access denied" - the problem caused by bug fixing when accessing SharePoint Online User Profile from Workflow 2013

Need to retrieve User Profile from SharePoint Online in workflow?

Easy. There are plenty post regarding this classic scenario, such as:

http://www.cleverworkarounds.com/2014/02/05/tips-for-using-spd-workflows-to-talk-to-3rd-party-web-services/

https://www.credera.com/blog/technology-insights/microsoft-solutions/sharepoint-2013-online-accessing-user-profile-properties-in-a-designer-workflow/

https://www.c-sharpcorner.com/article/get-user-profile-properties-data-in-sharepoint-designer-2013-workflow/

The solutions are all similar.

• Grant permission to workflow

<AppPermissionRequests>
  <AppPermissionRequest Scope="http://sharepoint/social/tenant" Right="Read" />
</AppPermissionRequests>

• Build the Request Header dictionary variable.

Name Type Value
Accept String application/json;odata=verbose
Content-Type String application/json;odata=verbose
Authorization String

• Build the RESTful API URL.

https://CompanyName.sharepoint.com/sites/SiteName/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName='PersonalSpace')?@v='i:0%23.f%7Cmembership%7CUserLoginName@CompanyName.org'

• Get the value from ResponseContent by path "d/GetUserProfilePropertyFor"

Easy! But, I still got authorization issue:

{"error":{"code":"-2147024891, System.UnauthorizedAccessException","message":{"lang":"en-US","value":"Access denied. You do not have permission to perform this action or access this resource."}}}

After bumping head on wall for hours, it turns out that Microsoft fixed a previous bug: we don't need to overwrite "Authorization" in request header anymore......

Without it, everything works well.

The way to debug workflow 2013 from SharePoint Online

Thanks for the post from Andrew Connell, we got basic concept of workflow 2013 debugging.

As more and more enterprises migrating their SharePoint to Office 365, we cannot rely on "workflow history list" on debugging.

What's the solution?

So far, the only choice is "replication". Replicating the Online Site collection to On-Premise Dev environment, then test it there through Fiddler.

As Andrew Connell mentioned, we need to build the On-Premise Dev environment carefully, but it's possible to replicate the whole site through third-party migration tool, such as ShareGate, then debug from there.

ShareGate is still expensive (although it's possible the cheapest one comparing to other competent). But it should be all right for medium to large enterprise. It's not such a big number comparing to Office 365 subscription fee of the whole company, anyway.

The confusion when a user just moved from Shared Folder to SharePoint

Traditionally, how a user write a document?

1. Launch a MS Office Program, such as MS Word;
2. Give the document a topic;
3. Put content into it;
4. Save.

The problem with SharePoint is the 4th step: "Save". "How can I save the document to SharePoint?" This is one of the most common questions.

One option is to map a SharePoint document library to local network mapped folder:

For SharePoint On-Premise:

For SharePoint Online:

Then users can save the document to that Shared Drive directly, just like what they did with "Shared Folder".

That works, but then we lost most of the benefit from SharePoint.

"SharePoint" means team work. So if a user wants to write a document, below are the steps.

1. Ask themselves the question: Where should I store this document, so other users can find it easily?
2. Who should have rights to view it, and who should be able to modify it?
3. What kind of metadata should this document has? So users can get the basic information without opening it, such as "due date, document owner, project name, etc.".
4. Go to the SharePoint document library in web browser (IE 11 is recommended at the moment), then click "new" button.

If we want to get thousands of documents to be organised well, please think about the document management (as a team) with each of the documents.

SharePoint cannot do that by itself.

PS: Thanks for the reminding from my colleague Andrew Warland, nowadays, users can save the document to SharePoint sites with the help from the latest MS Office. That saves a lot of trouble.

It's still better to think more for other team members at the very beginning.

How to get SharePoint Online access authentication for third-party tools, such as Postman or Fiddler

Third-party tools need "token"(OAuth 2.0) to get authenticated. And the token is generated based on "Client Id" and "Client Secret (key)". We can do this either manually (Postman or Fiddler), or programmatically (C#, JavaScript, etc.). This is how our cloud based application to run across different cloud platforms.

You can get more details of SharePoint OAuth 2.0 here.

For SharePoint Online, we have two options to get the token, that depends on what type of admin rights we have and what we need.

There are already some pretty good posts tell us how to do it. However, I found some description is confusing, especially about the naming of some parameters. So, I try to explain it here, based on my understanding.

The GUIDs and Keys in the sample code below are all generated randomly.

• In tenant scope (need Tenant, Global or AAD admin rights)

We can follow this post. It contains 6 steps.

====== 1. Register app ======

Go to azure portal site through web browser:
https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps

Display name: PostMan
Application type: Web app / API
Home page (Sign-on URL): https://www.getpostman.com/oauth2/callback
Application ID (Client ID of PostMan, auto-generated): 7f925812-d466-4c46-8737-0fcc1e172a98
Object ID (not used, auto-generated): 864dc037-153d-4097-8105-0454bf3042fd
Managed application in local directory



====== 2. Set permissions ======

Go to the settings of this app, then click "required permission"

Set permissions as needed, such as:
Office 365 SharePoint Online -> Delegated permissions -> Read items in all site collections

====== 3. Generate Key (Client Secret) ======

test, 27/06/2020, abcd/efghijklmnopqrstuv4yWLFWswZJGHlm9UFDp0cU=

Copy the key to a safe place. This key will be used to get the token.

====== 4. Access SPO through restful API ======

Launch PostMan,

https://<company name>.sharepoint.com/sites/test2/_api/web/lists

Get

Headers
Key             Syntax                             Value
Accept         application/json; odata=verbose    application/json; odata=verbose

====== 5. Get the Oauth 2.0 Bearer Token ======

Get New Access Token

Callback URL: https://www.getpostman.com/oauth2/callback
Auth URL : https://login.microsoftonline.com/common/oauth2/authorize?resource=https%3A%2F%2Fkenowau.sharepoint.com
Access Token URL : https://login.microsoftonline.com/common/oauth2/token
Client ID : 7f925812-d466-4c46-8737-0fcc1e172a98
Client Secret (Key) : abcd/efgsdksFME6u4yWLFWswZJGHlm9asdfasdflk=
Grant Type : Authorization Code

Click "Request Token" button.

Access Token:
dfasdfferqergfasdfasdfasdfhbGciOiJSUzI1NiIsIng1dCI6IlRpb0d5d3dsaHZkRmJYWjgxM1dwUGF5OUFsVSIsImtpZCI6IlRpb0d5d3dsaHZkRmJYWjgxM1dwUGF5OUFsVSJ9.eyJhdWQiOiJodHRwczovL2tlbm93YXUuc2hhcmVwb2ludC5jb20iLCJpc3MiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9kNjU3MGM0NC1jMGY0LTQ1MzMtOGQzZC02NTdhOGFjODBlMTQvIiwiaWF0IjoxNTMwMDczMDc3LCJuYmYiOjE1MzAwNzMwNzcsImV4cCI6MTUzMDA3Njk3NywiYWNyIjoiMSIsImFpbyI6IkFTUUEyLzhIQUFBQUh4c2JkOWE5NHYxdEYwZklRWmlVNGxRczdEeWxKN29JZmZmSGNVWGNwRVk9IiwiYW1yIjpbInB3ZCJdLCJhcHBfZGlzcGxheW5hbWUiOiJQb3N0TWFuIiwiYXBwaWQiOiIzOTE4MmRhYi1iMjI4LTQ0ZTEtODhjYS1kNmM2NGY5MGNlNjYiLCJhcHBpZGFjciI6IjEiLCJmYW1pbHlfbmFtZSI6IkZhbmciLCJnaXZlbl9uYW1lIjoiRXJpYyIsImlwYWRkciI6IjIzLjEwMS4yMTcuMTU0IiwibmFtZSI6IkVyaWMgRmFuZyIsIm9pZCI6IjY0Y2ZlMzJhLTMxYzAtNDI5MC04MjQ5LTljMjY1MjI0NjBlZiIsInB1aWQiOiIxMDAzM0ZGRkFDMEMxOTdEIiwic2NwIjoiQWxsU2l0ZXMuRnVsbENvbnRyb2wgQWxsU2l0ZXMuTWFuYWdlIEFsbFNpdGVzLlJlYWQgQWxsU2l0ZXMuV3JpdGUgTXlGaWxlcy5SZWFkIE15RmlsZXMuV3JpdGUgU2l0ZXMuU2VhcmNoLkFsbCBUZXJtU3RvcmUuUmVhZC5BbGwgVGVybVN0b3JlLlJlYWRXcml0ZS5BbGwgVXNlci5SZWFkLkFsbCBVc2VyLlJlYWRXcml0ZS5BbGwiLCJzdWIiOiJPNkZtZVNnVk1ieUxXcUUtVVFhLUNxRUdxZ0ZOZW9adrwevzvzxcvcvzxVlX25hbWUiOiJlcmljZmFuZ0BrZW5vd2F1Lm9ubWljcm9zb2Z0LmNvbSIsInVwbiI6ImVyaWNmYW5nQGtlbm93YXUub25taWNyb3NvZnQuY29tIiwidXRpIjoibzY1WFZNOG53MGlWTFZxZnNvRU9BQSIsInZlciI6IjEuMCJ9.LjxUSSWnsMTzk1Mj4Y5xn2X9Q4arUxb1Tp1FDvQqckOYIlLhg8WPg0LcAOvQVBiTA3U9IkedpXaqfre6rvycj8OZI7a6UY3YUoppJMyZ9VmmvDuDHZVIawwIk61XBQGzfVrbRu5w9BJzrbTwJCw-zlGWxbtnx_Acvz1D8kPmsWKNP7OUCVjB9hlqdBx-wAwofKxNRuJRKzIcixHhwBAveNs9MoAvn-hQ3qLIuckkW6zyjhFAqo7C_n-3Gsu_ajvin0uIbEK2G_I3SqtEMOBa9ZMdCC4aq9Mlu9AADnBYMua_29-f5SoBXy1OIfjEasdfwer35asefyuksyhtBBkW1Chog

Click "Use Token"

====== 6. Get the Response Body ======

Click "Send" button. Done.

• In site collection scope (need SharePoint or Site Collection admin rights)

We can follow this post. It contains 5 steps. (I choose an alternative way in step 3)

====== 1. Register app ======

Go to SPO site through web browser:

https://<company name>.SharePoint.com/sites/test1/_layouts/15/appregnew.aspx

The app identifier has been successfully created.

Client Id:  71b53e1e-6260-4a8d-8423-8ca65439271a

Client Secret:  SbyJ/JghRiadfdasfadsLFWswZJGHlm9UFDp0cU=

Title:  postman

App Domain:  localhost

Redirect URI:  https://localhost

Copy the Client Secret (key) to a safe place. This key will be used to get the token.

Click "OK"

====== 2. Grant permission to this App ======

https://<company name>.SharePoint.com/sites/test1/_layouts/15/appinv.aspx

Read-Only rights of the sub site:

 <AppPermissionRequests AllowAppOnlyPolicy="true">
    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Read" />
</AppPermissionRequests>

Full-Control rights of the whole site collection:

<AppPermissionRequests AllowAppOnlyPolicy="true">

    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />

</AppPermissionRequests>

====== 3. Get SPO Tenant GUID, resource GUID and client GUID ======

Instead of PostMan, it's much easier to get the information from this page: https://<company name>.sharepoint.com/sites/test1/_layouts/appprincipals.aspx

The app identifier syntax is: i:0i.t|ms.sp.ext|AppGUID@TenantGUID

"resource GUID" means the app GUID of SharePoint Online itself, which is: 00000003-0000-0ff1-ce00-000000000000

====== 4. Get Bearer token ======

Launch PostMan, 

https://accounts.accesscontrol.windows.net//tokens/OAuth/2

Post

Headers

Key                     Value

Content-Type application/x-www-form-urlencoded

Body

Key                     Value

grant_type           client_credentials

client_id              <ClientID>@<TenantID>

client_secret        SbyJ/JghRiadfdasfadsLFWswZJGHlm9UFDp0cU=

resource               00000003-0000-0ff1-ce00-000000000000/<company name>.sharepoint.com@<TenantGUID>

Click "Send". Copy "access_token" to a safe place.

====== 5. Access SPO through restful API ======

Launch PostMan,

https://<company name>.sharepoint.com/sites/test2/_api/web/lists

Get

Headers
Key                               Value
Accept                       application/json;odata=verbose
Authorization               Bearer

Click "Send"

Done.

The procedure is similar with C# or Javascript.

Missing properties in workflow 2013 definition

On SharePoint workflow 2010 platform, we can get some properties straightaway from list.WorkflowAssociations, such as "AllowManual, AutoStartChange, AutoStartCreate, HistoryListTitle, TaskListTitle, Created, Modified".

However, they are not there in Workflow 2013.

Here is the tip: all properties are still there, but in different place.

Below is the PowerShell script based on SharePoint Online and CSOM.

We can get the values from "SubscriptionCollection"

$WfServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ctx,$Web)

$WfSubscriptionService = $WfServicesManager.GetWorkflowSubscriptionService()

$WfSubscriptionCollection = $WfSubscriptionService.EnumerateSubscriptions()

$ctx.Load($WfServicesManager)
$ctx.Load($WfSubscriptionService)
$ctx.Load($WfSubscriptionCollection)

$ctx.ExecuteQuery()

ForEach ($WfObj in $WfSubscriptionCollection.GetEnumerator()){

$ManualStart = $WfObj.PropertyDefinitions["SharePointWorkflowContext.Subscription.EventType"] -Match "WorkflowStart#;"

$AutoStartChange = $WfObj.PropertyDefinitions["SharePointWorkflowContext.Subscription.EventType"] -Match "ItemUpdated#;"

$AutoStartCreate = $WfObj.PropertyDefinitions["SharePointWorkflowContext.Subscription.EventType"] -Match "ItemAdded#;"

$HistoryListId = $WfObj.PropertyDefinitions["HistoryListId"]

$TaskListId = $WfObj.PropertyDefinitions["TaskListId"]

$Created = $WfObj.PropertyDefinitions["SharePointWorkflowContext.Subscription.CreatedDate"]

$Modified = $WfObj.PropertyDefinitions["SharePointWorkflowContext.Subscription.ModifiedDate"]

}

=====================
Or, we can get the values from "DefinitionCollection"

$WfServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ctx,$Web) $WfDeploymentService = $WfServicesManager.GetWorkflowDeploymentService() $WfDefinitionCollection = $WfDeploymentService.EnumerateDefinitions($false)

$ctx.Load($WfServicesManager)
$ctx.Load($WfDeploymentService)
$ctx.Load($WfDefinitionCollection)

$ctx.ExecuteQuery()

ForEach ($WfObj in $WfDefinitionCollection.GetEnumerator()){
 $ManualStart = [System.Convert]::ToBoolean($WfObj.Properties["SPDConfig.StartManually"])   $AutoStartChange = [System.Convert]::ToBoolean($WfObj.Properties["SPDConfig.StartOnChange"])
 $AutoStartCreate = [System.Convert]::ToBoolean($WfObj.Properties["SPDConfig.StartOnCreate"])
 $HistoryListId = $WfObj.Properties["HistoryListId"].Trim("{","}")
 $TaskListId = $WfObj.Properties["TaskListId"].Trim("{","}")

 $Created = $WfObj.Properties["Definition.CreatedDate"]
 $Modified = $WfObj.Properties["Definition.ModifiedDate"]
 $AutosetStatusToStageName = [System.Convert]::ToBoolean($WfObj.Properties["AutosetStatusToStageName"])
}

Coding on CSOM - Performance

Server Object Model is really quite different from Client Object Model. In my opinion, one of the the major difference is "latency".

When migrate the same functionality from SharePoint on-premise Server Object Model to SharePoint Online Client Object Model, it's much slower. However, we can change the "pattern" to make it much faster.

Below is what I do for most of the functionalities in PowerShell. Other languages should be similar.

1. Download relevant data from remote data source (such as SharePoint Online lists) to hash table variables.

2. Process the data.

3. Upload (sync) the changed data back to the remote data source.

For the unchanged data, we can skip them to improve performance.

4. Delete the data from the remote data source as needed.

After migrating a few modules from SOM to COM, I realized that it's not hard at all. The best part: I don't need to worry about "SharePoint memory leaks" any more.

How to implement "GetItemsWithUniquePermissions" through PowerShell and CSOM

In C# or JavaScript, it's easy. As this link shows, we can do it through the script below:

var items = list.GetItems(CamlQuery.CreateAllItemsQuery());
ctx.Load(items, col => col.Include(i => i.HasUniqueRoleAssignments));
ctx.ExecuteQuery();
int itemCount = items.Where(i => i.HasUniqueRoleAssignments).Count;

However, can we do similar thing in PowerShell by ONE "ctx.ExecuteQuery()" submit?

The answer is YES.

Below is the script.

$query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()

$items = $list.GetItems($query)

$items | %{

$_.Retrieve("HasUniqueRoleAssignments")

$ctx.Load($_)

$ctx.Load($_.RoleAssignments)

}

$ctx.ExecuteQuery()

foreach($item in $items){

if ($item.HasUniqueRoleAssignments){

# your code here

}

}

If there are too many items in the list, we may see the error message:

"The request message is too big. The server does not allow messages larger than 2097152 bytes"

Based on my test, 1000 items is fine. In that case, we need to do it in batches. Below is the script.

$Global:_BatchRowLimit = 1000
$caml = ""
$viewFields = ""
$position = $null
$allItems = @()

Do{
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml = "$caml$viewFields$Global:_BatchRowLimit"
$camlQuery.ListItemCollectionPosition = $position

$listItems = $list.getItems($camlQuery)
$ctx.Load($listItems)
$ctx.ExecuteQuery()

$listItems | %{
$_.Retrieve("HasUniqueRoleAssignments")
$ctx.Load($_)
}
$ctx.ExecuteQuery()

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

How to handle "429" error in PowerShell

Sometimes we got error "The remote server returned an error: (429) Too Many Requests", when accessing SharePoint Online through PowerShell script.

Below is how I handle it:

$Global:_retryCount = 1000
$Global:_retryInterval = 10

for($retryAttempts=0; $retryAttempts -lt $Global:_retryCount; $retryAttempts++){

Try{

$ctx.ExecuteQuery()

break

}

Catch [system.exception]{

Start-Sleep -s $Global:_retryInterval

}

}

Simple way to get absolute URL of a list object through PowerShell and CSOM

There is no absolute URL property in list object.

Below is the relevant attribute values:

$oList.RootFolder.ServerRelativeUrl: /sites/SPAdmin/Lists/testList1
$oWeb.Url: https://company.sharepoint.com/sites/SPAdmin
$oList.ParentWebUrl: /sites/SPAdmin

So, we can get the url here:

$url = $oWeb.Url + $oList.RootFolder.ServerRelativeUrl.Replace($oList.ParentWebUrl, "")

The result is:

https://company.sharepoint.com/sites/SPAdmin/Lists/testList1


Hope this script saves you a few minutes.

[update 20180123]

If the user account has SharePoint admin rights, we can do it through tenant "RootSiteUrl" property.

$oTenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($ctx)
$Global:_RootSiteUrl = $oTenant.RootSiteUrl
$url = $Global:_RootSiteUrl + $oList.RootFolder.ServerRelativeUrl

How to check available properties of CSOM client object in PowerShell?

The script is quite simple, but it took me quite a while to figure it out.

The variable "$obj" could be any client object, such as "web", "content type", etc.

$obj.psobject.properties | ?{$obj.IsPropertyAvailable($_.Name)} | %{
 Write-Host "$($_.Name): $($_.Value)"
}

Some thoughts about Microsoft FLOW

After watching Deep dive: Advanced workflow automation with Microsoft Flow, I have to admit that FLOW is much more powerful than I thought. It can replace SharePoint workflows in most of the cases!

However, as it is designed for power users, I smelled something bad.

1. Now I start to understand that why "everyone needs to learn coding". Simple coding(or drag and drop style software development) allows users to do much more work efficiently.

2. We will get millions of worst "software programmers" to build billions of FLOW modules. These FLOW modules may run very slow, may consume a lot of hardware resource, and almost no one can maintain them. Because these FLOW modules are running in Azure, clients need to pay much higher fee than normal. (Microsoft will be very happy about that, and we cannot blame Microsoft)

3. No user would write document for the FLOW functionalities they build.

4. Fix/improve those FLOW modules is not easy, and troubleshooting on those modules would be nightmare.

5. Who is going to test the FLOW modules built by users? A module may accidentally delete a lot of data (which may not be able of recovering), or send out thousands of emails.

6. For most of the FLOW functionalities, if a developer can do it through C# in one day, he/she may need 3 days to do it through "drag and drop". And it's pretty hard to maintain those functionalities. It would take much more time to make minor changes.

7. Not sure how many security issues it will cause if we allow users to build their own FLOW modules.

8. If Microsoft decides to change/upgrade/obsolete some API/function, who is going to upgrade existing customized FLOW modules?

Conclusion: FLOW is too powerful, so it is not for power users but for developers. Normal power users can use it, but only for very simple functionality, especially when some system other than SharePoint is involved. Only in those cases, FLOW is useful to power users and can improve productivity.

When should we use "Office 365 Groups"?

[updated regarding "Group Identity", 2017-07-04]

There are quite a lot of posts (such as here, here, and here) trying to explain what "Office 365 Groups" is. But, after reading them, I am still confused.

"Office 365 Groups" covers many areas: Azure AD, permission management, document depository (SharePoint sites), email (Exchange Online), group chat (Microsoft Teams), Yammer, etc. But,

• When should Group be used?
• Should we convert existing Email Distribution List into Group automatically, during the migration to Office 365?
• Should we allow users to create new Group when they believe it is necessary?
• Should we use Group Site to replace normal "Team Site"?
• Should we create a Group for a new project?
• Can one user belong to more than one Group?

After months of investigation, finally, I got my own conclusion. However, it is so faraway from the mainstream opinion, it even shocked myself!

There is only one principle: Office 365 Group should be based on basic "Group Identity Job Role".

Why? Because Group is designed to conquer the challenge of communication around group. Let me explain it through an use two cases here.

1. Communication between a person and a team

A user, let's say, Tom, needs help from DBA team to fix a database issue.

Obviously Tom doesn't care which member of the DBA team can help, and he just want the problem get resolved. The procedure may take months, existing DBA member may takes annual leave or even resign, new DBA may join the team at anytime. The communication between Tom and DBA team should not be affected by the those events, and it happens in many apps, such as SharePoint, Yammer, Outlook, Microsoft Teams, etc.

In other words, Tom wants to talk to DBA team, instead of any specific DBA, without the obstacles of permission management, information sharing, action history tracking.

That's why we need Group. Group should only be created for the bottom level, most basic organisation unit, just above individual user.

We should create a Group for DBA team (if there is more than one DBA in the team, and they all look after all databases), but not for IT department (because there is no such job role called "IT staff").

2. Communication in a team which needs to be shared across the whole team

For small projects, if it needs a lot of communication and those communication need to be shared across the whole team, then, it's better to create a group for it.

So, group members can use different tools to talk to each other, and don't need to worry about permission management or missing relevant project information.

=========

Now, it's easy to answer the previous questions.

• When should it be used?

For each job role, if there are more than one person share the same responsibility, we should create a Group for them.

Normally it's just a few users, but, in some special case, for a role like "help desk", there could be more than 30 people.

• Should we convert existing Email Distribution List into Group automatically, during the migration to Office 365?

No.

Unless the DL is created exactly for a job role.

• Should we allow users to create new Group when they believe it is necessary?

No.

Users should contact Office 365 administrators to create it.

• Should we use Group Site to replace normal "Team Site" during migration (from On-Premise to Office 365)?

No.

Unless the existing team site is created exactly for a job role.

• Should we create a Group for a new project?

It depends.

Normally it's needed for small projects.

• Can one user belong to more than one Group?

Normally, no.

Unless he/she happens to take two roles in two teams.

I know many people have different thoughts about "Office 365 Groups". Any comments are welcome!

PS 1: Microsoft Teams are perfect Application for Office 365 Group. We can create a new team/channel for each topic/task.

PS 2: Microsoft Teams really should be part of "Skype for Business".

PS 3: Documents should not be copied from SharePoint Sites to "Teams Files". It's much better to just create a file link in Microsoft Teams.

Simple Email Reminder through SharePoint Workflow 2013

For SharePoint reminder, my first thoughts is "scheduled PowerShell script". Three years ago, I posted how to do that. But that needs SharePoint administrator to get involved.

Can business users do it by themselves? Yes, they can, but the workflow is a bit complicated.

Thanks for the "Loop" functionality from SharePoint Workflow 2013, we get much simpler solution.

But it's not as simple as it should be, due to lack of "DateTime" relevant functions.

Anyway, only one workflow and one list is needed.

1. Workflow.

2. Three variables are needed in the workflow. ("create" is automatically created by Designer)

3. Three calculated fields "CurrentDay, CurrentHour, CurrentMinute" are created here.

But normally we only need one of them.

To send out email every hour, we need field “CurrentMinute”; (this is the one being used in the example above, pause for one minute each time)

To send out email every day, we need field “CurrentHour”; (pause for one hour each time)

To send out email every month, we need field “CurrentDay”. (pause for one day each time)

When the value of field “Title” is set to “exit”, the workflow will exit.

Every time when an email is sent out, a new item is created in the same list.

[update, 2017-06-06]

The other way is to do it through OverDue Task. Two emails will be sent out, and can only be sent to the same SharePoint user group (or same user).

But normally that's fine.

Since it's much easier to configure, I believe it's a better solution.

Best simple guide about SharePoint search

Found an excellent post about SharePoint Search

It covers all major parts, and can be read through in a few minutes.

Strongly recommend it!

All credit goes to icansharepoint