Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hello,

    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.

    Code:
    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
    sheet.getRange(managementCell).setValue("1");
    // 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
    Senior Coder Arbitrator's Avatar
    Join Date
    Mar 2006
    Location
    Splendora, Texas, United States of America
    Posts
    3,387
    Thanks
    32
    Thanked 288 Times in 282 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:

    Code:
    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
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,461
    Thanks
    11
    Thanked 600 Times in 580 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?
    my site (updated 2014/10/20)
    BROWSER STATS [% share] (2014/9/03) IE7:0.1, IE8:4.3, IE11:9.2, IE9:2.7, IE10:2.6, FF:16.8, CH:47.5, SF:7.8, NON-MOUSE:37%


  •  

    Posting Permissions

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