Configure SharePoint managed metadata columns using PowerShell CSOM

I received the request from a colleague to create a script that will configure all managed metadata columns in SharePoint Online to allow people to fill in values.
My colleague first tried this using PnP but was unable to configure the necessary property of a managed metadata column.
It is possible to set this property using the client object model (CSOM) in PowerShell.
You will need the SharePoint Online Client Components SDK which you can download at https://www.microsoft.com/en-us/download/confirmation.aspx?id=42038

We first verified that we were able to change this property by using the below script:

$url = "https://<TenantName>.sharepoint.com"
$userName = "admin@<TenantName>.onmicrosoft.com"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

$context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$context.Credentials = $SPOcredentials
$web = $context.Web
$context.Load($web)
$list = $web.lists.GetByTitle("Documenten")
$context.Load($list)
$field = $list.fields.getbytitle("Zoekwoorden")

$context.load($field)
$context.ExecuteQuery()

$taxField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($context, $field)

$taxfield.open = $true
$taxfield.update()

$context.executequery()

Once we retrieved the correct field we had to cast this to a taxonomyField to be able to set the property “Open”.
The next step was to build a script around this that allows us to configure all managed metadata columns on the rootsite and all subsites.
This script has been upload to GitHub: https://github.com/peetersm12/ConfigureManagedMetadaColumnCSOM

The script will loop through all subsites to find the specified column on all the lists and libraries available.
You can first use a “preview” action to find all lists and libraries which have a column with the name you are looking to update.
Then you can update these columns so that users are allowed to fill in values.
Note that you can use this script for all updates for a specific column, just change the part where you  find the above code.

Using the script

Open the .ps1 file and copy the whole content of the file to preload the functions in the SharePoint Online Management Shell as administrator.

Next run 1 of the following commands:

To first list all lists/libraries which currently has the specified column title
set-mmcolumn -Action “Preview”

To update all lists and libraries which has the specified column title
set-mmcolumn -Action “Update”

Running the preview

Run the following command:

set-mmcolumn -Action "Preview"

and answer the following questions 1 by 1

The script will start and the output will be shown on screen, you can also verify the transcript although this isn’t color coded but search for “Found:”

We can see that our column has been found for the library “Documenten” and users are currently not allowed to fill in values.
Next run the script to update these columns.

Running the update

Run the following command:

set-mmcolumn -Action "Update"

and enter the following questions 1 by 1

The script will start and the output will be shown on screen, you can also verify the transcript although this isn’t color coded but search for “Found:”


We can see that our column has been found for the library “Documenten” and the value has been updated from False to true.
Run the preview again to see if all “green lines” are now set to true

Hide / Disable Export to Spreadsheet

I got a question from a colleague where the customer has added an out of the box survey in SharePoint and granted the required users permissions to respond. The scenario has been recreated on my environment and the issue is that visitors/members can read other responses even when specified that they are not allowed to view them using the Export to Spreadsheet action. This occurs in all SharePoint environments and I’ve created the scenario in SharePoint Online (Office 365). I would like to suggest using the Office 365 Forms app to build a survey when using Office 365!

I responded to a survey with my “External” account with edit permissions:

image

I changed the edit permissions to read and navigated to the “Overview” page.

image

I still have actions available and also Export to Spreadsheet

image

I was able to see all answers from all users.

Solution

There is no option to really disable this functionality but we are able to hide the Actions tab.

You can hide the Actions tab using CSS or JQuery. I suggest to do the below actions on all .aspx pages used by the form although Overview.aspx is the one hosting the “Export to Spreadsheet” :

  • Overview.aspx
  • AllItems.aspx
  • Summary.aspx

CSS

SNAGHTMLde51c4c

Edit page

image

Add a Script Editor web part

image

Edit snippit and add the below CSS code. This will select all ID’s that contains _ListActionsMenu_t and hides it.

<style>
*[id*=’_ListActionsMenu_t’]{ display:none; }
</style>

image

Insert and stop editing the page

image

The Actions menu is now hidden.

JQuery

I suggest using CSS and JQuery combined in case the browser does not support the wildcard selection from CSS.
Add the below code in the same script editor webpart where you can add this also as a .js file to the site assets and reference those .js files.

<script src=”//code.jquery.com/jquery-3.1.1.min.js”></script>
<script>
$(document).ready(function(){
$(“table.ms-menutoolbar tr td.ms-toolbar”).has(“span[id*=’ListActionsMenu_t’]”).hide();
$(“table.ms-menutoolbar tr td.ms-separator:first”).hide();
});
</script>

image

Insert and stop editing the page

image

PowerShell Office 365 Inventory tool

I’ve thought of creating a different tool after creating the PowerShell Office 365 tool a couple of days ago to improve my PowerShell coding and to make my work and that of my colleagues a little bit easier. The PowerShell Office 365 inventory tool lists a lot of information you would like to see when troubleshooting or getting information from a tenant you never connected to.

You can follow and download the PowerShell Office 365 Inventory Tool at GitHub: https://github.com/peetersm12/Office365Inventory-GUI

This PowerShell Office 365 Inventory tool will retrieve the following information:

  • Azure Active Directory Users
  • Azure Active Directory Deleted Users
  • Azure Active Directory External Users
  • Azure Active Directory Contacts
  • Azure Active Directory Groups
  • Azure Active Directory Licenses
  • Azure Active DIrectory Domains
  • Exchange Mailboxes
  • Exchange Archives
  • Exchange Groups
  • SharePoint Sites
  • SharePoint Webs

Please note that you will need a few pre-requisites before fully able to run this tool:

Please let me know which information you would like to see added to this tool and I’ll add this is as soon as possible.

FrontWindow2

[How to] PowerShell Office 365 Inventory Tool

image

Run ‘Start-Office365Inventory.ps1’. You will automatically be asked to run as administrator if you ran this as a normal user.

image

First Connect to Office 365 by clicking on the credential logo, by pressing f4 or via the menu

image
Fill in your credentials and press OK

image

You are connecting successfully when the icons are green.

There are 2 possible actions now:

  • Navigate to a tab and run only this action
  • Press the run all button to run all available actions

image

Only the Azure Active Directory Licenses have been returned. You can verify which actions have been run on the home tab.

image

Now click on the run all actions button. You can see the progress on the Home tab.

Please note that you will need site collections permissions for the SharePoint Webs option as this uses CSOM to connect to the different webs.

You will receive the following message but it will continue looking for more webs

image

After all actions everything should be green

image

Run the action individually if you encounter an error as the exception will be shown in the below message center.

Reports
You can create a .CSV or .HTML file based on the items visible at any time in the view by clicking on the button next to the CSV Report.
The report will be saved to the report folder located in the script root.

The .CSV report will look like:
report1

The .HTML report will look like:
report2

It is also possible to create a .HTML file for all the available tabs in a nice format. Please note that this is still a work in progress but it will lists everything at the moment.

report3

Error log
Some error information is displayed on the background PowerShell window as other information is only readable using the errorlog.
Please send me this information and the message in the below message box if you encounter an error.

image