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.