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');

Fixing dates from a Google Form

Following on from my 'Check if a date is more than a month ago' post I proceeded to place the Google Apps Script code within a Form to achieve the original task that was asked. To my surprise it did not work anymore!

What was wrong?
Google Apps Script was flipping the Day/Month from the Form date - so 01/02/2019 (1st February 2019) became 02/01/2019 (2nd January 2019) when creating a 'new Date()' variable. Interestingly the date that went into the Response sheet was perfectly fine and remained intact.

Why?
After plenty of Googling I finally discovered a number of complaints about Googles capability to handle dates (Stack Overflow 1, Stack Overflow 2). So even though I was using a Google Form with Google Apps Script it was ineffective to get a date directly from it.

Workaround
I had a couple of workarounds in mind:

1. Get the date directly from the Response spreadsheet instead - as it was correct here Google Apps Script did not try to do anything erroneous with it. My issue with this workaround was that I am already taking other data directly from the Form - I was not happy with mixing and now getting it from the sheet too.
2. Parse the date myself and create it by breaking down the date from the Form (so getting the Day, Month, Year separately). This way I was getting everything from the Form and could in effect create a function to perform the task for multiple dates on a Form. This is the option I have explored below ...

Check if a date is more than a month ago

Recently during a consultation I was asked if it would be possible to check if the date submitted on a Google form was over a month ago, to which I responded "sure ...".

Admittedly this was a lot more difficulty than I thought it would (and should) be - I was bamboozled by online forums and posts suggesting to convert dates into numbers and perform other incoherent functions to achieve this. Eventually however I came across the single JavaScript function that I would need to achieve this feat ...... 'getMonth'.

In the below Google Apps Script code we start by getting the current date and presenting it in a nice format ('toLocaleDateString'). We need this date as it will be used to compare if our selected date is more than a month ago:

var currentDate = new Date();
var niceCurrentDate = currentDate.toLocaleDateString();