Adding HTML to SharePoint Columns – Color, Images and More – Round 2!

Back in June Microsoft announced they were blocking HTML created by Calculated Columns with the June 2017 Public Update (PU) for SharePoint 2013, 2016 and SharePoint Online.

See here: http://techtrainingnotes.blogspot.com/2017/12/no-more-html-in-sharepoint-calculated.html

Before the June update:


After the June update:


There’s a workaround!

There's a fairly simple solution, if you don't mind using a workflow.

  1. Edit the Calculated Column with the HTML and change it's "The data type returned from this formula is" back to "Single Line of Text". (Just change the result type... leave the column as a Calculated Column.)
  2. Create a new Multiple Lines of Text column and set it to "Enhanced rich text (Rich text with pictures, tables, and hyperlinks)".
  3. Create a workflow that simply copies the Calculated Column to the new Multiple Lines of Text column. Set the workflow to run on Created and Changed.
  4. Edit your views to hide the Calculated Column and add the Multiple Lines of Text column.

The workflow is just a single Set Field in Current Item action.


Set the “field” to the new Multiple Lines of Text column and set “value” to the Calculated column. Publish and test!

This solution will let you keep the Calculated Column for easy revising of the formula logic. You could also let the workflow do all of the work to create the logic and HTML using a String Builder, and eliminate the need for the Calculated Column.

Update the Existing Items

You now need to get the workflow to run on all of the existing items. You can run a PowerShell script to start the workflows, you can run a PowerShell script just to copy the data from the Calculated column the new column, you can manually run the workflows on each item, or if you don't mind the Modified date and Modified By being changed switch to the Quick Edit view and copy all the items in one column and then paste them right back.

After the workaround:


What does not work?

Script tags and Style tags. (and I’m sure there are a few more) Style blocks are emptied and script blocks are completely removed. But, basic HTML for hyperlinks, image tag, etc. still work.

Before: <style>#test { color:red }</style><script>alert(1)</script> more HTML…

After: <style></style> more HTML…



SharePoint 2013 Workflow Error: Invalid Text Value

I got the not so obvious error below from a simple SharePoint 2013 style workflow.

Invalid text value.
A text field contains invalid data. Please check the value and try again.

The problem was actually pretty simple. I was trying to write more the 255 characters to the Workflow Log. You will get the same error writing more than 255 characters to a Single Line of Text column. You can use an Extract Substring from Start of String action to retrieve only the first 255 characters.



PowerShell to Bulk Add Lists and Libraries to SharePoint’s Quick Launch

Someone created a bunch of lists and libraries in a new site, and then a few days later wondered why they were no longer in Quick Launch. They had seen them there the day before in the “Recent” section.

I told them the steps to edit the properties of each list to add them to Quick Launch, and then they told me that there are over 30 lists. So… PowerShell to the rescue!  Here’s the on-prem version:

$site = Get-SPSite http://sp2016/sites/calcdemo

$web = $site.RootWeb     #or which ever web is needed

$lists = $web.Lists

$lists | where {-not $_.Hidden -and $_.Created -gt (Get-Date 12/21/2017)} |
           foreach { $_.OnQuickLaunch = $true; $_.Update() }


All done!

I filtered by date so that I would not change any lists that existed before their new work, and filtered by Hidden to exclude the SharePoint auto-generated lists.


No More HTML in SharePoint Calculated Columns!

Just in case you missed it:

“Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013.”


So, no more of this:


Or this:

    =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])



Topic Mapping for Course 20347 to Microsoft Certification Exams 70-346 and 70-347

Microsoft’s “20347A: Enabling and Managing Office 365” course is a little different in that it does not have a one-to-one mapping to a single Microsoft exam. For example, while course 20483 maps to exam 70-483, 20347 maps to two exams: 70-346 and 70-347.

Here’s a guide for my students who are working on the 70-346 and 70-347 exams…

The course:

The exams:

  • 70-346 - Managing Office 365 Identities and Requirements
  • 70-347 - Enabling Office 365 Services

In the content below note that:

  • Some 20347 modules are not on the exam:
    • Module 1 (overview and create a Office 365 tenant)
    • Module 3 (Microsoft Office)
  • Two exam 70-347 topics () are not in 20347!
    • Implement Microsoft Flow and PowerApps
    • Configure and manage Microsoft StaffHub

For details of each topic see:

  • 70-346 - Managing Office 365 Identities and Requirements
  • 70-347 - Enabling Office 365 Services

    Exam 70-346

    Manage cloud identities (15–20%)

    · 20347 Module 2 – Configure password management

    · 20347 Module 2 – Manage user and security groups

    · 20347 Module 2 – Manage cloud identities with Windows PowerShell

    Implement and manage identities by using Azure AD Connect (15–20%)

    · 20347 Module 4 – Prepare on-premises Active Directory for Azure AD Connect

    · 20347 Module 4 – Set up Azure AD Connect tool

    · 20347 Module 4 – Manage Active Directory users and groups with Azure AD Connect in place

    Implement and manage federated identities for single sign-on (SSO) (15–20%)

    · 20347 Module 13 – Plan requirements for Active Directory Federation Services (AD FS)

    · 20347 Module 13 – Install and manage AD FS servers

    · 20347 Module 13 – Install and manage WAP servers (Web Application Proxy)

    Monitor and troubleshoot Office 365 availability and usage (15–20%)

    · 20347 Module 12 – Analyze reports

    · 20347 Module 12 – Monitor service health

    · 20347 Module 12 – Isolate service interruption

    Exam 70-347

    Manage clients and end-user devices (20–25%)

    · 20347 Module 5 – Manage user-driven client deployments

    · 20347 Module 5 – Manage IT deployments of Office 365 ProPlus

    · 20347 Module 5 – Set up telemetry and reporting

    · 20347 Module 5 – Plan for Office clients

    Provision SharePoint Online site collections (20–25%)

    · 20347 Module 9 – Configure external user sharing

    · 20347 Module 9 – Create SharePoint site collection

    · 20347 Module 9 – Plan a collaboration solution

    Configure Exchange Online and Skype for Business Online for end users (20–25%)

    · 20347 Module 6 – Configure additional email addresses for users

    · 20347 Module 6 – Create and manage external contacts, resources, and groups

    · 20347 Module 11 – Configure personal archive policies

    · 20347 Module 8 – Configure Skype for Business Online end-user communication settings

    Plan for Exchange Online and Skype for Business Online (20–25%)

    · 20347 Module 7 – Manage antimalware and anti-spam policies

    · 20347 Module 7 – Recommend a mailbox migration strategy

    · 20347 Module 11 – Plan for Exchange Online

    · 20347 Module 7 – Manage Skype for Business

    Configure and Secure Office 365 services (20–25%)

    · 20347 Module 10 – Implement Microsoft Teams

    · 20347 Module 10 – Configure and manage OneDrive for Business

    · (not covered in 20347!) – Implement Microsoft Flow and PowerApps

    · (not covered in 20347!) – Configure and manage Microsoft StaffHub

    · 20347 Module 11 – Configure security and governance for Office 365 services



    Working with PowerShell’s -ExpandProperty (or… messing with the pipeline)

    One of the great things about PowerShell is how easy and clear much of it is. But then there’s the other things that seem like magic. One of those is when you are dealing with objects that have properties that are collections of other objects.

    Here will look at three solutions for dealing with properties that are collections of other objects:

    • Using -ExpandProperty (and a custom column or two)
    • Using ForEach (and a couple variables)
    • Using ForEach (and a customized object)


    Sorry, there’s not such a cmdlet. Get-Service does return service objects that have a dependent services property. The only problem is that it returns a summary of the dependent services, and only a partial list. While you could pipe this to Format-List -Wrap, that will not give you any details about the dependent services.


    Using -ExpandProperty

    You can use -ExpandProperty to expand any single property.


    While the above works nicely for a single service, it creates a mess for a collection of services. Which DependentService belongs to which service?


    Your first attempt to solve this will probably fail. In this case, it fails as both the Service object has a Name property and the DependentServices objects have a Name property.


    Your second attempt may run without error, but we still can’t see which Service belongs with which DependentService. Adding a custom column seems to be ignored here:


    This didn’t work as expected because a Service object has a default list of Select properties defined in an XML file somewhere. ( :-) )  Your custom column was passed through the pipeline, but as a NoteProperty (a PowerShell added custom property).


    You will need to pipe the last attempt to another Select to see your NoteProperty.


    So the steps are:

    • Create a custom column with the data to pass on down the pipeline.
    • Expand the property with the collection of child objects.
         -ExpandProperty DependentServices
    • Pipe the child object and its new NoteProperty to a final Select.
         select ParentServiceName, Status, Name

    Using ForEach with Variables

    ExpandProperty works great when you need to expand a single property. When you need to futher manipulate the data before sending it on down the pipeline you may want to use ForEach, formally ForEach-Object.

        Get-Service | ForEach { some code } | Select ….

    As a simple ForEach, let’s list Services in color based on Status.

       Get-Service | foreach { 
          if ($_.Status -eq "Running") 
             { Write-Host $_.Name -ForegroundColor Green } else
             { Write-Host $_.Name -ForegroundColor Red }


    Write-Host does not add its content to the pipeline, so that’s the end of the line for this one.

    Using ForEach to solve our Service and DependentServices problem, we will create and then reuse a variable that contains the name of the service. (I call this “Store and Forward”.) The ForEach code block can contain one or many statements seperated by semicolons. The last object returned in the code block is forwarded into the pipeline.

       Get-Service | ForEach { $parent = $_.Name; $_.DependentServices } |
                             Select {$parent}, Status, Name

    This collects one or more pieces of data from the parent object (the Service) as variables, and then passes the DependentServices object on through the pipeline. On the other side of the pipeline you can continute expanding objects and using the variables as needed. Here’s the Service and DependentServices solution using “Store and Forward”.


    Using ForEach with Custom Objects

    You can customize objects by adding “NoteProperties” to the object. You saw one form of this when we used the -ExpandProperty while listing other properties.


    As a fun example, lets create a custom object that looks like a Service object, but has two properties named RunningDependentProperties and NonRunningDependentProperties.

    We will need:

    • A variable for the parent Service
    • Two variables to store the child services (Empty arrays created as $r=@().)
    • The Add-Member cmdlet to add a new NoteProperty to the Service object

    Get-Service |
         foreach {
           # initialize the arrays
           $r = @();
           $nr = @();
           # process all of the DependentServices
           ($_.DependentServices |
              foreach { if ($_.status -eq "Running") { $r += $_ } else {$nr += $_ } }
           );  `
           # attach the results to the original service
           Add-Member -InputObject $_ -name RunningDependentServices -Value $r -MemberType NoteProperty;
           Add-Member -InputObject $_ -name NonRunningDependentServices -Value $nr -MemberType NoteProperty;
         } |
    Select name, RunningDependentServices, NonRunningDependentServices

    So what’s it good for?

    How about a list of only services with running dependent services? Just add a WHERE before the SELECT:

        where { $_.RunningDependentServices.Count -gt 0 }


    Or, how about a list of all of the services where the DependentService “applockerfltr” is not running? Just add a WHERE before the SELECT:

       where { "applockerfltr" -in $_.NonRunningDependentServices.Name }

    (If you are not reading this at http://TechTrainingNotes.blogspot.com… you are reading stolen content!)


    In addition to the expansion of properties that are collections, -ExpandProperty can also be used to return a simple datatype like a string or a number. By default, a Select returns a collection. In the example below note that a basic Select Status returns a property named “Status” that contains the value “Running”. If you pipe that to Get-Member you will see that it is still a ServiceController object. If you expand the property Status you will then get the simple string value of the property.




    SharePoint: Running JavaScript Code Only When in Page Edit Mode

    One of my old Content Editor Web Part “tricks” would not work when run in a Publishing page. I was hiding a web part when not in Page Edit mode. You can detect this mode by checking for ‘PageState.ViewModeIsEdit != "1"’. The PageState object is automatically created in normal site pages (“Wiki Pages”). It is also created in Publishing Pages, but only when the page has not been published. I.e. when the page is checked out, or in edit mode.

    My code originally looked like this:

      if ( PageState.ViewModeIsEdit != "1" )
      {  …code to run when not in edit mode… }

    As the PageState object does not exist in a Published page, I had to change it to this:

      if ( typeof PageState == 'undefined' || (PageState.ViewModeIsEdit != "1") )
      {  …code to run when not in edit mode… }

    Of course… none of the above works in SharePoint Online “Modern UI” pages!



    SharePoint Audiences are not Security!

    In a nutshell… The SharePoint Audience feature is not security… ever. Audiences are used to filter (hide), not secure.

    Some may consider the use of Audiences as “security by obscurity”, but it is not security.

    From my SharePoint Security book…  (on Amazon – 2013/2016 version coming soon.)


    SharePoint audiences are used to target content to specific groups of people by hiding it from those who don’t need to see it. These groups can be SharePoint groups, Active Directory security groups, Active Directory distribution lists and SharePoint global audiences that are based on user profile data.

    Audience targeting can be used with:

    • List and library items, but only when displayed using a Content Query Web Part (part of the Publishing feature).
    • Entire web parts.
    • Top Link and Quick Launch navigation links. (when Publishing features are enabled)

    Note: The Audiences feature is only available with SharePoint Server Standard and Enterprise editions. SharePoint Foundation has no support for Audiences. (Audiences is part of User Profile Services.)

    Audiences are not security!

    Audiences are usually described as being used to "target" content to a selected group. Audiences could also be described as being used to hide content from all users except for the target audiences. This second form sounds like security, but absolutely is not. While a list item or document web part might have a target audience, and non-audience members won't see the web part, if it is not otherwise secured they can still get to the item by using a direct URL or find it from search.

    The Audience feature should be thought of as a filtering option, not security.

    To filter list items using an Audience

    List and library items can be filtered using the Audience feature using the Publishing feature’s Content Query Web Part. While the regular list web parts have an Audience feature, that feature hides the entire web part, not selected items. The Content Query Web Part is added to a site collection when you enable the Publishing features.

    Four steps are required to filter list content using an Audience:

    · Create a publishing site or enable the Publishing Infrastructure feature on a site collection.

    · Enable the Audience feature on the list.

    · “Tag” list items by Audience.

    · Display the list using the Content Query Web Part.

    Step 1: Enable the SharePoint Server Publishing Infrastructure Site Collection feature

    First make sure there are no other reasons to not enable the Publishing features. (Policy, support, governance, etc.)

    1. Go Settings (gear), Site Settings of the top level site of the site collection.

    2. In the Site Collection Administration section click Site collection features.

    3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

    Step 2: Enable the Audience feature on the list or library

    1. Go the list or library, click the LIST or LIBRARY tab in the ribbon.

    2. Click List Settings or Library Settings.

    3. Click Audience targeting settings.

    4. Checkmark Enable audience targeting.
    A new field named Target Audiences will now be displayed in the New and Edit pages for the list.

    5. Click OK.

    Step 3: “Tag” list items by Audience

    1. Edit the properties of a list or library item.

    2. In the Audience area click the Browse button ( ) and select an Audience.

    3. Save the changes to the item.

    Step 4: Display the list using the Content Query Web Part

    1. Move to your page where you want to display the web part.

    2. Edit the page.

    3. Click the Insert ribbon tab and the Web Part button.

    4. Click on the Content Rollup category and then click the Content Query web part. (If the web part is not listed then you do not have the SharePoint Server Publishing Infrastructure site collection Feature enabled.)

    5. Click Add to add the web part to the page.

    6. Click the web part’s dropdown menu and click Edit Web Part.

    7. Expand the Query section.

    8. Select the Source (the scope) for the rollup from one of the following: Show items from all sites in this site collection, Show items from the following site and all subsites, or Show items from the following list.

    9. Select the List Type and the Content Type to select the content to display from the Source selected above.

    10. In the Audience Targeting section checkmark Apply audience filtering.

    11. Optionally add filters or Presentation options.

    12. Click OK to save your web part changes.

    13. Save the page and test the results.

    Note: the Target Audience option in the Advanced section of the web part’s property panel is used to control if the entire web part will be displayed for an audience.

    Search Web Parts vs the CQWP

    Microsoft currently recommends using the Search Web Parts in many of the places that we might have used the CQWP. While search uses cached content, and can be quite a bit faster, the data is only as current as the last search crawl. (I.e. Completed tasks may still display as incomplete.) The CQWP is always using live data and can be Audience filtered.

    Resources for the CQWP:

    Display data from multiple lists with the Content Query Web Part:
    https://support.office.com/en-US/article/Display-a-dynamic-view-of-content-on-a-page-by-adding-the-Content-Query-Web-Part-3e35bd58-d159-43d6-bfc7-77878b4a856d (or just do a web search for “Display a dynamic view of content on a page by adding the Content Query Web Part”)

    To show web parts for an Audience

    You can use Audiences to hide an entire web part from all users except for the selected audiences. Simply edit the web part, expand the Advanced section and select an audience.

    Note: The SharePoint Server Publishing Infrastructure Site Collection feature is not needed for web part Audience filtering.

    To display a Quick Launch or Top Link Bar link for an Audience

    Links in the Quick Launch and the Top Link Bar can be filtered by Audience when the Publishing Infrastructure feature has been activated. Once this feature has been activated the Quick Launch and Top Link Bar options are replaced with a single Site Settings option named Navigation. In the Navigation page Quick Launch is called Current Navigation and the Top Link Bar is called Global Navigation.

    Enable the SharePoint Server Publishing Infrastructure Site Collection feature:

    1. Go Settings (gear), Site Settings of the top level site of the site collection.

    2. In the Site Collection Administration section click Site collection features.

    3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

    To filter navigation links:

    1. Go Settings (gear), Site Settings.

    2. In the Look and Feel section click Navigation.

    3. Scroll down to the Navigation Editing and Sorting section of the page.

    4. Add or edit a Heading or a Link.

    5. In the Audience area click the Browse button ( ) and select an Audience.

    6. Set the Title, URL and other options as desired and click OK.

    7. Test! The new navigation item should only be displayed for the selected Audiences.


    Office 365 / SharePoint Online – Where’s Site Settings This Week?

    SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will change.

    Where’s Site Settings?

    It depends… today, 10/23/2017 it’s here:

    • Classic UI pages: It’s where it has always been… in the Settings (gear) menu.
    • Modern UI pages: It’s…  Settings (gear), Site Info, Site Information, and then at the bottom of the popup panel, click “View all site settings”.
    • Modern UI Site Contents page: Top right corner, click Settings

    I wonder will it will be next week?

    Purpose of the change?

    No real clue… If it was to surface the most often used Site Settings, they are not the ones I use most often. Are the most often used options on your list Delete Site, rename the site or change the description or icon.



    A SharePoint REST API Tester with an AJAX and Workflow Writer


    A JavaScript project to use with a Content Editor Web Part to test SharePoint REST API calls, and create AJAX sample code and SharePoint Designer 2013 Workflow steps. It includes over 40 ready to test samples to query SharePoint and to create and delete items, folders, lists and sites.

    While learning the SharePoint REST API, I created a little REST tester Content Editor Web Part. Later when I explored SharePoint 2013 Workflow REST calls I expanded the tool to include step by step instructions to add the calls to a workflow. After presenting this at the Cincinnati SharePoint User Group and at the Nashville SharePoint Saturday I decided to take the time to clean it up a bit and share it here.

    What you will need:

    You can also download the file from the GitHub project.

    This is the main screen.


    This is partial list of the sample REST calls. A more complete list is at the end of this article, and I’ll be adding more over time.



    The in the page test of a REST call.



    The generated AJAX Code Sample



    The SharePoint 2013 Workflow Steps for the Web Service Call



    Steps to install to your SharePoint Site
    1. If your master page is not already loading jQuery, download jQuery (just about any version) and upload to the Site Pages library. 
    2. Download the SharePointRESTtester.html file to your PC. 
    3. Edit the file and update the line that loads jQuery to point your jQuery file or CDN.
    4. If your master page already loads jQuery, then delete the <script> block that loads the jQuery file.(the first line of the file)
    5. Upload the SharePointRESTtester.html file to your Site Pages library. (Copy the URL to the file.)
    6. Add a Web Part Page to your project:
      1. In the Site Pages library, click the FILES ribbon, click New Document and click Web Part Page.
      2. Enter a page name like "SharePointRESTtester". 
      3. From the library dropdown select Site Pages
      4. Click Create.
    7. Click Add a Web Part
    8. Add a Content Editor Web Part.
    9. Click the web part's dropdown and click Edit Web Part.
    10. Enter or paste the path to the SharePointRESTtester.html file.
    11. Click OK and then in the ribbon click Stop Editing.
    12. You should now see the tester. Click the dropdown and you should see data in the boxes. If not, then the jQuery library did not get loaded.
    13. Add to your Quick Launch or your Follow list!


    To use the tester…
    1. Select a sample from the dropdown, or enter your own URL, Method, Header JSON and if needed, the Body JSON.
    2. Find the Do It! button. The first check box will actually run the code. *** Warning Will Robinson, stuff could get added, changed or deleted! ***
    3. The second and third checkboxes simple hide or show the JavaScript Ajax code and the SharePoint 2013 workflow steps.


    SharePoint REST Examples for Queries

    • Get information about the current site collection.
    • Get information about the current web.
    • Get the Regional Settings for the current web.
    • Get the Time Zone for the current web.
    • Get SharePoint's list of Time Zones.
    • Get a list of all webs below the current web.
    • Get the primary site collection administrator (Owner).
    • Get the primary site collection Secondary Contact.
    • Get a web's LastItemModifiedDate
    • Get a list of lists from the current web. (all data)
    • Get a list of lists from the current web. (Just the title)
    • Get a count of items in a library.
    • Get a count of items in a library. (Option #2)
    • Get all items in a list/library.
    • Get all items in a library with filename and path.
    • Get a list folder's properties.
    • Get a count of items in a list folder.
    • Get all items in a list/library filtered by date.
    • Get all items in web level recycle bin.
    • Get selected properties of all items in web level recycle bin.
    • Get all items in a list/library filtered by a range of dates.
    • Search
    • People Search

    SharePoint REST Examples for Lists

    • Create a new list
    • Add a new item to a list
    • Add a new folder to a list
    • Delete an item from a list using ID
    • Delete an item, to the Recycle Bin, from a list using ID
    • Update an item using ID
    • Delete a list
    • Delete a list to the Recycle Bin

    SharePoint REST Examples for Sites

    • Create a new subsite.
    • Delete a site (Warning Will Robinson! Does not go to the Recycle Bin!)

    SharePoint REST Examples for User Profiles

    • Get User Profile info about the current user.
    • Get all User Profile properties for a user.
    • Get User Profile info about a user's manager.

    SharePoint REST Examples for Permissions

    • Get a list of Role Definitions for a site.
    • Get a list of Site Users. The ID is useful when setting permissions.
    • Get a list of Site Groups. The ID is useful when setting permissions.
    • Get a list of Site Groups by name.
    • Get a list of Site Groups where name contains 'string'.
    • Break inheritance on a subsite.
    • Break inheritance on a list.
    • Break inheritance on a list item.
    • Grant permissions (Role Assignment) on a list.
    • Remove permissions (Role Assignment) on a list.

    SharePoint REST Examples for Filter Select and OrderBy

    • Get a list of Site Users who are not Site Collection admins. Get selected fields and sort.

    SharePoint REST Examples for SharePoint 2010 style REST - _vti_bin/ListData.svc

    • Get a list of lists and libraries (EntitySets).
    • Find list items greater than a date.
    • Find list items between two dates.



    Note to spammers!

    Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.