0

I have a script that I like to check when the script last ran. I thus log the "last run" datetime in a sheet before the script ends, and pull the data everytime the script starts. However, the calculation is always wrong when I compare the current date to the last ran date.

var dtToday = new Date();
var dtDebug=new Date(shtYahooDataRef.getRange(2,2).getValue());  //This is extra code for my debugging
var dtLastUpdate=new Date(shtYahooDataRef.getRange(1,2).getValue()); //This is where I retrieve the last ran datetime

// Just to check that the dates are correct
Logger.log (Utilities.formatDate(dtToday, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtDebug, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtLastUpdate, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));

//Given that dtToday and dtDebug are very close, I expect the below to be very close but this is not case. Hence I wonder why
Logger.log (dtToday-dtLastUpdate);
Logger.log (dtDebug-dtLastUpdate);

Here is the log

December 07, 2021 02:26:25 +0800
December 07, 2021 02:26:13 +0800
December 07, 2021 02:25:13 +0800
4.3272544E7
60000.0

How is it that dtToday-dtLastUpdate and dtDebug-dtLastUpdate can be so different? Won't dtToday-dtLastUpdate be in the ball-park of 60000?

Additional info. This is the actual code where I exit the script if the last ran time was less than 6 hours ago. The condition was always false.

  var t1 = dtToday.getTime(),
      t2 = dtLastUpdate.getTime();
  if ( Math.floor((t1-t2)/(3600*1000))< 6) {
    return;
    }; // 3600*1000 is milliseconds in an hour Update only is last update was 6 hr ago

Additional comment: If I have a simple script

var dtToday = new Date();
Logger.log (Utilities.formatDate(dtToday, "Asia/Hong_Kong", 'MMMM dd, yyyy hh:mm:ss Z')+" "+Session.getScriptTimeZone());

and the output is

1:57:21 PM   Info  December 10, 2021 01:57:21 +0800 Asia/Hong_Kong

How is it that running the script at 1:57PM returns 01:57:21 +0800 ? Shouldn't it be 13:57:21 +0800?

Leon
  • 33
  • 1
  • 1
  • 6
  • I thought that this thread might help understand your issue. https://stackoverflow.com/q/492994 – Tanaike Dec 07 '21 at 06:48
  • Have you checked if the two dates are coming in the same format? If possible share a sample spreadsheets and the full scripts to make it work. – Kessy Dec 07 '21 at 10:23
  • Provide `console.log(...[dtToday,dtDebug,dtLastUpdate].map(d=>d.getTime()))` – TheMaster Dec 07 '21 at 11:55
  • dtToday is December 09, 2021 02:44:44 +0800. dtDebug is December 09, 2021 02:44:44 +0800 and dtLastUpdate is December 09, 2021 02:32:15 +0800. The output is 1639032284315 1638989084000 1638988335000. – Leon Dec 09 '21 at 06:47

2 Answers2

0

I found the issue. I log the time wrongly.

Instead of using 'MMMM dd, yyyy hh:mm:ss Z', I should have used 'MMMM dd, yyyy HH:mm:ss Z'

Leon
  • 33
  • 1
  • 1
  • 6
-1

You can not directly "minus" a date from another date.

You have to first convert the date into millisecond. Then you can substract one date from another and then you have to convert the difference into minutes or hours.

var dtToday = new Date();
var dtDebug=new Date(shtYahooDataRef.getRange(2,2).getValue());  //This is extra code for my debugging
var dtLastUpdate=new Date(shtYahooDataRef.getRange(1,2).getValue()); //This is where I retrieve the last ran datetime

// Just to check that the dates are correct
Logger.log (Utilities.formatDate(dtToday, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtDebug, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtLastUpdate, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));

//Given that dtToday and dtDebug are very close, I expect the below to be very close but this is not case. Hence I wonder why
Logger.log (dtToday.valueOf()-dtLastUpdate.valueOf());
Logger.log (dtDebug.valueOf()-dtLastUpdate.valueOf());
arul selvan
  • 616
  • 4
  • 17