Getting today's date in google Apps Scripts

Question

I am trying to get Today's date as a variable to be able to compare how it's been since a date registered in the Spreadsheet.

However i have tried the syntax:

var now = new Date();

But it returns

31/12/1899.

How can I fix it?

function onEdit(event) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = event.source.getActiveSheet();
      var r = event.source.getActiveRange();
      var now = new Date();


      if(r.getColumn() == 1 && r.getValue() <= (now -10)) {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        s.getRange(row, 1, 1, numColumns).setBackgroundColor('#DBFF33');
        ss.getRange('B3').setValue(now);
      }

      else {
      }
    }

score 2 · Answer 1 · answered Jun 08 '19 at 08:17

Google Apps Script date objects are similar but slightly different to core JavaScript date objects. The reference goes through the details.

In the Date Math section they recommend calculating dates using the date.getTime() function and adding or subtracting milliseconds and explicitly creating a new date:

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var checkDate = new Date(now.getTime() - (MILLIS_PER_DAY * 10);

Subtracting 10 from a Date object just seems to convert it to an integer and it is no longer recognised as a date.

So in your case to check for a date that is older than 10 days you should do the following:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var now = new Date();

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var checkDate = new Date(now.getTime() - (MILLIS_PER_DAY * 10));

  if (r.getColumn() == 1 && r.getValue() <= checkDate) {

    var row = r.getRow();
    var numColumns = s.getLastColumn();
    s.getRange(row, 1, 1, numColumns).setBackgroundColor('#DBFF33');
    ss.getRange('B3').setValue(now);
  } else {

  }
}

Getting today's date in google Apps Scripts

1 Answers1