Import excel data to list using PnP SharePoint Online

 

$credentials = Get-Credential -Message “Please Enter SharePoint Online credentials”

$Site="https://test.sharepoint.com/teams/OC-Product/"

Connect-PnPOnline -Url $Site -Credentials $credentials

$CustomerData = Import-CSV "D:\\LegalEntityData.csv"

 

foreach ($Record in $CustomerData){

Write-Host "adding" $Record.'DivisionNumber '

 Add-PnPListItem -List "Legal Entity" -Values @{

"Title"= $Record.'DivisionNumber ';

"DivisionAddress"= $Record.'Address';

"City"= $Record.'City';

"State"= $Record. 'State';

"Zip"= $Record.'Zip';

"BUNumber"= $Record.'Business Unit';

"Area"= $Record.'Area';

"DivisionName"= $Record.'DivisionName'

}

}

PnP script for Site Scope Feature Activation for SPO (online)

 #Config Variable

$SiteURL = "https://test.sharepoint.com/sites/HR-EngagementChamps/"

$FeatureId = "f6924d36-2fa8-4f0b-b16d-06b72890180fa" #Site Scoped Publishing Feature

 

#Connect to PNP Online

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

 

#get the Feature

$Feature = Get-PnPFeature -Scope Site -Identity $FeatureId

 

#Get the Feature status

If($Feature.DefinitionId -eq $null)

{   

    #Activate the Feature

    Write-host -f Yellow "Activating Feature..."

    Enable-PnPFeature -Scope Site -Identity $FeatureId -Force

 

    Write-host -f Green "Feature Activated Successfully!"

}

Else

{

    Write-host -f Yellow "Feature is already active!"

}

Create Site collection in SharePoint Online using PnP

 

#Get-Command -Module *PnP*

Connect-PnPOnline –Url https://dev-admin.sharepoint.com/ –Credentials (Get-Credential)

New-PnPTenantSite -Title "Legal"-Url https://dev.sharepoint.com/sites/Legal -Owner "admin@test.com" -Lcid 1033 -Template “STS#0” -TimeZone 2 -StorageQuota 1000 

Set version limit for list/lbrari items in sharepoint

 cls

Add-PSSnapin Microsoft.SHarePoint.Powershell -erroraction SilentlyContinue

$SystemLists =@("Pages", "Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery", "Reporting Templates", "Site Collection Images", "Site Pages", "Solution Gallery", "Style Library", "Web Part Gallery", "wfpub") 

$URLListFile = "C:\setversion.txt"  

$URLList = Get-Content $URLListFile -ErrorAction SilentlyContinue

   Foreach($SiteURL in $URLList) 

        { 

            $SPweb = Get-SPWeb $SiteURL

            

            Write-Host "Site Name: '$($SPweb.Title)' at $($SPweb.URL)" -ForegroundColor Green

        # loop through all lists in web

            foreach ($SPlist in $SPweb.Lists)

              { 

                if($SPlist.EnableVersioning -eq $true)

                    {

                        write-host "Setting versioning Limit for :" $SPlist.title

                        if(($SPlist.BaseType -eq "DocumentLibrary") -and ($SPlist.Hidden -eq $false) -and ($SystemLists -notcontains $List.Title)){

   

                             $SPlist.MajorVersionLimit = 5;                  

                             $SPlist.EnableMinorVersions = $true;

                             $SPlist.MajorWithMinorVersionsLimit = 5; 

                             $SPlist.Update();

                             }

                       }

                             

                             $list = $SPweb.Lists[$SPlist.Title]

                             foreach($spitem in $list.Items)

                             {

                                #Perform a empty update - without creating new version, so that versioning changes will take effect 

               

                                $itemId= $spitem.ID

                                $item = $list.GetItemById($itemId)                

                                $item.SystemUpdate()

                             }

           } 

     }

     $SPweb.Dispose() 

    

Delete Major and Minor versions

 cls

#$w = Get-SPWebApplication -Identity http://qaintranet2013.com/

#$w.GrantAccessToProcessIdentity("domain\admin1")

$webUrl = "http://qaintranet2013.com/sites/test/";

$web = Get-SPWeb $webUrl;

$list = $web.Lists["Pages"]

foreach($item in $list.Items)

{

    $minor = 0

    $major = 0

    $listsToDelete = @()

    write-host $item.Title

    

    foreach($version in $item.Versions)

    {

        if($version.Level -eq "Draft")

        {

          

          

            $minor += 1

            if($version.VersionLabel.Contains(".0"))

            { 

                #do nothing 

            }

            else

            {

                if($minor -gt 5)

                {

                    #$version.Delete();

                    $lstminor +=$version

                }

            }

        }

        else #Assume Level must be Published

        {

            $major += 1

           

           

            if($major -gt 5)

            { 

               $listsToDelete +=$version 

                #$version.Delete();

            }

        }

    }

        

    Write-Host $major "/" $minor

}

Get List/ document Items version size

 cls

Add-PSSnapin Microsoft.SHarePoint.Powershell -erroraction SilentlyContinue

#create a CSV file 

"List URL, List Name, Item Count, Last modified, List Type, List Base Template, Versioning Enabled, Major Versions Limit, Minor Versions Enabled, Minor Versions limit, Force Checkout Enabled " > D:\SPO\ListDetails.csv #Write the Headers in to a text file 

#Write the CSV Header - Tab Separated 

"Site Collection Name `t Site Name`t Library `t File Name `t File URL `t File Type `t Last Modified `t No. of Versions `t Latest Version Size(MB) `t Versions Size(MB) `t Total File Size(MB)" | out-file D:\SPO\VersionSizeReport.csv 

  

#Arry to Skip System Lists and Libraries 

$SystemLists =@("Pages", "Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery", "Reporting Templates", "Site Collection Images", "Site Pages", "Solution Gallery", "Style Library", "Web Part Gallery", "wfpub") 

   

#Get Last Year's Same day! 

$DateFilter=([DateTime]::Now.AddYears(-1)) 

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

# The following secion iterates through an entire farm. Two closing loops at the end finish out the        #

# For-Each login here                                                                                      #

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

$Farm = Get-SPWebApplication

Foreach ($WebApplicationname in $Farm)

    {

    $WebApplication = Get-SPWebApplication -identity $WebApplicationname

    $WebAppSites = $WebApplication.Sites

    Foreach ($SiteCollectionUrl in $webappsites) 

        {

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

# End farm interative initiation                                                                           #

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

                

        $site = Get-SPSite -identity $SiteCollectionUrl

        If ($site.ReadLocked -eq $false)

        {

        foreach($web in $site.allWebs) 

        { 

            write-host "Scaning Site" $web.title "@" $web.URL 

               foreach($list in $web.lists) 

               { 

                #Get only Document Libraries & Exclude Hidden System libraries 

                if ( ($List.BaseType -eq "DocumentLibrary") -and ($List.Hidden -eq $false) -and($SystemLists -notcontains $List.Title) ) 

                { 

                    foreach ($ListItem  in $List.Items) 

                    { 

          #Consider items with 5+ versions And apply Date Filter 

                        if ($ListItem.Versions.Count -gt 20) 

                        { 

          $versionSize=0 

  

                            #Get the versioning details 

                            foreach ($FileVersion in $ListItem.File.Versions) 

                            { 

                                $versionSize = $versionSize + $FileVersion.Size; 

                            } 

       #To Calculate Total Size(MB) 

       $ToalFileSize= [Math]::Round(((($ListItem.File.Length + $versionSize)/1024)/1024),2) 

         

                            #Convert Size to MB 

                            $VersionSize= [Math]::Round((($versionSize/1024)/1024),2) 

         

       #Get the Size of the current version 

       $CurrentVersionSize= [Math]::Round((($ListItem.File.Length/1024)/1024),2) 

  

                            #Log the data to a CSV file where versioning size > 0MB! 

                            if ($versionSize -gt 0) 

                            { 

                                "$($Site.RootWeb.Title) `t $($Web.Title) `t $($List.Title) `t $($ListItem.Name) `t $($Web.Url)/$($ListItem.Url) `t $($ListItem['File Type'].ToString()) `t $($ListItem['Modified'].ToString())`t $($ListItem.Versions.Count) `t $CurrentVersionSize `t $($versionSize) `t $($ToalFileSize)" | Out-File D:\SPO\VersionSizeReport.csv -Append

                            } 

                        } 

                    } 

                } 

}

}

            } 

  $Web.Dispose()           

        } 

 $Site.Dispose()           

    } 

   

    #Send message to console 

    write-host "Versioning Report Generated Successfully!"

List All WebParts

 #----------------------------------------------------------------------------- 

# Name:             list-all-webparts.ps1  

# Description:      This script will create a list of all web parts in all 

#                   pages in a site collection.

#                     

# Usage:            Run the script passing three paramters: Url, Folder and WP. 

# Url Accepts multiple values (comma separated).

#                   Folder is the destination of the report.

# WP is the name of the web part (optional).

# By:               Riccardo Celesti blog.riccardocelesti.it

#----------------------------------------------------------------------------- 

Param([Parameter(Mandatory=$true)] 

      [String] 

      $Url,

      [Parameter(Mandatory=$true)] 

      [ValidateScript({Test-Path $_ -PathType 'Container'})]

      [String] 

      $folder,

  [Parameter(Mandatory=$false)]

      [String] 

      $WP

) 

if ((gsnp Microsoft.SharePoint.Powershell -EA SilentlyContinue) -eq $null){

    asnp Microsoft.SharePoint.Powershell -EA Stop

}

$filename = "WebPartsReport_" + (Get-Date).ToFileTimeUtc().ToString() + ".csv"

$filenamewp = "WebPartsReport_" + $WP.Replace(" ","-") + "_" + (Get-Date).ToFileTimeUtc().ToString() + ".csv"

$logfile = Join-Path $folder $filename

$logfilewp = Join-Path $folder $filenamewp

$urlArray = $Url.Split(",")

$header = "File Url, Web Part Title, Web Part Type, Visible"

ac $logfile $header

ac $logfilewp $header

$logfilecontrol = $null

foreach ($SPsite in $urlArray){

    Get-SPSite $SPsite| % {

        foreach ($web in $_.AllWebs){

        

            if ([Microsoft.SharePoint.Publishing.PublishingWeb]::IsPublishingWeb($web)){

                $library = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)

                $pages = $library.PagesList

            

                foreach ($file in $pages.Items){

                    $fileUrl = $web.Url + "/" + $file.File.Url

                    $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                    $webparts = $manager.webparts

                    $webparts | %{

                        ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                            $logfilecontrol = 1

}

                    }

                }

                $sitepages = [Microsoft.Sharepoint.Utilities.SpUtility]::GetLocalizedString('$Resources:WikiLibDefaultTitle',"core",$web.UICulture.LCID)

                $pages = $null

                $pages = $web.Lists[$sitepages]

                if ($pages -and $pages.ItemCount -gt 0){

                    foreach ($file in $pages.Items) {

                        $fileUrl = $web.Url + "/" + $file.File.Url

                        $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                        $webparts = $manager.webparts

                        $webparts | %{

                            ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                                $logfilecontrol = 1

}

                        }   

                    }

                }

            } else {

                $sitepages = [Microsoft.Sharepoint.Utilities.SpUtility]::GetLocalizedString('$Resources:WikiLibDefaultTitle',"core",$web.UICulture.LCID)

                $pages = $null

                $pages = $web.Lists[$sitepages]

                if ($pages){

                    foreach ($file in $pages.Items) {

                        $fileUrl = $web.Url + "/" + $file.File.Url

                        $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                        $webparts = $manager.webparts

                        $webparts | %{

                            ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                            

                            if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                                $logfilecontrol = 1

}

                        }

                    }

                }

            }

        }

    }

}

notepad.exe $logfile

if ($logfilecontrol -eq 1){

    notepad.exe $logfilewp

}