PowerShellSharePoint Online

Tips: Working with Date and Time in PowerShell and SharePoint List Items – Part 2

Last week I blogged about the Date Time tips with reference to SharePoint and PowerShell – Refer here. In this blog post let me show case how to implement in SharePoint practically.

Create a List and name as you wish and in my case it’s “Employee Feedback”! Create two columns and name it as “WeekStart” and “WeekEnd“. The columns I have is shown below!

 Here is the exact formulas. (Weekdays Wednesday through Tuesday)

=Created-(WEEKDAY(Created)+IF(WEEKDAY(Created)<4,-3,4)-1)
=Created-(WEEKDAY(Created)+IF(WEEKDAY(Created)<4,-3,4)-1) + 6

With no wait let us see the result

Here comes another requirement for which we need to put a small effort! Feedback is a Choice column in SharePoint list which has Good, Bad and Neutral as values! We need to create a SharePoint view which shows 2 Good and 2 Bad per employee entries for the current week (Wednesday till Tuesday) .Yes, we don’t need to work hard on date time because we have Week Start and Week End columns as our saviour.

Simple logic is to add a group filter for Feedback and Created By Column and set the item limit for view.

Filter

Group By

Set Item Limit

Here is the outcome!

In my machine the Start Week Day is Monday till Friday! So Manager View turns empty. That’s obvious. So, let me spin PowerShell to change my Created At value to AUG 15, 2018.

Note: DO NOT ENTERTAIN MANIPULATING CREATED or MODIFIED FIELDS IN PRODUCTION BOX – YOU MAY VIOLATE PROCESS. USE THE BELOW CODE AT YOUR OWN RISK

Update List Item

Import-Module ".\assemblies\Microsoft.SharePoint.Client.dll"
Import-Module ".\assemblies\Microsoft.SharePoint.Client.Runtime.dll"
$UserName = "SuperAdmin"
$Password = "SuperPassword" | ConvertTo-SecureString -AsPlainText -Force
$Credential = New-Object pscredential -ArgumentList ($UserName , $Password)
$Url = "https://<tenant>.sharepoint.com"
$SPOClientContext = [Microsoft.SharePoint.Client.ClientContext]::new($Url)
$SPOClientContext.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Credential.UserName,$Credential.Password)
$ListItem = $SPOClientContext.Web.Lists.GetByTitle('Employee Feedback').GetItemById(1)
$ListItem["Created"] = (Get-Date).AddDays(2)
$ListItem.Update()
$SPOClientContext.Load($ListItem)
$SPOClientContext.ExecuteQuery()
$SPOClientContext.Dispose()

See the end result – Manager View

Can we hide the Show All on the web page? Let me think about it and blog if possible! Cheers and Enjoy PowerShell and SharePoint.

Leave a Reply

Your email address will not be published. Required fields are marked *