Delete List Item with Excel Data Using Powershell

function DeleteListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $ID = $worksheet.cells.item($intRow,1).value2
            Remove-PnPListItem -List $listName -Identity $ID -Force # -Force will delete without confirm
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}

Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

DeleteListItem # Call Function

Update List Item with Excel Data Using Powershell

function UpdateListItem(){

    $inputfile = Get-FileName "c:\"

    if($inputfile -ne "")

    {

        $objExcel=New-Object -ComObject Excel.Application

        $objExcel.Visible=$false

        $WorkBook=$objExcel.Workbooks.Open($inputfile)

        $worksheet = $WorkBook.sheets.Item(1)

    

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

        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count

        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)

        {

            $ID = $worksheet.cells.item($intRow,1).value2

            $Title = $worksheet.cells.item($intRow,2).value2

            $Description = $worksheet.cells.item($intRow,3).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value

            Set-PnPListItem -List $listName -Identity $ID -Values $itemValue            

        }  

        $WorkBook.close()

        $objexcel.quit()

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

    }

}

Function Get-FileName($initialDirectory)

{

    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

    

    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog

    $OpenFileDialog.initialDirectory = $initialDirectory

    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"

    $OpenFileDialog.ShowDialog() | Out-Null

    $OpenFileDialog.filename

}

UpdateListItem # Call Function

Create List Item with Excel Data Using Powershell

function AddListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $Title = $worksheet.cells.item($intRow,1).value2
            $Description = $worksheet.cells.item($intRow,2).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value
            Add-PnPListItem -List $listName -Values $itemValue
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}
# Below function for Dialog Box to select file.
Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

AddListItem # Call Function

capitalize , lowercase, uppercase to HTML input Text

I have found number of JavaScript to convert text to upper while keypress. But the way we can use CSS is the best... capitalize , lowercase, uppercase

Css :
input.upper { text-transform: uppercase; } input.lower { text-transform: lowercase; } input.capital { text-transform: capitalize; }

Input Field Example.

UPPER CASE :
lower case :
Capital Case :

Download URL Link using VBA

'Add module in VBA, Copy and Paste following code into it.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow '<~~ 2 because row 1 has headers
        Ret = URLDownloadToFile(0, ws.Range("A" & i).Value, ws.Range("B" & i).Value, 0, 0)
'URLDownloadToFile(0, ,
        If Ret = 0 Then
            ws.Range("D" & i).Value = "File successfully downloaded"
        Else
            ws.Range("D" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

SharePoint REST Filters Operators

Filter operators

Following are the various filter operators that can be used with REST interface

eq Equal to
ne Not equal to
gt Greater than
ge Greater than or equal to
lt Less than
le Less than or equal to
and Logical and
or Logical or
not Logical negation
add Addition
sub Subtraction
mul Multiplication
div Division
mod Modulo
( ) Precedence grouping

SP.ClientContext (siteURL) error this.set_formDigestHandlingEnabled

When you are using Site URL to get ClientContext use 'new' keyword while define else you will see error...
this.set_formDigestHandlingEnabled


example.

$(document).ready(function () {

    //get context 
    context = new SP.ClientContext(siteURL);
    web = context.get_web();

});