Skip to content

Instantly share code, notes, and snippets.

@shivanshthapliyal
Last active March 28, 2022 12:12
Show Gist options
  • Save shivanshthapliyal/fca424df85868e3eba2a995af27da4e3 to your computer and use it in GitHub Desktop.
Save shivanshthapliyal/fca424df85868e3eba2a995af27da4e3 to your computer and use it in GitHub Desktop.
AppScript to send reminders from a Google Sheet based on a timeframe.
/* @Maintainer Shivansh Thapliyal
** AppScript to send reminders from a Google Sheet based on a timeframe.
**/
// The event handler triggered when opening the spreadsheet. Adds a new Reminders menu to menu bar.
function onOpen(e) {
// Add a custom menu to the spreadsheet.
SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
.createMenu('Sheet1')
.addItem('Send Mail','reminder')
.addToUi();
}
// This is our main function that is being called by the
function reminder() {
var timezone = "Asia/Kolkata"
var hour = 14 // 2 PM
if(!checkIfTriggerExists("reminder")){
console.log("Creating new trigger as trigger that calls reminder function does not exist.")
createTimeDrivenTriggers(timezone,hour)
}
else{
console.log("Skipping creating trigger as trigger that calls reminder function already exists.")
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Reminders")
var range = sheet.getDataRange();
var values = range.getDisplayValues();
var lastRow = range.getLastRow();
var sheerUrl = ss.getUrl();
var email = Session.getActiveUser().getEmail();
try {
sendMail(sheet, lastRow, values); // Function to send reminder Email
}
catch (e) {
// Logs an ERROR message.
console.error('sendMail() yielded an error: ' + e);
}
}
function sendMail(sheet, lastRow, values) {
for (var i = 1; i< lastRow; i++)
{
if (values[i][8] == 'Pending' && values[i][11] == 'True')
{
console.log(values[i][8], values[i][7], values[i][6], values[i][3], values[i][4])
MailApp.sendEmail(values[i][7], values[i][6], values[i][3], values[i][4]);
var date = Utilities.formatDate(new Date(), "GMT+5:30", "MM/dd/yyyy HH:MM:SS")
var loggingCell = sheet.getRange(i+1, 13);
console.log("Reminder sent to "+values[i][7]+" from " + values[i][6] +" on "+date)
loggingCell.setValue("Reminder sent to "+values[i][7]+" from " + values[i][6] +" on "+date);
}
}
}
function createTimeDrivenTriggers(timezone, hour) {
// Trigger every day at 2 pm IST
ScriptApp.newTrigger('reminder')
.timeBased().everyDays(1).atHour(hour).inTimezone(timezone)
// .forSpreadSheet(ss)
// .onWeekDay(ScriptApp.WeekDay.MONDAY)
// .atHour(9)
.create();
}
function checkIfTriggerExists(handlerFunction) {
var triggers = ScriptApp.getProjectTriggers();
var triggerExists = false;
triggers.forEach(function (trigger) {
if(trigger.getHandlerFunction() === handlerFunction)
triggerExists = true;
});
return triggerExists;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment