CHunky Universe of Vigourous Astonishing SHarepoint :)

Optimizing lookups in PowerShell

Have you had a PowerShell script that contains two bigger arrays and you wanted merge the information. It can become quite slow if you need to search for every item from array A through all items in array B. The solution is called a HashTable! It might be not an advanced tip for some, but I was really glad to see a huge improvement, so I decided to share it as a post.

My Array A ($sites) is a list of SharePoint Sites (over 10K of them). For every site I need to get information on the owner (such as UsageLocation). In order to minimize calls to the server I want to reuse the information – in my array B: $users. This array of users has also thousands of entries.

Here is my main (simplified) setup:

$users = # @() array, code ommitted for brevity
$sites = # @() array, code ommitted for brevity
$sitesAndOwners = $sites | ForEach-Object {
Site = $_
Owner = GetUserInfo($_.Owner)

Traversing the array B for the right item for every entry in array A is slow: Where-Object:

function GetUserInfoSlow($upn) {
$user = $users | Where-Object { $_.UserPrincipalName -eq $upn }
if ($user.Count -eq 0) {
$user = Get-AzureADUser SearchString $upn
$users = $users + $user
return $user

Using a hashtable is much faster:

$sersHash = @{}
function GetUserInfoFast($upn) {
# we check if there is an entry even if value is null
if ($sersHash.Contains($upn)) {
$user = $sersHash[$upn]
else {
$user = Get-AzureADUser SearchString $upn
$sersHash.Add($upn, $user)

In my example it took hours first. Now it takes seconds. A bonus: here is how you can convert an array to a hash table:

#bonus: convert array to a hash table
$users | ForEach-Object {
$usersHash.Add($_.UserPrincipalName, $_)

That’s all I’ve got today. Sharing is caring… And of course, big thanks to my colleague Anton H. for his advise.

Page Diagnostics for SharePoint

While trying to set up a new Home Site, I discovered that there is a tool (browser extension) called Page Diagnostics for SharePoint.

After running this, I tried that command again and it was smart enough to detect the problem the tool discovered.

Also Network Trace is available.

Network trace

Page Diagnostics Tool is defnitely a tool to have in the troubleshooting toolbelt for SharePoint.

Setting up a Home Site

Here is the script:

# Sets up a SharePoint Home Site at Skanska
$tenant = "takana17"
Connect-SPOService https://$
$baseUrl = "https://$"
# site swap takes 1-2 minutes. be patient
Invoke-SPOSiteSwap SourceUrl "$baseUrl/sites/futurehomesite" TargetUrl "$baseUrl" ArchiveUrl "$baseUrl/sites/oldroot-deleteit"
# Home Site. Docs:, it make take some time
Set-SPOHomeSite HomeSiteUrl $baseUrl
view raw spo-home-site.ps1 hosted with ❤ by GitHub

Deploying SPFx using Office 365 cli, custom AAD App and Azure Pipelines

In this post I would like to share some findings from setting a deployment of SPFx. In my work:

  • I need to deploy SPFx solutions using Azure Pipelines
  • I need to use the least privileges/permissions
  • I cannot use Legacy Authentication

First of all, big thanks to @waldekm and the whole community of @office365cli and @m365pnp for the quick help, and that outside working hours.

Let’s take a look at the setup piece by piece

Least Privileges

I followed this guide to set up a custom App Registration for Office 365 CLI in order to use the least privileges:

Custom Azure AD App

For uploading and deploying SPFx packages I found these permissions to be the bare minimum:

  • Delegated Microsoft Graph User.Read
  • Delegated SharePoint AllSites.FullControl

Service Account

The second part is the service account that just has access to one site collection – Tenant App Catalog. That plus Delegated AllSites.FullControl of the app registration narrows the access to just that site. To install apps the Uploader Account needs to be Site Collection Administrator.

Least privileges for SPFx Upload & Deploy

Azure Pipelines

In our project we use Azure Pipelines where we also define the release using .yml. The deployment consists of series of bash inline scripts.

I am not going to describe all the steps for setting up node, npm and installing the office 365 cli. If you already have used Office 365 CLI with the default AAD APP it might look like this:

task: Bash@3 # login
displayName: "Login to O365 spAppCatalogSiteUrl with user $(username)"
targetType: "inline"
script: 'o365 login "${{ parameters.spAppCatalogSiteUrl }}" -t password -u $(username) -p $(password)'
task: Bash@3 #upload
displayName: "Upload web part ${{ parameters.spfxPackageName }} to catalog"
targetType: "inline"
script: 'o365 spo app add -p "$(Pipeline.Workspace)/${{ parameters.environment }}/${{ parameters.spfxPackageName }}" –overwrite'
task: Bash@3 #deploy
displayName: "Deploy ${{ parameters.spfxPackageName }} web part"
targetType: "inline"
script: 'o365 spo app deploy –name "${{ parameters.spfxPackageName }}" –appCatalogUrl "${{ parameters.spAppCatalogSiteUrl }}"'
view raw deploy-spfx.yml hosted with ❤ by GitHub

Now comes the tricky part! If you followed the guide mentioned above, you must have noticed the two environment variables that you need to have:

export OFFICE365CLI_AADAPPID=506af689-32aa-46c8-afb5-972ebf9d218a
export OFFICE365CLI_TENANT=e8954f17-a373-4b61-b54d-45c038fe3188
view raw hosted with ❤ by GitHub

That’s straight forward when you run the cli in your own console. But the fact is (or at least from what I can see), you cannot “export” variables to other pipeline tasks.

Instead of setting the variables in the inline script, we can take advantage of the Bash task parameter called env:.

Some other findings:

  • Office 365 CLI needs them in all three commands: login, spo app add, and spo app deploy
  • If you create and export a variable in a pipeline task, it won’t persist, because every task starts a new shell session.

That means that we need to provide environment variables in every task in the pipeline, that uses Office 365 CLI with a custom Azure AD App. Or is there a better way? Anyway, the version below (the same tasks plus `env`) will work:

task: Bash@3 # login
displayName: "Login to O365 spAppCatalogSiteUrl with user $(username)"
targetType: "inline"
script: 'o365 login "${{ parameters.spAppCatalogSiteUrl }}" -t password -u $(username) -p $(password)'
OFFICE365CLI_AADAPPID: "${{ parameters.o365cliAppId }}"
OFFICE365CLI_TENANT: "${{ parameters.tenantId }}"
task: Bash@3 #upload
displayName: "Upload web part ${{ parameters.spfxPackageName }} to catalog"
targetType: "inline"
script: 'o365 spo app add -p "$(Pipeline.Workspace)/${{ parameters.environment }}/${{ parameters.spfxPackageName }}" –overwrite'
OFFICE365CLI_AADAPPID: "${{ parameters.o365cliAppId }}"
OFFICE365CLI_TENANT: "${{ parameters.tenantId }}"
task: Bash@3 #deploy
displayName: "Deploy ${{ parameters.spfxPackageName }} web part"
targetType: "inline"
script: 'o365 spo app deploy –name "${{ parameters.spfxPackageName }}" –appCatalogUrl "${{ parameters.spAppCatalogSiteUrl }}"'
OFFICE365CLI_AADAPPID: "${{ parameters.o365cliAppId }}"
OFFICE365CLI_TENANT: "${{ parameters.tenantId }}"
view raw deploy-spfx-env.yml hosted with ❤ by GitHub

Eliminating Legacy Authentication

My goal is to remove the need of legacy authentication. Previously we installed spfx packages using PnP PowerShell. PnP PowerShell in Pipelines causes Legacy Authentication, it can be solved, though:

Using Office 365 CLI rather than PnP PowerShell with a certificate has some significant benefits:

  • Office 365 CLI is multi-platform, you can reuse the scripts. PnP PowerShell requires Windows (yet, but still).
  • Setting up certificates and using it in the deployment process is a bigger initial task.

Release Pipelines

Just for completeness, in a classic release pipeline, you can use a bash script to upload and deploy an app:

#runs in Ubuntu 20.04 Bash Task
sudo npm install -g @pnp/office365-cli
o365 login –authType password –userName $(AppCatalogUsername) –password "$(AppCatalogPassword)"
export filePath="$(System.DefaultWorkingDirectory)/dist/$(env)/$(fileName)"
o365 spo app add -p "$filePath" –overwrite
o365 spo app deploy –name "$(fileName)" –appCatalogUrl "$(AppCatalogSiteUrl)"
view raw hosted with ❤ by GitHub

In our example we also send data to Azure CDN using Azure CLI:

az storage blob upload-batch \
–source $(sourceFolder)/bundledFiles \
–destination $(storageContainer)/$(toolPath) \
–account-name $(storageAccount)
view raw hosted with ❤ by GitHub

Power Automate for a one-time operations

Honestly, Power Automate is great for automating repetetive stuff. But I think there is room for one-time flows as well. I’ll give you an example.

I’ve got an excel file with quite a few rows. And I need to convert it to a SharePoint List. I know there is a couple of options, such as Quick Edit in Classic View, Import an Excel file as a list (it also requires the classic view), there will be Excel import in Modern as well. But I need to also change the column names, maybe adjust something “on-the-go”.

If you had asked me to do that same thing one year ago, I would have created a script (powershell or javascript), loaded the rows and created all the list items.

But today, I find it much faster to set up a Power Automate (No worries, there is still need of “real” scripts and applications).

So my spreadsheet has two columns.

I create a new SharePoint List and adjust the columns to my needs.

After that I set up a very simple flow.

I could have loaded that excel, but I just pasted the rows directly in that flow. Hey, I will only run this once!

A positive side effect is that I also get a verification of the user accounts (my second column)

Since it run in an “Apply to each”, it keeps working even if specific rows fail.

Modern Team Site without an Office 365 Group

These are my findings around Modern Sites without Office 365 Groups. It is, of course, a subject to change.

Today (2020-02-21) when you create a Modern Team Site without a group, you will get a site with the template STS#3. This oldie has been around for a while, hasn’t it?

I would always recommend creating Office 365 Group Connected sites.

How it is created

Through PowerShell/REST or from SharePoint Home, if your account is not allowed to create Office 365 Groups, it will automatically create a site without a group.

Site Creation Speed

It is really fast, much faster than the old STS#3, so something is different.

Custom Script

CustomScript is enabled. Which is the same as DenyAddAndCustomizePages=Disabled. CustomScript is a security risk, so I hope Microsoft will change that.


Yes, connecting to a new group is possible.

Listing Team Sites without Office 365 Groups

In SharePoint Admin you can filter them based on “Team Site (no Office 365 group)”

Multilingual MS Forms

Want to translate your MS Forms into other languages? Create a form, not a quiz. It is available in both Forms and Forms Pro licenses.

Today I want to share one of my findings. One of those that seem obvious once you know, but that take time to find out.

Unfortunately, there is no official comparison of what is included in MS Forms vs. MS Forms Pro. So I thought that the ability to have forms in multiple languages was connected to the license.

It showed after many tests, that it is up to the type of form. The Form has the “Multilingual” feature. The Quiz does not have. Why? I don’t know. 🙂

Lesson learned: If you want to have your form in different languages, create a form, not a quiz in MS Forms.

One of the reasons why it is confusing is also the translation into Swedish.

  • Form – FormulĂ€r
  • Quiz – FrĂ„geformulĂ€r. You can say “Quiz” in Swedish, too. FrĂ„geformulĂ€r is very confusing.
English interface of MS Forms
Mulilingual only availalbe in a form, not a quiz

Using Sway as a simple static site builder

Sometimes all you need is just a simple static web page: instructions, a landing page, a collection of links. I think I have a perfect use case for Sway. Consider a scenario similar to what Laura Kokkarinen writes in her blog post:

An external user invitation needs an inviteRedirectUrl. Usually it is In Laura’s case it was a given extranet url.

In our case we don’t know where an external user will land. After the invitation the external user will be added to some team or a collaboration site.

The default is a tool where a user can administer his account and accesses, but it might be a confusing place to be sent to after the invitation acceptance process.

A simple static page with clear information is just enough in our case. Fortunately, there is Sway, a simple (but still great) web page builder.

An example of a landing page, defined in Sway.

Following alternatives were considered for our landing page:

  • An “extranet” page in SharePoint Online. It takes time to set up if you don’t have an extranet.
  • A page in a public portal. Comms and IT must be involved.
  • A static web page in a blob storage / Azure CDN. It requires some basic web development for design and IT-driven deployment.
  • Azure App or Azure Function. Actually here it would mean going beyond static. For the initial phase, serving a static page, would also mean basic development and deployment by IT.

Advantages of a Sway page

  • Easy to create a static web page
  • Beautiful templates and an easy way to alter the design
  • Can be driven by the business/comms completely. We only need the url (to put into the invitation call to MS Graph).
  • Does not require any development or deployment.
  • Videos, documents can be embedded easily
  • A sway can be shared with anyone using the link. It means no additional infrastructure steps for this to work (such as firewall, dns etc).

There are some disadvantages, too:

  • The domain is too generic: It might look suspicious to some users. Maybe there is a way to use own domain?
  • A Sway cannot have different languages and switch them based on the user’s locale. It would be great to have something similar to the “Multilingual” functionality in Forms. But still, as a static web page, Sway is doing great, even without the “Multilinguality”.


Sway is an easy “business friendly”, no-code solution for simple, still good-looking web pages, that can be created and updated in no time and shared easily. Being a member of the bigger Microsoft 365 ecosystem, it fills a certain gap where the business can work together with IT and deliver solutions faster.

Kalendern i SharePoint

Dags för ett svenskt inlÀgg igen. Idag vill jag titta pÄ kalenderfunktionaliteten i SharePoint Online.

Fortfarande gammalt (classic) utseende

TyvÀrr Àr det gammalt utseende som gÀller och det finns inga planer frÄn Microsoft att modernisera kalendern:

Jag förstÄr att det Àr vÀldigt mycket kod för att fÄ till kalendervyn och att det inte Àr sÄ lÀtt omvandla till ett modernt utseende, men det stÀller till eftersom det upplevs som gammalt och inte anvÀndarvÀnligt ute i verksamheten.

Varför behövs en SharePoint-kalender

Jag ska lista nĂ„gra alternativ och förklara varför en gammal dinosaurie Ă€r fortfarande det enda vettiga alternativet (i vĂ€ntan pĂ„ en “modernisering” av lsitan)

En Office-365-gruppkalender

Det Àr en bra funktionalitet för en enkel kalender. Den saknar:

  • Stöd för extra kolumner och innehĂ„llstyper
  • Den Ă€r svĂ„r att dela med externa anvĂ€ndare

HĂ€ndelser (Events)

Om du inte hittar lÀnken till den hÀr sidan, finns den under <site>/_layouts/15/Events.aspx. En snygg modern sida som kan visa kalenderhÀndelser frÄn flera kalendrar pÄ SharePoint-siten. Dess begrÀnsningar Àr:

  • GĂ„r inte att anpassa, eftersom det Ă€r en systemsida (layous-sida)
  • Den visar bara det minimala om hĂ€ndelser

En kalender utanför Office 365

Har man egen kalenderdata, Àr man friare, men man förlorar fördelarna med att ha/betala för en dyr platform. Om man ocksÄ har ett eget grÀnssnitt för det kostar det att i lÀngden med alla anpassningar och buggrÀttningar.


I SharePoint skulle man kunna ha en SPFx-lösning för att presentera kalenderdata pÄ ett snyggt sÀtt. Men det fÄr avvÀgas mot kostnader. Det fina med inbyggda kalendern var att den bara fanns dÀr, den ingick i plattformen.

Kalendern i SharePoint

Till fördelarna av kalendern i SharePoint hör:

  • Det Ă€r en del av ett större ekosystem. Power Automate eller PowerApps kan kopplas pĂ„, som ett exempel. Det visas ocksĂ„ i moderna Events.aspx
  • Det Ă€r anpassningsbart pĂ„ ett enhetligt sĂ€tt, som alla andra listor. AnvĂ€ndare med rĂ€tt behörighet kan skapa nya kolumner, definiera innehĂ„llstyper med mera.
  • PnP-mallramverket kan bĂ„de exportera och importera en kalender.
  • Listvyer, behörigheter, mappar, allt det ingĂ„r om man behöver det, pĂ„ samma sĂ€tt som det gör för alla andra listor i SharePoint.
  • Möjlighet till sök och aggregering ifrĂ„n flera siter.
  • En chans att det moderniseras utan att man behöver investera nĂ„got i det extra.
En förenklad vy genom “HĂ€ndelser” av samma kalenderlista.

Lite tips och tricks för gamla kalendern

HÀr kommer en liten samling av enkla men intressanta tips för gamla kalendern.

#1 VÀlj tid för en ny hÀndelse med en annan innehÄllstyp pÄ ett smidigt sÀtt

Det hÀr har varit en av de sakerna som man varit mest frustrerad över. NÀr en kalender har flera innehÄllstyper, gÄr det inte att byta innehÄllstyp i NewForm nÀr du klickar i kalendervyn.

Om du vÀljer en annan innehÄllstyp, mÄste du manuellt ange tiden. Det tar tid. Föreslagna tiden Àr bara aktuella timmen hÀr och nu.

Tipset Àr att:

  1. Hitta rÀtt dag och markera rÀtt tid i kalendervyn. Rutan fÄr en blÄaktig fÀrg.
  2. Klicka pĂ„ “Ny hĂ€ndelse” i Ribbon (vad var svenska namnet för det?).
  3. Vips sÄ har du tid och datum rÀtt ifrÄn början

#2 Kalenderöverlag

En klassiker, en av mina favoriter Ă€r möjligheten att visa kalenderhĂ€ndelser med olika fĂ€rger – kalenderöverlag. TyvĂ€rr Ă€r det begrĂ€nsat med fĂ€rger.

#3 Visa veckovyn utan att Àndra instÀllningar

I lĂ€nken till din kalender (t.ex. i vĂ€nstermenyn), lĂ€gg till “?calendarperiod=week” i URL:en, sĂ„ visas veckovyn automatiskt.

Hiding Teamify Prompt

If you want to remove the Microsoft Teams Banner on your SharePoint Site, the only thing you need is to set a web property on a site: TeamifyHidden=TRUE. I’ll give you some guidance below. But before you do that, consider following:

  • If there is already a team created for a group connected site, the prompt won’t show up. Why fix something that is not a problem?
  • Only group owners will get the prompt, if they are few and they know what it is, it is better to let them to decide whether to create or not to create a team.
  • Only licensed users within your organization will be shown that choice. No external users or users without a license.
  • And the most important part: if any site owner selects “Don’t show me again” it will stop popping up for all other site owners. If you happen to have a manual step in the group creation process, then you can just click it away.
“Don’t show me again” option is per site, not per user.
Internally, it is stored as a web property on root site of a site collection.

How to hide Teamify programmatically

Allright, if you still want to hide this prompt and you want to do it programmatically, you need to ensure that this web property is on every site collection root site.

Iterate through all sites

You can find a script for that on Microsoft Fasttrack github repository:

This script has a couple of drawbacks:

  1. Performance and need for re-run. It iterates through all existing sites, meaning that it takes time to iterate through all of them and it requires that you run it again. In an organization with many new collaboration sites it might be a bad option.
  2. Access to all sites. It requires SharePoint Admin, which is okay-ish, but it also requires that this admin account has access to all sites, in order to update the web property, which is not a good option when admins have a restricted access to actual sites due regulations.
  3. CustomScript. CustomScript is disabled by default for a reason. Because it is a security risk. At least you should alter the script to reset the CustomScript setting on a site after you have changed the web property.

Hide Teamify site by site

You can of course hide the Teamify Prompt on a site without iterating. It is the same principle, but it doesn’t require iterating through all sites. You still need to be a SharePoint Administrator (or a Global Admin of course) in order to enable Custom Script. As in the case with all sites, you should reset the CustomScript to what it was.

Use GroupSiteManager Api

There is a new SharePoint Site API that you can call to hide a Teamify Prompt. All you need is to use the solution described in this sharepoint stackexchange answer:

This still requires that access to the site, but you will not need SharePoint Admin, nor will you need to change any CustomScript settings on a site. That’s better.

Calling PnP Rest Call to hide the Teamify Prompt.


There are ways of hiding the Teamify Prompt on a site collection. If you do that you should include that step in some provisioning process, and not iterate through all sites. It is better to not alter the CustomScript settings at all. First of all it is best to ask yourself why you should disable something that is done for users and for a better user adoption. Also consider that usually only few will be shown that (group owners with a license and within the organization). The group owners can also hide it by clicking “Don’t show me again” and it will disappear for all other group owners.

Using secrets in Logic Apps in a secure way

This is a guide for how to handle secrets in a logic app in a secure way. It combines three resources:

First, enable a Managed Identity for your Logic App:

In the KeyVault, add a new Access Policy for the new Managed Identity (from the previous step). Use the least priviliges. In my case it is just enough with GET for secrets.

Next add an HTTP action to the key vault.

The values should be:

Next, open the Settings of the “Get Client Secret” action and tick the “Secure Outputs (Preview)”

To get the secret we need to parse the http response. Only the value is needed.

"properties": {
"value": {
"type": "string"
"type": "object"

Now let’s call the Graph API and authenticate using this secret:

In the run history we can see now, that the password is not shown anymore.

Neither it is visible in the next http call:

Note that the run history is kept for a while, if you have used secrets in plain text, it is a good practice to change them.

Đ’ŃƒĐ»Đ° ЧăĐČашла

VulaCV - ЧăĐČашла ĐČŃƒĐ»Đ°Ń‚Ń‚Đ°Ń€Đ°ĐșĐ°Đœ саĐčт

Discovering SharePoint

And going crazy doing it

Bram de Jager - Architect, Speaker, Author

Microsoft 365, SharePoint and Azure

SharePoint Dragons

Nikander & Margriet on SharePoint

Mai Omar Desouki

PFE @ Microsoft

Cameron Dwyer

Office 365, SharePoint, Azure, OnePlace Solutions & Life's Other Little Wonders


Me and My doings!

Share SharePoint Points !

By Mohit Vashishtha

Jimmy Janlén "Den Scrummande Konsulten"

Erfarenheter, synpunkter och raljerande om Scrum frÄn Jimmy Janlén

Aryan Nava

DevOps, Cloud and Blockchain Consultant


SharePoint for everyone


Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

SharePoint 2020

The Vision for a Future of Clarity

Aharoni in Unicode

Treacle tarts for great justice

... And All That JS

JavaScript, Web Apps and SharePoint


Mostly what I know and share about...


SharePoint pÄ ren svenska