I've the following columns in a spreadsheet

With following code deployed as add-on in google apps script console I want to autamtically send emails to certain adresses at certain date and time, I#ve activeted a trigger to run the app every minute, but somehow it does not work...any hint what could be wrong? I'm 100% sure it is connected with the right spreadsheet

   function sendReminderEmails() {
      var spreadsheet = SpreadsheetApp.openById("1h570Eb4IqAaAHciQzz0U9oY_uqGo9UDzpWtth78NkCY").getSheetByName("test");
      var data = spreadsheet.getDataRange().getValues();
      // console.log(data);
      for (var i = 1; i < data.length; i  ) {
        var email = data[i][0];
        var name = data[i][1];
        var link = data[i][2];
        var date = data[i][3];
        var time = data[i][4];
        var file = data[i][5];
        
        var now = new Date();
        
        if (now.toLocaleDateString() == date && now.toLocaleTimeString() == time) {
          MailApp.sendEmail(email, "Reminder: "   file, "Hello "   name   ",\n\nThis is a reminder about the "   file   " at "   link   " on "   date   " at "   time   ".");
        }
      }
    }

CodePudding user response：

There are three problems with your code and the way you're approaching this problem:

• The variables date and time that you got from the Sheet are Date objects, so you also have to call toLocaleDateString() and toLocaleTimeString() on them if you want to compare them with the now values.
• Even if you correct the above, the value returned by toLocaleTimeString() is a time in the format HH:MM:SS XM so the trigger would have to run at the exact same second as the time in your Sheet, which is very unlikely. You would need to cut out the seconds to compare the exact minute.
• Even if you correct the above and compare the exact minute, Apps Script's

  In this case roundedtime and roundednow would cut off the seconds and round down the minutes so, something like 14:41:00 would become 14:40, so the email would be sent when the trigger fires at any time between 14:40 to 14:49, then the checkbox is selected with spreadsheet.getRange(i 1, 8).setValue(true) so it won't send another email within the same timeframe. Most often the emails will be sent closer to the lower bound.

  Another possibility could be to instead create the triggers programmatically by using the time in the Sheet. If you use a trigger to fire at a specific time it will run once and then expire, but there's a limit of 20 triggers / user / script, so you wouldn't be able to use this method with a lot of data. Either way, keep in mind that you'll need to sacrifice some accuracy if you want your current approach to work.