Tuesday, December 27, 2016

SharePoint Online: Add Calculated Column to List using PowerShell

Requirement: Add Calculated Column to SharePoint Online List

How to Add Calculated column to SharePoint Online List?
  • Browse to your SharePoint Online site and Navigate to the target list in which you want to create calculated column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the type as "Calculated (calculation based on other columns)"
    Add calculated Column to SharePoint Online List using PowerShell
  • Enter the Formula for your calculated column in "Formula" field and specify the return type of the data. 
  • Scroll down and set other parameters such as decimal places and format of the data and Click on "OK" to create Calculated field in SharePoint Online list.

PowerShell to Create Calculated Column to List in SharePoint Online: 
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

#Custom function to add column to list
Function Add-CalculatedColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description="",
        [Parameter(Mandatory=$true)] [string] $FieldsReferenced,
        [Parameter(Mandatory=$true)] [string] $Formula,
        [Parameter(Mandatory=$true)] [string] $ResultType
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Frame FieldRef Field
            $FieldRefXML=""
            $FieldRefs = $FieldsReferenced.Split(",")
            foreach ($Ref in $FieldRefs)
            {
                $FieldRefXML = $FieldRefXML + "<FieldRef Name='$Ref' />"
            }

            #Create Column in the list
            $FieldSchema = "<Field Type='Calculated' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' ResultType='$ResultType' ReadOnly='TRUE'><Formula>$Formula</Formula><FieldRefs>$FieldRefXML</FieldRefs></Field>"
            $FieldSchema
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    
            
            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="TotalInvestmentBuffer"
$DisplayName="Total Investment Buffer"
$Description="5 Percentage in Total Investment"
$Formula = "=([Total Investment]*5)/100"
$ResultType="Currency" 
$FieldsReferenced="[Total Investment]" #Fields Names participating in Formula. Should be Comma (,) Separated

#Call the function to add column to list
Add-CalculatedColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Formula $Formula -ResultType $ResultType -FieldsReferenced $FieldsReferenced


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, December 26, 2016

SharePoint Online: Add Managed Metadata Column to List using PowerShell

SharePoint Online: How to Add Managed Metadata Field to List or Library:
  • Browse to your SharePoint Online site and Navigate to the target list in which you want to add Managed Metadata column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the field type as "Managed Metadata" 
    Add Managed Metadata field to SharePoint Online List using PowerShell
  • Scroll down and select the appropriate Term set or Term from the taxonomy store.
    PowerShell to create managed metadata column in SharePoint Online List
  • Fill other optional values and Click on "OK" to create Managed Metadata field in SharePoint Online list.

PowerShell to Create Managed Metadata column in SharePoint Online List:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

#Custom function to add column to list
Function Add-ManagedMetadataColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description="",
        [Parameter(Mandatory=$false)] [string] $IsRequired = "FALSE",
        [Parameter(Mandatory=$false)] [string] $EnforceUniqueValues = "FALSE",
        [Parameter(Mandatory=$true)] [string] $TermGroupName,
        [Parameter(Mandatory=$true)] [string] $TermSetName
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get the web, List and Lookup list
        $Web = $Ctx.web
        $List = $Web.Lists.GetByTitle($ListName)
        $LookupList = $Web.Lists.GetByTitle($LookupListName)
        $Ctx.Load($Web)
        $Ctx.Load($List)
        $Ctx.Load($LookupList)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Get the Term set data
            $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
            $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
            $Ctx.Load($TaxonomySession)
            $Ctx.Load($TermStore)
            $Ctx.ExecuteQuery() 
            #Get the Term Group
            $TermGroup=$TermStore.Groups.GetByName($TermGroupName)
            #Get the term set
            $TermSet = $TermGroup.TermSets.GetByName($TermSetName)
            $Ctx.Load($TermSet)
            $Ctx.ExecuteQuery()
            
            #Create Managed Metadata Column
            $FieldSchema = "<Field Type='TaxonomyFieldType' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' />"
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    

            #Bind Managed Metadata Termset to the column
            $TaxField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($Ctx, $NewField)  
            $TaxField.SspId = $TermStore.Id
            $TaxField.TermSetId = $TermSet.Id
            $TaxField.Update()
            $Ctx.ExecuteQuery()

            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="ProjectRegions"
$DisplayName="Project Regions"
$Description="Select the Project Region"
$TermGroupName="Regions"
$TermSetName="MENA"

#Call the function to add column to list
Add-ManagedMetadataColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -TermGroupName $TermGroupName -TermSetName $TermSetName


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: Add Multiple Lines of Text Columnto List using PowerShell

How to Add Multiple Lines of Text Field to List in SharePoint Online?
  • Browse to your SharePoint Online site and Navigate to the target list in which you want to add Choice column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the type as "Multiple lines of text"
    PowerShell to add multiline text field to sharepoint online list
  • Fill other optional values such as Number of lines, Type of text, append changes to existing text, etc. and Click on "OK" to create Multiple lines of text column in SharePoint Online list.

PowerShell to Create Multiple Lines of Text Column in SharePoint Online List:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add column to list
Function Add-MultilineTextColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description="",
        [Parameter(Mandatory=$false)] [string] $IsRequired = "FALSE",
        [Parameter(Mandatory=$false)] [string] $IsRichText="FALSE",
        [Parameter(Mandatory=$false)] [string] $NumLines = "6",
        [Parameter(Mandatory=$false)] [string] $EnhancedRichText = "FALSE"
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            if($EnhancedRichText -eq "TRUE") #Enhanced Rich Text Mode
            {
                $FieldSchema = "<Field Type='Note' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' NumLines='$NumLines' RichText='TRUE' RichTextMode='FullHtml' IsolateStyles='TRUE' />"
            }
            else  #Plain Text or Rich Text
            {
                $FieldSchema = "<Field Type='Note' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' NumLines='$NumLines' RichText='$IsRichText' />"
            }
            
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    

            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="ProjectDescription"
$DisplayName="Project Description"
$Description="Enter the Project Description"
$IsRichText="FALSE" #FALSE for Plain text / TRUE for Rich text
$EnhancedRichText="FALSE" #FALSE for Rich text / TRUE for Enhanced Rich Text, Takes Precedence over IsRichText parameter value

#Call the function to add column to list
Add-MultilineTextColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -IsRichText $IsRichText -EnhancedRichText $EnhancedRichText 


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: Create Date and Time Column in List using PowerShell

How to Add Date and Time Column to SharePoint Online List?
  • Browse to your SharePoint Online site and then Navigate to the target list in which you want to create date and time column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the type as "Date and Time" 
  • Fill other optional values and Click on "OK" to create date and time column in SharePoint Online list.
    SharePoint Online Create Date and Time Column in List using PowerShell
Here is the PowerShell script to create date and time column for SharePoint online list or library.

PowerShell to Add Date and Time Field in SharePoint Online List:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add column to list
Function Add-DateTimeColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$true)] [string] $Description,
        [Parameter(Mandatory=$true)] [string] $Format,
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $FriendlyDisplayFormat,
        [Parameter(Mandatory=$true)] [string] $EnforceUniqueValues
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='DateTime' ID='{$FieldID}' Name='$Name' StaticName='$Name' DisplayName='$DisplayName' Format='$Format' Required='$IsRequired'  Description='$Description' EnforceUniqueValues='$EnforceUniqueValues' FriendlyDisplayFormat='$FriendlyDisplayFormat' />"
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    

            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="ProjectStartDate" #Column Name
$DisplayName="Project Start Date"
$Description="Enter the Project Start Date"
$Format="DateOnly" #DateTime
$IsRequired = "FALSE"
$EnforceUniqueValues="FALSE"
$FriendlyDisplayFormat="Disabled" #Relative

#Call the function to add column to list
Add-DateTimeColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -IsRequired $IsRequired -Format $Format -EnforceUniqueValues $EnforceUniqueValues -FriendlyDisplayFormat $FriendlyDisplayFormat -Description $Description


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, December 25, 2016

SharePoint Online: Create Site Column using PowerShell

Site columns in SharePoint Online
Site columns provides great re-usability in SharePoint without having to recreate fields every time  you need them in lists and libraries. They save a lot of time especially when you have to standardize columns, create columns with data (such as choice fields) or assign default values to them. Once created at the top site, a site column can be utilized in any list or library or even content types throughout your site collection (or across site collections with content type hub).

How to Create Site Column in SharePoint Online?
To create site column in SharePoint online, follow these steps:
  • Login to SharePoint online site. Click on Site Settings Gear and select Site Settings.
  • On Site Settings page, Click on "Site Columns" link under "Web Designer Galleries" group. 
  • In Site Columns page, Click on "Create" Link at the top.
    sharepoint online powershell create site column
  • In create column page, Enter the name for your site column. Select the column type, Specify settings specific to the column. Click on "OK" button at the bottom to complete creating site column in SharePoint Online.
    create site columns using powershell in sharepoint online
    Now the site column will be added to the site.
Create Site Columns using PowerShell in SharePoint Online:
Here is my collection of PowerShell scripts to create site columns in SharePoint online using client side object mode (CSOM).
Important: Field Schema XML is case sensitive!

Create Single Line of Text Site Column in SharePoint Online using PowerShell
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Parameters
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ProjectCode"
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"

Try {
    $Cred= Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials

    #Get all Site columns from the site
    $Fields = $Ctx.web.Fields
    $Ctx.Load($Fields)
    $Ctx.executeQuery()

    #Check if the column name exists
    $NewField = $Fields | where {$_.Title -eq $ColumnName}
    if($NewField -ne $NULL)  
    {
        Write-host "Site Column $ColumnName already exists!" -f Yellow
    }
    else
    {
        #Define XML for Field Schema
        $FieldSchema = "<Field Type='Text' DisplayName='$ColumnName' Name='$ColumnName' required='$IsRequired' Group='$ColumnGroup'/>"
        $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
        $Ctx.ExecuteQuery()    

        Write-host "Site Column Created Successfully!" -ForegroundColor Green  
    }
}
Catch {
    write-host -f Red "Error Creating Site Column!" $_.Exception.Message
}

Add Multiple Lines of Text Field using PowerShell: 
This time, lets wrap the code into a re-usable function
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add Multiline Text Field
Function Add-MultilineSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,        
        [Parameter(Mandatory=$true)] [string] $NumberofLines,
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $RichText,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists
        $NewField = $Fields | where {$_.Title -eq $ColumnName}
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='Note' ID='{$FieldID}' DisplayName='$ColumnName' Name='$ColumnName' RichText='$RichText' Required='$IsRequired' Group='$ColumnGroup'/>"
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Define value for parameters
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ProjectDescription"
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"
$RichText ="FALSE"
$NumberofLines="5"

#Call the function to create Multiline site column
Add-MultilineSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -IsRequired $IsRequired -NumberofLines $NumberofLines -RichText $RichText -ColumnGroup $ColumnGroup


PowerShell  to Create Person or Group (People Picker) Site Column to SharePoint Online:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-PersonOrGroupSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,             
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists already
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='User' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' Required='$IsRequired' Group='$ColumnGroup' ShowField='ImnName' List='UserInfo' UserSelectionMode='PeopleOnly' />"
            $FieldSchema
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ProjectMembers"
$ColumnDisplayName="Project Members"
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"

#Call the function to create site column
Add-PersonOrGroupSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -ColumnGroup $ColumnGroup 

Add Date and Time Site Column to SharePoint Online:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-DateTimeSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,
        [Parameter(Mandatory=$true)] [string] $Format,        
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists already
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='DateTime' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' Format='$Format' Required='$IsRequired' Group='$ColumnGroup'/>"
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ProjectStartDate"
$ColumnDisplayName="Project Start Date"
$Format="DateOnly"
$IsRequired = "FALSE"
$ColumnGroup="Crescent Projects"

#Call the function to create site column
Add-DateTimeSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -Format $Format -ColumnGroup $ColumnGroup

PowerShell CSOM script to Add Currency Site Column for SharePoint Online:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-CurrencySiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,
         [Parameter(Mandatory=$true)] [string] $LCID,        
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists already
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='Currency' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' LCID='$LCID' Required='$IsRequired' Group='$ColumnGroup'/>"
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="TotalInvestment"
$ColumnDisplayName="Total Investment Amount"
$LCID="1081" #India
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"

#Call the function to create site column
Add-CurrencySiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -LCID $LCID -ColumnGroup $ColumnGroup

Refer https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splocale.lcid.aspx for all LCIDs.

SharePoint Online: Add Number Site Column with PowerShell
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-NumberSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,        
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='Number' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' Required='$IsRequired' Group='$ColumnGroup'/>"
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="HeadCount"
$ColumnDisplayName="Head Count"
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"

#Call the function to create site column
Add-NumberSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -ColumnGroup $ColumnGroup

How to Add Choice Site Column to SharePoint Online using PowerShell:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-ChoiceSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,
        [Parameter(Mandatory=$true)] [string] $ChoiceValues,        
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Fields = $Ctx.web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists already
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Frame Choices
            $ChoiceOptions=""
            $Choices = $ChoiceValues.Split(",")
            foreach ($Choice in $Choices)
            {
                $ChoiceOptions = $ChoiceOptions + "<CHOICE>$Choice</CHOICE>"
            }
            #Define XML for Field Schema
            $FieldSchema = "<Field Type='Choice' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' Required='$IsRequired' Group='$ColumnGroup'> <CHOICES>$ChoiceOptions</CHOICES></Field>"
            $FieldSchema
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ProjectDepartment"
$ColumnDisplayName="Project Department"
$IsRequired = "TRUE"
$ChoiceValues="IT,Sales,Operations,Marketing,HR"
$ColumnGroup="Crescent Projects"

#Call the function to create site column
Add-ChoiceSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -ChoiceValues $ChoiceValues -ColumnGroup $ColumnGroup
You can also add Type="MultiChoice", Format="RadioButtons" to customize it further.

Create Lookup Site Column in SharePoint Online using PowerShell:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add site column
Function Add-LookupSiteColumn()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ColumnName,
        [Parameter(Mandatory=$true)] [string] $ColumnDisplayName,
        [Parameter(Mandatory=$true)] [string] $LookupListName,
        [Parameter(Mandatory=$true)] [string] $LookupField,
        [Parameter(Mandatory=$true)] [string] $IsRequired,
        [Parameter(Mandatory=$true)] [string] $ColumnGroup
    )

    #Generate new GUID for Field ID
    $FieldID = ([GUID]::NewGuid()).GUID

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials

        #Get all Site columns from the site
        $Web = $Ctx.web
        $Ctx.Load($Web)
        $Fields = $web.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()

        #Check if the column name exists already
        $NewField = $Fields | where { ($_.Internalname -eq $ColumnName) -or ($_.Title -eq $ColumnDisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Site Column $ColumnName already exists!" -f Yellow
        }
        else
        {
            #Get IDs of Lookup List and Web
            $LookupList=$Web.Lists.GetByTitle($LookupListName)
            $Ctx.Load($LookupList)
            $Ctx.executeQuery()

            $LookupListID= $LookupList.id
            $LookupWebID=$Ctx.web.Id

            #Define XML for Field Schema
            $FieldSchema = "<Field Type='Lookup' ID='{$FieldID}' DisplayName='$ColumnDisplayName' Name='$ColumnName' Required='$IsRequired' Group='$ColumnGroup' List='$LookupListID' WebId='$LookupWebID' ShowField='$LookupField' />"
            $NewField = $Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
            $Ctx.ExecuteQuery()    

            Write-host "Site Column Created Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Creating Site Column!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ColumnName="ParentProject"
$ColumnDisplayName="Parent Project"
$IsRequired = "TRUE"
$ColumnGroup="Crescent Projects"
$LookupList="Projects" #Parent Lookup
$LookupField="ProjectName"

#Call the function to create site column
Add-LookupSiteColumn -SiteURL $SiteURL -ColumnName $ColumnName -ColumnDisplayName $ColumnDisplayName -IsRequired $IsRequired -ColumnGroup $ColumnGroup -LookupList $LookupList -LookupField $LookupField
Refer this MSDN article to frame schema XML attributes for site columns: https://msdn.microsoft.com/en-us/library/office/aa979575.aspx

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, December 20, 2016

Add Links to Resources List in SharePoint Central Administration

The Resources list in SharePoint Central Administration site lets you to keep frequently accessed links to the home page. Say for e.g. To access User profile service application, you'll have to navigate through:
  • Central Administration >> Application Management 
  • Manage Service Applications >> Search and pick your user profile service Application
in some other cases, you'll find a difficulty in navigating To-And-Fro in SharePoint Central Administration. So, Resources list comes handy to manage this hassle. Just add your frequently accessed links to it! When you log into SharePoint Central Administration site, you'll see the Resources link list in right pane.
Consider Resources list as your Favorites or Bookmarks List!
sharepoint 2013 central admin resources list
To Add a link/remove links in resources list:
  • Click on "Resources" link from SharePoint Central Admin home page (or your can Click the gear icon and click Site Contents >>  Find the Resources list)
  • From here you can add or delete the link like any list item.
    sharepoint 2013 add link to resources list in central admin
This saves time and effort, especially if you have trouble finding service applications in Central Admin.

Populate Resources List using PowerShell:
Lets use PowerShell to add items to Resources list in SharePoint Central Administration site.
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Get Central Administration Site
$CentralAdminUrl = Get-SPWebApplication -includecentraladministration | where {$_.IsAdministrationWebApplication} | Select -ExpandProperty URL

#Get Resources list from Central Admin
$List = (Get-SPWeb -identity $CentralAdminUrl).Lists["Resources"]

#Get Service Applications to add to Resources List
$ServiceApps = Get-SPServiceApplication | Where {($_.TypeName -eq "Excel Services Application") `
                                 -or ($_.TypeName -eq "Managed Metadata Service") `
                                 -or ($_.TypeName -eq "User Profile Service Application") `
                                 -or ($_.TypeName -eq "Search Service Application") `
                                 -or ($_.TypeName -eq "Business Data Connectivity Service Application") }

#Loop through and Add Links to Resources list
foreach ($App in $ServiceApps)
{
    $Item = $List.Items.Add()
    $Item["URL"] = "$($App.ManageLink.Url), $($App.DisplayName)"
    $Item.Update()
}

Write-Host "Service Application Links added to Resource List!" -f Green


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Get All Available Cmdlets in SharePoint Online Management Shell

Here is a nifty trick to find all available cmdlets in SharePoint online management shell.
  • Open SharePoint Online Management Shell and Enter:
    Get-Command | ? { $_.ModuleName -eq "Microsoft.Online.SharePoint.PowerShell" }
sharepoint online powershell cmdlet
This lists down all cmdlets from SharePoint Online Management Shell.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, December 19, 2016

SharePoint Online: Rename Folder using PowerShell

Requirement: Rename a Folder in SharePoint Online.
sharepoint online rename folder using powershell
 
PowerShell to Rename Folder in SharePoint Online
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Variables
$SiteURL="https://crescent.sharepoint.com"
$FolderURL="/Project Documents/Active" #Relative URL
$FolderNewURL="/Project Documents/InActive"

Try {
    $Cred= Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials

    #Get the Folder
    $Folder = $Ctx.Web.GetFolderByServerRelativeUrl($FolderURL)
    $Ctx.Load($Folder)
    $Ctx.ExecuteQuery()
    
    #Rename Folder
    $Folder.MoveTo($FolderNewURL)
    $Ctx.ExecuteQuery()

    Write-host -f Green "Folder has been renamed to new URL:"$FolderNewURL
}
Catch {
    write-host -f Red "Error Renaming Folder!" $_.Exception.Message
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, December 18, 2016

SharePoint Online: Delete Column from List using PowerShell

Requirement: SharePoint Online delete column from list.

How to remove a column from SharePoint Online List?
To delete a list column, follow these steps:

  • Go to List settings by going to List tab >> Under Settings group, click on List Settings button in the ribbon.
  • Under the List Settings page, in the Columns section, click on the column title you wish to delete.
  • Scroll down and Click on Delete button.
    sharepoint online list delete column
  • Confirm the deletion prompt by clicking OK.
This deletes sharepoint online list column.

PowerShell to delete column from list in SharePoint Online:

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Variables for Processing
$SiteURL="https://crescent.sharepoint.com"
$ListName= "Projects"
$ColumnName="Project Code"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials

    #Get the List
    $List = $Ctx.Web.Lists.GetByTitle($ListName)

    #Get the Column to delete
    $Column = $List.Fields.GetByTitle($ColumnName)
    $Column.DeleteObject()
    $Ctx.ExecuteQuery()

    Write-host "Column '$ColumnName' deleted Successfully!" -ForegroundColor Green
 }
Catch {
    write-host -f Red "Error Deleting Column from List!" $_.Exception.Message
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, December 17, 2016

SharePoint Online: Create Content Type using PowerShell

What are content types in SharePoint Online?
Content types are set of columns grouped together to serve a specific purpose. Think about a Invoice template! Instead of you and your users end up creating different templates each time from the scratch, you can define an Invoice template once and your users can start consuming it at any number of lists or document libraries! By this way, you force standardization in your collaboration environment. Or think of "Project Tracking" list with columns required to track projects for your organization. Instead creating and adding each column for Project tracking list every time, you'll be able to simply utilize project tracking content type template in any number of lists in few clicks.

SharePoint Online lists or document libraries can contain multiple content types. E.g. You can add multiple content types such as "Invoice", "Proposal", "Purchase Order", etc to a library to organize everything related to a project as a single entity. Once you associate a content type with SharePoint online list or library, SharePoint lets you to create new items of that content type from New Item command in that list or library. In the case of a document content type, you can also define a document template that will be the base for all the documents created from this particular content type.

As a best practice, content types should be ideally defined at the top site, independent of any list or library, So that it can be reused at any SharePoint site underneath or even across SharePoint site collections with content type hub.

How to Create Content Type in SharePoint Online?
To add a content type in SharePoint online, follow these steps:
  • Go to Site Settings >> Click on "Site Content Types" under "Web Designer Galleries" group.
  • In Site content types page, You''ll see the list of default content types like Item, Tasks, Document, etc grouped by sections. Click on "Create" link at the top.
  • Provide a name to your custom content type. Optionally you can enter the description for your new content type to make it clear.
  • Select the parent content type such as "Item" to which your content type is based on. You can either create a new content type group or select any existing.
    sharepoint online powershell create content type
  • Click OK to complete adding content type. Once the content type is created, The next step is to add required Site columns to the content type to make metadata available from the content type.

SharePoint online Create Content Type using PowerShell
Here is the PowerShell script to create content type programmatically in SharePoint Online.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#function to turn ON Content Type in SharePoint Online list or library
Function Create-SPOContentType()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $CTypeName,
        [Parameter(Mandatory=$true)] [string] $CTypeDesc,
        [Parameter(Mandatory=$true)] [string] $ParentCTypeName,
        [Parameter(Mandatory=$true)] [string] $CTypeGroup
    )

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get all content types from the site
        $ContentTypeColl = $Ctx.web.ContentTypes
        $Ctx.Load($ContentTypeColl)
        $Ctx.ExecuteQuery()

        #Get the parent content type
        $ParentCType = $ContentTypeColl| Where {$_.Name -eq $ParentCTypeName}

        #Check if content type exists already
        $ContentType = $ContentTypeColl| Where {$_.Name -eq $CTypeName}


        If($ContentType -ne $Null)
        {
            Write-host "Content type '$CTypeName' already exists!" -ForegroundColor Yellow
        }
        else
        {
            #Specify properties for the new content type
            $CTypeCreationInfo=New-Object Microsoft.SharePoint.Client.ContentTypeCreationInformation
            $CTypeCreationInfo.Name=$CTypeName
            $CTypeCreationInfo.Description=$CTypeDesc
            $CTypeCreationInfo.Group=$CTypeGroup
            $CTypeCreationInfo.ParentContentType=$ParentCType

            # Add the new content type to the collection
            $ContentType=$ContentTypeColl.Add($CTypeCreationInfo)
            $Ctx.ExecuteQuery()
           
            Write-host "Content Type '$CTypeName' Created Successfully!" -ForegroundColor Green
        }
    }
    Catch {
        write-host -f Red "Error Creating Content Type!" $_.Exception.Message
    } 
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$CTypeName="Projects"
$CTypeDesc="Contnet type for Project template"
$ParentCTypeName="Item"
$CTypeGroup="Crescent Projects"

#Call the function
Create-SPOContentType -SiteURL $SiteURL -CTypeName $CTypeName -CTypeDesc $CTypeDesc -ParentCTypeName $ParentCTypeName -CTypeGroup $CTypeGroup
This script creates content type in sharepoint online from the specified parent content type. Here in this example, I've created "Project" content type from "Item" content type which has "Title" as its default field. You may want to add additional fields tot the content type and you can add columns to content type in SharePoint online with PowerShell.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Thursday, December 15, 2016

SharePoint Online: Export Term Store Data to CSV using PowerShell

Requirement: Export Term store data from SharePoint Online site to a CSV file

Solution: There is no out of box way to export complete term store data in SharePoint online. However, we can utilize PowerShell to export term store data including all term groups, term sets and terms to a CSV file. Here is the script.
sharepoint online export term set

PowerShell to Export Term Store data to CSV:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$ReportOutput="C:\Temp\TermStoreData.csv"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

     #Array to Hold Result - PSObjects
    $ResultCollection = @()

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get all term groups   
    $TermGroups = $TermStore.Groups
    $Ctx.Load($TermGroups)
    $Ctx.ExecuteQuery()

    #Iterate through each term group
    Foreach($Group in $TermGroups)
    {
        #Get all Term sets in the Term group
        $TermSets = $Group.TermSets
        $Ctx.Load($TermSets)
        $Ctx.ExecuteQuery()

        #Iterate through each termset
        Foreach($TermSet in $TermSets)
        {
            #Get all Terms from the term set
            $Terms = $TermSet.Terms
            $Ctx.Load($Terms)
            $Ctx.ExecuteQuery()

            #Iterate through each term
            Foreach($Term in $Terms)
            {
                $TermData = new-object PSObject
                $TermData | Add-member -membertype NoteProperty -name "Group" -Value $Group.Name
                $TermData | Add-member -membertype NoteProperty -name "TermSet" -Value $Termset.Name
                $TermData | Add-member -membertype NoteProperty -name "Term" -Value $Term.Name   
                $ResultCollection += $TermData
            }
        }
    }
    #Export Results to a CSV File
    $ResultCollection | Export-csv $ReportOutput -notypeinformation

    Write-host "Term Store Data Successfully Exported!" -ForegroundColor Green   
}
Catch {
    write-host -f Red "Error Exporting Termstore Data!" $_.Exception.Message
}
This produces a CSV file as in this image:
sharepoint online powershell export term set

To import this CSV data to any other term store, use my another article: PowerShell to Import Term Store Data from CSV in SharePoint Online

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


How to Create SharePoint 2016 Farm using PowerShell

Creating SharePoint 2016 farm using PowerShell is almost as same as in its previous version. In SharePoint 2016 there is an additional parameter -LocalServerRole added as SharePoint 2016 introduced MinRoles.

Pre-Requisites:
  • SQL Server is already installed and ready to use.
  • You have created a Farm account for SharePoint 2016.
  • You have logged in to the server (Setup account) which has Administrator access on all SharePoint servers and DB_Creator, Security_Admin Server roles in SQL Server.

Step 1: Install SharePoint 2016 prerequisites and binaries to each server in your proposed SharePoint 2016 farm.
install sharepoint 2016 farm using powershell
On completing the installation, Uncheck "Run the SharePoint Products Configuration Wizard now" and close the wizard.
create new sharepoint 2016 farm with Powershell

Step 2: PowerShell Script to Create SharePoint 2016 Farm:
Save the below script as "Create-Farm.ps1" or something like that, change the configuration settings parameters as per your environment. Open SharePoint 2016 Management Shell as Administrator, and run the script. You'll see
"The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered."  error for the first time, which is quite normal, since we have not created the Farm yet! proceed running the script.

#Configuration Settings 
$DatabaseServer = "SP16-SQL"
$ConfigDatabase = "Intranet_Farm_Config"
$AdminContentDB = "Intranet_Farm_Content_Admin"
$Passphrase = "2FJlsXghFsas5vdJJKEXXwWF"
$FarmAccountName = "Crescent\Sp2016admin"
$ServerRole="SingleServerFarm"

#Get the Farm Account Credentials
$FarmAccount = Get-Credential $FarmAccountName
$Passphrase = (ConvertTo-SecureString $Passphrase -AsPlainText -force)
  
#Create SharePoint Farm
Write-Host "Creating Configuration Database and Central Admin Content Database..."
New-SPConfigurationDatabase -DatabaseServer $DatabaseServer -DatabaseName $ConfigDatabase -AdministrationContentDatabaseName $AdminContentDB -Passphrase $Passphrase -FarmCredentials $FarmAccount -LocalServerRole $ServerRole

$Farm = Get-SPFarm -ErrorAction SilentlyContinue -ErrorVariable err  
if ($Farm -ne $null) 
{
Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity
 
Write-Host "Installing Farm Services ..."
Install-SPService
 
Write-Host "Installing SharePoint Features..."
Install-SPFeature -AllExistingFeatures
 
Write-Host "Creating Central Administration..."              
New-SPCentralAdministration -Port 2016 -WindowsAuthProvider NTLM
  
Write-Host "Installing Help..."
Install-SPHelpCollection -All  
 
Write-Host "Installing Application Content..."
Install-SPApplicationContent
  
Write-Host "SharePoint 2016 Farm Created Successfully!"
} 
Once the script completed successfully, You'll see farm created successfully message. Here I've used "SingleServer" as MinRole. You can adjust it based on your requirement.
Alternatively, You can create SharePoint Farm (Run script til line#15) and then Run Products configuration wizard to complete the rest of the steps in farm creation.

Add Additional Servers to the SharePoint 2016 Farm:
Once you are done with the creation of the SharePoint 2016 farm from first server, You can connect rest of the servers to the Farm either using SharePoint products configuration wizard or with PowerShell.

Open SharePoint products configuration wizard and choose "Connect to an existing server farm" and run through the wizard! Select the server MinRole as per your topology.
powershell script to create sharepoint 2016 farm

Join Additional Server to the SharePoint 2016 farm using PowerShell:
If you prefer PowerShell way to add an additional server to the farm, use this PowerShell script.  
$ServerRole="Application"
#"Custom","WebFrontEnd","Application","DistributedCache","SingleServerFarm","Search","ApplicationWithSearch","WebFrontEndWithDistributedCache"

Connect-SPConfigurationDatabase -DatabaseServer $DBServer -DatabaseName $DBName -PassPhrase $SecurePassPhrase -LocalServerRole $ServerRole
 
Write-Host "Installing SharePoint Resources..."
Initialize-SPResourceSecurity
 
Write-Host "Installing Farm Services ..."
Install-SPService
 
Write-Host "Installing SharePoint Features..."
Install-SPFeature -AllExistingFeatures
 
Write-Host "Installing Help..."
Install-SPHelpCollection -All  
 
Write-Host "Installing Application Content..."
Install-SPApplicationContent
  
Write-Host "Joined the Server to Farm Successfully!"
If you don't want the server to host Distributed cache, use the parameter: -SkipRegisterAsDistributedCacheHost and the end!
Related post: How to Create SharePoint 2013 farm using PowerShell

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Wednesday, December 14, 2016

SharePoint Online: Version History Report using PowerShell

Requirement: Generate version history report for all sites in a SharePoint site collection.
version history report for all libraries in sharepoint online using powershell

PowerShell to Generate Version History Report for SharePoint Online:
#Import SharePoint Online module
Import-Module Microsoft.Online.SharePoint.Powershell

Function Generate-VersionHistoryReport()
{
  param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ReportOutput
    )
    Try {
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get all subsites and Lists from the site
        $Web = $Ctx.Web
        $Ctx.Load($Web)
        $Ctx.Load($Web.Webs)  
        $Lists = $Web.Lists
        $Ctx.Load($Lists)        
        $Ctx.ExecuteQuery()
        
        #Exclude system lists
        $ExcludedLists = @("Access Requests","App Packages","appdata","appfiles","Apps in Testing","Cache Profiles","Composed Looks","Content and Structure Reports","Content type publishing error log","Converted Forms",
            "Device Channels","Form Templates","fpdatasources","Get started with Apps for Office and SharePoint","List Template Gallery", "Long Running Operation Status","Maintenance Log Library", ,"Master Docs","Master Page Gallery"
               "MicroFeed","NintexFormXml","Quick Deploy Items","Relationships List","Reusable Content","Reporting Metadata", "Reporting Templates", "Search Config List","Site Assets", "Site Pages", "Solution Gallery",
                    "Style Library","Suggested Content Browser Locations","Theme Gallery", "TaxonomyHiddenList","User Information List","Web Part Gallery","wfpub","wfsvc","Workflow History","Workflow Tasks")

        
        Write-host -f Yellow "Processing Site: "$SiteURL
        $VersionHistoryData = @()

        #Iterate through each list in a site and get versioning configuration
        ForEach($List in $Lists)
        {
            if( ($ExcludedLists -NotContains $List.Title) -and ($List.EnableVersioning) -and ($List.BaseType -eq "DocumentLibrary"))
            {
                #Get All Files --Excluding Folders
                $Query =  New-Object Microsoft.SharePoint.Client.CamlQuery
                $Query.ViewXml = "<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Where><OrderBy><FieldRef Name='ID' /></OrderBy></Query></View>"
                $ListItems=$List.GetItems($Query)
                $Ctx.Load($ListItems)
                $Ctx.ExecuteQuery()

                #Iterate throgh each version of file
                Foreach ($Item in $ListItems)
                {
                    $File = $web.GetFileByServerRelativeUrl($Item["FileRef"])
                    $Ctx.Load($File)
                    $Ctx.Load($File.Versions)
                    $Ctx.ExecuteQuery()

                        If($File.Versions.Count -ge 1)
                        {
                            $VersionSize=0 
                            #Calculate Version Size
                            Foreach ($Version in $File.Versions)
                            {
                                $VersionSize = $VersionSize + $Version.Size
                            }

                            #Send Data to object array
                            $VersionHistoryData += New-Object PSObject -Property @{
                            'Site' = $SiteURL
                            'Library' = $List.Title
                            'File Name' = $File.Name
                            'Version Count' = $FileVersions.count
                            'Version Size' = ($VersionSize/1024)
                            'URL' = $SiteURL+$File.ServerRelativeUrl
                            }
                        }
                }
            }
        }
        #Export the data to CSV
        $VersionHistoryData | Export-Csv $ReportOutput -Append -NoTypeInformation

        #Iterate through each subsite in the current web
        Foreach ($Subweb in $Web.Webs)
        {
            #Call the function recursively to process all subsites underneaththe current web
            Generate-VersionHistoryReport -SiteURL $Subweb.URL -ReportOutput $ReportOutput
        }
     }
    Catch {
        write-host -f Red "Error Generating version History Report!" $_.Exception.Message
    } 
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ReportOutput="C:\Temp\VersionHistory.csv"

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Delete the Output report file if exists
if (Test-Path $ReportOutput) { Remove-Item $ReportOutput }

#Call the function to generate version History Report
Generate-VersionHistoryReport -SiteURL $SiteURL -ReportOutput $ReportOutput 

This script produces a CSV file with data such as:
  • Site 
  • Library 
  • File Name
  • Number of Versions
  • Version History Size
  • URL of the document


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Open this link on a Phone - QR Code Feature in SharePoint 2016

SharePoint 2016 introduces a new feature to open links on your phone or tablet using QR codes. Here is how this feature works:
  • Go to any of your SharePoint 2016 library such as document library. From the document's context menu, click on the tiny phone icon.
    QR Code Open this link on a phone Feature in SharePoint 2016
  • This takes you to a page with QR code generated for the document URL. By default, every document is assigned with a Unique QR code.Open documents with QR code in sharepoint 2016
  • From your phone, use QR reader app to read the QR code and navigate to the document URL. This makes easier to share documents on mobile devices.


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: Versioning Analysis on All Lists and Libraries using PowerShell

Requirement: Audit versioning settings for all lists and libraries in SharePoint online site collection.
powershell to get versioning settings in sharepoint online

PowerShell to Generate Versioning Settings Analysis for SharePoint Online Site Collection:
#Import SharePoint Online module
Import-Module Microsoft.Online.SharePoint.Powershell

Function Audit-VersioningSettings()
{
  param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ReportOutput
    )
    Try {
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get all subsites and Lists from the site
        $Web = $Ctx.Web
        $Ctx.Load($Web)
        $Ctx.Load($Web.Webs)  
        $Lists = $Web.Lists
        $Ctx.Load($Lists)        
        $Ctx.ExecuteQuery()
        
        $VersioningData = @()
        Write-host -f Yellow "Processing Site: "$SiteURL
        #Iterate through each list in a site and get versioning configuration
        ForEach($List in $Lists)
        {
            $VersioningData += New-Object PSObject -Property @{
            'Site' = $SiteURL
            'List' = $List.Title
            'Versioning Enabled' = $List.EnableVersioning
            'Major Versions Limit' = $List.MajorVersionLimit
            'Draft Versions Limit' = $List.MajorWithMinorVersionsLimit
            'Draft Item Security' = $List.DraftVersionVisibility
            'Content Approval Required' = $List.EnableModeration
            'Checkout Required' = $List.ForceCheckout
            }
        }
        #Export the data to CSV
        $VersioningData | Export-Csv $ReportOutput -Append -NoTypeInformation

        #Iterate through each subsite in the current web
        Foreach ($Subweb in $Web.Webs)
        {
            #Call the function recursively to process all subsites underneaththe current web
            Audit-VersioningSettings -SiteURL $Subweb.URL -ReportOutput $ReportOutput
        }
     }
    Catch {
        write-host -f Red "Error Auditing versioning Settings!" $_.Exception.Message
    } 
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ReportOutput="C:\Temp\VersioningData.csv"

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Delete the Output report file if exists
if (Test-Path $ReportOutput) { Remove-Item $ReportOutput }

#Call the function to generate versioning data audit report
Audit-VersioningSettings -SiteURL $SiteURL -ReportOutput $ReportOutput

This PowerShell script generates a CSV output provided in the $ReportOutput parameter.
sharepoint online versioning analysis report using powershell

Please note, this script gets versioning configurations for a given site collection. You can retrieve all site collections, loop through and call the function to get version history analysis for all site collections in SharePoint online.

How to Exclude System Lists and Libraries?
Well, the above script scans all lists and libraries including system lists. Here is the change to exclude all system lists (You can add any missing!")
    
        #Exclude system lists
        $ExcludedLists = @("Access Requests","App Packages","appdata","appfiles","Apps in Testing","Cache Profiles","Composed Looks","Content and Structure Reports","Content type publishing error log","Converted Forms",
            "Device Channels","Form Templates","fpdatasources","Get started with Apps for Office and SharePoint","List Template Gallery", "Long Running Operation Status","Maintenance Log Library"
                ,"Master Docs","Master Page Gallery","MicroFeed","NintexFormXml","Quick Deploy Items","Relationships List","Reusable Content","Reporting Metadata", "Reporting Templates", "Search Config List","Site Assets", 
                    "Site Pages", "Solution Gallery","Style Library","Suggested Content Browser Locations","Theme Gallery", "TaxonomyHiddenList","User Information List","Web Part Gallery","wfpub","wfsvc","Workflow History","Workflow Tasks")


        $VersioningData = @()
        Write-host -f Yellow "Processing Site: "$SiteURL
        #Iterate through each list in a site and get versioning configuration
        ForEach($List in $Lists)
        {
            if($ExcludedLists -NotContains $List.Title)
            {
                $VersioningData += New-Object PSObject -Property @{
                'Site' = $SiteURL
                'List' = $List.Title
                'Versioning Enabled' = $List.EnableVersioning
                'Major Versions Limit' = $List.MajorVersionLimit
                'Draft Versions Limit' = $List.MajorWithMinorVersionsLimit
                'Draft Item Security' = $List.DraftVersionVisibility
                'Content Approval Required' = $List.EnableModeration
                'Checkout Required' = $List.ForceCheckout
                }
            }
        }


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, December 13, 2016

SharePoint Online: Export Term Set to CSV using PowerShell

Requirement: Had to extract all terms in a term set to CSV format in SharePoint Online.

Unfortunately, there is no way to export sharepoint online export term set from UI. But PowerShell can help!
sharepoint online powershell export term set


SharePoint Online PowerShell to Export Term Set
PowerShell to export all terms from a given term set in Term store of SharePoint Online.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName = "Regions"
$TermSetName = "East Africa"
$CSVFile="C:\Temp\TermSetData.csv"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get the Term Group
    $TermGroup=$TermStore.Groups.GetByName($TermGroupName)

    #Get the term set
    $TermSet = $TermGroup.TermSets.GetByName($TermSetName)

    #Get all tersm from the term set
    $Terms = $TermSet.Terms
    $Ctx.Load($Terms)
    $Ctx.ExecuteQuery()

    Write-Output $TermsetName > $CSVFile
    #Export Terms to CSV
    Foreach($Term in $Terms)
    {
        Write-Output $Term.Name >> $CSVFile
    }     
    Write-host "Term Set Data Exported Successfully!" -ForegroundColor Green
}
Catch {
    write-host -f Red "Error Exporting Term Set!" $_.Exception.Message
} 
This script extract each term in the given term set into a CSV file. But wait! each term can go up to 7 levels deep! While the above script exports terms at one-single level, Here is my another PowerShell script to export terms and child terms of all levels of a given term set.
SharePoint Online Export Term Set
Export Term Set in SharePoint Online using PowerShell:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName = "Sectors"
$TermSetName = "Political Geography"
$CSVFile="C:\Temp\TermSet.csv"

#Custom Function get child terms of a given term
Function Get-Terms([Microsoft.SharePoint.Client.Taxonomy.Term] $Term,[String]$ParentTerm,[int] $Level)
{
  $ChildTerms = $Term.Terms
  $Ctx.Load($ChildTerms)
  $Ctx.ExecuteQuery()
  if($ParentTerm)
  {
    $ParentTerm = $ParentTerm + "," + $Term.Name
  }
  else
  {
    $ParentTerm = $Term.Name
  }

  Foreach ($SubTerm in $ChildTerms)
  {
     $Level = $Level + 1
     #Terms may have upto 7 levels
     $NumofCommas =  7 - $Level
     $commas =""
     
     #Append Commas
     For ($j=0; $j -lt $NumofCommas; $j++)  
     {
        $Commas = $Commas + ","
     }
    
    #Append the Output to CSV File
    "," + "," + "," + $Term.IsAvailableForTagging + ",""$($Term.Description)""," + $ParentTerm + "," + $SubTerm.Name + $Commas >> $CSVFile
    
    #Call the function recursively
    Get-Terms -Term $SubTerm -ParentTerm $ParentTerm -Level $Level
  }
}
Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Write Termset CSV Header (As in the standard format)
    "Term Set Name,Term Set Description,LCID,Available for Tagging,Term Description,Level 1 Term,Level 2 Term,Level 3 Term,Level 4 Term,Level 5 Term,Level 6 Term,Level 7 Term" > $CSVFile

    #Get the Term Group
    $TermGroup=$TermStore.Groups.GetByName($TermGroupName)

    #Get the term set
    $TermSet = $TermGroup.TermSets.GetByName($TermSetName)
    $Ctx.Load($Termset)
    $Ctx.ExecuteQuery()

    #Get all tersm from the term set
    $Terms = $TermSet.Terms
    $Ctx.Load($Terms)
    $Ctx.ExecuteQuery()

    #Write 2nd line as Termset properties(As per standard format)
    $TermSet.Name + ",""$($TermSet.Description)""," + $TermStore.DefaultLanguage + "," + $TermSet.IsAvailableForTagging + ",""$($Terms[0].Description)""," + $Terms[0].Name + "," + "," + "," + "," + "," + "," >> $CSVFile
    
    #Process each Term in the termset
    Foreach($Term in $Terms)  
    {
        write-host $Term.Name
        Get-Terms $Term -Level 1 -ParentTerm ""
    }     
    Write-host "Term Set Data Exported Successfully!" -ForegroundColor Green
}
Catch {
    write-host -f Red "Error Exporting Term Set!" $_.Exception.Message
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Thursday, December 8, 2016

How to Create State Service Application in SharePoint 2016

Lot of components like InfoPath Form services, Visio, Search Service, workflows, etc relies in State Service Application of SharePoint to store the application sessions. If its not configured already, you'd probably see error messages such as:

"The form cannot be rendered. This may be due to a misconfiguration of the Microsoft SharePoint Server State Service. For more information, contact your server administrator."

How to Create State Service application in SharePoint 2016?
State service application can't be created through new service application page. If you go to New dialog of Central Administration >> Manage Service Application page, you won't find it! here is how to create state service application in SharePoint 2016.
  • Go to Central Administration site >> Click on "Configuration Wizards" link from the bottom
  • In Configuration Wizards page >> click Launch the Farm Configuration Wizard link >> and then click "Start the Wizard".
    sharepoint 2013 configure state service application
  • On the services configuration page, under Services applications section, select the State Service check box (select any other service applications you may require) and click Next.
    create state service application sharepoint 2016 powershell
  • Click on Skip button if you set-up a top-level site collection. If not, go ahead and create it now by clicking OK.
You can verify the new state service application by: going to Central Administration > Application Management > Manage service applications >> State service application should be listed there!
create state service application sharepoint 2016
Once you created state service application, make sure your new state service application is associated with your web application by going to Central Administration >> Manage Web Applications >> Select your web application >> Click on "Service Application Associations" from the ribbon >> and in "Configure Service Application Associations" page, State Services should be selected.

Disadvantage: Configuration wizard creates state service database with GUIDs! E.g. StateService_2349fb4359fb45c9a5255562cb0eab0b. So, to avoid GUIDs in database, Create State service application using PowerShell:

SharePoint 2016: Create State Service Application using PowerShell
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration variables
$ServiceAppName = "State Service Application" 
$ServiceAppProxyName ="State Service Application"
$DatabaseName ="SP16_State_Service"

#Create New State Service application
$StateServiceApp = New-SPStateServiceApplication -Name $ServiceAppName 

#Create Database for State Service App
$Database = New-SPStateServiceDatabase -Name $DatabaseName -ServiceApplication $StateServiceApp  

#Create Proxy for State Service
New-SPStateServiceApplicationProxy -Name $ServiceAppProxyName -ServiceApplication $StateServiceApp -DefaultProxyGroup  

Initialize-SPStateServiceDatabase -Identity $Database

Write-host "State Service Application Created Successfully!"


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...