Copy template sheet to Google Sheets and reorder

The following Google Apps Script was developed to take a template Google Sheet (containing fields for 3 signatures) and copy it into other Google Sheet files within a given Google Drive folder and then reorder those sheets so that it was the first one. The idea was based around needing users to authorise student marks with their signature from multiple Google Sheet reports produced from a third party system.

Template sheet with signatures to copy to other files

HTML email body from spreadsheet

The following Google Apps Script code is an example of creating an HTML email and getting the main body of text directly from the spreadsheet. Both HTML and Plaintext versions of the email are created to be fully adaptable to the type of email reader being used. The benefit of having the majority of the email text in the spreadsheet is that the end user can make future tweaks much easier and safer than embedding it into the Apps Script code.

Screenshot of email content in spreadsheet with HTML

Bulk create Google Drive folders

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It uses the Module Name and Marker to form the folder name as it iterates through each row in turn. At the same time it takes the Marker Email Address and uses the 'DriveApp' to provide edit access to the folder. Finally it creates another sub-folder in each folder as an extra.

The Folder ID is stored directly in the spreadsheet rather than extracting from the Folder Link via regex (example here) just for simplicity purposes. A confirmatory Yes is placed in the Permissions Added? column to help verify success as the script loops through each row.

There are lots of comments in the code to explain what each line is doing and most of the features have been covered elsewhere in the blog. Remember to add your main Google Drive folder ID into the config sheet where all the sub-folders will be created under.

Note there is now an updated/improved version of this tool detailed here.

Dynamically remove Google Form options

The following Google Apps Script code was developed as I wanted to learn about removing Google Form option choices as part of exploring its capabilities for use with appointment slots. I am aware there are a number of Google add-ons that achieve this but they come with potential data protection issues when accessing your Google Account, hence an in-house approach.

The principle is simply that a user would select their desired appointment slot on the Form and then that option would be unavailable for the next user accessing the Form. This is achieved via apps script running in the Form response sheet to recreate the multiple choice list on the Form after a submission, with only the available options left - based on their quota allowance.

Google Form appointment slot

JavaScript name:value pairs

The following JavaScript code looks at using name:value pairs when storing multiple bits of data within an array. This was recently shown to me by a colleague who used it instead of the default method of picking an item from an array by calling its position. In their example they were using it to capture multiple errors which could occur if data was missing when running a function. For this example I am just extracting a few items of data to explain the concept.

Traditionally (and throughout most of my scripts up to this point) I have extracted data from an array by calling its position only and storing the value in a variable such as this:

var data = [surname, postcode, shoeSize];
var surname = data[0];
var postcode= data[1];
var shoeSize= data[2];

Search for and remove protected named ranges

Following on from this blog post to search for protected named ranges, we now look towards removing said protection once we have found it. The following Google Apps Script is designed to loop through the protection in a spreadsheet and find a name match with a specific one we are targeting, then remove the protection.

The below line gets an array of objects representing all protected ranges in the spreadsheet:

var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

As we loop through getting the name of each named range from the above array (see previous blog post), we perform an additional if check to look for a specific name (studentDetails in this example):

if (name == 'studentDetails') {
      protections[i].remove();
      break;
    }

Upon finding the matching name we 'remove' the protection and 'break' out of the loop (as we no longer need to keep looping through the other names).

Search for protected named ranges

Following on from this blog post to protect a named range, we now look towards searching through protected named ranges in a spreadsheet.

The following Google Apps Script is designed to loop through all protected ranges and get their name. With this ability we could choose to do more in future such as removing the protection, modifying who has access, etc but for now we just demonstrate returning the name.

The below line gets an array of objects representing all protected ranges in the spreadsheet:

var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

With this simple loop we can then extract the names:

for (var i=0; i<protections.length; i++) {
  var name = protections[i].getRangeName();
  Logger.log('Named range is: ' + name);
}

Protect a named range in a Google sheet

The following Google Apps Script code is from some recent learning I have been doing when asked about locking-down certain areas of a sheet. I knew of named ranges and protecting cells but not quite that it could be done with apps script.

The user was looking towards restricting access to only certain rows or sheets that particular individuals needed to edit, in a file shared with multiple people. Over the next couple of blog posts I have dabbled with creating/finding/removing certain aspects of named range protections. The below code is designed to protect a named range in a sheet and log the name of the protected range.

Once we perform the usual getting of the spreadsheet and range we want to manage, we can use the protect feature of apps script:

var protection = range.protect().setDescription('Sample protected range');