Monday, July 16, 2018

"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:

The solutions are all similar.
  • Grant permission to workflow
  <AppPermissionRequest Scope="http://sharepoint/social/tenant" Right="Read" />
  • 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.,propertyName='PersonalSpace')?@v=''
  • 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.

Monday, July 2, 2018

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.

Thursday, June 28, 2018

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:

Display name: PostMan
Application type: Web app / API
Home page (Sign-on URL):
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>


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:
Auth URL :
Access Token URL :
Client ID : 7f925812-d466-4c46-8737-0fcc1e172a98
Client Secret (Key) : abcd/efgsdksFME6u4yWLFWswZJGHlm9asdfasdflk=
Grant Type : Authorization Code

Click "Request Token" button.

Access Token:

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>

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>

Read-Only rights of the sub site:

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

Full-Control rights of the whole site collection:

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

====== 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>

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,


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

Key                     Value
grant_type           client_credentials
client_id              <ClientID>@<TenantID>
client_secret        SbyJ/JghRiadfdasfadsLFWswZJGHlm9UFDp0cU=
resource               00000003-0000-0ff1-ce00-000000000000/<company name><TenantGUID>

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

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

Launch PostMan,

https://<company name>


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

Click "Send"


The procedure is similar with C# or Javascript.

Thursday, June 21, 2018

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()



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)



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"])

Tuesday, May 1, 2018

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.

Wednesday, April 4, 2018

"TypeError: Unable to get property '{GUID}' of undefined or null reference" on Access Request list

Some site owners reported that they could not approve or reject "access requests". When they tried to click "ellipsis" button from http://SiteUrl/sites/SiteName/Access%20Requests/pendingreq.aspx , they got error message: "TypeError: Unable to get property '{GUID}' of undefined or null reference"

I checked it. In the field "permission" of the request item, it says "Can't display permissions in this view".

It seems someone (accidently) deleted the system list "access requests". This list is re-created automatically when new request arrives, but, something is wrong.

It's not so easy to trouble shoot. In the end, when I deleted the "access requests" list and then sent out a new request, I got the error message from ULS log.

To fix it is easy. We need to delete the relevant web properties after deleting the "access requests" list. Or else, it caused error "key is already in the web property bag", which stopped the remaining steps. This list could be deleted from SharePoint designer.

Below is the PowerShell script to delete those two web properties.

$WebURL = "http://SiteUrl/sites/SiteName"

$Web = Get-SPWeb -Identity $WebURL
$Web.AllowUnsafeUpdates = $true