Last active
March 28, 2022 12:12
-
-
Save shivanshthapliyal/fca424df85868e3eba2a995af27da4e3 to your computer and use it in GitHub Desktop.
AppScript to send reminders from a Google Sheet based on a timeframe.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* @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