Skip to content

Instantly share code, notes, and snippets.

@mbierman
Last active September 6, 2019 23:25
Show Gist options
  • Select an option

  • Save mbierman/5e9e2366c7a6d8a7f59b238a0a717877 to your computer and use it in GitHub Desktop.

Select an option

Save mbierman/5e9e2366c7a6d8a7f59b238a0a717877 to your computer and use it in GitHub Desktop.
email based on past date
function overdueCheck() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Data");
// var values = sheet.getRange("J2:J").getValues();
var values = sheet.getRangeByName("FollowUp").getValues();
var today = new Date();
var todays = today.getTime();
var TO = "foo@domain.com";
var URL = "https://docs.google.com/spreadsheets/d/1hMZnkvsVE1B_9X37V-WH6o7dYmX_BoHxwP2YCY8DOWE/edit#gid=0";
var results = [];
var trash = [];
for(var i=0;i<values.length;i++){
Logger.log("\ni. " + i + " " + values[i]);
if(values[i]==""){
Logger.log("BLANK\n value is: " + values[i] + "\n" );
trash.push( " ○ " + values[i] + ""+(i+2)); // +2 because the loop start at zero and first line is the second one (F2)
} else {
var testDate = new Date(values[i]).getTime();
Logger.log("Else\n today is: " + today + " ("+todays+")\n value is: " + values[i] + " ("+testDate+")");
if(todays>testDate){
var line = (i+1);
Logger.log(" Overdue" + " i is: " + i + " line: " + line );
results.push( " ○ " + line); // +2 because the loop start at zero and first line is the second one (F2)
} else {
Logger.log("Not late\n");
}
}
}
Logger.log("\ntrash are:\n" + trash.join("\n") + "\n\nResults are:\n" + results.join("\n"));
if(results.length > 0){
Logger.log("Results are:\n" + results.join("\n"));
if(results.length > 1){
Logger.log("results: "+results.length);
MailApp.sendEmail(TO, 'We Care issues require attention', 'The following WeCare issues need to be checked because it past the deadline. See these lines in the wecare spreadsheet.\n\n' + results.join("\n") + "\n\n" + URL);
} else {
Logger.log("results: "+results.length);
MailApp.sendEmail(TO, 'We Care issue requires attention', 'The following WeCare issue needs to be checked because it past the deadline. See this line in the wecare spreadsheet.\n\n' + results.join("\n") + "\n\n" + URL);
}
} else {
Logger.log("No results. " + results.length);
// MailApp.sendEmail(TO, '*** No We Care issues require attention', 'Have a good day.\n\n' + results.join("\n") + "\n\n" + URL);
}
}
@mbierman

mbierman commented Sep 1, 2019

Copy link
Copy Markdown
Author

This looks at column J and sends an email if any date is older than today.

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