SharePoint CSOM Dashboard- Thangu

SharePoint CSOM to add data from CSV

PowerShell and Excel

PowerShell is very simple and powerful. Microsoft Office products like Word, Excel, PowerPoint has changed the world and become a part of every household.Advanced COM programming is possible, which I use for advanced operations on Word. Lets see how PowerShell is helpful with  single line code with Excel.

Command Available
Export-CSV
$data| Export-csv -Path $csvPath -Append

Import-CSV
$newFruits=Import-Csv -Path $csvPath

Let is say you have a Fruits data along with color information, we will display the fruits information in Excel as shown below:

$yourFolder="C:\demo\thangu"
$csvPath=$yourFolder+"\fruitsCsv.csv"
#New powerShell Table
$hashContent = [PSCustomObject]@{Name="Apple";Color="Red"}
$hashContent | Export-csv -Path $csvPath -Force
$hashContent = [PSCustomObject]@{Name="Mango";Color="Yellow"}
$hashContent | Export-csv -Path $csvPath -Append

$newFruits=Import-Csv -Path $csvPath

Sample Application to move data to external system(SharePoint)

SharePoint List Addition
Lets try to add some items from CSV to SharePoint List having fruits data.I used CSOM to read the data from CSV file created above and used the following CSOM code to move the data from CSV to Fruits list.

foreach($fruit in $newFruits){
$newItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newItem=$psList.AddItem($newItemInfo)
$newItem["Title"] = $fruit.Name
$newItem["Color"] = $fruit.Color
$newItem.Update()
$Context.ExecuteQuery()
}

Resting in Peace with SharePoint Rest Apis

Introduction

Are you tired of writing many lines of code to do CRUD functionality? Lets take the simple case of trying to Read a SharePoint List and add items to a SharePoint list.You would have to write two functions using CSOM/JSOM/SSOM:

1. ReadList() 
2. AddListItem()

With the advent of REST in SharePoint, you can focus on your functionality and not on the code/language. All you have to do is to construct the URL based on your needs and reuse the REST CRUD code.

SharePoint team has done a lot of coding and made common functionality available for access from a URL.

You append the SharePoint site URL with REST Url.

Construction of REST Url

The REST Url begins with /_api/

After the api, you specify the Object. Example web, site. For nested objects /web/lists

Lets begin with a simple Url that reads the Site Url.

First, you get the Site Collection  Url and then append that with the Rest Url

Example:

https://abc.sharepoint.com/sites/dev/_api/site/Url

If you open the above Url, you would see a output like below:

<d:Url>https://abc.sharepoint.com</d:Url>

The results are mostly accessed by data.d or data.d.results for multiple items

If you are new to REST, here is a quick explanation on REST basics. REST technology allows developers to write functions using any technology of their choice and publish that as a URL which does the following tasks for a resource/object:

GET- Read the object and returns the results mostly in JSON/Xml format

POST- Get the data details and Add Items

PUT - Update the Object 

DELETE - Delete the Object

SharePoint and PowerShell code to do REST

Lets use PowerShell to get connected to SharePoint URL and do various Read operations. The cool thing is there is only one PowerShell code and the URL alone changes. In regular CSOM coding, you need to remember the Class and the methods to get your job done. in REST, the code remains the same and the URL alone changes.

You can download the code from here.

Here is a quick summary of the PowerShell code to do the CRUD operations.

1. Get connected to SharePoint using SharePoint credentials
2. Add the necessary headers to $headers based on CRUD operation
   1. GET- "Accept", "application/json;odata=verbose"
   2. POST- "X-RequestDigest", $formDigest from /_api/contextinfo
   3. PUT- "X-HTTP-Method", "PUT" or "MERGE"
   4. PUT- "X-HTTP-Method", "DELETE"
3. Invoke-RestMethod -Uri $fullUrl -Headers $headers -Method Post -WebSession $webSession -ContentType "application/json;odata=verbose"

Once, you run the above code, you can start using Read-SPObject,Create-SPObject,Update-SPObject on various SharePoint objects like Site, Lists,Files and many more based on your needs.

Add List Items using REST

Lets start with our original need to read a SharePoint List and add Items to a SharePoint list say PSRestList. 

$targetSite="https://xxx.sharepoint.com/sites/dev"
$User="admin@xxx.onmicrosoft.com"
$restUrl="/_api/web/lists/getbytitle('PSRestList')"
$listsPSRest=Read-SPObject -targetSite $targetSite -User $User -restUrl

Below are the details needed to add a item to a SharePoint list. Note that the data part contains the List Name, List Columns like Title.

#Add List Item
$listName="PSRestList"
$data=@"
{ '__metadata': { 'type': 'SP.Data.$($listName)ListItem' }, 'Title': 'OneTwoThree','CurrencyThangu':'2500' }
"@
$restUrl="/_api/web/lists/GetByTitle('"+$listName+"')/items"
$listItemNew=Create-SPObject -targetSite $targetSite -User $User -restUrl $restUrl -data $data

Summary

As a quick summary, you learnt the following:

1. Basics of REST and its benefits
2. Construction of SharePoint REST Url
3. PowerShell code that Invokes the SharePoint URL
4. Adding a SharePoint List Item using the REST Url.

If you like to learn by watching videos, here is a sample REST demo.

JSON Requests in PowerShell

PowerShell is extremely powerful. I am a trainer and consultant in various technologies like PowerShell, SharePoint, Javascript etc and no matter what the technology is, what is crucial for any technology? DATA

Data can be exchanged in various formats like Xml, Variables in parameters in URL.

What is the most recent way to exchange data? JSON

JSON is a different way of expressing the data. Lets take the example of object Apple.
The Apple is a fruit red in color.

Fruit named Apple
In computer language, we can express that as

var fruit;
fruit="apple";

For additional details, JSON comes to the rescue where color description can be given.

fruits="{'Name':'Apple','Color':'Red'}"

In Powershell, we can read JSON easily using ConvertTo and ConvertFrom Json function.PowerShell is very simple compared to other programming languages. One line of code!

Below is a script that converts the fruits JSON and displays that in beautiful table format.
$fruits="{'Name':'Apple','Color':'Red'}","{'Name':'Mango','Color':'Yellow'}"

$fruits |ConvertFrom-Json

In real world, I heavily use SharePoint and there are times when the JSON results are not converted sometimes. In that case, I have to do minor tweaks to display the JSON data in powershell. Below is a code that reads REST results from SharePoint, but the tabular data is not displayed.

$listName="PSRestList"
$restUrl="/_api/web/lists/GetByTitle('"+$listName+"')/items"
$listItems=Read-SPObject -targetSite $targetSite -User $User -restUrl $restUrl
$listItems

How to handle the error? By replacing the conflicting columns with another column.
#ID error
$results = $listItems.ToString().Replace("ID", "_ID") | ConvertFrom-Json
$results.d.results|select Title

Updating Registry Values

PowerShell is simply superb. Windows changed the world by giving a computer for every Home. I love Windows and prefer Windows over Mac, since looks are always deceptive.

As a kid, I used to play on Windows games like Xatax and enjoyed BASICs programming in school. I had to type in the command prompt to login to Windows. Windows can be modified using Registry values. Just as A,B,C is fundamental to English. Registry values are low level Windows Fundamentals. This contains many Windows Configuration details.

You can open the Registry Editor by typing "regedit" in the command prompt(Start->Run). You are advised not to change any settings unless you are an administrator. In case , you are changing, take a backup and then change.The registry contains Key Value pairs.They are grouped under various folder like locations like HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG. They are called hives, because the original Windows developer of NT version hated bees.

How can we view the registry values in PowerShell,example of HKEY_CURRENT_USER?

Make sure you have permission to view the folder:

READ:

Get-ChildItem -Path Registry::HKEY_CURRENT_USER

CREATE:

1. Take a backup of Registry using File->Export 

Save in a location like c:\backup\registry\backup.reg

You can restore in case of any errors by using File->Import from above location.

2. Suppose you want to add a "Open with notepad" icon, you need to add a registry value to 

"Open with notepad" under shell location

Change the value to notepad.exe %1

Below are the commands:

Push-Location
Set-Location -Path Registry::HKEY_CLASSES_ROOT

Test-Path ".\*\shell\Open With Notepad"

Set-Location -LiteralPath "Registry::HKEY_CLASSES_ROOT\*\shell"

New-Item -Name "Open With Notepad"

Set-Location -LiteralPath  "Registry::HKEY_CLASSES_ROOT\*\shell\Open With Notepad"
New-Item -Name "command"

UPDATE

Use Set-Item to update the key value

Set-Item  -LiteralPath "Registry::HKEY_CLASSES_ROOT\*\shell\Open With Notepad\command" -Value "notepad %1"

Reference:

https://docs.microsoft.com/en-us/powershell/scripting/getting-started/cookbooks/working-with-registry-keys?view=powershell-6

https://www.howtogeek.com/370022/windows-registry-demystified-what-you-can-do-with-it/

SharePoint Calculated Columns

SharePoint Calculated Columns are very interesting.

References:
https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

SharePoint and PowerShell PnP View Default Value of a Field

In this page, the steps are given to get the default value of a Choice Column in a list in SharePoint.

Open PowerShell and execute the following commands. Change the URL to your sharepoint URL and execute with your credentials

Install-Module -Name "SharePointPnPPowerShellOnline"
#Please change Demo to yourname in the url
$siteurl = "https://xxx.sharepoint.com/sites/XXX/Demo"

Connect-SPOnline -Url $siteurl -Credentials (Get-Credential)
#View all Fields for Student List
Get-PnPField -List "FruitsDemo1" -Identity "Color" |select DefaultValue