PowerShell to create SharePoint online managed metadata terms from xml file

Our company started to SharePoint online governance plan and design the information architecture. One topic is to provide a global term for tagging and categorization. There are few good article to use PowerShell or C# to create terms from input file, however none of them can satisfy the requirement to create terms from Powershell with multiple levels of terms, and multiple termSet, and mutiple term groups.

I've modified the script we used previously for SharePoint on-premises and here is the code you could use for SPO.

#################################################################################
# Tod: Please update the following paremeters:
# $user        SharePoint admin user account like "harryc@mycompany.onmicrosoft.com"
# $siteUrl     SahrePoint onloine admin site url like "https://mycompany-admin.sharepoint.com/"
# Please install the SharePoint client components SDK - http://www.microsoft.com/en-us/download/details.aspx?id=35585 prior to running this script.

# Input Arguments
param([string]$userName,[string]$siteUrl)

[System.Net.ServicePointManager]::SecurityProtocol =[System.Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy -Scope Process -Confirm:$False -ExecutionPolicy Bypass -Force

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "../Assembly\Microsoft.SharePoint.Client.dll"
Add-Type -Path "../Assembly\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "../Assembly\Microsoft.SharePoint.Client.Taxonomy.dll"

#Specify tenant admin and URL
#$user = "harryc@mycompany.onmicrosoft.com"
#$siteUrl = "https://mycompany-admin.sharepoint.com/"
$user = $userName
$siteUrl = $siteUrl
$password = Read-Host -Prompt "Please enter your password" -AsSecureString

# Import term xml file
[xml]$importxml = Get-Content "..\Config\InputTerms.xml" -Encoding UTF8
#$Import.InnerXml
if ($importxml -eq $null) {
  write-host "Term creation file content is empty!"
  return

}

try{
        #Bind to MMS
        $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
        $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($user,$password)
        $ctx.Credentials = $creds
        $mms = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($ctx)
        $ctx.Load($mms)
        $ctx.ExecuteQuery()

        #Retrieve Term Stores
        $termStores = $MMS.TermStores
        $ctx.Load($termStores)
        $ctx.ExecuteQuery()

        #Bind to Term Store
        $termStore = $termStores[0]
        $ctx.Load($termStore)
        $ctx.ExecuteQuery()


        Foreach ($groupName in $importxml.Group.Name)
        {
            #Create Groups
            $group = $termStore.CreateGroup($groupName,[System.Guid]::NewGuid().toString())
            $ctx.Load($group)
            $ctx.ExecuteQuery()
            Write-Host "Create group - " $groupName;

            Foreach ($termSet in $importxml.Group.TermSet){
                #Create Term Sets
                $newTermSet = $group.CreateTermSet($termSet.Name,[System.Guid]::NewGuid().toString(),1033)
                $ctx.Load($newTermSet)
                $ctx.ExecuteQuery()
                Write-Host "Create termSet - " $termSet.Name;

                Foreach ($term in $termSet.Term){
                    #Create Terms
                    $newTerm = $newTermSet.CreateTerm($term.Name,1033,[System.Guid]::NewGuid().toString())
                    $ctx.Load($newTerm)
                    $ctx.ExecuteQuery()
                    Write-Host "Create term - " $term.Name;
                    if($term.Term -ne $null){
                        CreateChildTerms $ctx $newTerm $term
                    }
                }
          }
        }
}
catch [System.Exception]
{ 
    Write-Host "Exception:"  + $_.Exception.Message -foregroundcolor black -backgroundcolor yellow  
}


#############################################################
# Function to create sub-terms recursively
# $ctx        ClientContext
# $rootTerm   Parent term to create sub-term 
# $term       xml sub-term need to be created
#############################################################
function CreateChildTerms([Microsoft.SharePoint.Client.ClientContext]$ctx, [Microsoft.SharePoint.Client.Taxonomy.Term]$rootTerm, [System.Xml.XmlNode]$term)
{

       foreach ($childnode in $term.Term)
       {
             $addedTerm = $rootTerm.CreateTerm($childnode.Name, 1033, [System.Guid]::NewGuid().toString());
             $ctx.Load($addedTerm);
             $ctx.ExecuteQuery();
             Write-Host "Create child term - " $childnode.Name;

             # If sub-term need to be created, create recursively
             if($childnode.Term -ne $null){
                CreateChildTerms $ctx $addedTerm $childnode
             }
       }

}

There are many things we might need to development around terms like we did for SharePoint on-premises. Here is the list of the scripts in order to provide disaster recovery capability for terms.

1. Script to export the tenant term groups, termSets, and terms along with object GUID in xml file
2. Script to import the tenant term groups, termSets, and terms along with object GUID in xml file

We will keep you posted the progress on these scripts.

Produce to disable end users to create SharePoint online site and groups

Most companies will establish a SharePoint online site governance policy and provide a self provisioning process for sites and groups. So the question is how to disable end users to create site and groups? 

Microsoft provided the following two procedures.

1. Manage site creation in SharePoint Online
2. Manage who can create Office 365 Groups

Since the first step is straightforward, you can apply this quickly. The second step is tedious and I've modify the Microsoft PowerShell to make it easy. You will create the security group as instructed first. Then pass the group name to the PowerShell. The script will either print the configuration successfully completed or failed. Here is the modified script.

# You might need to set up your environemnt before running this script

#Install-Module AzureADPreview

#Import-Module AzureADPreview

# Input Arguments

param([string]$mySecurityGroupName)

Set-ExecutionPolicy -Scope Process -Confirm:$False -ExecutionPolicy Bypass -Force

[System.Net.ServicePointManager]::SecurityProtocol =[System.Net.SecurityProtocolType]::Tls12

Connect-AzureAD

$mySecurityGroup = $mySecurityGroupName #"SPCreator"

$myGroup = Get-AzureADGroup -SearchString $mySecurityGroup

$Id = $myGroup.ObjectId

$Template = Get-AzureADDirectorySettingTemplate | where {$_.DisplayName -eq 'Group.Unified'}

$Setting = $Template.CreateDirectorySetting()

$newSetting = Get-AzureADDirectorySetting

if ($newSetting -eq $null){

    New-AzureADDirectorySetting -DirectorySetting $Setting

    $newSetting = Get-AzureADDirectorySetting

}

$Setting = Get-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id

$flag = $Setting["EnableGroupCreation"]

Write-Output "Group created enabled before = $flag"

$Setting["EnableGroupCreation"] = $False

$flag = $Setting["EnableGroupCreation"]

Write-Output "Group created enabled after = $flag"

#$Setting["EnableGroupCreation"] = $True

$Setting["GroupCreationAllowedGroupId"] = (Get-AzureADGroup -SearchString $mySecurityGroup).objectid

Set-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id -DirectorySetting $Setting

$newSetting = Get-AzureADDirectorySetting 

#$newSetting.Values

foreach ($value in $newSetting.Values)

{

     if($value.Name -eq "GroupCreationAllowedGroupId"){

        if($Id -eq $value.Value){

            Write-Output "Only membesr of group named $mySecurityGroup can create groups now!"

        }

        else{

            Write-Output "Group named $mySecurityGroup not configured correct!"

        }

      }

}

If you need to disable users to create Yammer, planner or anything that would create SharePoint groups or site, you could further disable their license and restrict the creation.

SharePoint online site migration process across different tenants with both internal users and external users

Recently we have few requests to migrate SharePoint online site from one tenant to another and keep the same permission assignment. The challenge part is the site owners have invited many external users and we would need to keep the content with same permissions. There are seven major steps and here is the detailed procedures with some automation.

1. Original site analysis and report. This will include the following details.

• Identify the site template - we need to create the destination site with same site template
• Identify all the security internal AD groups used - we need to create same security groups in destination and add all the internal users
• Identify all internal users - we need to create them in destination AD if not already and add to site
• Identify all external users - we need to "add" them to destination site
• Identify all breaking permission inheritance using Sharegate

Here I'll focus on the last part to get all external users as automated Powershell script to avoid tedious manual process. Other steps I'll provide some automation and tips in separate blogs. Here is the PowerShell to get all external users Email into a file so it can be used for next steps.

# GetUsersPnP.ps1   - Get all external users from a SPO site when passing site url

# $siteUrl                  -SPO site collection url

# $outputFileName  - Output file w/ all external users

param([string]$siteUrl,[string]$outputFileName)

Set-ExecutionPolicy -Scope Process -Confirm:$False -ExecutionPolicy Bypass -Force

Connect-PnPOnline -Url $siteUrl -Credentials (Get-Credential)

$exeUsers = Get-PnPUser |? LoginName -like  "*EXT*"

# If you remove the query, you will get all users on the site and handle internal users in destination site

$outputFileName = ".\Output\" + $outputFileName

$outputFile = $myinvocation.mycommand.path.Replace($MyInvocation.MyCommand.name,"") + $outputFileName

$rows = @()

foreach($exeUser in $exeUsers)

{

    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                       

                                            LoginName = $exeUser.LoginName

                                            Email = $exeUser.Email                             

                                            } | Select-Object LoginName,Email

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue

2. Prepare destination site. This will include the following details.

• Create site collection using the template from step #1
• Create AD groups with same name as in step #1 and sync to Azure AD in destination
• Create internal users in destination and add them to the AD groups using by the site
• Add the security group to the site - this is optional but would be safe to ensure to migrate the groups

You could use PowerShell scripts to automate these steps. I can provide some of them in the future blog.

3. Invite external users from destination site. The purpose is to get all external users to the destination site so the migration will find the external users and migrate the permissions correctly. Since there is no easy to add all external users to the destination tenant and site, you could use the following PowerShell to invite external users and monitor if them accepted. 

The script will use the external user report generated from previous step #1.

# InviteExeUsersfromCSV.ps1 - Invite external users for a SPO site 

# $siteUrl                                  - SPO site collection url

# $outputFileName                   - Output file w/ all external users

# Input Arguments

param([string]$siteUrl,[string]$inputFileName)

Set-ExecutionPolicy -Scope Process -Confirm:$False -ExecutionPolicy Bypass -Force

Add-Type -Path ".\Assembly\Microsoft.SharePoint.Client.dll"

Add-Type -Path ".\Assembly\Microsoft.SharePoint.Client.Runtime.dll"

$inputFileName = ".\Input\" + $inputFileName

$CSVFile = $myinvocation.mycommand.path.Replace($MyInvocation.MyCommand.name,"") + $inputFileName

$users = Import-Csv $CSVFile

if ($users -eq $null) {

  write-host "User invitation file content is empty!"

  return

}

$userName = Read-Host -Prompt "Please enter your user name"

$Secure_String_Pwd = Read-Host -Prompt "Please enter your password" -AsSecureString

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $Secure_String_Pwd)

$ctx.Credentials = $credentials

# Create request list

$userList = New-Object "System.Collections.Generic.List``1[Microsoft.SharePoint.Client.Sharing.UserRoleAssignment]"

# For each user, set role

ForEach($user in $users)

{

    $userRoleAssignment = New-Object Microsoft.SharePoint.Client.Sharing.UserRoleAssignment

    $userRoleAssignment.UserId = $user.Email

    #$userRoleAssignment.Role = [Microsoft.SharePoint.Client.Sharing.Role]::None

    $userRoleAssignment.Role = [Microsoft.SharePoint.Client.Sharing.Role]::View

    $userList.Add($userRoleAssignment)

    Write-Host "Request for user " $user.Email " to site - " $siteUrl

}

try

{

    # Send invites

    $message = "Please accept this invite to our SharePoint site. Thanks!"

    $res =[Microsoft.SharePoint.Client.Sharing.WebSharingManager]::UpdateWebSharingInformation($ctx, $ctx.Web, $userList, $true, $message, $true, $true)

    $ctx.ExecuteQuery()

    $Success = $res.Status

    $StatusMessage = $res.message

    Write-Host "Send invite Status " $Success

}

catch

{

    Write-Host "Error to invite users"

    $hasError = ($error[0] | out-string)

}

4. Finalize destination site. This will include the following steps.

• Identify remaining external users who have not accepted the invitation
• Identify the using mapping for migration
• Add migration system account as site collection in destination site

You could set the invitation expiration days to 10 days for example (default is 90 days). After 10 days, identify any external users who have not accepted the invitation. You could use the following script similar to the one used step #1 run against destination site. The new script will report external users with flag "UserJoined" as "Y" or "N". 

You can compare and identify those users who have not accepted the invitation. You could either re-invite or map to replace them as either owner or migration system account during Sharegate migration.

# Input Arguments

param([string]$siteUrl,[string]$outputFileName, [string]$inputFileName)

################################################################################

#Set of test input data

#$siteUrl = "https://mycompany.sharepoint.com/sites/testsite"

#$outputFileName = ".\Output\NewExeUseresPnP.csv"

#$inputFileName = ".\Input\ExeUseresPnP.csv"

################################################################################

Set-ExecutionPolicy -Scope Process -Confirm:$False -ExecutionPolicy Bypass -Force

Connect-PnPOnline -Url $siteUrl -Credentials (Get-Credential)

$exeUsers = Get-PnPUser |? LoginName -like  "*EXT*"

$outputFileName = ".\Output\" + $outputFileName

$outputFile = $myinvocation.mycommand.path.Replace($MyInvocation.MyCommand.name,"") + $outputFileName

$inputFileName = ".\Input\" + $inputFileName

$inputFile = $myinvocation.mycommand.path.Replace($MyInvocation.MyCommand.name,"") + $inputFileName

$invitedUsers = Import-Csv $inputFile

$invitedUsers

$rows = @()

ForEach($user in $invitedUsers)

{

    $userJoined ="N"

    foreach($exeUser in $exeUsers)

    {

         if($exeUser.Email -eq $user.Email){

            $userJoined ="Y"

         }  

    }

    $newLoginName = "N/A"

    if($userJoined -eq "Y"){

        $newLoginName = $exeUser.LoginName

    }

    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                              

                                                LoginName = $user.LoginName

                                                Email = $user.Email                                          

                                                UserJoined = $userJoined

                                                NewLoginName = $newLoginName

                                                } | Select-Object LoginName,Email,UserJoined, NewLoginName

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue

Write-host "Completed the site user report in " $outputFile -foregroundcolor black -backgroundcolor green

5. Content migration w/ permissions and user mapping. This will include the following steps.

• Sharegate migration or other tools
• Migrate whole site collection with permission mappping in previous step

 6. Make original site read only.

After content migrated, we should make the original site ready only so no updates will be made to the site. The easy way to do this is to apply the site policy as described here. Here are detailed steps.

• Activate Site Policy site collection feature
• Create one Site Policy and select "The site collection will be ready only when it closed"
• In Site Closure and Deletion, associate the policy
• In Site Closure and Deletion, select "Close this site now"

Your site will be ready only like below and you could archive or delete in the future. You could also add redirect if you need.

7. Clean up destination site and UAT. Here are the steps.

• Make a backup of the site
• Delete external users that should not be in the visitor group
• Remove migration system account as site collection admin
• Complete UAT
• Remove the backup
• Set up redirect from original site
• Archive or delete original site

After content migrated, it's better to remove the external users invited to the SharePoint groups. In this case, all of them are invited to visitor group. Since we normally invite to the individual library, folder, or file, they should not have all view permission to whole site. This final step is to verify original site any external should not be in the visitor group, then remove them form the destination site.

Now you should have the site migrated to another tenant with permission mapped as original site.

How to avoid "Ajax error" when calling SharePoint Rest services in SharePoint framework?

When I tried to follow the instructions to set up a SPFx with SharePoint rest call, I get the following error when running from local.

"DataTables warning: table id=DataTables_Table_0 - Ajax error. "

After debugging the code, it seems like we need to change the ajex code to following line. Please note the two level for the url.


  'ajax': {
          'url': "../../_api/web/lists/getbytitle('IT Requests')/items?$select=ID,BusinessUnit",
…
}

Then we deploy the code to the site and it got the error again. The actual url is now pointing to:

https://mytennat.sharepoint.com/sites/_api/web/lists/getbytitle('IT Requests')/items?$select=ID,BusinessUnit

The url is missing the site name. I had to change the ajax call url to the following line with one level up.

  'ajax': {
           'url': "../_api/web/lists/getbytitle('IT Requests')/items?$select=ID,BusinessUnit",
…
}


Now it's one level up deployed on server and two level up running from local!

The best practice is to change the code to get the site url and pass to the ajax.

siteurl: this.context.pageContext.web.absoluteUrl

    jquery.ajax({ 

        url: `${this.props.siteurl}/_api/web/lists/getbytitle('EmployeeList')/items`, 

        type: "GET", 

        headers:{'Accept': 'application/json; odata=verbose;'}, 

        success: function(resultData) { 

            this.setState({ 

            items: resultData.d.results 

          }); 

        }, 

        error : function(jqXHR, textStatus, errorThrown) { 

        } 

    }); 

Hope this will avoid some frustration for you when using ajax in SPFx.

How to display bigger image in SharePoint online list?

We have a SPO list migrated from SharePoint on-premises that displays a image from a URL. The previous image size is kind of object sequence decent and people are able to read the content. However, when we migrate to SPO, the image is extremely small as in the below screenshot that users could not read.

The solution for this type of the image display is to utilize the SPO column formatting. We quickly added the following column formatting to the column and it will display the image in desired size.

{

  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",

  "elmType": "div",

  "children": [

    {

      "elmType": "img",

      "attributes": {

        "src": "@currentField",

        "title": "=if(@currentField == '', 'No picture available', @currentField)"

      },

      "style": {

        "position": "relative",

        "top": "50%",

        "left": "50%",

        "width": "480px",

        "height": "auto",

        "margin-left": "-50%",

        "margin-top": "0%"

      }

    }

  ]

}

Bow you should fell conformable to adjust any SharePoint online list column based on what you prefer.

Procedure to resolve error "The request was aborted: Could not create SSL/TLS secure channel." for SharePoint CSOM against SharePoint online

Recently, when I try to use SharePoint client object model (CSOM) to retrieve, update, and manage data in SharePoint online, I got the following error.

"The request was aborted: Could not create SSL/TLS secure channel." 

This is strange since we have been doing this type of PowerShell using CSOM against SharePoint online for few years. After working with Microsoft on this issue (#12681743), we identify the root cause is SharePoint online stopped to support SSL/TLS 1.0 since November 2018. This change has been gradually pushed to tenant gradually.

The solution to fix this issue is to add the following line in the beginning of the PowerShell script.

[System.Net.ServicePointManager]::SecurityProtocol =[System.Net.SecurityProtocolType]::Tls12

You could also to use the following line to allow different version of TLS to run the script.

[System.Net.ServicePointManager]::SecurityProtocol =[System.Net.SecurityProtocolType]::TLS -bor [System.Net.SecurityProtocolType]::Tls12 -bor [System.Net.SecurityProtocolType]::Tls11  

Here is one example when we enable app SideLoading in your non-developer SharePoint online site collection.

[System.Net.ServicePointManager]::SecurityProtocol =[System.Net.SecurityProtocolType]::Tls12

# Add-Type dll.

# Get user name and password

# …

[Microsoft.SharePoint.Client.ClientContext]$cc = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)

[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

$cc.Credentials = $spocreds

$sideLoadingEnabled = [Microsoft.SharePoint.Client.appcatalog]::IsAppSideloadingEnabled($cc);

#Add your commands here …

   

$cc.ExecuteQuery()

Bow the code runs without error.

Please note Microsoft may change the supported TLS version in the future, you may need to keep track the supported version.