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,} - https://regex101.com/

• [ ]  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.

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.

Developing an Exceptional Circumstances system

Outline
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).

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.

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.

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.

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.

Download

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

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.

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.

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

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.

Add-ScriptEvent
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).

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.

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 {
<# .SYNOPSIS
Retrieves information on local windows updates with status 'In Progress installing'.
.DESCRIPTION
Get-WindowsUpdateStatus retrieves a list of local windows updates filtered via
'ResultCode 1' for 'In Progress installing' and the source 'CcmExec' or 'AutomaticUpdates'.

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 {
<# .SYNOPSIS
Disables Sleep on remote PC(s).
.DESCRIPTION
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.
.PARAMETER LogErrors
Specify this switch to create a text log file of the PC(s)
that could not be queried.

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 {
<# .SYNOPSIS
Gets remote PC(s) Sleep settings.
.DESCRIPTION
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.