Tuesday 23 June 2020

Bulk create Google Drive folders 2.0

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 is an improved version of the tool built in April 2019 (original blog link here).

Improvements that have been made include:
  • A Log sheet to support troubleshooting problems.
  • More use of 'try/catch' to handle errors.
  • Adding permissions is optional.
  • Some of the column numbers can be tweaked in the Config sheet rather than diving into the code.

Tuesday 16 June 2020

Remove Google Form created during copy

The following Google Apps Script is designed to remove the Google Form that is created when copying the Response Sheet file in Google Drive. The script checks to see if the original file has a linked-Form before making a copy, after which it finds the new Form's Url and removes it.

The 'Spreadsheet App' is used to open the new file and get the Url of the Form (if there is one):
var formUrl = SpreadsheetApp.openById(newFileId).getFormUrl();
Next the 'Form App' opens the newly created Google Form and gets its Id:
var form = FormApp.openByUrl(formUrl);
var formId = form.getId();
With the Id we can perform the final 2 steps of un-linking the Google Form from the Response Sheet and then deleting it:
form.removeDestination();
DriveApp.getFileById(formId).setTrashed(true);

Tuesday 9 June 2020

Access RandomFox API for images

The following Google Apps Script uses the RandomFox website to access their API and generate a random image of a fox that is then pasted into a Google Sheet. The script is a follow on from this blog post for Numbers API as I continue to develop my skills with APIs.
Screenshot of fox image generated in spreadsheet
Screenshot of fox image generated in spreadsheet

Tuesday 2 June 2020

Sort through data using JavaScript Filter

The following Google Apps Script is designed to sort through a large amount of data and return only that which matches a certain criteria, using the JavaScript Filter method. In this example there is a list of students belonging to various groups (A1, B1, C1, etc) and we want to compile a list of those that belong to a Group ID that we specify.
Screenshot of dataset sorted by Group ID
Screenshot of dataset sorted by Group ID