3 ms·
I have a reminders spreadsheet that is set up similarly to a crontab file. Each row has: - subject - recipient (usually my own email address) - day of month
by rahimnathwani 4y ago
I have a reminders spreadsheet that is set up similarly to a crontab file. Each row has:
- subject
- recipient (usually my own email address)
- day of month
- day of week
- every x days
- starting on date
- trigger today? (calculated)
A daily apps script refreshes the spreadsheet formulae (so that the TRUE/FALSE values in the last column are correct for today's date) and then sends the emails.
It took some initial setup, but it's super-easy to add new regular or one-off reminders, and obviously to remove old ones.
- ant6n 4y agoCan u share a dummy script? That would be nice.
- notRobot 4y agoSeconded, this sounds really handy!
- rahimnathwani 4y agoSure. Here's a view-only copy of the spreadsheet: https://docs.google.com/spreadsheets/d/1kJVja4xo6GG-SanHvqYjMGsfQ0V-InXJrwcsiluURAE/edit#gid=0 https://docs.google.com/spreadsheets/d/1kJVja4xo6GG-SanHvqYj... Note the formula in column H, which generates TRUE/FALSE. You can obviously replace this logic with whatever you want. I set a daily 8am trigger to run this script, which sends notifications for the rows that are marked TRUE: function sendReminderEmails() { var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange('B1').setValue(""); SpreadsheetApp.flush(); sheet.getRange('B1').setValue("=TODAY()"); SpreadsheetApp.flush(); var headerrows = 3; var numColumns = 8; var subjectColumn = 1; // one-based, i.e. column A is 1, B is 2 etc. var addressColumn = 2; var conditionColumn = 8; var startRow = 1 + headerrows; // First row of data to process var numRows = sheet.getLastRow()-headerrows; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, numColumns); var data = dataRange.getValues(); for (var i in data) { var row = data[i]; if (row[conditionColumn-1]) { var emailAddress = row[addressColumn-1]; var message = 'Reminder: ' + row[subjectColumn-1]; var subject = message; MailApp.sendEmail(emailAddress, subject, message); } } }
- notRobot 4y agoThanks!