Extending the List of Sites You can Embed From in SharePoint Using PowerShell

The Embed web part for modern pages lets you display content from secure websites right on your page. Want to show a YouTube video? Grab the embed code from youtube.com and slap it in the Embed web part. Wowee!

By default, modern pages support 30+ sites including the most common like YouTube, Vimeo, TED, and internal domains like Stream and OneDrive. But what about when you’ve got content from a site not on this list? You’ll end up with an error similar to this:

Don’t cry! Wipe those tears off that wet face! If you just need to allow the domain for a single site, the instructions are right there (here’s a quick summary):

  • Go to Site Settings
  • Click on HTML Field Security under Site Collection Administration
  • Type the domain from the error message (no https://) into the box and click Add
  • Click OK
  • Give it another try

But wait… Corporate just rolled out a video hosting platform for the enterprise and they want all sites to be able to embed content from this new site. Does the thought of repeating the above steps hundreds or even thousands of times make you weep in despair? Smack those tears off your moistened face!

Here’s a quick snippet of PowerShell which will show you how to add it to multiple sites:

In the PowerShell above, I’m using PnP PowerShell. You can technically do this without PnP PowerShell since it’s just CSOM, but… why would you make your life harder?

Here’s what’s happening:

  • The list of sites in line 1 is just an array of the URL portion of the site after /sites/. You could easily alter this to grab all associated sites for a hub or to get all sites within a classification, etc. But I find a simple list of URLs works pretty well.
  • We connect to the site in line 9 and grab the site object in line 11
  • We get the Client Context in line 12
  • We create a new ScriptSafeDomainEntityData object and set the only part we care about, DomainName, to the URL from the error message before
  • Then in line 17 we use the Create method to add it to the list of domains (there’s no problem if the site already has that domain, it won’t be added twice)
  • We execute the query for the client context to save our changes in line 19
  • Finally we disconnect from the site in line 21 and move on to the next site

You can easily adapt the script above as part of your provisioning process to ensure that new site have the correct domains whitelisted as well. So fun!

Now you can take content from all over the web and mash it together to bring all the relevant stuff directly to your users. WOWEE!

Dog, Pug, Bitch, Pet, Animal, Obedient, Funny, Cute

Customizing the Flow Panel with List Formatting actionParams

I’ve covered launching a flow for a list item using List Formatting a number of times, along with conditionally launching a flow, and recently I even showed how to take advantage of background list updates with automatic format updates using Flow.

In this post I’ll demonstrate some new tweaks the team has made that lets you customize the flow panel itself using list formatting!

Specifically, you can now provide custom text for the panel header and/or the run flow button:

This goes a long way to making the flow panel less scary. The words “Run flow” don’t mean a whole lot to users. Even if you’ve named your flow well, it can still be confusing. Now you can make things even easier by providing context and meaning directly in the panel. You could even customize these values based on values of the list item!

Customizing the Flow Panel

Here is a very basic flow button Column Format from the generic-rowactions sample:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\":\"f7ecec0b-15c5-419f-8211-302a5d4e94f1\"}"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary ms-fontColor-themeDark--hover",
    "title": "Launch Flow"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "Flow",
        "class": "ms-font-xxl"
      }
    }
  ]
}

The part we’re interested in is line 6, the actionParams. The actionParams property is currently only used for the executeFlow action. It is an escaped JSON string (the double quotes have a slash in front of them). And thus far, it’s been used to specify the ID of the flow.

Now you can specify the headerText and the runFlowButtonText properties inside of actionParams as well! Here’s what that looks like using the above format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\":\"f7ecec0b-15c5-419f-8211-302a5d4e94f1\", \"headerText\":\"Do the things and stuff\",\"runFlowButtonText\":\"Lazerify!\"}"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary ms-fontColor-themeDark--hover",
    "title": "Launch Flow"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "Flow",
        "class": "ms-font-xxl"
      }
    }
  ]
}

The ID is always required but you can specify either or both of the headerText and runFlowButtonText to provide that customization:

Escaped PropertyWhat it does
IDThe ID of the flow to run. This is required.
headerTextReplaces the big text at the top of the panel with whatever you specify.
runFlowButtonTextSets the text of the primary button with whatever you specify.

Some things to keep in mind:

  • headerText will wrap if you get especially wordy:
  • But just because you can, doesn’t mean you should. It’s best to keep the header as short and concise as possible. (for anyone who wants to ignore me, I’ve found I had no problem displaying 5000+ characters)
  • runFlowButtonText will also allow you to put a large amount of text in the button, but it looks terrible because the cancel button wraps below it somewhere around 14-16 characters:
  • Unlike the headerText, runFlowButtonText will eventually just run off the screen as it never wraps to a new line.
  • Changing these properties does nothing to obscure connection details or the name of the Flow (yay!)
  • The headerText is shown even the first time a flow is executed (when the user is asked about the connections used), but as you might expect, the runFlowbuttonText is not:

This is a fantastic addition by the team! Making a flow button that simplifies launching a flow for an item is a great way to increase adoption, decrease confusion, and impress your boss! Special thanks to Cyrus Balsara (Microsoft) for letting me know about these awesome changes!

Generate List Formatting Elements in a Loop Using forEach

A few weeks back I demonstrated how to work with multi-select person or choice fields using indexOf to perform startsWith or contains checks to make some pretty cool formats.

While these are still valid techniques, they have some limitations that the new forEach property and the related operator, loopIndex, can solve. Specifically, applying formatted elements for each value of a multi-select field!

The Problem

I previously created a sample, multi-person-currentuser, that allows you to highlight a multi-person field when one of the users is the current user. The results end up looking something like this:

This sample takes advantage of the contains logic previously discussed by looking for the indexOf the @me (current user’s email address) within a flat string generated using the join operator:

multi-person-currentuser

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=join(@currentField.title, ', ')",
  "attributes": {
    "class": "=if(indexOf(join(@currentField.email,';'), @me) != -1, 'ms-fontColor-themePrimary ms-fontWeight-semibold', '')"
  }
}

Works pretty well and if that’s all you need, go grab that sample!

But what if we could take it further than just displaying the fields as a string? What if we could apply elements for each item? Well… good news, that’s exactly what the new forEach property allows us to do!

The forEach Property

The forEach property is not yet part of the schema (so don’t be surprised if it gets highlighted as invalid in something like VS Code). You can use it within column formatting or inside of your rowFormatter for view formatting.

The forEach property allows you to create virtual fields that you can access within an element. The element where you add the property (along with all of it children) are rendered once for each item within the array (array refers to the collection of selected people or choices).

Because the element is rendered multiple times, you must have a containing element. This is why if you attempt to use the forEach property in the root element, you’ll get an error.

The forEach property’s value is a simple sentence in the form virtualFieldName in ArrayField. Let’s look at an example.

For this first example we’ll use a simple multi-select choice field. In this case we’ve just made the choices some letters. Here it is with no formatting applied:

Now let’s apply a format using the forEach property:

multi-choice-foreach:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "choiceIterator in @currentField",
      "elmType": "div",
      "txtContent": "[$choiceIterator]",
      "attributes": {
        "class": "ms-bgColor-themePrimary ms-fontColor-white",
        "title": "='I am the letter ' + [$choiceIterator]"
      },
      "style": {
        "width": "16px",
        "height": "16px",
        "text-align": "center",
        "margin": "1px"
      }
    }
  ]
}

Here’s what that looks like:

So what did we do? As mentioned above, you can’t apply forEach to the root element. So we created a div and then gave it a single child. However, by using forEach within the child we’re using this element as a template that will be repeated within the root container once per selected choice.

The forEach value requires you to provide the virtual field name followed by the word in and concluding with the name of the array to loop over. In this case, as seen on line 6, we are using choiceIterator as the virtual field name and our array is the @currentField (this could have just as easily been another array field in your view using the [$FieldName] syntax).

Note that the virtual field name should be unique. It is possible to clobber your other fields if you use the same name as one of the internal names of your fields! This means that if you use Title then you’ll no longer have access to the actual Title field’s value! This will be true even after the loop completes. So choose carefully. I find it best practice to use either the field name or type followed by the word Iterator. This has the added benefit of making it obvious that you are retrieving a loop value within your element – but that’s up to you.

Now that we’ve added the forEach property, we can access the virtual field anywhere within our template object (and its children) just like it was any other field! You can see this in the txtContent property on line 8 and we even use it in an expression within the title property to create a nice tooltip on line 11.

Taking it further with loopIndex

Back to our person example from above, wouldn’t it be great to do more than simply show their names? There’s another sample called person-roundimage-format that applies the standard circle image for users. Using the techniques above we can quickly convert it to support multi-select person fields (add a forEach and change our field accessors):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin":"2px"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%"
          }
        }
      ]
    }
  ]
}

Here’s what that looks like:

Now the images are showing up and we even get multiple when more than one person is selected! But what happens when we have lots of selections? The results aren’t great and give us all a sad:

You can see that up to 3 people looks just fine, but 4+ starts to have some weird squishing (and nobody likes weird squishing). So we need some way of knowing how many people we have for an item and which one we are on in the template.

Fortunately, you can use the length and loopIndex operators to accomplish this!

The length operator will provide the total number of items in an array (it does NOT provide string length). We can use this value to determine when we shouldn’t show an element (to remove face 4, 5, 6, etc.).

The loopIndex operator provides us with the zero-based index of where we are in the forEach loop. To use it, simply provide it the virtual property name you want to get the index of (since you can nest multiple forEach loops) as a string. So, in our case we can use "=loopIndex('choiceIterator')".

We’re going to base our solution on the UI Fabric Facepile with descriptive overflow. In order to do that, we want to accomplish the following:

  • Show 1 to 3 faces without change (that seems to work great)
  • Never show more than 3 circles
  • Replace the third circle with a descriptive overflow circle when there are 4 or more people selected

The first case we’ve got handled. The second can be done by using "display":"none" as mentioned in my last post. The third requires an alternate element that only shows when there are 4 or more people and we are on the 3rd person.

multi-person-facepile:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin": "2px",
        "display": "=if(loopIndex('personIterator') >= 3, 'none', '')"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') >= 2, 'none', '')"
          }
        },
        {
          "elmType": "div",
          "attributes": {
            "title": "=join(@currentField.title, ', ')",
            "class": "ms-bgColor-neutralLight ms-fontColor-neutralSecondary"
          },
          "style": {
            "width": "100%",
            "height": "100%",
            "text-align": "center",
            "line-height": "30px",
            "font-size": "14px",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') == 2, '', 'none')"
          },
          "children": [
            {
              "elmType": "span",
              "txtContent": "='+' + toString(length(@currentField) - (2))"
            }
          ]
        }
      ]
    }
  ]
}

Here’s what that looks like:

Here’s what we did:

  • On line 14, we added a display property to our template element to set the value to none if the loopIndex is greater than or equal to 3 (keep in mind that the index starts at zero so we’re basically saying never show items 4 and up)
  • On line 31, we added a similar display property to our img element to set the value to none if the number of items is greater than 3 and the loopIndex >= 2. This allows us to show it as normal if there are 3 or less people selected but when there are 4 or more, we don’t want to show that 3rd person.
  • On lines 34-54 we add our descriptive overflow element. This is a gray circle that says how many more people were selected than are shown.
  • On line 46, we once again take advantage of the display property to ensure that the overflow element is only shown when there are more than 3 people selected and we are on the 3rd element (loopIndex = 2).
  • We use the join operator to create a tooltip with everybody’s name in it on line 37.
  • We determine the number of additional people by simply subtracting 2 (since we know how many we are showing) from the length of the array. Notice that the 2 is wrapped in parenthesis. This is to combat an issue with the subtraction operator.

While that certainly isn’t the simplest sample in the world, it demonstrates common list formatting patterns such as conditional display, element loops, and customization based on loop position.

This opens up even more possibilities in the already awesome List Formatting world! Whoo!!

Conditionally Launch Flows using List Formatting

Creating a button with Column Formatting to launch a flow is a fantastic way to make actions obvious (not hidden in the Flow menu). If you have a list item flow, I strongly recommend doing this (just copy, paste, and tweak the sample). But what if you want to go further? What if you have multiple flows for list items and you want to help guide your users through this more complex (but relatively common) scenario?

Good news! List formatting expressions make it possible to only show the flow launch button(s) that make sense based on the values of the item! Here’s what we’re building:

In the list above, we have 3 separate flows (Develop, Deploy, and Destroy). We want to only prompt the user to launch one of these based on the value of the Status column in the list item. You could argue that it might make more sense to launch a single flow that handles the conditional logic directly. Sure, but customizing the text, icon, and color to make it obvious to the user what action they are taking is still an awesome thing to do.

Conditional Logic Across Properties

In our sample, we want to conditionally change the color, icon, text, visibility, as well as the flow launched. This brings us to a very common scenario in List Formatting: applying the same logic to multiple properties.

It would be awesome to apply your logic to entire elements, but in List Formatting it is only possible to conditionally apply the value of a property. This means you can’t conditionally include/exclude a property or element. These properties/elements have to be included with their individual values conditionally set.

So, if you want to set a style property based on a condition, like the text color, you have to include the property and set its value regardless. Generally, you handle this by setting up an expression like this: “color”:”=if(@currentField>2,’red’,”)”

You cannot, however, apply that same logic to the inclusion of the color property itself.

Conditional Logic for Elements

Although you must include all the elements and properties whose values you want to set (even if only in some conditions), you can use the style display attribute to remove entire elements by simply setting the value to none. This is how we remove the entire button when the status is ‘Destroyed’ in our sample (line 15 below). We don’t want to prompt the user to launch any flow, so we simply remove the button altogether.

You can extend this pattern further by creating a placeholder top element of a div with children. The individual child elements can be turned on or off by conditionally setting the display property. That’s not what we’re doing here, but it’s something to keep in mind.

Conditional Flows

generic-start-flow-conditionally:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "='{\"id\": \"' + if([$Stage]=='','b60a26d3-fd87-4947-9d1d-344cb31d953a',if([$Stage]=='Development','3a27a39c-0ec9-4342-8fe3-bfb37fefc3da','3091d383-f8ed-48da-9962-bd7c24e70688')) + '\"}'"
  },
  "attributes": {
    "class": "='ms-fontColor-' +if([$Stage]=='','orangeLight',if([$Stage]=='Development','teal','redDark'))"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer",
    "display": "=if([$Stage]=='Destroyed','none','inherit')"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "=if([$Stage]=='','Lightbulb',if([$Stage]=='Development','Deploy','HeartBroken'))"
      },
      "style": {
        "padding-right": "6px"
      }
    },
    {
      "elmType": "span",
      "txtContent": "=if([$Stage]=='','Develop!',if([$Stage]=='Development','Deploy!','Destroy!'))"
    }
  ]
}

You can see that we are applying each of our elements conditionally by comparing the value of the Stage column (also present in the view). For the flow, we build the actionParams conditionally by building the escaped JSON value and swapping the ID value in and out based on the stage (line 6).

You can customize this sample by adding additional conditions, changing the comparison column (use the internal name), and the ID(s) of the flows themselves.

Getting a Flow’s ID

To use the code, you must substitute the ID of the Flow(s) you want to run. The IDs are contained within the expression inside the customRowAction attribute inside the button element.

To obtain a Flow’s ID:

  1. Click Flow > See your flows in the SharePoint list where the Flow is configured
  2. Click on the Flow you want to run
  3. Copy the ID from the end of the URL (between flows/ and /details)

Update

See this demoed on the PnP Call (Live from MVP Summit):

Love List Formatting?

Join the Bi-weekly (every other Thursday) SharePoint Patterns and Practices special interest group for general development call where I will be presenting a new List Formatting Quick Tip on each call!

Also, come get the full picture in my sessions about List Formatting at the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May:

Alternating Row Styles with List Formatting

An easy way to make your list views far more readable is to provide alternating styles between rows. This is especially helpful for wide lists with lots of columns.

Importantly, the alternating colors need to alternate regardless of the content of the list items. This means that even with changing sorts and filters applied, the alternating styles should remain consistent. Up until a few days ago, this wasn’t possible with List Formatting. However, thanks to a new operation and magic string, applying alternating styles is super easy!

Although you can use this same basic concept for advanced visualizations within column formatting or row format style view formatting, the most common usage is to apply a color across the whole row for every other row. That’s what I’ll demonstrate here using a row class style view format.

@rowIndex

A new magic string has been added that will provide you the index (relative to the view) for a given row. The index starts at 0. So, when a view is rendered, the row at the top has a @rowIndex of 0, the next is 1, the next is 2 and so on. If the sort changes, whatever row just became the new top row has a @rowIndex of 0.

We can see this in action with a simple column format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@rowIndex"
}
The @rowIndex value remains consistent based on render position!

Awesome! But just showing the index isn’t very helpful. Fortunately, a new operation has been added that makes using this value in an expression super easy.

Modulus (Remainder)

The modulus operator is the % sign. This operator returns the remainder left over when one operand is divided by a second operand. Here are some examples:

ExpressionResult
“=13 % 5”3
“=0 % 2”0
“=1 % 2”1
“=2 % 2”0
“=3 % 2”1

It’s those last 4 examples that are particularly relevant for us. By using the remainder operator with the second operand of 2, we can consistently get results for every other value!

Alternating Row Class

View formats provide an additionalRowClass property that allows us to apply a class(es) to whole rows. We can even do this conditionally! So, using what we learned above we can use this simple format to apply a class to every other row:

alternating-rowclass:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if(@rowIndex % 2 == 0,'ms-bgColor-themeLighter ms-bgColor-themeLight--hover','')"
}

We are checking to see if the remainder of dividing the @rowIndex by 2 is 0 (even numbers will be 0 and odd numbers will be 1). When it’s 0, we use the UI Fabric theme classes to apply a theme color across the whole row. We also add an additional class to add a hover effect as well. Here’s the result:

So beautiful!
On the fly sorts? No Problem!
On the fly filters? No Problem!

That’s all there is to it!

Thank you SPTechCon Austin!

I had the honor of attending and speaking at SPTechCon West this week. It was great! I attended several really awesome sessions, attendees were super engaged, and I even got some sweet swag. Even better? I crushed Vlad Catrinescu at Mario Kart on the same big screen we both presented on the next day.

Getting the Most out of SharePoint Patterns and Practices (PnP)

Photo via David Warner II

On Monday I presented one of my favorite sessions. This session is like a sampler platter of the awesome stuff the PnP team and community has made available for everyone to use and learn from. There were lots of people who hadn’t heard of PnP, which means their jobs are now going to be so much easier. Whoo!

There was a lot of great feedback and participation. People were very excited about Page Transformation for Modernization and, as always, PnP PowerShell. The samples and contribution opportunities were also of great interest.

Slides: Getting the Most out of SharePoint Patterns and Practices

List Formatting in O365 and 2019

Earlier today I got to present on my other favorite topic: List Formatting. Unfortunately, I got over ambitious and tried to fill my session with tons of information AND demos. I ran out of time. Sadness.

It was a big crowd and lots of people were very excited about the amazing things you can do with List Formatting. I had lots of people asking questions afterwards and even helped setup some formats for attendees right in the room. That’s the power of List Formatting, we can apply them with no installations, deployments, or admin privileges!

Slides: O365 List Formatting

Thank you so much to all the attendees, speakers, sponsors, and organizers. SPTechCon has lots of user targeted information, but so many of the sessions went into a lot of depth that I left with lots of great tips and I’m sure everyone else did too!