MVP for the fifth time

Proud, happy and grateful to be member of Microsft MVP family
For the fifth time!

Posted in award, Sharepoint | Comments Off on MVP for the fifth time

Search for user or group in Sharepoint groups with Powershell

If you would like to search for a user o domain group in your SharePoint on-premises groups, the following script will help you

The needed mandatory paramaters are:

$searchelement , which is the name or a port of the name of the user/group
$DC which is the Domain Controller name , used to search for the user in the domain

Running the script you will be asked to fill the credentials for an user with read permission in DC

To run the script and get informations from al sites in your farm you shoul run it under a farm admin account

Continue reading
Posted in Powershell, Sharepoint | Comments Off on Search for user or group in Sharepoint groups with Powershell

Calculated Column – get first Monday on the next month

If you have a column Data1 – DateTime type column , only date – and you need to get first Monday of the following month, the needed formula in a calculated column will be:

=IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1;DATE(YEAR(Data1);MONTH(Data1)+1;1+1);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=7;DATE(YEAR(Data1);MONTH(Data1)+1;1+2);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=6;DATE(YEAR(Data1);MONTH(Data1)+1;1+3);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=5;DATE(YEAR(Data1);MONTH(Data1)+1;1+4);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=4;DATE(YEAR(Data1);MONTH(Data1)+1;1+5);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=3;DATE(YEAR(Data1);MONTH(Data1)+1;1+6);DATE(YEAR(Data1);MONTH(Data1)+1;1)))))))

Depending on your regional settings, maybe is necessary to replace “;” with “,”

In my env., Sunday is the first day in the week

The conditions

WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1

get which day will be the first day in the next month. ( 1 is for Sunday
7 Saturday, 6 Friday…. , 2 Monday )
Based on your env, change these values, but only the red values

Posted in CalculatedColumn, Sharepoint Online | Comments Off on Calculated Column – get first Monday on the next month

Deleting specific files from Sharepoint on-premises library

Starting from this request, bellow you will find the script needed to try 2 option

The first one , the Powershell script will read the csv file and for each entry, fill parse the SharePoint library for the file

The second one, will use CAML queries to locate the specific file

For both of them, the measure command will show the time needed to resolve the requests.

Even for small libraries you will see that the second option is faster.
For large libraries and for long csv file, it is recommended yo use queries.

<#
DESCRIPTION
This script can be useful to search specific files in sharepoint on-premises, in a specific site.
The files are located in the csv file. The mandatoryy parameters are:
– $SiteUrl     : the URL for the site
– $LibraryName : the library where the files are located
– $Filename    : the csv which contains the files
The files in the csv files need to be with the full url
If the site is the root site , you can use $myweb and $mylist variables
This script can be use as it is or can be changed based on your needs. For more details please feel free to contact me
Romeo Donca, May 2017
romeodonca@outlook.com
http://www.romeodonca.ro
#>
 param(
[Parameter(Mandatory=$true,HelpMessage=”The site URL”)][string]$SiteUrl,
[Parameter(Mandatory=$true,HelpMessage=”The name of the library”)][string]$LibraryName,
[Parameter(Mandatory=$true,HelpMessage=”The csv file name”)][string]$Filename
)
Add-PSSnapin microsoft.sharepoint.powershell
###################################### parse all items
function deleting($mylist,$yoursourcefile)
{
    $files = @()
    foreach ($searchedfile in (Import-Csv $yoursourcefile))
    {
        #$myweb = get-spweb (($searchedfile.url).split(‘/’)[0]+”//”+$searchedfile.url.split(‘/’)[2])
        #$mylist = $myweb.Lists | Where-Object {$_.Title -match ($searchedfile.url).split(‘/’)[3]}
        foreach ($file in $mylist.items)
        {
            if (($searchedfile.url) -eq (($searchedfile.url).split(‘/’)[0]+”//”+($searchedfile.url).split(‘/’)[2]+”/”+$file.url))
            {
                “Deleting: ” +  $searchedfile.url
                $files = $files + $file
                break
            }
        }
    }
    foreach ($item in $files)
    {
        $item.Delete()
    }
}
#############################filter items using CAML queries
function deleting_with_queries($mylist,$yoursourcefile)
{
    $files = @()
    foreach ($searchedfile in (Import-Csv $yoursourcefile))
    {
        #$myweb = get-spweb (($searchedfile.url).split(‘/’)[0]+”//”+$searchedfile.url.split(‘/’)[2])
        #$mylist = $myweb.Lists | Where-Object {$_.Title -match ($searchedfile.url).split(‘/’)[3]}
        $query = New-Object Microsoft.SharePoint.SPQuery
        $query.ViewAttributes=”Scope=’RecursiveAll'”
        $caml ='<Where><Eq><FieldRef Name=”EncodedAbsUrl”/><Value Type=”Text”>’+$searchedfile.url+'</Value></Eq></Where>’
        $query.Query = $caml
        $query.RowLimit = 1
        $file = $mylist.GetItems($query)
        $files = $files + $file
    }
    foreach ($item in $files)
    {
        $item.Delete()
    }
}
function Show-Menu
{
     cls
     Write-Host “================================”
     Write-Host “1: Press ‘1’ for Deleting using Powershell parsing the list “
     Write-Host “2: Press ‘2’ for Deleting using Powershell and queries”
     Write-Host “Q: Press ‘Q’ to quit.”
}
do
{
    $myweb = Get-SPWeb $SiteUrl
    $mylist = $myweb.Lists[$LibraryName]
     Show-Menu
     $input = Read-Host “Please make a selection”
     switch ($input)
     {
           ‘1’ {
                Measure-Command{
                    deleting $mylist $filename
                }
                ‘You chose option: Powershell parsing the list’
           } ‘2’ {
                Measure-Command{
                    deleting_with_queries $mylist $filename
                }
                ‘You chose option: Powershell and queries’
           } ‘q’ {
                return
           }
     }
     pause
}
until ($input -eq ‘q’)
Posted in Powershell, Query, Sharepoint | Comments Off on Deleting specific files from Sharepoint on-premises library

User experience in utilizarea fisierelor din Sharepoint Online

In nou episod, al 10-lea din seria de tutoriale despre Office 365 a fost publicat pe youtube.
Acest episod este destinat utilizatorilor cu in nivel mic de cunostinte, la un prim contact cu platforma SharePoint Online din cloud-ul Office365 de la Microsoft.

 

Posted in Sharepoint Online, YouTube | Comments Off on User experience in utilizarea fisierelor din Sharepoint Online

Backup site collections from a .csv file using Powershell

Assuming that you have a .csv file which contains a list of site collections in this format

URL,path
http://site1,path1
http://urlpath/site2,path2

the line you need to backup them is this one:

ImportCsv yourfile.csv | ForEach-Object { Backup-SPSite $_.URL -Path $_.path -force}

 

But what if do you have only site’s URL? and the csv file looks like:

URL,path
http://site1
http://urlpath/site2

In this case you need to use URL to get the .bak file name

ImportCsv yourfile.csv | ForEach-Object { Backup-SPSite $_.URL -Path (($_.Url).replace(“/”,”).replace(“:”,”)+”.bak”)  -force}

where ($_.Url).replace(“/”,”).replace(“:”,”)+”.bak” will be the name of the backup file like this one
httpurlpathsite1.bak

To avoid overwriting, you could add datetimestamp in file’s name like this

Import-Csv yourfile.csv | ForEach-Object { Backup-SPSite $_.URL -Path (($_.Url).replace(“/”,”).replace(“:”,”)+”_”+(Get-Date -Format “yyyyMMddhhmm”)+”.bak”) -force
 }

and the back-up file will looks like:
httpurlpathsite1_201705010324.bak

Posted in backup, Powershell, Sharepoint | Comments Off on Backup site collections from a .csv file using Powershell

Deleting empy folders in Sharepoint Online using Powershell CSOM

starting from this post

I propose you following script

It will search all folders and subfolders  and if some of folders doesn’t contains files it will shows you these folderfs and it will ask you if you would like to delete them or not.
For any issue or question don’t hesitate to post a comment.

<#
DESCRIPTION
This script can be useful to search for empty folders and delete them, in a library located on a Sharepoint Online tenant
You need to know 2 mandatory parameters:
– $SiteCollectionUrl     : the URL for the site collection
– LibraryName           : the name of the destination libraryCopyRight
This script can be use as it is or can be changed based on your needs. For more details please feel free to contact me
Romeo Donca, April 2017
romeodonca@outlook.com
http://www.romeodonca.ro
#>
 param(
[Parameter(Mandatory=$true,HelpMessage=”The site collection URL”)][string]$SiteCollectionUrl,
[Parameter(Mandatory=$true,HelpMessage=”The name of the library”)][string]$LibraryName
)

 

Add-Type -Path “Your_PATH\Microsoft.SharePoint.Client.dll”
Add-Type -Path “your_PATH\Microsoft.SharePoint.Client.Runtime.dll”
#$SiteCollectionUrl = Read-Host -Prompt “Enter site collection URL: “;
#$LibraryName = Read-Host -Prompt “Provide library’s name “;
#$SiteCollectionUrl = “https://romeodonca.sharepoint.com”;
#$LibraryName = “Folders”;
#$folderRelativeUrl =$LibraryName+”/”
$folders_list=@()

Continue reading

Posted in Powershell, Sharepoint Online | Comments Off on Deleting empy folders in Sharepoint Online using Powershell CSOM

Sum of time values for two DateTime columns

Starting from this post let’s suppose that your two DateTime Columns are

COL_DAT1
COL_DAT2

and the request is to sum the time values. The final value  ( as a text ) will be in this format hh:mm.
For this you will need to create a calculated column and there to insert the final formula

Bellow are presented each step, one by one. Based on your regional settings, could be necessary to replace “;” with “,” in final formula ( Step 3. )

Step 1. Hours

The easiest part is to add hours, and the formula for this is
=HOUR(COL_DAT1)+HOUR(COL_DAT2)

What about minutes? The easiest  way –  add minutes from both columns
(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)

But what if this value exceed 60? This mean an hour at least. Well in this case the formula:
INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)

So, the numbers of hours will be:
=HOUR(COL_DAT1)+HOUR(COL_DAT2)+INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)

Continue reading

Posted in CalculatedColumn, Sharepoint Online | Comments Off on Sum of time values for two DateTime columns

Add domains in Office 365 with Powershell

If you would try to add a new domain to your Office 365 tenant , using Powershell, the steps are ( of course after you install Windows Azure Active Directory Module for Powershell )

Step 1. Connect to your tenant
Connect-MSolService
Get-MSolDomain ( this is just to verify which domaains are already attached to your tenant )

Step 2. Create a new Domain
New-MsolDomain -TenantId “customer_TenantId” -Name “FQDN_of_new_domain”

Step 3. Get domain verification key
Get-MsolDomainVerificationDNS -TenantId “customer_TenantId” -DomainName “FQDN_of_new_domain”

Step 4. Modify your DNS and wait for replication

Step 5. Confirm the domain
Confirm-MsolDomain -TenantId “customer_TenantId” -DomainName “FQDN_of_new_domain”

So far so good

But my question is:
Why a similar parameter ( Domain name ) use different names in these cmdlets ( -Name versus -DomainName )? Check Step 2 versus 3 and 5.

A complete Microsoft document about the procedure can be found here.

 

Posted in Office 365, Powershell | Comments Off on Add domains in Office 365 with Powershell

Create a library in each subsite in SharePoint Online

Starting from this post, bellow is the script which will create a library in each site for a specific site collection.

$url = “your site collection URL”
$description = ” This is my library”
$Title = “00My List Title in
$list_ID = 101
####### connectiong to SharePoint Online
Add-Type -Path “path\Microsoft.SharePoint.Client.dll”
Add-Type -Path “path\Microsoft.SharePoint.Client.Runtime.dll”
Add-Type -Path “path\Microsoft.Online.SharePoint.Client.Tenant.dll”
$global:cred = Get-Credential
Connect-MsolService -Credential $global:cred -ErrorAction SilentlyContinue
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.username, $cred.Password) 
$clientContext.Credentials = $cred
$web = $clientContext.Web
$clientContext.Load($web)
$clientContext.ExecuteQuery()
#create document library in site collection
 write-host ” Creating the library in site: “$web.Title
$newlistinfo  = New-Object Microsoft.SharePoint.Client.ListCreationInformation
$newlistinfo.Description=$description
$newlistinfo.TemplateType = $list_ID
$newlistinfo.title = $title + $web.Title
$newlist = $Web.Lists.Add($newlistinfo)
$clientContext.Load($newlist)
$clientContext.ExecuteQuery()
############# creation for each sub-site in that site-collection
$sites = $web.Webs
$clientContext.Load($sites)
$clientContext.ExecuteQuery()
foreach ($item in $sites)
{
    write-host ” Creating the library in site: “$item.Title
    $newlist = $item.Lists.Add($newlistinfo)
    $clientContext.Load($newlist)
    $clientContext.ExecuteQuery()
}
Posted in CSOM, Office 365, Powershell, Sharepoint Online | Comments Off on Create a library in each subsite in SharePoint Online