Google Apps Scripts Email Scheduler

Code.gs

function initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  
  triggers.forEach(function(trigger) {
    if (trigger.getHandlerFunction() == "sendMails" || trigger.getHandlerFunction() == "updateSheet") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var drafts = GmailApp.getDraftMessages();
  
  sheet.getRange(2, 1, sheet.getLastRow() + 1, 9).clearContent();
  
  drafts.forEach(function(draft) {
    if (!idExists(draft.getId())) {
      sheet.appendRow([
        "❌",
        "❌",
        draft.getId(),
        Utilities.formatDate(draft.getDate(), "EST", "MM/dd/yyyy, H:mm:ss z"),
        draft.getTo(),
        draft.getFrom(),
        draft.getSubject(),
        draft.getBody().substr(0, 40) + "...",
        Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy, H:mm:ss z")
      ]);
    }
  });
  
  updateSheet();
}

function updateSheet() {
  var triggers = ScriptApp.getProjectTriggers();
  
  triggers.forEach(function(trigger) {
    if (trigger.getHandlerFunction() == "sendMails" || trigger.getHandlerFunction() == "updateSheet") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var drafts = GmailApp.getDraftMessages();
  
  drafts.forEach(function(draft) {
    if (!idExists(draft.getId())) {
      sheet.appendRow([
        "❌",
        "❌",
        draft.getId(),
        Utilities.formatDate(draft.getDate(), "EST", "MM/dd/yyyy, H:mm:ss z"),
        draft.getTo(),
        draft.getFrom(),
        draft.getSubject(),
        draft.getBody().substr(0, 40) + "...",
        Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy, H:mm:ss z")
      ]);
    }
  });
  
  var range = sheet.getRange(2, 2, sheet.getLastRow() + 1, 3).getValues();
  
  range.forEach(function(id, elem) {
    var found = false;
    for (var i = 0; i < drafts.length; i++) {
      if (id[1] == drafts[i].getId()) {
        found = true;
      }
    }
    
    if (id[0] == "✅") {
      found = true;
    }
    
    if (!found) {
      sheet.deleteRow(elem + 2);
    }
  });
  
  sendEmails();
  
  ScriptApp.newTrigger("updateSheet")
    .timeBased()
    .everyMinutes(1)
    .create();
  
  ScriptApp.newTrigger("updateSheet")
    .timeBased()
    .after(10000)
    .create();
}

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(2, 1, sheet.getLastRow() + 1, 9).getValues();
  
  range.forEach(function(row, elem) {
    if (row[0] == "✅" && row[1] == "❌" && (new Date(row[8])).getTime() <= (new Date()).getTime()) {
      var message = GmailApp.getMessageById(row[2]);
      var body = message.getBody();
      var options = {
        cc: message.getCc(),
        bcc: message.getBcc(),
        htmlBody: body,
        replyTo: message.getReplyTo(),
        attachments: message.getAttachments()
      };

      GmailApp.sendEmail(message.getTo(), message.getSubject(), body, options);
      message.moveToTrash();
      sheet.getRange("B" + (elem + 2)).setValue("✅");
    }
  });
}

function idExists(id) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(2, 3, sheet.getLastRow() + 1).getValues();
  
  for (var i = 0; i < range.length; i++) {
    if (range[i] == id) {
      return true
    }
  }
  
  return false;
}

Email Scheduler.xlsx