Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Feb 2014
    Thanked 0 Times in 0 Posts

    Help creating Script that sends a Notification Email when a certain date is passed


    I am trying to create a Script to go with a Google Spreadsheet that will cause a Notification Email to be sent to a predetermined set of email addresses once a specific date has been entered into the spreadsheet and been passed.
    (i.e. I enter 13/2/14 into a cell and at the end of day the Script is triggered and sends me a Notification email that task was completed on celldate.)
    I can't seem to get the Script to send me any emails. I think it has to do with the portion (timeLeft < DateTrigger), but I'm quite a bit amateur at this, so I don't know.
    (And, yes, I know I have to put my email in where I've got var managementEmails = ["email@myemailaddress.com"] That isn't the problem. )
    Below is the code I'm using.

    function notifyManagement() {
    // Update & Edit values below ###############
    var testing = false; // false = Live emails ... true = Browser popup...
    // The Column for the task name
    var taskColumn = "A";
    // The Column for Time Remaining
    var dateColumn = "C";
    // Number of Hours (less than X) that you want to be notified
    var DateTrigger = "12/31/2020";
    // Column for notifications
    var managementNotifiedColumn = "O";
    // Emails addresses in array format ['','','']
    var managementEmails = ["email@myemailaddress.com"];
    // Stop Editing values here ##################
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
    var row = 0;
    // loop through the timeleft column
    for(row=1; row < lastRow; row++){
    var cell = dateColumn + row;
    var managementCell = managementNotifiedColumn + row;
    var taskCell = taskColumn + row;
    // let us get all the values ready for checking
    var timeLeft = sheet.getRange(cell).getValue();
    var notified = sheet.getRange(managementCell).getValue();
    var taskName = sheet.getRange(taskCell).getValue();
    //check to see if we should send an email
    if ((timeLeft < DateTrigger) && (!notified) && (isInt(timeLeft))) { 
    // Set the notified cell
    // email management
    for (var i = 0; i < managementEmails.length; i++) {
    if (testing) { Browser.msgBox("Email to " + managementEmails[i] + "\n" + taskName + " has been completed!"); }
    else {
    MailApp.sendEmail(managementEmails[i], taskName + " has been completed!", taskName + " completed " + timeLeft);
    } // end testing check
    } // end of send emails loop
    } // end of checking if we should notify
    } // end of for loop of all rows
    } // end of mailMe function
    function isInt(n) {
    return typeof n === 'number' && n % 1 == 0;
    Thank you in advance for any help that can be provided.

  2. #2
    Senior Coder Arbitrator's Avatar
    Join Date
    Mar 2006
    Splendora, Texas, United States of America
    Thanked 301 Times in 295 Posts
    Quote Originally Posted by kikd80 View Post
    I can't seem to get the Script to send me any emails. I think it has to do with the portion (timeLeft < DateTrigger), but I'm quite a bit amateur at this, so I don't know.
    I have no way to test your code, but that would be my guess too. You're using a numeric comparison operator, <, to compare a stringified date in DateTrigger with an undescribed value in timeLeft.

    You should break the dates into parts using something like DateTrigger.split("/") and convert each item into a number using something like:

    DateTrigger = DateTrigger.split("/");
    var month = Number(DateTrigger[0]);
    var day = Number(DateTrigger[1]);
    var year = Number(DateTrigger[2]);
    Rinse and repeat for timeLeft assuming it's in the same format.
    For every complex problem, there is an answer that is clear, simple, and wrong.

  3. #3
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Thanked 619 Times in 599 Posts
    why don't you strip your personal data from a new copy and publish that one so that we can try out the code and spreadsheet at once?
    Create, Share, and Debug HTML pages and snippets with a cool new web app I helped create: pagedemos.com


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts