Hello I currently have a google sheet with dates of certs that expire on different days. I would like to set up an email alert for 30 days prior to the entered date in each cell.
is this possible? I tried to figure it out with the conditional notifications but seems like its not possible through that and will have to be done with coding it through the google scripts.
Please advise.
photo attached of column of dates:
Set 30 days email notification
You can try this.
This code will effectively go through the dates written in your Google sheets then checks if the date is 30 days from the current date then if it is, the script will send an email.
function myFunction() {
// Gets your current active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Declaration of the range to be used in the spreadsheet and its values. Kindly change `A2:A` with the range where your dates are in the screenshot
var rg = ss.getRange("A2:A").getValues();
// 30 days from today
var td = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate() + 30, 0, 0, 0);
// Loops through the rows and sends emails if today is 30 days from the dates in Column A
rg.forEach(r => {
var cd = new Date(r[0]);
cd.getTime() == td.getTime() ? MailApp.sendEmail("<email-address-here>", "Due date reminder", `This is a reminder that ${cd.toLocaleDateString()} is 30 days from today.`) : null;
});
}
Set-up a container-bound script
To set-up a Google Apps Script that is connected to the Google sheet where the dates are present, follow these steps:
Open the Google sheet file>From the navigation at the top, click “Extensions”>Choose Apps Script>An Apps Script editor browser window will open> This is where you will paste the code above and where you will set-up a time-driven event trigger.
Set-up time-driven trigger on Apps Script
To set-up an automatic email notification, you must first set-up a time-driven event that is set to fire everyday but the code will still check if the current date is 30 days from the date present on your sheet before sending an email notification. You can follow these steps on setting-up time-driven event trigger on Google Apps Script.
1.) Click on triggers (Clock icon on the left side of your screen)
2.) Click “Add Triggers”
3.) On the option that says “Choose which function to run
” choose the name of the function, in my given code’s case the function name is “myFunction”
4.) On the “Choose which deployment should run
” choose “Head”
5.) “Select event source” choose “Time-driven”
6.) “Select type of time based trigger
” choose “Day timer”
7.) For the option that says “Select time of day
” select your preferred time
8.) Click save button
Images for further reference:
Sample Output
References
Installable triggers