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