Sunday, June 26, 2016

Export SharePoint List Data to SQL Server Table using PowerShell

Requirement:
For an in-house built business intelligence tool, had a requirement to export SharePoint list data into SQL Server table. As directly querying SharePoint content databases is not supported by Microsoft and it also could cause performance issues to your SharePoint farm - Lets not think about it further! Of course, there are 3rd party products available on the market to do this, however PowerShell can be utilized to fulfill this requirement.

This PowerShell script retrieves given SharePoint List(s), iterate through all available fields and dynamically creates SQL Server Table and then inserts the data after translating it, and inserts into the SQL Server database table.

Here is my PowerShell script to Convert SharePoint list into SQL Server table and extract data from SharePoint to SQL:

PowerShell Script to Extract SharePoint List Data into SQL Server Table:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

Function Create-List2Table($ListName,$TableName, $WebURL)
{
#Configuration Variables
$DatabaseServer="SP16-SQLServer01"
$DatabaseName="SharePointBI"
#SQL Authentication 
$UserID="UserID"
$Password="Password"

#Log File
$CurrentDir=Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$LogFile = $CurrentDir+ "\" + $(Get-Date -format "yyyyMMdd_hhmmss")+".txt"

#Get Web, List and Fields
$Web= Get-SPWeb $WebURL
$List= $Web.Lists[$ListName]
#Get all required fields from the lists 
$ListFields = $List.Fields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }

#Function to Execute given SQL Query
Function Execute-SQLQuery([string]$Query)
{
    #Write-Host "Executing SQL: ", $Query
    #Connection object
    $Connection = New-Object System.Data.SqlClient.SqlConnection("server=$DatabaseServer;database=$DatabaseName;User Id=$UserID;Password=$Password")
    try
    {
        $Connection.open() 
        $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $Connection)
        $ReturnValue = $cmd.ExecuteNonQuery()
    }
    catch  { Write-Host "Error: ", $error[0];  "$($Query): $($_.Exception.Message)" >> $LogFile }
    finally{ $Connection.close() }

}

#Function to Drop Table, if exists!
Function Drop-Table([string]$TableName)
{
  $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]"
  
  #Run the Query 
  Execute-SQLQuery $Query
}

#Get SQL column Definition for SharePoint List Field
Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field) 
{
    $ColumnDefinition=""
    Switch($Field.Type)
    {
      "Boolean" { $ColumnDefinition = '['+ $Field.InternalName +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $Field.InternalName +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $Field.InternalName +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $Field.InternalName +'] [int] NULL '}
      "Lookup" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "MultiChoice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $Field.InternalName +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (255) NULL '}      
      default { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}

################ Format Column Value Functions ######################
Function Format-UserValue([object] $ValueToFormat)
{
     $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $Users = $Users -replace "'", "''"
     return "'" + $Users + "'"
}

Function Format-LookupValue([Microsoft.SharePoint.SPFieldLookupValueCollection] $ValueToFormat)
{
     $LookupValue = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $LookupValue = $LookupValue -replace "'", "''"
     return "'" + $LookupValue + "'"
}

Function Format-DateValue([string]$ValueToFormat)
{
  [datetime] $dt = $ValueToFormat
  return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'"
}

Function Format-MMSValue([Object]$ValueToFormat)
{
  return "'" + $ValueToFormat.Label + "'"
}

Function Format-BooleanValue([string]$ValueToFormat)
{
  if($ValueToFormat -eq "Yes") {return 1} else { return 0}
}

Function Format-StringValue([object]$ValueToFormat)
{
  [string]$result = $ValueToFormat -replace "'", "''"
  return "'" + $result + "'"
}

#Function to get the value of given field of the List item
Function Get-ColumnValue([Microsoft.SharePoint.SPListItem] $ListItem, [Microsoft.SharePoint.SPField]$Field) 
{
    $FieldValue= $ListItem[$Field.InternalName]
    
    #Check for NULL
    if([string]::IsNullOrEmpty($FieldValue)) { return 'NULL'}
    
    $FormattedValue = ""
    
    Switch($Field.Type)
    {
    "Boolean"  {$FormattedValue =  Format-BooleanValue($FieldValue)}
    "Choice"  {$FormattedValue = Format-StringValue($FieldValue)}
    "Currency"  {$FormattedValue = $FieldValue}
    "DateTime"  {$FormattedValue = Format-DateValue($FieldValue)}
    "Guid" { $FormattedValue = Format-StringValue($FieldValue)}
    "Integer"  {$FormattedValue = $FieldValue}
    "Lookup"  {$FormattedValue = Format-LookupValue($FieldValue) }
    "MultiChoice" {$FormattedValue = Format-StringValue($FieldValue)}
    "Note"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "Number"    {$FormattedValue = $FieldValue}
    "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "URL"  {$FormattedValue =  Format-StringValue($FieldValue)}
    "User"  {$FormattedValue = Format-UserValue($FieldValue) } 
     #Check MMS Field
     "Invalid" { if($Field.TypeDisplayName -eq "Managed Metadata") { $FormattedValue = Format-MMSValue($FieldValue) } else { $FormattedValue =Format-StringValue($FieldValue)}  }
    default  {$FormattedValue = Format-StringValue($FieldValue)}
  }
  Return $FormattedValue
}

#Create SQL Server table for SharePoint List
Function Create-Table([Microsoft.SharePoint.SPList]$List)
{
    #Check if the table exists already
    $Query="CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, "
    foreach ($Field in $ListFields)
    {
        $Query += Get-ColumnDefinition($Field) 
        $Query += ","
    }
    $Query += ")"

    #Run the Query   
    Execute-SQLQuery $Query  
}

#Insert Data from SharePoint List to SQL Table
Function Insert-Data([Microsoft.SharePoint.SPList]$List)
{ 
    #Iterate through each row from the list 
    $ListItems= $List.Items # | where {$_["ID"] -eq 820}

    #Progress bar counter
    $Counter=0
    $ListItemCount=$ListItems.Count
    
    Write-host "Total SharePoint List Items to Copy:" $ListItemCount    
    foreach ($Item in $ListItems) 
    {

        Write-Progress -Activity "Copying SharePoint List Items. Please wait...`n`n" -status "Processing List Item: $($Item['ID'])" -percentComplete ($Counter/$ListItemCount*100)

        $sql = new-object System.Text.StringBuilder
        [void]$sql.Append("INSERT INTO [dbo].[$($TableName)] ( [ID] ")
        $vals = new-object System.Text.StringBuilder
        [void]$vals.Append("VALUES ("+ $Item["ID"])
        
        foreach ($Field in $ListFields) 
        {
            [void]$sql.Append(",[$($Field.InternalName)]")
            $ColumnValue =  Get-ColumnValue $Item $Field
            [void]$vals.Append( ","+ $ColumnValue)
        }
       
    [void]$sql.Append(") ")
    [void]$vals.Append(") ")
    
    #Combine Field and Values
    $SQLStatement = $sql.ToString() + $vals.ToString()
    
    #Run the Query   
    Execute-SQLQuery $SQLStatement 
    
    $Counter=$Counter+1;  
    }
"Total SharePoint List Items Copied: $($ListItemCount)" >> $LogFile
}

 #Call functions to export-import SharePoint list to SQL table
 Drop-Table $TableName
 Create-Table $List
 Insert-Data $List
}

#Call the function to Create SQL Server Table from SharePoint List
Create-List2Table -ListName "Projects" -TableName "ProjectData" -WebURL "https://portal.crescent.com/projects"
#Create-List2Table -ListName "Documents" -TableName "Documents" -WebURL "https://portal.crescent.com"

Make sure you have the database created (in my case, its "SharePointBI") and the User Name provided in the script has DBO or similar access on the database before running the script.

Credits: My sincere thanks to http://www.terrymarshall.com.au/Blog/tabid/162/EntryId/164/SharePoint-2010-Exporting-Lists-to-SQL-Server-Part-1.aspx

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


Tuesday, June 21, 2016

Add Site Collection Administrator to All Sites in SharePoint using PowerShell

Site collection administrators have God like Power within a Site Collection in SharePoint 2016. Apart from Primary & Secondary site collection administrators, we can add additional site collection administrator on any SharePoint site collection.

How to Add a Site Collection Administrator in SharePoint 2016?
To Add a Site Collection Administrator follow these steps:
  • Site Settings Gear >> Click on "Site Settings" Menu Item
  • Click on "Site collection administrators link under "Users and Permissions"
  • Enter the New user in Site Collection Administrators Field. Click OK to save your changes.
    PowerShell to Add Site Collection Administrator to All Sites in SharePoint
OK! Now, How about adding a same user as administrator in all site collections?

PowerShell Script to Add a Site Collection Administrator to All Site Collections in SharePoint:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#User Account to add as Site Collection Admin
$UserAccount="Crescent\Salaudeen"

Get-SPSite -Limit "All" | ForEach-Object { 
$User = $_.RootWeb.EnsureUser($UserAccount)
    if($User.IsSiteAdmin -ne $True)
    {
        $User.IsSiteAdmin = $True
        $User.Update()
        Write-Host "Added Site Collection Administrator for Site Collection:" $_.URL -ForegroundColor Green
    }
    else
    {
        Write-Host "User is already an Site Collection Administrator for Site Collection:" $_.URL -ForegroundColor Yellow
    }
} 
You can use Web Application User Policy in Central Admin to give Full control to the Entire web application, instead of adding users into individual site collections!


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


Fast Site Creation in SharePoint 2016

Fast Site Creation is a new feature introduced in SharePoint 2016. As the name suggests, Fast site Creation helps to speed up the site collection creation process. Internally, site collections are copied at at SQL Server content database level, instead of calling traditional object model calls. Typically, this helps any large organization or multi-tenant environments to create site collections faster by mitigating feature activation overhead.

Step 1: Enable Fast site creation for SharePoint Site Template
Lets enable Fast-Site Creation on SharePoint Team site Template .
Enable-SPWebTemplateForSiteMaster -Template "STS#0" -CompatibilityLevel 15

To get all site templates with fast site creation enabled, use the cmdlet:
Get-SPWebTemplatesEnabledForSiteMaster

To Disable fast site creation for a site template, use the cmdlet: Disable-SPWebTemplateForSiteMaster. E.g.
Disable-SPWebTemplateForSiteMaster -Template STS#0
But you'll have to remove the site master created for the template first!

Step 2: Create new Site master:
Once Fast Site Creation is enabled on the template, a new Site Master should be created. Create Site Master in Content Database with New-SPSiteMaster
New-SPSiteMaster -ContentDatabase "SP16_Intranet_Content01" -Template "STS#0" 
This creates a master copy of site collection. Site Master acts as the master copy for the site collection based on the template you choose, with all features, template settings,etc preserved.

Step 3: Create new site collection using the Site Master
Create new Site collection with -CreateFromSiteMaster switch in the New-SPSite cmdlet.
New-SPSite http://intranet.crescent.com/sites/operations -ContentDatabase "SP16_Intranet_Content" -CompatibilityLevel 15 -CreateFromSiteMaster -OwnerAlias "Crescent\Salaudeen" -Template "STS#0" 
With fast site creation, site Collections are created by copying at Content Database level, So the usual feature Activation cycle doesn't happen! Fast site creation took 5 seconds instead of 40 seconds to create a site collection!

Remove Site Master
If you no longer use Remove-SPSiteMaster cmdlet to remove the Site Master from SharePoint  Content database.  
Remove-SPSiteMaster -ContentDatabase "Content-database-name" -SiteId "GUID-Of-The-Site-Master"


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


Saturday, June 18, 2016

How to Restore a Previous Document Version in SharePoint 2016 using PowerShell?

How to Restore a previous document version in SharePoint?
  • Go to your document library and click and Right click on the document name and date, and then click Version History from the menu. 
  • You'll see a list of versions of the file. Hover your mouse over the Modified date column of the version you want to recover. Click on the arrow to get the menu.
    restore previous version in sharepoint
  • Click "Restore" from the version history menu and the confirm by clicking "OK" button.
    PowerShell to Restore a previous document version in SharePoint
This creates a new version from the previous version you choose.

Please note, You may have to "Check Out" prior to restoring the document, restore and then check-i, if Check out is mandatory of the particular document library.

PowerShell to restore a File's Previous version:
Here is the PowerShell script to restore the previous version of a document.
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
 
#Set Configuration Parameters
$WebURL = "http://portal.crescent.com/deals/"
$ListName = "Templates"
$ItemID="3" 

#Get the Web, List, Item and Item objects
$Web= Get-SPWeb $WebURL
$List= $web.Lists[$ListName]
$Item = $List.GetItembyID($ItemID)
 
#Get the File versions
$File = $item.File
$FileVersions = $file.Versions
#Restore the Previous version of the file
$fileVersions.Restore($fileVersions.Count - 1)  
write-host "Restored version: $($FileVersions.Count - 1) on $($File.Name)"

Restore Previous Versions of All Documents in a Library:
If you would like to go to one previous version of all the documents in a SharePoint document library, Use this PowerShell script:
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
 
#Set Configuration Parameters
$WebURL = "http://portal.crescent.com/"
$ListName = "Templates"
 
#Get the Web, List, Item and User objects
$Web= Get-SPWeb $WebURL
$List= $web.Lists[$ListName]
 
#Get all Items from the List
$ListItems = $List.Items
ForEach ($Item in $ListItems)
{
    #Get the File versions
    $File = $Item.File
    $FileVersions = $File.Versions
    
    #If the File has versions
    if ($FileVersions.Count -gt 0)
    {           
        #Restore the previous version
        $FileVersions.Restore($FileVersions.Count - 1)    
        write-host "Restored Previous Version on $($File.Name)"
        #Write-host:"Version Label: $($File.Versions[$(($FileVersions.Count-1))].VersionLabel)"
    }   
}


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


Tuesday, June 14, 2016

Fix "Unable to render the data. If the problem persists, contact your web server administrator." Issue in SharePoint BCS

Problem:
Tried created an external list SharePoint 2016 using business data connectivity services, configured secure store target application and created external list in SharePoint. All went good, however the external list displayed this error message with a correlation ID.
Unable to render the data. If the problem persists, contact your web server administrator.
Unable to render the data. If the problem persists, contact your web server administrator.
Ran ULS Log viewer and found this message in the logs:
Error while executing web part: Microsoft.BusinessData.Infrastructure.BdcException: The shim execution failed unexpectedly - Unable to obtain the application proxy for the context.. ---> Microsoft.Office.SecureStoreService.Server.SecureStoreServiceException: Unable to obtain the application proxy for the context.   

Solution:
The root cause of the problem is: The web application is not associated with the secure store service application. So, the solution is:
  • Go to SharePoint 2013/2016 Central Administration site
  • Application management >> Click on Configure service application associations under Service Applications.
  • Pick the web application in which your site exist, Check the "Application proxy group" column >> Make sure the BDC and secure store service applications check boxes checked.service application association in sharepoint 2016


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


Sunday, June 12, 2016

Create Bulk Alerts from CSV File in SharePoint using PowerShell

Requirement: Create alerts on bunch of libraries in different site collections of a SharePoint Web application!

Solution: Identified and exported list of Sites and Document Library names in the CSV file and used this PowerShell script to create multiple alerts.

PowerShell script to crate bulk alerts from CSV File:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

#Function to Create alert on Given List to given users
Function Create-Alert([Microsoft.SharePoint.SPList]$List,[Microsoft.SharePoint.SPUser]$User)
{
    $newAlert = $user.Alerts.Add()
    $newAlert.Title = "Minitoring Alert for: "+$List.Title
    $newAlert.AlertType=[Microsoft.SharePoint.SPAlertType]::List
    $newAlert.List = $List
    $newAlert.DeliveryChannels = [Microsoft.SharePoint.SPAlertDeliveryChannels]::Email
    $newAlert.EventType = [Microsoft.SharePoint.SPEventType]::Add
    $newAlert.AlertFrequency = [Microsoft.SharePoint.SPAlertFrequency]::Immediate
    $newAlert.Update()
}

#Configuration parameters
$CSVPath= "C:\Alerts.csv"  
$UserAccount= "Crescent\Salaudeen" 
 
#Get the CSV Data
$CSVData = Import-CSV -path $CSVPath

#Iterate through each Row in the CSV
foreach ($row in $CSVData) 
{
    Write-host Creating Alerts on $Row.URL, Library: $Row.LibraryName
    #Get the Web and List
    $Web = Get-SPWeb $Row.URL.TRIM()
    $List = $Web.Lists[$Row.LibraryName.Trim()]  
    $user = $web.EnsureUser($UserAccount)
    
    #Call the function to create Alert
    Create-Alert $List $User    
}     
Here is my CSV File:
Create Bulk Alerts from CSV File in SharePoint 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, June 8, 2016

SharePoint Online: Deactivate Feature using PowerShell

Requirement: Deactivate a feature in SharePoint online using PowerShell.

SharePoint Online PowerShell to disable feature:
Here is how to disable a SharePoint Online feature using the Client Side Object Model and PowerShell. 

#Load SharePoint Online 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 Disable Feature in SharePoint Online
Function Disable-SPOFeature 
{ 
    param ($SiteCollURL,$UserName,$Password,$FeatureGuid)
    Try 
    {     
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteCollURL)
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $Password)
        $Ctx.Credentials = $Credentials
        $Site=$Ctx.Site

        #Check the Feature Status
        $FeatureStatus =  $Site.Features.GetById($FeatureGuid)
        $FeatureStatus.Retrieve("DefinitionId")
        $Ctx.Load($FeatureStatus)
        $Ctx.ExecuteQuery()

        #Deactivate the feature if its enabled
        if($FeatureStatus.DefinitionId -ne $null)
        {
            Write-Host "Disabling Feature $FeatureGuid..." -ForegroundColor Yellow
            $Site.Features.Remove($FeatureGuid, $true) | Out-Null
            $Ctx.ExecuteQuery()
            Write-Host "Feature has been disabled on site $SiteCollURL!" -ForegroundColor Green
        }
        else
        {
            Write-host "Feature is Not Active on the Site collection!" -ForegroundColor Red
        }
    } 
    Catch
    {
        write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
    }
}
 
#Parameters to Activate Feature
$SiteCollURL = "https://Crescent.sharepoint.com/sites/Demo"
$UserName = "SPAdmin@Crescent.com"
$Password = "Password goes here"
$FeatureGuid= [System.Guid] ("f6924d36-2fa8-4f0b-b16d-06b7250180fa") #Publishing Feature
$SecurePassword= ConvertTo-SecureString $Password –asplaintext –force  

#Disable Feature
Disable-SPOFeature -SiteCollURL $SiteCollURL -UserName $UserName -Password $SecurePassword -FeatureGuid $FeatureGuid
This deactivates feature using PowerShell 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


Monday, June 6, 2016

SharePoint Online: PowerShell to Get Site Collection Administrators

How to Get site collection administrators in SharePoint Online?
If you want to get all site collection administrators,
  • Click on Site Settings Gear, select Site Settings
  • In Site Settings page, click on "Site Collection Administrators" link under "Users and Permission" group.
  • This page gives you the list of site collection administrators on the particular site collection.
    sharepoint online get site collection admin powershell
PowerShell online: PowerShell to get site collection administrator
You can use the PowerShell cmdlet Get-SPOUser along with the filter IsSiteAdmin property to get the list of Site collection administrators of the given site collection.
#Variables for processing
$AdminURL = "https://Crescent-admin.sharepoint.com/"
$AdminName = "SPAdmin@Crescent.com"
$SiteCollURL="https://Crescent.sharepoint.com/sites/sales"
 
#User Names Password to connect 
$Password = Read-host -assecurestring "Enter Password for $AdminName" 
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Password

#Connect to SharePoint Online
Connect-SPOService -url $AdminURL -credential $Credential

#Get the Site colection
$SiteColl = Get-SPOSite $SiteCollURL
    
#Get all Site Collection Administrators
$SiteAdmins = Get-SPOUser -Site $SiteCollURL -Limit ALL | Where { $_.IsSiteAdmin -eq $True}
foreach($Admin in $SiteAdmins)
{
    Write-host $Admin.LoginName        
}

PowerShell to get site collection Administrators of all site collections in SharePoint online:
Lets add some error handling and get the list of site collection administrators from all site collections.
#Variables for processing
$AdminURL = "https://Crescent-admin.sharepoint.com/"
$AdminName = "spadmin@Crescent.com"
 
#User Names Password to connect 
$Password = Read-host -assecurestring "Enter Password for $AdminName" 
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Password

Try {
    #Connect to SharePoint Online
    Connect-SPOService -url $AdminURL -credential $Credential

    #Get all Site colections
    $Sites = Get-SPOSite -Limit ALL

    Foreach ($Site in $Sites)
    {
        Write-host $Site.URL
    
        #Get all Site Collection Administrators
        $SiteAdmins = Get-SPOUser -Site $Site.Url -Limit ALL | Where { $_.IsSiteAdmin -eq $True}
        foreach($Admin in $SiteAdmins)
        {
            Write-host $Admin.LoginName        
        }
    }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}


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


Sunday, June 5, 2016

SharePoint Online: PowerShell to Add Users to Group

Requirement: Add user to group in SharePoint online using PowerShell.
Prerequisites: You need to have SharePoint online Management Shell installed in your client machine!
Solution: To add an individual user to SharePoint online site collection group using PowerShell, use Add-SPOUser cmdlet from SharePoint online management shell. Here is an example:

Step 1: Enter the connection Credentials for SharePoint Online Admin Center
$Credential = Get-credential
This prompts for user name and password. Key-in your SharePoint online Administrator credentials.

Step 2: Connect to SharePoint Online Service:
Connect-SPOService -url https://Crescent-admin.sharepoint.com -Credential $Credential

Step 3: Add User to SharePoint Online Site Group:
Once the connection is established successfully in the previous step, you can add user to group.
Add-SPOUser -Site "https://Crescent.sharepoint.com/sites/Sales" -Group "Sales Members" -LoginName salaudeen@Crescent.com
The SharePoint Online Management Shell looks like this:
add user to group in sharepoint online using powershell
This adds the given user to the group in SharePoint Online.
 
Bulk Add Users from a CSV File to SharePoint Online Group using PowerShell:
Now the next question is, What if you want to add 100's of users to a site collection? Here is how to add multiple users to groups in SharePoint online from a CSV file.

Step 1: Create a CSV file and fill in according to your requirements
sharepoint online powershell add user to group

Step 2: Use this PowerShell script to Read from CSV and import users to SharePoint Group:
#Connection paramaters
$AdminURL = "https://Crescent-admin.sharepoint.com/"
$AdminName = "SPAdmin@Crescent.com"
$CSVFile="C:\UsersToAdd.csv"

#User Names Password to connect 
$Password = Read-host -assecurestring "Enter Password for $AdminName" 
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Password

#Connect to SharePoint Online
Connect-SPOService -url $AdminURL -credential $Credential

#Import Users from CSV File to the Site colection
Import-Csv $CSVFile | ForEach-Object { Add-SPOUser -Site $_.SiteURL -Group $_.GroupName -LoginName $_.UserAccount}


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


Create Site Collections in Bulk from a CSV File using PowerShell in SharePoint 2016

PowerShell cmdlet New-SPSite is used to create new site collections in SharePoint. It takes URL and Owner mandatory parameters with other parameters. Here is an example:

Create SharePoint Site Collection Using PowerShell :
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Parameters to create new site collection
$SiteName = "Demo"
$SiteURL= "http://intranet.Crescent.com/sites/demo"
$SiteOwner = "Crescent\SPAdmin"
$SiteTemplate = "STS#0"
 
#create site collection with given parameters
New-SPSite -Url $URL -Name $SiteName -OwnerAlias $SiteOwner -Template $SiteTemplate

PowerShell to Create Multiple Site Collections in Bulk from a CSV File:
Lets add some error handling and wrap New-SPSite into a reusable PowerShell function, to create multiple site collections from a CSV file.
  • Step 1: Create a CSV file in the below format. Populate the column values according to your requirements.
    create site collections from csv in sharepoint
  • Step 2: Use this PowerShell script to read from CSV and create site collections in bulk.
    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    
    #Function to Create new Site Collection
    Function Create-SPSite
    {
      param
        (
            [string]$Name  = $(throw "Please Provide the Site Name!"),
            [string]$URL = $(throw "Please Provide the Site URL!"),
            [string]$Owner = $(throw "Please Provide the Site Owner!"),
            [string]$Template = $(throw "Please Provide the Site Template!")
        )
    Try {
       #Set the Error Action
        $ErrorActionPreference = "Stop"
    
        #Check if the site collection exists already
        $SiteExists = Get-SPSite | where {$_.url -eq $URL}
        #Check if site exists in the recycle bin
        $SiteExistsInRecycleBin = Get-SPDeletedSite | where {$_.url -eq $URL}
     
        If($SiteExists -ne $null)
        {
            write-host "Site $($url) exists already!" -foregroundcolor red
        }
        elseIf($SiteExistsInRecycleBin -ne $null)
        {
            write-host "Site $($url) exists in the recycle bin!" -foregroundcolor red
        }
        else
        {
            #create site collection
            New-SPSite -Url $URL -Name $Name -OwnerAlias $Owner -Template $Template
            write-host "Site Collection $($url) Created Successfully!" -foregroundcolor Green
        }
    }
    catch {
        write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
        }
    Finally {
        #Reset the Error Action to Default
        $ErrorActionPreference = "Continue"
     } 
    }
    
    #Read from CSV and create site collection
    Import-Csv "C:\SitesToCreate.csv" | Foreach-Object { 
       Create-SPSite -Name $_.SiteName -URL $_.SiteURL -Owner $_.SiteOwner -Template $_.SiteTemplate  }
    
This script uses the combination of Import-Csv and SharePoint cmdlet to create multiple site collections in SharePoint. There are other parameters such as Content Database, Language, Site description, etc you can incorporate with this.

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


Saturday, June 4, 2016

SharePoint Online: Upload Files to Document Library using PowerShell

Requirement: Upload files to SharePoint online using PowerShell.

How to Upload a File to SharePoint Online Library using PowerShell:
In SharePoint online, Lets upload a document 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"
 
#Variables for Processing
$WebUrl = "https://crescent.sharepoint.com/Sites/Sales/"
$LibraryName ="Documents"
$SourceFile="C:\SitesToCreate.csv"
$AdminName ="Salaudeen@crescent.com"
$AdminPassword ="password goes here"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminName,(ConvertTo-SecureString $AdminPassword -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl) 
$Context.Credentials = $Credentials

#Get the Library
$Library =  $Context.Web.Lists.GetByTitle($LibraryName)

#Get the file from disk
$FileStream = ([System.IO.FileInfo] (Get-Item $SourceFile)).OpenRead()
#Get File Name from source file path
$SourceFileName = Split-path $SourceFile -leaf
  
#Upload the File to SharePoint Library
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.URL = $SourceFileName
$FileUploaded = $Library.RootFolder.Files.Add($FileCreationInfo)
 
#Execute
$Context.Load($FileUploaded) 
$Context.ExecuteQuery() 

#Close file stream
$FileStream.Close()
 
write-host "File has been uploaded!" 

Upload All Files from a Folder to SharePoint Library:
Now, lets upload all files from a local folder to SharePoint library 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"
 
### Variables for Processing
$WebUrl = "https://crescent.sharepoint.com/Sites/Sales/"
$LibraryName ="Documents"
$SourceFolder="C:\Documents"
$UserName ="Salaudeen@crescent.com"
$Password ="password goes here"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl) 
$Context.Credentials = $Credentials

#Get the Library
$Library =  $Context.Web.Lists.GetByTitle($LibraryName)

#upload each file from the directory
Foreach ($File in  (dir $SourceFolder -File))
{
    #Get the file from disk
    $FileStream = ([System.IO.FileInfo] (Get-Item $File.FullName)).OpenRead()
  
    #Upload the File to SharePoint Library
    $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
    $FileCreationInfo.Overwrite = $true
    $FileCreationInfo.ContentStream = $FileStream
    $FileCreationInfo.URL = $File
    $FileUploaded = $Library.RootFolder.Files.Add($FileCreationInfo)
 
    #Execute
    $Context.Load($FileUploaded) 
    $Context.ExecuteQuery() 

    #Close file stream
    $FileStream.Close()

    write-host "File: $($File) has been uploaded!"
} 
and here is the result:
sharepoint online upload document powershell


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


SharePoint Online: How to Create New Site Collection using PowerShell

Requirement: Create a site collection in SharePoint online using PowerShell!

SharePoint Online: PowerShell to create site collection
To create site collections in SharePoint Online using PowerShell, Use: New-SPOSite cmdlet. Here is an example:
#Connect to SharePoint Online
$Credential = Get-credential 
Connect-SPOService -url https://crescent-admin.sharepoint.com -Credential $Credential

#Create new Site Collection
New-SPOSite -Url https://crescent.sharepoint.com/sites/marketing -Owner salaudeen@crescent.com -StorageQuota 1000 -Title "Marketing Team Site" -Template STS#0 
Wait for a minute and this creates a new site collection in SharePoint online.
sharepoint online powershell create site collection
Now, lets add some error handling, and wrap it inside a re-usable function:

SharePoint online PowerShell to create site:
#Function to Create Site Collection
Function Create-SPOSite 
{
  param
    (
        [string]$Title  = $(throw "Please Provide the Site Title!"),
        [string]$URL = $(throw "Please Provide the Site URL!"),
        [string]$Owner = $(throw "Please Provide the Site Owner!"),
        [int]$StorageQuota = $(throw "Please Provide the Site Storage Quota!"),
        [int]$ResourceQuota = $(throw "Please Provide the Site Resource Quota!"),
        [string]$Template = $(throw "Please Provide the Site Template!")
    )

#Connection parameters 
$AdminURL = "https://Crescent-admin.sharepoint.com"
$AdminName = "SpAdmin@Crescent.com"
$AdminPassword="Password Goes here"
$SecurePassword = $AdminPassword | ConvertTo-SecureString -AsPlainText -Force 
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $AdminName, $SecurePassword
 
Try{
    #Connect to Office 365
    Connect-SPOService -Url $AdminURL -Credential $Credentials
 
    #Check if the site collection exists already
    $SiteExists = Get-SPOSite | where {$_.url -eq $URL}
    #Check if site exists in the recycle bin
    $SiteExistsInRecycleBin = Get-SPODeletedSite | where {$_.url -eq $URL}

    If($SiteExists -ne $null)
    {
        write-host "Site $($url) exists already!" -foregroundcolor red
    }
    elseIf($SiteExistsInRecycleBin -ne $null)
    {
        write-host "Site $($url) exists in the recycle bin!" -foregroundcolor red
    }
    else
    {
        #create the site collection
        New-SPOSite -Url $URL -title $Title -Owner $Owner -StorageQuota $StorageQuota -NoWait -ResourceQuota $ResourceQuota -Template $Template
        write-host "Site Collection $($url) Created Successfully!" -foregroundcolor Green
    }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Parameters to create new site collection
$SiteTitle = "Demo"
$SiteURL= "https://Crescent.sharepoint.com/sites/demos"
$SiteOwner = "spsadmin@Crescent.com"
$StorageQuota = 1000
$ResourceQuota = 300
$SiteTemplate = "STS#0"

#Call The function to create site collection
Create-SPOSite -Title $SiteTitle -URL $SiteURL -Owner $SiteOwner -StorageQuota $StorageQuota -ResourceQuota $ResourceQuota -Template $SiteTemplate 

Now the site collection will be in a minute! You can verify by going to SharePoint Admin Center.

Create Site Collections in Bulk from a CSV File:
If you want to create site collections in bulk, you can use the above function with a CSV file.
  • Step 1: Create a CSV file in the below format. Populate the column values according to your requirements.
    powershell to create site collections from csv in sharepoint online
  • Step 2: Use this PowerShell script to read from CSV and create site collection using the function created above.
    #Read from CSV and create site collection
    Import-Csv "C:\SitesToCreate.csv" | Foreach-Object { 
       Create-SPOSite -Title $_.SiteTitle -URL $_.SiteURL -Owner $_.SiteOwner -StorageQuota $_.StorageQuota -ResourceQuota $_.ResourceQuota -Template $_.SiteTemplate  }
       


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


Wednesday, June 1, 2016

How to Hide a Field in InfoPath New Form of SharePoint List

Requirement:
We have a customized InfoPath form for a SharePoint List with "Request Number" field, which gets updated by an event receiver (or workflows sometimes) on Item created event. So, we wanted to hide the "Request Number" field from New Form.

Solution:
In high level, Insert a "section" control and move the field you want to hide inside to it. Add a formatting rule to hide that section with condition "ID is blank"! ID will be always blank on new forms!
infopath hide field in new form

Important: You need to have InfoPath Designer installed in your client machine!

Hide Fields in New Form of the InfoPath-SharePoint List form:
Here is how to hide a field in New Form, step by step:
  1. Navigate to your SharePoint list, Click on Customize Form from the List Tab. (Or you can open InfoPath Designer, New >> SharePoint List >> Design Form >> Provide the URL of SharePoint site , and run through the wizard)
  2. Insert a New row to the appropriate place in the form. In Home >> Click on Section control under Controls Group.
  3. Cut and paste the fields you want to hide in New Form to the "Section" control.
  4. Select the "Section" control >> Click on Manage Rules button from the ribbon >> Click on New, Provide a Name to it.
  5. In Condition, Set: ID is Blank
  6. Under Rule type, Select Formatting, and the tick the Hide this control check box.
  7. Save and publish your InfoPath form.

Alternate approach: You can also create a new "View" for "NewItem" in the InfoPath form with fields you want to show. and then Customize List >> Form Web Parts >>  Edit the form web part in NewForm page, and then select the default view in the properties.

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...