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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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

Le roi est mort, vive le roi!

 

 

 

Because public sites on SharePoint Online will be not anymore supported starting to first of April 2017, some changes will arrive.

The new domain is here romeodonca.com !

The old one romeodonca.ro is still running on the old platform and it will be redirected to .com

All pots are migrated, but some customizations can arrive in the next days. The link to the photo and  lme sites are not working for the moment.

Comments, suggestions and any details are welcome!

        Le roi est mort! Vive le roi!

Posted in WWW | Comments Off on Le roi est mort, vive le roi!

Validation item in Sharepoint

…this post was published on 2017 March 03.

How we can force users to fill a secondary line text if another column ( yes/no ) is checked as Yes

Suppose that , the Yes/No column with the name yesno, and as single line text column I created the column <<textcolumn>>

The formulat I propose is
=10*yesno+COUNTA(textcolumn)<>10


and the result is this one

 

Posted in CalculatedColumn, Sharepoint, Sharepoint Online, Validation | Comments Off on Validation item in Sharepoint