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

Getting started with creating a web app

This blog post is about starting with creating a web app from the ground up as I look to develop my skills in this area for possible future projects.

A web app like this sits independently from the other Google Apps meaning it is not bound to Google Sheets or Docs for instance, instead it provides a webpage that can be interacted with directly. At some point it is likely you will want the script to interact with Google Apps in some way so that you can achieve a desired task - this will come in later blog posts.

Code.gs
We begin with the function that is called when the webpage first opens and that will ultimately launch our index.html file. We use 'doGet(e)' to capture the HTTP request for which we can use the 'HtmlService' to then create the HTML webpage.

NOTE: This snippet of code is very much a 'copy and re-use' for each of your web app projects going forwards.

return HtmlService.createTemplateFromFile('index')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME);