Create alphabetised folders in Google Drive

The following Google Apps Script is designed to bulk create folders from a Google Sheet and to arrange them into alphabetised folders within Google Drive, by extracting the first letter of their surname.

Screenshot of example Google Sheet data for creating folders

If a suitable alphabetised folder does not exist in Google Drive, then one is created. The script also adds the created folder to an 'Everybody' folder so that there is a single area with all the folders listed in.

Screenshot of Google Drive folder layout

Bulk send emails from Google Sheet

The following Google Apps Script is designed to loop through each row of a Google Sheet and send an email containing information from it. This means you can send personalised emails with ad-hoc information for each recipient quickly.

Screenshot of spreadsheet data used for creating emails

Search Google Calendar date range for specific events

The following Google Apps Script builds on this previous post for searching Google Calendar for event details by expanding the search scope to a date range and including search criteria to narrow down the results.

There are only a couple of extra lines to add since we did most of the work in the previous blog post.

var options = {search: 'Test'};
var allEvents = calendar.getEvents(startDate, endDate, options);

So this time we can specify further criteria when getting events - like searching for the text Test and providing a start/end date.

Remove HTML tags in a cell by a regex

The following Google Apps Script is designed to remove all HTML tags within the content of a cell by using a regular expression (regex). It was developed as a more advanced version of this HTML email body blog post in that it could dynamically remove various instances of HTML tags used (eg <font></font>, <br />, <strong></strong>) all in one go.

Screenshot of cell containing HTML tags and text

The regex uses 'replace' to identify the HTML tags and remove then (or technically replace them with nothing):

var cleanContent = cell.replace(/<[^>]+>/mg,"");

Split single cell contents with new line

The following Google Apps Script is designed to separate the contents of a cell that have been entered on a new line (by pressing Alt + Enter in Windows). In this example there are 3 email addresses in the one cell that we want to 'split' up so we can extract each one individually.

Screenshot of cell with content added via a new line

Bulk rename files in Google Drive

As of February 2022 this tool is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script is designed to automate bulk renaming of multiple files within a single Google Drive folder. It has been developed as a Google Sheet with flexibility towards entering your own search pattern and replacement string as you see fit.

Download

Bulk rename files in Google Drive download (please use 'File' > 'Make a copy' for your own version).

As of February 2022 this is now available as a Google Workspace Marketplace Add-on.

Clear cell content on Google Sheet edit

The following Google Apps Script is designed to clear the adjacent cell to the right of the one that has just been edited - so long as it is within the column we specify. It was developed as I was testing the available options for a Stock Inventory system where an item that was to be marked as back in stock would automatically clear an email timestamp in the adjacent cell (ready for the whole ordering process to start again).

We make use of the 'onEdit(e)' trigger to achieve this task which takes an 'event object' - e - that is passed to the function so we can determine which cell has been edited. This example focuses upon taking action on edits in column 2 only.

Screenshot of coloured columns to highlight actions

Adding basic Apps Script and CSS to a web app

This web app project now builds on the foundations we established here and adds the following:

• A CSS Style Sheet for adjusting layout, colours, font-styles, margins, etc;
• An event listener to wait for the webpage to fully load before running other functions;
• An Apps Script function to get the email address of the person visiting the web page.

Screenshot of webpage with CSS styling and email address of visitor