Skip to content

Instantly share code, notes, and snippets.

@dwerbam
Last active June 18, 2017 11:16
Show Gist options
  • Save dwerbam/488daf798db9ed832503 to your computer and use it in GitHub Desktop.
Save dwerbam/488daf798db9ed832503 to your computer and use it in GitHub Desktop.
Gmail Scheduler
function installScript() {
// Makes sure that only 1 scheduler is installed
uninstallScript();
// Trigger every 1 hour.
ScriptApp.newTrigger('gmailScheduler')
.timeBased()
.everyHours(1)
.create();
}
function uninstallScript() {
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
Logger.log(allTriggers[i]);
// If the current trigger is the correct one, delete it.
if (allTriggers[i].getHandlerFunction() === "gmailScheduler") {
ScriptApp.deleteTrigger(allTriggers[i]);
}
}
}
// this function runs every 1 hour
function gmailScheduler() {
/* Clear the current sheet */
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(2, 1, sheet.getLastRow() + 1, 5).clearContent();
/* Delete all existing triggers */
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "sendMails") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
/* Import Gmail Draft Messages into the Spreadsheet */
var drafts = GmailApp.getDraftMessages();
if (drafts.length > 0) {
var rows = [];
for (var i = 0; i < drafts.length; i++) {
if (drafts[i].getTo() !== "") {
var scheduled=null;
var subject = drafts[i].getSubject();
var regExp = new RegExp("^@(.*)@[ ]*(.*)", "gi");
var parsed = regExp.exec(subject);
if(parsed!=null) {
scheduled = parsed[1];
subject = parsed[2]
}
/*
//tries to analyse commons expressions: tomorrow, etc.
if(scheduled==="tomorrow") {
//tomorrow is at 9:00 of the next day
scheduled=new Date(new Date(tomorrow.setHours(9)).setMinutes(0)).toISOString().substring(0, 16).replace('T',' ');
//xxxxx
}*/
//use the spreadsheet to parse the format of the date
rows.push([drafts[i].getId(), drafts[i].getTo(), subject, scheduled,""]);
//scheduledLabel.addToThread(drafts[i].getThread());
}
}
sheet.getRange(2, 1, rows.length, 5).setValues(rows);
}
setSchedule();
}
/* Create time-driven triggers based on Gmail send schedule */
function setSchedule() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var time = new Date().getTime();
var code = [];
/* creates label 'Scheduled' in gmail */
var scheduledLabel = GmailApp.getUserLabelByName('Scheduled');
if( scheduledLabel == null ) {
scheduledLabel = GmailApp.createLabel('Scheduled');
}
/* */
for (var row in data) {
if (row == 0) {
continue;
}
var schedule = data[row][3];
if (schedule == undefined || schedule === "" || !(schedule instanceof Date) ) {
code.push("Not Scheduled");
scheduledLabel.removeFromThread(GmailApp.getMessageById(data[row][0]).getThread())
continue;
}
if (schedule.getTime() > time) {
ScriptApp.newTrigger("sendMails")
.timeBased()
.at(schedule)
.inTimezone(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())
.create();
code.push("Scheduled");
scheduledLabel.addToThread(GmailApp.getMessageById(data[row][0]).getThread())
} else {
code.push("Date is in the past");
scheduledLabel.removeFromThread(GmailApp.getMessageById(data[row][0]).getThread())
}
}
for (var i = 0; i < code.length; i++) {
sheet.getRange("E" + (i + 2)).setValue(code[i]);
}
}
function sendMails() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var time = new Date().getTime();
for (var row = 1; row < data.length; row++) {
if (data[row][4] == "Scheduled") {
var schedule = data[row][3];
if ((schedule != "") && (schedule.getTime() <= time)) {
var message = GmailApp.getMessageById(data[row][0]);
var subject = message.getSubject();
var regExp = new RegExp("^@(.*)@[ ]*(.*)", "gi");
var parsed = regExp.exec(subject);
if(parsed!=null) {
subject = parsed[2]
}
// there is no API for sending directly the email
//so, I send a copy of it
var body = message.getBody();
var options = {
cc: message.getCc(),
bcc: message.getBcc(),
htmlBody: body,
replyTo: message.getReplyTo(),
attachments: message.getAttachments()
}
GmailApp.sendEmail(message.getTo(), subject, body, options);
message.moveToTrash();
sheet.getRange("E" + (row + 1)).setValue("Delivered");
}
}
}
}
@wiseacre1
Copy link

Hello, i tried a lot, but i always get Not Scheduled. Once i got Date is in the past. What should i do?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment