Monday 31 December 2018

Extract ID from Url by a regex

The following Google Apps Script code is another take on this post for splitting a Google file Url to get the ID part - but this time using a regular expression (regex). An example Url has been included but in my projects I am usually getting this from a cell that has been linked using this value.

Regex breakdown - [-\w]{25,} -
  • [ ]  match any character in the set.
  • -  matches a "-" character.
  • \w  matches any word character (alphanumeric & underscore).
  • {25,}  match 25 or more of the preceding token

Credit to the following Stack Overflow post where I initially found this.

Sunday 30 December 2018

Push items into an empty array

The following Google Apps Script code is an example of how to push items into an empty array. In this example I simply use two arrays to explain the process but in reality I use it when collecting data from spreadsheets that is to then be copied elsewhere - eg looping through students of a particular criteria to then create a new Google sheet containing just them.

The script starts by creating an empty array to eventually push the items into. An array called age has the items we are going to push - but remember this could be formed by getting data from a spreadsheet. It then loops through each item in age and 'push'es it into students.

Saturday 29 December 2018

Developing an Exceptional Circumstances system

One of my first big Google Apps Script projects was in the form of an Exceptional Circumstances system for a specific department. This is where students could request an extension to a module assessment which is then considered by several members of staff; comments and decisions also need to be recorded. In many cases extensions to several modules may be requested. In addition to a narrative for the reason for the request students may also need to be able to upload evidence documents.

Solution - Overview
  • Students complete requests using a Google form.
  • Request data populates row in an associated spreadsheet (standard with forms).
  • Apps Script generates a Google doc (based on a template) from the submitted data, including links to evidence. This is automatically triggered when the form is submitted.
  • The Url of the Google doc is written to an extra column on the Responses sheet as part of the script.
  • Staff can add comments and narrative directly to the document (document history will log all updates).

Friday 28 December 2018

Set column Url link to Doc

The following Google Apps Script code is designed to create a clickable Url hyperlink (with a nice name) to a document on a spreadsheet. This has been developed for scenarios where documents are created on Google form submissions (eg Exceptional Circumstances or bulk student feedback) and it is necessary to provide a master spreadsheet where a user can easily access these documents from one screen rather than searching through Google Drive folders.

The code below includes an example docUrl and name which would not be in the function when you use it but is there for demo purposes. Setting the hyperlink requires creating a formula ('setFormula') that is a combination of the docUrl and given name, that is then appended to the last spreadsheet row.

Thursday 27 December 2018

Split file upload Url

The following Google Apps Script code is designed to extract the file ID from a Url of a document that has been uploaded via a Google Form. You can use the function to feed in a Url (typically acquired from a spreadsheet of data), split it to get the ID, then instruct 'DriveApp' to get the file so you can do something with it.

An example Url has been included below for demo purposes only, with 'split' being used to separate the Url via the equals sign (=). The result of this is now 2 components of which the latter is the ID we want (so 1ZvhkehvoL99EmmzUsy481givp31odMaO in this example) so we can assign this to a variable. Finally, we can now use 'getFileById' to open it.

Wednesday 26 December 2018

Copy a Doc and add paragraphs

The following Google Apps Script code is designed to make a copy of an existing Google Doc and then make changes to the content of the new file. This was initially developed to feed into an Exceptional Circumstances system that will be detailed in a later blog post, where a document is created for each student that fills in a form request.

The script starts by getting the ID of the file to copy ('getFileById'), then the destination folder ID of where the new file will be copied ('getFolderById'). A copy is then made using these parameters, along with a new file name of new doc (this can be altered as required). During the copy it grabs the file Url ('getUrl') so that 'DocumentApp' can then open it and get the body to then change a tag for <<Name>> and loop through adding 5 paragraph lines.

Tuesday 25 December 2018

Email on Form submit

The following Google Apps Script code was developed for the Library to better handle postal refund forms - in the way that staff were alerted to their submission. The code is embedded in the response sheet of the form and has a project trigger to run the onFormSubmit function, of the event type - On form submit.

It gets the form data and assigns each value to a variable by specifying the column heading, it then structures an email body containing these fields along with some descriptive text (plain-text format). Using the 'MailApp' this email would be sent to a delegated mailbox that multiple staff have access to. The final part of the script then writes a value to the 'Email Alert Sent' column as a record of the action successfully completing.

Email on Form submit download here (please use 'File' > 'Make a copy' for your own version).

Monday 24 December 2018

Create a calendar event from a spreadsheet

The following Google Apps Script code is designed to send an event invitation to a number of assignee's for the date their name is against, along with a room location. For a couple of years I project managed our annual summer rebuilds which involved the help of other technical assistants who would be assigned a room to attend on the day rebuilds were scheduled. During my JavaScript learning I decided to use this small project as a way of trying out some functions that could automate creating a calendar event automatically for each person.

A number of the functions have been described in my other blog posts (Check cell colour, Get date for email event, Lookup email address), here I now combine them all. By changing the cell background colour I can provide a way to safely repeat the function should an issue occur - or more likely, if there is a change in assignee.

In terms of the calendar event this is managed via 'CalendarApp' and it will create the event in your default calendar, subsequently inviting the assignee. 'createEvent' is used to set the title, start/end details, along with extra options - location, guests (to invite the assignee), etc.

Sunday 23 December 2018

Lookup email address

The following Google Apps Script code is designed to lookup an email address from a given set of initials. It will do this by searching for a matching set of initials on another sheet then returning the email address associated with it.

The code loops through the Assignee column taking one set of initials at a time, to then create another loop to go through the Initials column of the Email Address tab. If it finds a match then the corresponding value from column 2 (the actual email address) is defined as a variable. Eventually this function will lead into sending automated email invites to people that have an associated email address.

Saturday 22 December 2018

Get date for email event

The following Google Apps Script code is designed to collect a date from a spreadsheet then appropriately format it so it can be used to later form an event invite.

Once the date has been collected from the sheet a new date variable is created, to which I then add 16 hours to for a start-time of 4pm ('setHours'). It is then necessary to format the date appropriately ('formatDate') so it has the correct timezone and layout for later feeding into the email event. This process is repeated for an end-time of 4:30pm. A menu item is created on-open to easily run the function.

Friday 21 December 2018

Check cell colour and loop

The following Google Apps Script code is part of a larger project detailed in a later post (here). It is designed to get the background colour of a cell which can be later used to determine if the rest of the function has already been run for a user - by colouring it when it has. I developed the function for a technician rota where it was highly likely there would be a change in staffing at some point and so I could clear the cell colour and re-run it without emailing everybody.

In this example once the script has acquired data from the sheet it creates a loop to go through each assignee. During the loop it gets the assignee cell ('getCell') first, which then allows for 'getBackground' to check the cell colour is white. If true, it then picks up the corresponding 'room number'. A menu item is created on-open to easily run the function.

Thursday 20 December 2018

Get column by name

The following Google Apps Script code gets the value of a cell based on a specified column name as the current row ('forename' in this instance). This is different to the typical method where a script relies on the specific column being defined by which number it is, which is prone to failing if a user then adds/removes a column within the data sheet.

In this example the function 'getColByName' looks through the headings of each column, uses 'indexOf' to find the position of 'forename' (in this example). It then returns the position (with a '+1' to account for the array starting at zero). Student data is then acquired via 'getRange' - by using the 'activeCell' (for the row number) and the position of the 'forename' column (as the starting column).

Wednesday 19 December 2018

Archive sheet data

The following Google Apps Script code is designed to move a row of data from one sheet to another - effectively 'archiving' it. Understandably this task can be performed by copying/pasting in a spreadsheet, but with a tool like this it can all be done at the click of a button.

The row number is acquired via 'getActiveCell / getRow' along with 'getLastColumn', thus allowing for the full row of data to be acquired as an array. This can now be appended to the destination sheet (so it will always appear below the last row of data) and deleted from the current source sheet.

The 'onOpen' function automatically creates a menu item called 'Custom Menu' when the spreadsheet is opened. It then contains an option called 'Archive Data' which runs this 'archiveData' function hence dramatically outperforming manual copying/pasting.

Tuesday 18 December 2018

Get specific sheet data - Improved

The following Google Apps Script code is an improved version of getting specific sheet data to translate onto another sheet (Rookie version). The code makes use of arrays to achieve this more efficiently by getting all of the data from the sheet in one go.

This time once the active spreadsheet/sheet/cell, row and last column number are found, an array of all values on the current row is created ('getRange'). This is a 1-D array which means it only has 1 set of square brackets as it is only for 1 row in this instance. Now the 4 variables can be created from the array by assigning them the position of the relevant string in the array (eg 'var postcode = theData[3]'). From here we create a 2-D array as the destination will be a 2-D array also (as it covers more than 1 row). At the same time as getting the destination array we can assign the relevant values all in one go ('setValues'), as we do in the final line of the script.

Monday 17 December 2018

Get specific sheet data - Rookie

The following Google Apps Script code was my first attempt at collecting specific data from a Google Sheet, to then translate onto another sheet. As you can see the code is a lot longer than necessary as I get each individual piece of data line by line before setting values row by row. It does however give you an idea of the process involved when scripting.

Once the code has the active spreadsheet/sheet/cell it gets the row number which will be used to acquire the specific data items via 'getRange'. A variable is then created for each of the 4 items where only the starting column number differs. Once all items have been collected the code opens the 'justOne' sheet and starts to set the values one at a time into column B.

Improved version here.

Sunday 16 December 2018

Create a Table in a Doc

The following Google Apps Script code was developed to append tables to an existing Google Doc, that was originally being created to contain student feedback. It will insert a table with 3 rows and 1 column, which can be used for better laying out text.

You will need to feed in the ID of your file so that the DocumentApp can then open it and get the body ('getBody') for you to be able to start making changes. We then create a variable for the new table and append it to the document whilst providing an array of cell contents "([['Row 1, Cell 1']])". Finally we then get the cell of this new table and add two more rows.

Friday 14 December 2018

Get ActiveCell

The following Google Apps Script code is a very basic introduction to scripting in Google Sheets and formed the first function I created. It works by getting the value and row number of the selected cell in the active spreadsheet, which you could then use in another function.

The code starts by getting the current active spreadsheet and sheet (so this will vary depending on where you are clicked when you run the function - rather than specifying an exact sheet to access). From here the 'activeCell' is acquired (so where the cursor is clicked on the sheet), then the value ('getValue') of that cell is captured in a variable and logged for reference. Finally it looks for the row number ('getRow') of the current cell and logs that too.

Tuesday 11 December 2018

Array Splice

The following Google Apps Script is designed to remove 'undefined' values from a 1-D array. It has been developed as an independent function with an existing array as its input parameter - 'dataArray'. Be aware that it will ultimately change the length of the array so it is not suited for writing values back to a spreadsheet row for instance - as the data will no longer match the number of columns.

The code will loop through each item in the array, use 'indexOf' to find the position of it and return a value (otherwise it always returns "-1"). 'Splice' can then use this value to find the position of the string in the array and remove it, ultimately returning a 'cleansed' array for you to use.

Input array: ['one', undefined, 'two', null, undefined]
Output array: [one, two, null]

Thursday 6 December 2018

Creating a restart popup - Part II

Continuing from Part I, the following PowerShell code is what I developed to run the restart popup that would inform a user their PC required a restart to complete installing pending windows updates. The code has been separated into multiple functions due to its length, which allowed for each component to be developed in isolation before being brought together - which drastically helped with diagnosing issues as they occurred.

This adds the capacity to output information to a log file as the script runs. As the popup was to be rolled out campus-wide it was crucial to be able to effectively diagnose any issues users may report. Through this function I was able to have the script report what updates it had found (if any), when it had last displayed (important later) and how the user interacted with it (dismissed or restarted).

Tuesday 4 December 2018

Creating a restart popup - Part I

One of my biggest projects to date was creating a restart popup that notified an individual when their PC required a restart, based on set criteria, to encourage them to do so. At any one time we had about 320+ PCs that had not been restarted in over 1 month and therefore had not fully installed windows updates. The focus was just on office (not classroom) PCs, requiring a careful balance between ease of development (with the possibility for future alterations) and intrusiveness (to be suitably visible without taking the focus away from the users current task).

I looked into various methods and produced prototypes, which I have detailed below:
  • BurntToast - this was originally the expected solution as it was aesthetically pleasing and integrated with the Action Center. Unfortunately it only displayed for 5 seconds which could have easily been missed by the user.

Monday 3 December 2018

Get Windows Update status

The following PowerShell code was developed to retrieve Windows Updates that had the status 'In Progress Installing'. The purpose of which was to later help develop a Restart Popup that could acknowledge when a PC needed a restart, to finish installing these updates, after a set number of days. The script looks for 3 specific status codes in order to do this (Get-WmiObject), then passes any found through to ComObject so that a date can be retrieved.

Function Get-WindowsUpdateStatus {
Retrieves information on local windows updates with status 'In Progress installing'.
Get-WindowsUpdateStatus retrieves a list of local windows updates filtered via
'ResultCode 1' for 'In Progress installing' and the source 'CcmExec' or 'AutomaticUpdates'.

Sunday 2 December 2018

Disable remote PC sleep settings

The following PowerShell code is designed to disable the sleep settings on any number of remote PC. The tool can ultimately be incorporated as a module so you can call it straight from a PowerShell console.

function Disable-Sleep {
Disables Sleep on remote PC(s).
Disable-Sleep changes the 'standby-timeout' to '0' for
AC (Mains) & DC (Battery) which sets it to 'Never'.
.PARAMETER ComputerName
Name of PC(s) to disable Sleep on.
Specify this switch to create a text log file of the PC(s)
that could not be queried.

Saturday 1 December 2018

Get remote PC sleep settings

The following PowerShell code is designed to get the sleep settings of a remote PC. It was developed due to a number of PCs dropping their network connections when left idle (eg user in a meeting), which subsequently caused issues then accessing filestores (needing a restart to resolve). The tricky part was converting the hexadecimal into a 16-base decimal so you have something that makes sense on-screen. The tool can ultimately be incorporated as a module so you can call it straight from a PowerShell console.

I will publish my tool for remotely disabling the sleep settings in my next post.

function Get-SleepSettings {
Gets remote PC(s) Sleep settings.
Get-SleepSettings queries the active Power Scheme and returns
the AC (Mains) & DC (Battery) Sleep values in minutes.
.PARAMETER ComputerName
PC name(s) to query.

Friday 30 November 2018

Network profile reset

The following PowerShell code is designed to reset both the local and network profile (on any number of PCs). Without PowerShell such a task is cumbersome as you need to connect to the C: drive of a PC, navigate to the 'Users' folder, check said user is logged off before renaming/deleting this folder, clear the relevant Registry key for that user ...... repeat this for each PC. Then connect to your network profile share and rename/delete the users folder.

The tool below can do all of this from the PowerShell console within a few keystrokes. It can ultimately be incorporated as a module too.

function Invoke-ProfileReset {
Removes network & local user profile.
Invoke-ProfileReset deletes the network profile of a given user.
It also renames the local user profile on a given PC.
Username of the profile the remove.
.PARAMETER ComputerName
Name of PC(s) to remove local user profile from.

Saturday 24 November 2018

Sigcheck a user profile

The following PowerShell code was built for a malware scanning module. It uses the Microsoft tool Sigcheck to scan for .exe files in a users profile, checks the results with VirusTotal and logs them. It utilises a .csv file as a whitelist of trusted Publishers that I created for omission to reduce the number of results. The code implements a Try/Catch protocol to test certain parameters before proceeding, aiding in error logging should any issues occur prior to scanning. The tool can ultimately be incorporated as a module so you can call it straight from a PowerShell console.

function Get-SigcheckUserProfile {
Performs a Sysinternals Sigcheck scan on a user profile.
Get-SigcheckUserProfile uses the Sysinternals tool 'Sigcheck' on
a username to scan their profile and filter out results
from known-Publishers regarded as safe.
Username of the profile to scan.
Path of the 'Valid Sigcheck Publishers.csv' file to filter out known-Signers.
Defaults to current Powershell directory.

Friday 23 November 2018

Registry wildcard search match itemproperty

The following PowerShell code is part of a larger App Deployment Toolkit for troubleshooting installation problems with Adobe Reader. The issue faced was a '1612' CCM error when trying to upgrade to the latest version. A number of PCs were displaying errors in their logs that the source file for the previous version could not be located. Troubleshooting led to Registry entries that were pointing at CCM cache folders that no longer existed. Recreating these folders and placing the relevant .msi in there allowed for a successful upgrade.

The script searches through the Installer keys of the Registry looking for Adobe Reader, it then gets the property value that identifies the CCM cache folder last used and, if it does not exist, create it and copy the .msi files into it. Finally, initiate the upgrade again.

#Checks success value of previous code and only initiates if a '1612' error is returned
If (($ReturnCode3.ExitCode -eq '1612') -or ($ReturnCode4.ExitCode -eq '1612')) {

    Write-Log 'Install Attempt 2 has failed'

Thursday 15 November 2018

Change laptop power settings

The following PowerShell code was deployed to change the power settings of a number of classroom laptops after they had been rebuilt.

#Turn off sleep
powercfg.exe -change -standby-timeout-ac 0
Write-Host 'Success - turned off sleep' -ForegroundColor Yellow

#Hibernate after 4 hours
powercfg.exe -change -hibernate-timeout-ac 240
Write-Host 'Success - Hibernate to 4 hours' -ForegroundColor Yellow

Wednesday 14 November 2018

Remove folder if empty

The following PowerShell code is part of a larger App Deployment Toolkit for uninstalling an application. As programs are usually organised into department-specific folders on the Start Menu, it is necessary to delete them during the uninstall process, but taking into account that other programs may have shortcuts in there too (hence their scripts also have this uninstall snippet). It performs a count of the objects inside the folder to determine if it can be removed.

#Get any items within the 'Maths & Stats' folder and count them
If ((Get-ChildItem -Path "$envProgramData\Microsoft\Windows\Start Menu\Programs\Maths & Stats").count -eq 0) {

Tuesday 13 November 2018

IF Version AND Service

The following PowerShell code was used to run the updater for a specific version of E-Prime where its 'hasplms' Service had stopped running. By querying the .exe file in the installation folder it was possible to find the product version which could then be compared against.

#Create a variable of the path to the file
$EPrimePath = "$envProgramFilesX86\PST\E-Prime 2.0\Program\E-Studio.exe"
#If the file exists 
If (Test-Path -Path $EPrimePath) {

       #Create a variable of the 'ProductVersionRaw' attribute, as 'ProductVersion' on its own contains commas and spaces in this instance

Monday 12 November 2018

Create a Scheduled Task

The following PowerShell code was used to create a Scheduled Task for an application called Stata which needed to run an update file in its install directory each week. The snippet is part of a larger installation script.

#Create an Action to run PowerShell and feed into it a specific file
$action = New-ScheduledTaskAction -Execute "$envSystem32Directory\WindowsPowerShell\v1.0\powershell.exe" -Argument "-File `"C:\Program Files (x86)\Stata15\run-update.ps1`""

#Create a Trigger for when the task should run
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek 'Monday' -At '9am'

Sunday 11 November 2018

Do ... While command

The following PowerShell code is designed to be used as part of a larger script. My experience of Do ... While has typically been with installing/uninstalling applications where a script has exited before certain Processes have finished. This snippet creates a 10 second delay if the specified Process is found to be still running.

#A variable with the name of the Process is created
$ProcessName = "name_here"

Do {
    #Inside the 'Do' we have the action that should be taken if the Process is detected (so Sleep in this instance)
    Start-Sleep -Seconds 10

Saturday 10 November 2018

Launch a number of programs

The following PowerShell code very simply opens a number of programs when ran. I created this to speed up weekly restarts of my PC where I always had core applications that I used. Just simply add the relevant .exe location as required and the 'Start-Sleep' is there as a precaution to add a slight pause between each one opening. Launching this as an Administrator helped to avoid entering my credentials repeatedly.

Start 'C:\Program Files (x86)\VMware\VMware Workstation\vmware.exe' | Start-Sleep -Seconds 2
Start 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\Microsoft.ConfigurationManagement.exe' | Start-Sleep -Seconds 2
Start 'C:\Program Files\Client Center for Configuration Manager\SCCMCliCtrWPF.exe' | Start-Sleep -Seconds 2
Start 'C:\Program Files\Saladin\bin\saladin.exe' | Start-Sleep -Seconds 2
Start 'C:\Windows\System32\dsa.msc' | Start-Sleep -Seconds 2
Start 'C:\Windows\regedit.exe' | Start-Sleep -Seconds 2
Start 'C:\Windows\System32\cmd.exe'

Launch a number of programs.ps1

Friday 9 November 2018

Copy file into folder if not exist

The following PowerShell code was my first attempt at automating the copying of files to a large number of PCs (simplified to a single file for the purpose of this example). The destination folder already existed and contained a number of other files, hence the need to check the source file did not exist before copying. The results are additionally outputted to a log file as 100+ PCs were queried and any that failed needed to be followed-up.

Improved version.

#Text file of PCs to query
$targetPCs = Get-Content "C:\User Files\LabVIEW\LabVIEWPCs.txt"

#Location on remote PC to copy file to
$targetdirectory = "C$\PTL\YR1 FOUNDATION LAB\"

#Location on my PC where file exists
$sourcedirectory = "C:\User Files\LabVIEW\Files\*"
$file1 = "2017-18_Y1_Lab1_FoundationElectronics_2.exe"

#Variable to later output a log of results
$output =

Thursday 8 November 2018

Bulk folder creation from spreadsheet

The following PowerShell code was developed to bulk create student folders at the start of term for individual departments. By providing a .csv file a folder can be created for each student using a naming convention derived from the column headings (a convention unique to each department).
Snippet of student_folder.csv data (anonymised)
#File location of .csv
$csvPath = "C:\User Files\student_folder.csv"

#Import .csv into script
$csv = Import-CSV $csvPath

Wednesday 7 November 2018

Bulk combine content of files

The following PowerShell code was developed to copy the content of multiple .xml files and combine them into a single .txt file. At the time this was for a number of EndNote references which needed to be imported into the application.

#Looks for a folder called 'XML' in current working location (or PowerShell directory). Pipes this through to filter .xml files only, then pipes this again to get the raw file content (keeps any white space within the file).
$XMLFolder = Get-ChildItem -Path ((Get-Location).Path + '\XMLs') | Where-Object { $_.Extension -match ".xml" } | Get-Content -Raw

#Check a combined file does not already exist (this was to prevent a user from overriding anything they already had). If file does not exist then create it (hiding 'New-Item' output via 'Out-Null' to simply avoid clogging the console window).
If (-Not(Test-Path ($CombinedFile = ((Get-Location).Path + '\Combined.txt')))) {
    New-Item -ItemType File -Path "$CombinedFile" | Out-Null

Saturday 3 November 2018

Bulk check for a Service

The following PowerShell code was developed to get the status of a specific Service on a list of PCs. It was used to diagnose how many PCs with a piece of licensing software had become inactive - a task requiring bulk automation.

#The script requires a text file containing the names of the PCs to query. It will work through each one in turn
ForEach ($Computer in Get-Content 'Computers.txt') {

    #A list of Services for the PC are acquired, then piped through to specifically find the one that equals the value 'ServiceName'
    If (Get-Service -ComputerName $Computer | Where-Object {$_.Name -eq 'ServiceName'}) {

        #Output the results to the PowerShell console (with a ForegroundColor to help. Also displays the name of the current PC from the input text file
        Write-Host "$Computer service found" -ForegroundColor Green