Webpage photo gallery

The following JavaScript code is designed to present a selection of images as thumbnails with a bigger preview version displayed on mouse-over.

Screenshot of image gallery with thumbnails

Connect to MailJet API with Google Apps Script

The following Google Apps Script can be used to connect to the MailJet API, from where you can then send emails and manage contacts. There is a lot of documentation on how to connect via PHP, Python, Rub, C# etc but nothing suited towards Google Apps Script.

To connect you will need to have a MailJet account and your own API/Secret keys. In this example we will connect with the 'Contact List' aspect of the API. We need to create an encoded string which contains our API keys:

var encoding = Utilities.base64Encode(apiKey + ":" + secretKey);

From here we can set the HTTP headers authentication as part of the request, to authorise our account details:

var options = {
    'headers': {"Authorization": "Basic " + encoding},
  };

Then we can continue with the usual route of using the 'UrlFetchApp' to make the request and return our data.

Webpage background colour changer

The following JavaScript code is designed to randomly change the background colour of the webpage each time it is refreshed.

Screenshot of webpage colour

Bulk delete records from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk delete records in an existing SQL Table, from data in a Google Sheet. This naturally follows on from being able to bulk add data in this post.

Rather than getting all of the data from the Google Sheet, this time we are just going to use the Student No which provides a unique value for each record (as it is never repeated). Once we have these values in an array we can loop through each row and use them in our SQL query.

Our query specifies which Table we want to remove the record from and a where condition that has to be met (without this all records would be deleted leaving the Table empty). The where condition in this example is finding a corresponding Student No match.

var sql = "DELETE FROM students "
+ "WHERE StudentNo=" + studentNo;

Bulk add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table. This post improves upon this one for adding a single piece of ad-hoc data.

Screenshot of Google Sheet data for SQL Table

This time we have a Google Sheet of data with columns matching those in the students Table. We start by collecting this data into an array that we can loop through to push the values into an SQL query. When we create the query we construct it slightly differently however as we need to include variables rather than static text (as their value will change for each row when the script loops through).

var sql = "INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
+ "VALUES ('" + studentNo + "','" + firstName + "','" + lastName + "','" + shoesize + "')";

Get data from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to query an existing Table to then copy the data into a Google Sheet. This post follows on from this one for adding data to an SQL Table.

This time we make use of a 'select' SQL query to pickup all columns (*) from the students Table. We use 'executeQuery' to run it as we are returning a set of data from the database.

var studentsTableData = stmt.executeQuery("SELECT * FROM students");

Next we collect some spreadsheet data and select our starting cell where the first row of data will be inserted from - A2 in this instance. We then create a 'while' loop that goes through row-by-row for each of the 4 columns and inserts data from the students Table.

We make use of a method called 'offset' which is detailed more in this blog post which allows us to iterate from the first column across to the last, then move down each row. Then 'getString' retrieves a string value from the Table's specified row and column which we can then enter into the cell.

var newCell = startCell.offset(row, i);
var value = studentsTableData.getString(i+1);
newCell.setValue(value);

Add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to insert an ad-hoc entry (row of data) into an existing Table. This post follows on from this one for creating an SQL Table in Apps Script.

As before we begin by creating a statement for sending SQL code to the database. Then we can 'execute' the given SQL statement to 'insert into' the existing students Table the values provided.

var student = stmt.execute('INSERT INTO students'
                            + " VALUES ('456789', 'Donald', 'Duck', '9');"
                            );

4 values have been provided to match the 4 columns contained within the students Table:

1. StudentNo - 456789
2. FirstName - Donald
3. LastName - Duck
4. Shoesize - 9

Create an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to create a Table in an existing database.

db.gs
To begin we need to establish a remote connection to the database via the 'JDBC Service' to which we pass a number of credentials.

var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);

createStudentsTable.gs
With the connection made we next need to create a statement for sending SQL code to the database.

var stmt = db.createStatement();

Then we can 'execute' the given SQL statement which creates a Table called students containing 4 columns and their datatypes.

var studentsTable = stmt.execute('CREATE TABLE students'
                                   + ' (StudentNo int, FirstName varchar(255), LastName varchar(255), Shoesize varchar(255));'
                                  );

How to use 'offset' in a Google Sheet

The following Google Apps Script is a simple example of using 'offset' to return a new range from one previously given. So if we take cell A1 for instance and offset by 1 row & 1 column we would end up with cell B2. If we take cell A1 and offset by 2 rows & 3 columns we would end up with D3.

Screenshot of Google Sheet with values from offset function

Count word frequency within text

The following Google Apps Script is designed to take a chunk of text (pasted into the Google Sheet) and then count how many times each word appears within that text, displaying the results in a hierarchical list in another sheet.

The script also makes use of Stopwords which are designed to be ignored from the main body of text and not counted, so like 'they', 'are', 'a' and 'the' are all commonly used English words which would unnecessarily be at the top of the results list each time. The list of Stopwords can be adjusted as required if you want to add/remove some.

Screenshot of example word count results

Array - sort 2-D items each with a value

The following Google Apps Script builds on this previous blog post by introducing sorting for a 2-D array that contains items with a numerical value. As before we want to sort the array in ascending order based on the numerical value, but also keep the text associated with it (which in this example is part of a larger script that counts the frequency of words in a piece of text).

var myArray = [["means", 5.0], ["you’ve", 3.0], ["help", 19.0], ["they", 17.0]];

All we ultimately need to do in the sortNumbers function is define which part of the array the 'sort' needs to look at. As an array starts at zero that would give us the text value which is not what we want, so instead we specify that a and b should use position one for the numerical value. In this example we have logged the value to verify the correct element of the array is being called.

function sortNumbers(a, b) {
  Logger.log(a[1]);
  Logger.log(b[1]);
  return a[1] - b[1];
}

So in this example we end up with the result.

[[you’ve, 3.0], [means, 5.0], [they, 17.0], [help, 19.0]]

Array - sort simple numerical values

The following Google Apps Script is designed to take an array of numerical items and sort them in ascending order (from lowest to highest).

We begin with an array of values and call a function named sortNumbers.

var myArray = [40, 100, 1, 5, 25, 10];
myArray.sort(sortNumbers);

The sortNumbers function takes 2 values as input parameters and sorts them according to the returned (negative, zero, positive) value. If the result is negative a is sorted before b. If the result is positive b is sorted before a. If the result is 0 no changes are done with the sort order of the two values.

function sortNumbers(a, b) {
  return a - b;
}

So in this example we end up with the result.

[1.0, 5.0, 10.0, 25.0, 40.0, 100.0]

Here is a link to a version with a 2-D array of items each with a value.

Google Doc - add hyperlink text

The following Google Apps Script is designed to add some hyperlink text to an existing Google Doc - the BBC news website in this example.

Screenshot of Google Doc text with hyperlink

It makes use of 'setLinkUrl' when appending a paragraph of text.

docBody.appendParagraph('BBC news link').setLinkUrl('https://www.bbc.co.uk/news');

While loop with a Sleep and Toast

The following Google Apps Script is a simple 'while' loop that pops up a 'toast' message every 5 seconds by making use of 'Utilities.sleep' to pause the script.

This was created as part of some testing for another script designed to bulk move files between Google Drive folders. Without the toast notification it was difficult to determine the progress being made, so adding it in allowed for the name of the file last copied to be displayed to the user. This script removes all of the extra features and shows the basic while loop and toast.

Screenshot of toast notification in Google Sheet

Web app - capture information and add an apps script dropdown

Continuing with the web app project from here there are now some further additions that have been introduced:

• More fields for capturing information - dropdown list, textarea;
• A dropdown list populated via Google Apps Script which links to items in a spreadsheet.

Screenshot of webpage with input fields

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

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

Get weekday name from a date - alternative

The following Google Apps Script code is an alternative to this blog post where we instead format our date in a way as to only extract the week-day name, saving us the effort of getting the week-day as a number and then looking this up in a 'switch'.

By making use of 'formatDate' and the pattern 'EEEE' within a few lines of code we have a workable week-day name that could be used in an if statement as discussed in the previous post.

Get weekday name from a date 2.gs

Get weekday name from a date

The following Google Apps Script code is designed to take a date, extract the day of the week from it (as a number initially, so Monday=1, Tuesday=2 ...) then use a 'switch' to determine the corresponding name of the day (eg Wednesday, Thursday ...).

The reason I was looking at this sort of code was for a project where a Google Form had to be completed in which the user selected a date in the future that should be a Wednesday. Without validation directly on the Form I tinkered around with the notion that Apps Script could first check the date before proceeding with handling the rest of the data.

The below example is just the code itself rather than also including the extra bits of a Google Form and extracting data directly from it. You could slot this function into you existing project and swap out the first part that simply gets the current date as a variable. When we use 'getDay' we can only return the day of the week as a number hence the need to then use the switch which looks through each case to match with the week-day number.

The result is a variable called day that now contains the name of the week-day which we could use an if statement to evaluate and proceed with the rest of our script.

Search Google Calendar event for Guests

The following Google Apps Script is designed to search a specified Google Calendar event and extract details about the Guests attached to it (eg their email address and status). The script is standalone so it only logs the output but you could copy it into a Google Sheet and write the data into the spreadsheet.

The 'CalendarApp' is used to perform the task and we start by getting hold of the relevant event, which for the purposes of demonstrating is hard-coded into the Apps Script but is likely to be taken from a spreadsheet in reality.

var event = CalendarApp.getEventById(eventId);

Next we want to get a list of the Guests which will be returned as an array.

var guestList = event.getGuestList();

Finally we need to loop through each Guest and extract their email address and status.

for (var i=0; i<guestList.length; i++) {
    var guest = guestList[i];
    var guestEmail = guest.getEmail();
    var guestStatus = guest.getGuestStatus();
}

Search Google Calendar for event details

The following Google Apps Script is designed to search through a given date on Google Calendar, look through the events and then extract their Title & Id. The script is standalone so it only logs the output but you could copy it into a Google Sheet and write the data into the spreadsheet.

The 'CalendarApp' is used to perform the task and we start by getting hold of the relevant calendar.

var calendar = CalendarApp.getCalendarById(calendarId);

Next we get an array of the events for our given date.

var allEvents = calendar.getEventsForDay(date);

As we now have an array we need to loop through each returned event and extract the Title & Id.

for (var i=0; i<allEvents.length; i++) {
    var event = allEvents[i];
    var eventTitle = event.getTitle();
    var eventId = event.getId();
}

Monitor a cell in a Google Sheet

The following Google Apps Script is designed to monitor a cell within a Google Sheet to see if any text has been entered into it (such as a name for a signature) upon which it will then send an email to a specified account. The email contains a link to the Google Sheet and the value that has been entered into the cell.

It was developed as part of searching for a solution to allow a large number of student spreadsheets to be created each of which then required sign-off at some stage. Rather than administrators constantly checking a large number of files regularly however this function would allow passive monitoring so they only needed to take action once an email has been received.

Screenshot of example cell to monitor

Add Note to cell on Google Sheet edit

The following Google Apps Script is designed to automatically insert a Note into the active cell in a Google Sheet when a user edits the content of it. The purpose is to explore an alternative way to interact with editing a spreadsheet than described in this blog post (which uses OnEdit(e)). In this instance we will be making use of the 'edit' installable trigger which needs creating in addition to the code we write.

The Note that will be added to the cell will include the current date/time and the value of the cell before it is edited. We will then collect this information each time the cell is edited - in effect creating a history of edits to a cell.

Screenshot of cell with Note showing edit history

Change Google Sheet cell colour on edit

The following Google Apps Script is designed to automatically change the active cell background colour in a Google Sheet when a user edits the content of it. This was created as I was learning about the various ways to deal with somebody 'signing' a spreadsheet by entering their name into a cell. By changing the colour it would be easier for somebody to see the change when glancing at multiple spreadsheets.

Screenshot of cell colour change

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

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

Create Gmail label if not exist

The following Google Apps Script code is designed to search through the labels in your Gmail inbox looking for a specific one, then create it if not found. I developed the function as part of the Bulk email saving tool for Gmail that I will be detailing later in the blog, where completed emails that have been saved to Google drive would then be moved into this label as a way of tracking progress.

The tool takes an input parameter of newLabel which would simply be the name of the label to search for and then create. We start by getting the existing Gmail labels ('getUserLabels') from which we can loop through each one and get their name, for pushing into an array:

for (var i=0; i<labelsLength; i++) {
    var labelName = labels[i].getName();
    labelsArray.push(labelName);
}

Email scheduler for Gmail - delete row

Overview blog post.

The following Google Apps Script is designed to delete the entire row of data in case the user decides not to archive messages or for if they remove a draft from Gmail and now no longer need it displaying in the spreadsheet.

It is a straightforward function that behaves very similar to the archive one, the only main difference being that is utilises 'deleteRow' instead.

Email scheduler for Gmail - archive delivered

Overview blog post.

The following Google Apps Script code archives Delivered message to another sheet so that there is a permanent record of when emails were sent (as they do not appear in the Sent label of Gmail when using the scheduler) and so the main sheet does not become clogged.

Archived email

For the most part the script follows my previous blog post on archiving sheet data. A noticeable difference appears in the form of needing to count which rows have been deleted as the data all shifts (I have detailed the solution in this blog post).

Email scheduler for Gmail - delete trigger

Overview blog post.

The following Google Apps Script code is designed to delete a specific project trigger from a given trigger ID. In the email scheduler it is called upon by a few different functions to ensure redundant triggers do not remain - as other than their scheduled dates they will all appear identical due to them running the same sendEmail function.

We start by getting all of the existing project triggers:

var allTriggers = ScriptApp.getProjectTriggers();

From this array we can then loop through each in turn and 'getUniqueID' to compare it with the value we have stored in the spreadsheet - to look for a match:

if (allTriggers[i].getUniqueId() == triggerID)

Once we have a match we can 'deleteTrigger' and break the loop so it does not continue to run:

ScriptApp.deleteTrigger(allTriggers[i]);
break;

Email scheduler for Gmail - cancel schedule

Overview blog post.

The following Google Apps Script code cancels the selected scheduled message and removes its associated trigger. This was created as there may be times when a user changes their mind on sending an email or they make need to make a correction with the date/time - hence this provides a safety net for stopping the schedule.

Once we have all of the data from the spreadsheet we create a loop that will check for 3 components before trying to run - as if one of these is missing then it is not possible to fully cancel a schedule. We use the Message Actions column to select Cancel Schedule which along with a status of Scheduled and a valid trigger ID we satisfy the requirements of the if statement.

From this point we run the deleteTrigger function and feed in the trigger ID as a parameter (this function will be detailed in the next blog post). We then update the Status column to Cancelled to inform the user and 'clearContent' of Schedule ID and the Message Actions columns to tidy up.

Email scheduler for Gmail - send email

Overview blog post.

The following Google Apps Script code sends the email when the trigger runs and writes the date/time back to the spreadsheet as a record.

Confirmation of message delivery

After gathering the spreadsheet data the script gets the current date/time and begins to loop through each row looking for messages to be sent. Only messages with a Scheduled status and valid date/time will be dealt with. An important step of the script is to check the scheduled date/time is not in the future - as these messages will have their own trigger to run at a later date - we only want messages up until now that have not been sent.

Email scheduler for Gmail - create schedule

Overview blog post.

The following Google Apps Script code creates the trigger schedule for the message from the given date/time.

Add the date/time using the picker and dropdown boxes

Data Validation
Data validation has been used in the spreadsheet to ensure the date/time is entered in the correct format so that the script can handle the necessary values. A date-picker has been used to achieve the format yyyy-mm-dd by specifying the cell contents must be a valid date. The dropdown boxes for Hours/Minutes is a List from a range where there is a hidden sheet that has the hours from 00 to 23 and the minutes as intervals of 5 (so 00, 05, 10 etc).

Email scheduler for Gmail - check duplicate draft

Overview blog post.

The following Google Apps Script code takes the message ID from the getDraftMessages function and checks to see if a corresponding value already exists within the spreadsheet. This is likely to occur as a user runs to import their draft messages multiple times over the course of using the tool before they have all been sent. So this way we can avoid them seeing the same entry multiple times.

The script simply looks down the Message ID column (via 'getRange' and 'getValues'), then loops through each row looking for an ID match. It uses a duplicateFlag variable set to false by default, to signal a duplication (becomes true) and then stops the script from continuing to run via a return command.

This in turn then allows the parent function to behave appropriately and not import the duplicate draft message. If everything is fine and no duplicate is found then the flag remains false.

Email scheduler for Gmail - get draft messages

Overview blog post.

The following Google Apps Script code is designed to get any current draft messages in Gmail and copy their ID/Subject into the spreadsheet (assuming there is an email address in the To part).

By getting the ID of the message we have a way of referring back to it when we later create the scheduler trigger - so it knows exactly which message to send.

Using 'getDraftMessages' allows us to get various elements such as 'getTo' which we use in an if statement to check it is not blank - as some people do use drafts for just storing information so these messages will not be included.

Email scheduler for Gmail - overview

Over the next few blog posts I look to explore my development of an email scheduler for Gmail that is designed to let you schedule when you want an email composed in your drafts to be sent (by specifying the date/time).

Background
Over time there had been a number of requests of people wanting an email scheduler with nothing other than the likes of Boomerang or Gmelius suggested as alternatives. The issue with these being that you can only perform a limited amount of scheduling on the free plans before then needing to upgrade, which becomes costly for multiple people at an institution. The other issue is to do with GDPR and a third-party add-on accessing the entirety of a persons email inbox - this reason also applies to the free extensions that you can get for Chrome/Firefox.

So because of the above and the desire to continuing my learning I decided I would have a go at creating an email scheduler via a Google sheet. I will be sharing all of the code as usual and providing you with a downloadable version - which then becomes yours to run without accessing anything other than your Gmail.

Solution - Overview
The process:

Fully compose your Gmail message (To, Subject, Body, Attachments) > open the Google sheet and click to import your Gmail drafts > set a date/time and click to schedule the message.

Loop delete specific rows with counter

The following Google Apps Script has been created to help with a recent difficulty I was having with deleting rows containing a specific value, where the loop was losing the row position due to the spreadsheet data all shifting up a row each time one was deleted. Some searching online suggested looping through a spreadsheet in reverse (from bottom up) to avoid the shift, but that seems more like a workaround than a direct solution and could have implications elsewhere.

Example table with specific rows to delete

In the image above rows 3 and 5 have been marked for deletion, but as a script is typically designed to loop through each row in turn once it reaches row 3 and deletes it the rest of the data below all moves up one row - which means Mickey Mouse is no longer row 5 but instead row 4 - see below.

Table with row 3 now deleted and data shifted up

Save email to Drive add-on - finished card

Overview blog post.

The following Google App Script code creates a success card at the end of the makeTheCopy function, with a back button to start the whole process over.

The construction of the card follows the same process as detailed at the beginning of the add-on (blog post here). This time we 'setFunctionName' for the button to the getContextualAddOn.

Save email to Drive add-on - make the copy

Overview blog post.

The following Google Apps Script code makes the physical copy of the email and stores it as a PDF in the relevant Google Drive student folder.

It starts by getting the ID of the Google doc template file which will be duplicated and its tags replaced with fields from the email message. As we passed in a number a parameters from the showCard function we can now assign these to variables (saving us time by not having to query the whole spreadsheet data again):

var userId = e.parameters['userId'];
Logger.log('userId is: ' + userId);

Next we run the getCurrentMessage function which does so via an event object and passes it back through to the makeTheCopy function for interrogating. So now the subject, date, from, etc can be assigned to variables as well as 'getPlainBody' for the email contents.

Save email to Drive add-on - show card

Overview blog post.

The following Google Apps Script code is designed to return the relevant card based on the error code value from the getDetails function. If 0 then all is fine and a card displaying student data is shown as confirmation before providing a way to run the makeTheCopy function for saving the email to Drive. If 1, 2, 3 or 5 is returned then a card specifying the error is returned to the user.

Success card displaying found student details

showCard0 function
We pass through the student details as input parameters and then proceed to build an add-on card in the same way we did when first opening the add-on (blog post). 3 card sections are created in total: displaying the found name and userId, a button for saving the email and another for a back button to re-enter a username.

Save email to Drive add-on - get student details

Overview blog post.

The following Google Apps Script code queries the relevant Google sheet and proceeds to lookup the given username to find the corresponding user details (eg name, folder ID). It also maintains an error code value which is used to inform the user of any missing data in the spreadsheet as well as helping to prevent the script from encountering an error.

We start by getting all of the student data from the spreadsheet via the global ssId variable:

var ss = SpreadsheetApp.openById(ssId);
var dataSheet = ss.getSheetByName('data');
var list = dataSheet.getDataRange().getValues();

Next the variables used in the loop are reset so they do not hold any leftover values each time the add-on script runs. The spreadsheet data is then converted 'toString' so that 'indexOf' can be used to search the (array) of data for the userId value - so it checks this exists before proceeding to gather student-specific data.

If the userId exists we then loop through the array of data and pick up: forename, surname, folder ID. An if/else statement is used for each so that if a corresponding value is not found then we increment the detailsError variable to determine the error code value so we can display a specific message to the user informing them of which piece of data is missing:

Save email to Drive add-on - get user info

Overview blog post.

The following Google Apps Script code extracts the entered username from the event data and saves it as a variable:

var userId = e.formInput['userId'];

It checks to make sure the value is not blank before proceeding (ie the user has not just clicked the submit button) otherwise nothing else will occur. If a value has been entered we then call the getDetails function detailed in the next blog post, to get the corresponding name and folder ID. As this returns an array of student data we can then split the values appropriately - meaning we only need to interrogate the spreadsheet data once throughout the add-on:

var forename = details[0][0];
var surname = details[1][0];
var folderID = details[2][0];
var errorCode = details[3][0];

Next we need to determine the status of the error code in order to run the appropriate showCard(0) function, so we use an if statement. If the value is 0 then we run the showCard0 function and pass the above parameters through:

if (errorCode == 0) {
      Logger.log('errorCode is 0');
      //run showCard '0' function
      return showCard0(userId, forename, surname, folderID);
}

Save email to Drive add-on - get username

Overview blog post.

The following Google Apps Script code is the first to be run when the Gmail add-on is launched and is designed to prompt for a username of a student. The script is called via the 'onTriggerFunction' in the manifest file.

Save message to Drive add-on

We begin by building the card that requests this information and do so via the 'CardService.newCardBuilder' class, to which we need to create a 'newCardSection' to display our information. Next we create a 'textInput' widget to ask for the username:

var inputWidget = CardService.newTextInput()
    .setFieldName('userId')
    .setTitle('Enter username here')

Then add this to the section we created:

section.addWidget(inputWidget);

Save email to Drive add-on - overview

Over the next few blog posts I look to explore my development of a Gmail add-on that is designed to save the current open email in a specific Google Drive folder, by entering a value which corresponds to said folder.

Background
I was approached by a department a couple of months ago that were frustrated with saving student specific emails in a shared Team Drive folder. The process is quite cumbersome:

Open the email > click the Print all icon > change the printer to Save to Google Drive > click Save > now a PDF version of the email is in My Drive so you need to go in there and Move to the Team Drive folder ...

This was the current process for 1 cohort of students between about 4 members of staff, but they wanted to scale it up so that the next academic year they would have student folders for the new intake also, then subsequent years to come after that. Clearly this was going to become extremely time-consuming and needed a re-think.

So back in October I began to explore Gmail add-ons with a colleague, which use the concept of a card that contains a section composed of widgets to display information and interact via buttons. My initial exploration into this area (having only been learning Google Apps Script for about 1 month prior) was overwhelming and difficult to process. It was only once I got stuck into the meaty Student Feedback system did I really have the capacity to pick this up again and carry it into a completed project.

Batch get existing folder IDs & names - version 2.0

The following Google Apps Script is a revisit of this blog post for batch getting folder IDs and names from existing Google drive folders. I have made 2 changes/improvements:

1. The output array created at the end of the getFolders function is now 1-D. In reality it did not need to be a 2-D array (as in the original post) because it only contained one set of data. This also means we no longer need to specify output[0] when appending the data in the pasteData function.
2. Using 'appendRow' rather than 'getLastRow' - this was an extra unnecessary step as it meant we also needed to 'getRange' in order to use the last row. Now however data will always be written to the next empty row.

Here is an updated version of the code:

Get script time zone for a date

The following Google Apps Script is a quick example of a recent improvement I have learned when handling dates (example blog post of the old method).

Rather than defining the timeZone as GMT (or GMT+1 for daylight savings) in the 'formatDate' property, I will be starting to use 'getScriptTimeZone' when it is not bound to a Google sheet. This eliminates the issue of the script becoming inaccurate by 1 hour when the clocks go back/forward, as it will pick up the time zone from when/where the script is being run.

Get script time zone for a date.gs

Log actions performed by a user running a script

Overview
The following Google Apps Script code has been developed to provide a standalone function that you could include in your scripting projects to help determine which user ran it and what actions it has performed. It is one I hope to start embedding where suitable and is designed to output the information into a Google sheet - Date/Time, User, Action.

There have been a couple of instances where it would have been useful to know who and when a script was run, as part of diagnosing a fault. The function takes an input parameter of action which could simply be a variable with descriptive text at certain checkpoints in your script (particularly the start/end).

Example log output from script to spreadsheet