Using SharePoint with SPServices

SPServices is a great library for use with SharePoint.

I’ll show you a quick example of how we can use it to read from a SharePoint list as part of validating new or existing list items.

Prerequisite Knowledge

You will need to have a basic understanding of HTML, and browsing the DOM structure using the browser’s developer tools.  You will also need to have a basic understanding of CAML query syntax.

Example: Check for Duplicate Items with SPServices

When we want to add a new item to our SharePoint list, we want to ensure that it isn’t a duplicate.  Coupled with this, we also want to make sure that when users edit a list item they don’t create a duplicate item too!  So to do this we need to add some validation.

First you need to download and install a copy of SharePoint Designer.

Once installed, run it up and connect to your SharePoint site (you will need the appropriate permissions to do this).  You will simply click ‘Open Site’ and enter the URL.  As an example, if the URL for your list is:

https://alkane.sharepoint.com/alkanehome/Lists/alkane%20tracker/AllItems.aspx

You will open the site:

https://alkane.sharepoint.com/alkanehome/

Once opened, click on Lists and Libraries.  Then click on your list.  Under the Forms section click on the form you wish to edit.

  • NewForm.aspx is the form you see when you create a new entry.
  • EditForm.aspx is the form you see when you edit a record.
  • DispForm.aspx is usually the read-only form view of the record.  We shouldn’t need to edit this for now.

In our example, we’d probably want to make changes to both the NewForm.aspx and the EditForm.aspx.

Select NewForm.aspx and put the SharePoint form into Advanced Mode.

Find the tag called <asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server"> and just before its closing </asp:Content> tag we can start adding the following code:

Firstly add a link to the external jQuery and SPServices libraries hosted on a CDN:

<!--jQuery library-->	
<script type="text/javascript" src="https://code.jquery.com/jquery-2.2.4.min.js"></script>
<!--SPServices library-->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices-2014.02.min.js"></script>	
	

And below this we add the validation code – I have commented inline so you can understand the logic:

<script type="text/javascript">

//function to get a SharePoint form element by its Title attribute
//there's probably a more elegant way to do this
function getElementByTitle(elementTitle, elementType)
{
	return $(elementType + "[title='" + elementTitle + "']");
}

//function to get a SharePoint form element by the start of the ID attribute
//there's probably a more elegant way to do this	
function getElementByIDStart(elementID)
{
	return $("[id^='" + elementID + "']");
}

function checkIfItemExists()
{
        //get the value of the entries in our form that we want to check
        var appVendor = getElementByTitle('Application Vendor Required Field','input').val();
	var appName = getElementByTitle('Application Name Required Field','input').val();
		
        //get the ID of the current form item
	var thisItemId = GetUrlKeyValue('ID');	
        //construct a CAML query to return all items that match this vendor name, application name but NOT the same ID (in case we are in edit mode)
        var existingItemQuery = 
	"<Query>" +
	   "<Where>" +  
              "<And>" +
	  	    "<And>" +
			"<Eq>" +
			    "<FieldRef Name='Application_x0020_Vendor' /><Value Type='Text' >" + appVendor  + "</Value>" +
        		"</Eq>" +
	               	"<Eq>" +
	                 	"<FieldRef Name='Application_x0020_Name' /><Value Type='Text' >" + appName + "</Value>" +
	               	"</Eq>" +
	            "</And>" +
                    "<Neq>" +
         		"<FieldRef Name='ID' /><Value Type='Text' >" + thisItemId + "</Value>" +
	            "</Neq>" +
               "</And>" +
          "</Where>" +
       "</Query>";
	          
        //initialise our item found count to zero
	var ItemCount = 0;
	
        //user SPServices to 'GetListItems' of the 'Alkane Tracker' list, passing our CAML query	
	$().SPServices({
	    operation: "GetListItems",
	    async: false,
	    listName: "Alkane Tracker",
	    CAMLQuery: existingItemQuery,
	    CAMLViewFields: "<ViewFields><FieldRef Name='Application_x0020_Name' /></ViewFields>",
	    completefunc: function (xData, Status) {
	      $(xData.responseXML).SPFilterNode("z:row").each(function() {
	        ItemCount = ItemCount + 1;
	      });
	    }
	  });
          
        //if we have found 1 or more items then it already exists and is a duplicate
	if (ItemCount > 0)
	{
  		alert("The proposed item already exists!");			
  		return false;  
  	}
		
	return true;
}

//SharePoint checks if PreSaveItems return false. In this case it will not allow the form to submit.
var PreSaveItem = function() {	
    var itemDoesNotExist = checkIfItemExists();  	
    return (itemDoesNotExist);
}

</script>

That should be pretty much it for our basic example, so save your form and try it out. You’ll also want to paste the same code into EditForm.aspx too.

NewForm.aspx, EditForm.aspx, DispForm.aspx and SharePoint Designer

Every now and then I need to update some jQuery on our SharePoint site. But I do it so infrequently that I usually forget how to do it! You see, in SharePoint Designer i can select the List from under ‘Lists and Libraries’, and then select the form I want to edit from under the Forms section. From this point I can see the ASPX code and all of my jQuery. But all of the jQuery code is highlighted in yellow and disabled!

SharePoint Designer Disabled Yellow

 

 

 

At that point, I read that in order to edit things like jQuery in NewForm.aspx, EditForm.aspx and DispForm.aspx we need to put it into Advanced Mode.  But lo and behold, the Advanced Mode button is disabled too!

SharePoint Designer Advanced Mode

 

 

 

What a pain this is turning out to be!

The only way I could enable the Advanced Mode button is to make a change to some of the code which is NOT highlighted in yellow – for example a WebPart.  If i simply add a letter the Advanced Mode button becomes enabled.  Of course, I subsequently delete the letter to restore it back to its original form and the Advanced Mode button remains enabled!  If I then click the Advanced Mode button, say ‘Yes’ to save changes (because essentially it is the same) and switch to Advanced Mode, and I can then edit anything on the page!

List Certificates for Local Machine and Current User

This is a quick PowerShell script that I knocked up to list all certificates and their location (local machine or current user and the certificate store).  You can tweak it where required.

get-childitem -Path Cert:\ | foreach-object ({
    $location = $_.Location
    foreach($store in $_.StoreNames.Keys) {         
        get-childitem -Path "Cert:\$location\$store" | foreach-object ({
            $thumb = $($_.ThumbPrint)
            $issuer = $($_.Issuer)          
            write-host "$location $store $issuer"
        })
    }
}) 

Wait For Service To Stop in a Batch File

Sometimes in a batch file we need to wait until a service is stopped before we carry on processing our script.  NET STOP will only wait a finite amount of time for a service to stop, so for services that take a while to stop we can use a simple loop.  This is just an example of stopping the Adobe update service and waiting – just replace the service name as required:

@echo off

REM Run your command line here - probably: NET STOP AdobeARMservice 
 
REM Wait for Adobe Update service to stop
:LOOP
sc query AdobeARMservice | find "STOPPED" >nul 2>&1

IF ERRORLEVEL 1 (
  Timeout /T 5 /Nobreak
  GOTO LOOP
) ELSE (
  GOTO CONTINUE
)
 
:CONTINUE
 
REM Carry on with more logic

 

Find Membership Count of all AD Groups in OU

I was working on an application migration recently, and wanted to see how many users/computers were in each application Active Directory (AD) group in a specified Organisational Unit (OU):

$Groups = Get-ADGroup -Properties * -Filter * -SearchBase "OU=Applications,DC=alkane,DC=co,DC=uk" 
Foreach($group In $groups)
{
    $groupname = $group.Name 
    $groupdescription = $group.Description
    $groupdn = $group.DistinguishedName
    $groupcount = $group.member.count
}

 

Charting with PowerShell

This post is heavily plagiarised from here.  But sometimes I see an interesting nugget of code and like to make sure I never lose it!

I’ve made a few tweaks from the aforementioned article, but I could see me using this one day to run reports on a scheduled task (connecting to SQL server etc) and emailing some pretty charts to management teams.  Here’s the code:

# load the appropriate assemblies 
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

#create form
$Form = New-Object Windows.Forms.Form 
$Form.Text = "PowerShell Chart" 
$Form.Width = 600 
$Form.Height = 600 
$Form.controls.add($Chart) 

#create chart  
$Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart 
$Chart.Width = 500 
$Chart.Height = 400 
$Chart.Left = 40 
$Chart.Top = 30

#define font (otherwise when we export the chart as an image, the default text isn't legible)
$font = new-object system.drawing.font("calibri",12,[system.drawing.fontstyle]::Regular)

#create a chartarea to draw on and add to chart 
$ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea 
$Chart.ChartAreas.Add($ChartArea)

#add data to chart 
$Cities = @{London=7556900; Berlin=3429900; Madrid=3213271; Rome=2726539; Paris=2188500} 
[void]$Chart.Series.Add("Data") 
$Chart.Series["Data"].Points.DataBindXY($Cities.Keys, $Cities.Values)

#display the chart on a form 
$Chart.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right -bor 
                [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Left 

#add title and axes labels 
[void]$Chart.Titles.Add("Top 5 European Cities by Population") 
$ChartArea.AxisX.Title = "European Cities" 
$ChartArea.AxisY.Title = "Population"

#find point with max/min values and change their colour 
$maxValuePoint = $Chart.Series["Data"].Points.FindMaxByValue() 
$maxValuePoint.Color = [System.Drawing.Color]::Red
$minValuePoint = $Chart.Series["Data"].Points.FindMinByValue() 
$minValuePoint.Color = [System.Drawing.Color]::Green

#change chart area colour 
$Chart.BackColor = [System.Drawing.Color]::Transparent

#make bars into 3d cylinders 
$Chart.Series["Data"]["DrawingStyle"] = "Cylinder"

#define fonts for chart
$Chart.chartAreas[0].AxisX.LabelStyle.Font = $font
$Chart.chartAreas[0].AxisY.LabelStyle.Font = $font
$Chart.Titles[0].font = $font
$ChartArea.AxisX.Titlefont = $font
$ChartArea.AxisY.Titlefont = $font

#add a save button 
$SaveButton = New-Object Windows.Forms.Button 
$SaveButton.Text = "Save" 
$SaveButton.Top = 500 
$SaveButton.Left = 450 
$SaveButton.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right 
$SaveButton.add_click({$Chart.SaveImage("c:\temp\Chart.png", "PNG")})
$Form.controls.add($SaveButton)

#save chart to file 
$Chart.SaveImage($Env:USERPROFILE + "\Desktop\Chart.png", "PNG")

#show form
$Form.Add_Shown({$Form.Activate()}) 
$Form.ShowDialog()

 

PowerShell, Selenium and Browser Automation

Selenium is a portable software-testing framework for web applications.  It’s pretty cool (in a geeky way).  It’s primarily used to test web applications, but in this instance we’re using it to launch Internet Explorer, load a web-based helpdesk dashboard on the intranet, log into it and click a few buttons to customise the view.

You’ll need to download IEDriverServer.exe.  Go to this location:

http://selenium-release.storage.googleapis.com/index.html

Choose the folder for the most recent version, and download IEDriverServer_Win32_[version].zip

Then download Selenium.WebDriver Nuget package from here: https://www.nuget.org/packages/Selenium.WebDriver/

and Selenium.Support Nuget package from here: https://www.nuget.org/packages/Selenium.Support/

Rename the extensions of each file from .nupkg to .zip, and extract them as you would normally.  If you then look in the Lib folder of each archive you will find:

WebDriver.dll and WebDriver.Support.dll.

An Example of Browser Automation

Thi example demonstrates Internet Explorer browser automation since it’s probably the most prevalent browser in corporate environments.  However Selenium does have libraries for Firefox and Chrome too.

So for this example, put IEDriverServer.exe, WebDriver.dll, WebDriver.Support.dll and this PS1 file all in the same folder.  Read the inline comments in the script for an explanation.  To get the website element IDs you’ll need to use the DOM explorer of the browser, but I won’t go into detail of how to do that here.

#add references to the Selenium DLLs 
$WebDriverPath = Resolve-Path "$PSScriptRoot\WebDriver.dll"
#I unblock it because when you download a DLL from a remote source it is often blocked by default
Unblock-File $WebDriverPath
Add-Type -Path $WebDriverPath

$WebDriverSupportPath = Resolve-Path "$PSScriptRoot\WebDriver.Support.dll"
Unblock-File $WebDriverSupportPath
Add-Type -Path $WebDriverSupportPath

#before we start, we must ensure all zones are running either in protected mode, or not.  They need to all be the same.
#(we might be able to negate the requirement for some of these using InternetExplorerOptions.IntroduceInstabilityByIgnoringProtectedModeSettings)

#set protected
#local
New-ItemProperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\0" -Name "2500" -Value 0 -PropertyType DWORD -Force | Out-Null
#internet
New-ItemProperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\1" -Name "2500" -Value 0 -PropertyType DWORD -Force | Out-Null
#intranet
New-ItemProperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\2" -Name "2500" -Value 0 -PropertyType DWORD -Force | Out-Null
#trusted
New-ItemProperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\3" -Name "2500" -Value 0 -PropertyType DWORD -Force | Out-Null
#restricted
New-ItemProperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\4" -Name "2500" -Value 0 -PropertyType DWORD -Force | Out-Null

#fix to run in kiosk mode (to use ForceCreateProcessApi this must be 0. ForceCreateProcessApi is required to use BrowserCommandLineArguments)
New-ItemProperty "hkcu:\Software\Microsoft\Internet Explorer\Main" -Name "TabProcGrowth" -Value 0 -PropertyType DWORD -Force | Out-Null

#Set zoom 100%.  Again, we can probably use InternetExplorerOptions.IgnoreZoomSetting as an alternative
New-ItemProperty "hkcu:\Software\Microsoft\Internet Explorer\Zoom" -Name "ZoomFactor" -Value 100000 -PropertyType DWORD -Force | Out-Null

#can pass this stuff in when we instantiate driver if needs be (if we want a chromeless browser for example)
$seleniumOptions = New-Object OpenQA.Selenium.IE.InternetExplorerOptions
#open this URL when Internet Explorer launches
$seleniumOptions.InitialBrowserUrl = "https://localhost:8080";
#we require this option to run in kiosk mode
$seleniumOptions.ForceCreateProcessApi = $true
#open Internet Explorer in kiosk mode
$seleniumOptions.BrowserCommandLineArguments = "-k"
#untested - ignore zoom options, negating the registry fix above
#$seleniumOptions.IgnoreZoomSetting = $true

#now we create a default service so we can run Selenium without the black debug command prompt appearing
#pre-PowerShell 5 we can do it like so
New-Variable -Name IEDS -Value ([OpenQA.Selenium.IE.InternetExplorerDriverService]) -Force
$defaultservice = $IEDS::CreateDefaultService()

#PowerShell 5 we can do it like so
#$defaultservice = [OpenQA.Selenium.IE.InternetExplorerDriverService]::CreateDefaultService()

#hide command prompt
$defaultservice.HideCommandPromptWindow = $true;

#provide our default service and selenium options to the Internett Explorer driver (calling this opens the IE session)
$seleniumDriver = New-Object OpenQA.Selenium.IE.InternetExplorerDriver -ArgumentList @($defaultservice, $seleniumOptions)

#now we start clicking elements on the web page.  We do this by finding the ID of the element we want to interact with.

#enter a username into login prompt
$seleniumWait = New-Object -TypeName OpenQA.Selenium.Support.UI.WebDriverWait($seleniumDriver, (New-TimeSpan -Seconds 10))
$seleniumWait.Until([OpenQA.Selenium.Support.UI.ExpectedConditions]::ElementIsVisible([OpenQA.Selenium.By]::Id("textfield-1011-inputEl")))
$seleniumDriver.FindElementById("username_text").SendKeys("exampleusernname")

#enter a password into login prompt
$seleniumWait = New-Object -TypeName OpenQA.Selenium.Support.UI.WebDriverWait($seleniumDriver, (New-TimeSpan -Seconds 10))
$seleniumWait.Until([OpenQA.Selenium.Support.UI.ExpectedConditions]::ElementIsVisible([OpenQA.Selenium.By]::Id("textfield-1012-inputEl")))
$seleniumDriver.FindElementById("password_text").SendKeys("examplepassword")

#click 'login' button
$seleniumWait = New-Object -TypeName OpenQA.Selenium.Support.UI.WebDriverWait($seleniumDriver, (New-TimeSpan -Seconds 10))
$seleniumWait.Until([OpenQA.Selenium.Support.UI.ExpectedConditions]::ElementIsVisible([OpenQA.Selenium.By]::Id("loginButton")))
$seleniumDriver.FindElementById("loginButton").Click()

#when logged in, click another button
$seleniumWait = New-Object -TypeName OpenQA.Selenium.Support.UI.WebDriverWait($seleniumDriver, (New-TimeSpan -Seconds 10))
$seleniumWait.Until([OpenQA.Selenium.Support.UI.ExpectedConditions]::ElementIsVisible([OpenQA.Selenium.By]::Id("button-1025")))
$seleniumDriver.FindElementById("random_button").Click()

#we don't close it in this instance because we want to keep the browser open as a dashboard view
#$seleniumDriver.Close()
#$seleniumDriver.Dispose()
#$seleniumDriver.Quit()

 

Invoke-Sqlcmd returning unwanted columns such as RowError, HasErrors

I’ve been performing some SQL queries recently using PowerShell and Invoke-SqlCmd.  Here is a simple example of returning a list of devices (a single column of data) from a database:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices"

Of course I pipe this into Export-CSV like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"

When we read the exported data in the CSV we expect one column of data called ‘Device’.  However, instead we can also see columns called RowError, HasErrors and others!  And whilst I’m not sure why these appear, we can omit them like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors

and of course if we wanted to pipe this into a CSV we can do so like this:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"

And voila.  We only get the columns of data that we asked for!