Last active
September 6, 2019 23:25
-
-
Save mbierman/5e9e2366c7a6d8a7f59b238a0a717877 to your computer and use it in GitHub Desktop.
email based on past date
This file contains hidden or 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
| 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); | |
| } | |
| } |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This looks at column J and sends an email if any date is older than today.