Struggling with extracting date from timestamp

[Nigel Hunt]

Here's some code I've been trying....

 for (var Row = FoundRow + 1; Row <= LastResponsesRow; Row = Row +1){  

   //Test row below to see if the day is greater will only

   //work if not crossing a weekend

  var Today = SpreadsheetApp.getActiveSheet().getRange(Row, 1).getValue();

  var TodaysDate = Today.getDay() + Today.getMonth() + Today.getYear()

  var ThisYear = Today.getYear()

  SpreadsheetApp.getUi().alert("Today's Date is")

  SpreadsheetApp.getUi().alert(ThisYear)

  var Tomorrow = SpreadsheetApp.getActiveSheet().getRange(Row + 1, 1).getValue();

  //SpreadsheetApp.getUi().alert("Tomorrow is")

  //SpreadsheetApp.getUi().alert(Tomorrow)

   Logger.log("Row is " + Row);

   Logger.log("Today is " + Today);

   Logger.log("Tomorrow is " + Tomorrow);

     SpreadsheetApp.getUi().alert("Tomorrow is")

  SpreadsheetApp.getUi().alert(Tomorrow.getDate())//Gets Day of the month - no good

  // might have to concatenate getDay, getMonth & getYear to make 

  //a 'real' date to compare. It would be a string

  if (Tomorrow.getDate() > Today.getDate()) {  

     var FoundRow = Row

   var StopLoop = 1

   SpreadsheetApp.getUi().alert("End of date group is at row ");

   SpreadsheetApp.getUi().alert(FoundRow)

   if (StopLoop = 1) { break; } //Doesn't stop till 2nd date in next group

   //so row is one more than it should be

  }

 }

You can see various trials using .getDate(), .getMonth() & .getYear, & concatenating them.

Surely there must be an easier way to get 24/02/2022 from 24/02/2022 10:43:20, in a manner where I can tell if Tomorrow's date is greater than Today's.

Any help would be much appreciated.

Thanks

Nigel

[Martin Hawksey]

Hi Nigel,

Google Apps Script has a handy utility for formatting date values .fornatDate(). You might be able to use something like this in your code:

  var Today = SpreadsheetApp.getActiveSheet().getRange(Row, 1).getValue();

  var TodaysDate = Utilities.formatDate(Today, "GMT", "dd/MM/yyyy");

Best

Martin

[Nigel Hunt]

Thanks Martin, I'll try that.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1108ccbb-4b0f-41d7-8687-722b6cad83ean%40googlegroups.com.

[Nigel Hunt]

Not quite there yet.

The alerts show the date in the correct format, but the logger shows them in full

The loop doesn't end when tomorrow is a day, or more, later than today

That also begs the question why the loop doesn't break at the first row, because the time is later in the same day of the group.

Here's the latest version of  the relevant code

Any clues?

[Nigel Hunt]

Oops

I forgot the code!

 for (var Row = FoundRow + 1; Row <= LastResponsesRow; Row = Row +1){  

  var Today = SpreadsheetApp.getActiveSheet().getRange(Row, 1).getValue();

  var TodaysDate = Utilities.formatDate(Today, "GMT", "dd/MM/yyyy");

  SpreadsheetApp.getUi().alert("Today's Date is")

  SpreadsheetApp.getUi().alert(TodaysDate)

  var Tomorrow = SpreadsheetApp.getActiveSheet().getRange(Row + 1, 1).getValue();

  var TomorrowsDate = Utilities.formatDate(Today, "GMT", "dd/MM/yyyy");

  SpreadsheetApp.getUi().alert("Tomorrow's Date is")

  SpreadsheetApp.getUi().alert(TomorrowsDate)

   Logger.log("Row is " + Row);

   Logger.log("Today's Date is " + Today);

   Logger.log("Tomorrow's Date is " + Tomorrow);

  if (TomorrowsDate > TodaysDate) {  

[Martin Hawksey]

Hi Nigel,

If you are doing a date comparison ignoring time rather than formatting the date it is easier to do a comparison on the cell date value. So for you code example I would do:

for (var Row = FoundRow + 1; Row <= LastResponsesRow; Row = Row +1){  

  var Today = SpreadsheetApp.getActiveSheet().getRange(Row, 1).getValue();

  var TodaysDate = Today.setHours(0, 0, 0, 0); // this removes hours, minutes, seconds, milliseconds

  SpreadsheetApp.getUi().alert("Today's Date is")

  SpreadsheetApp.getUi().alert(TodaysDate)

  var Tomorrow = SpreadsheetApp.getActiveSheet().getRange(Row + 1, 1).getValue();

  var TomorrowsDate = Tomorrow.setHours(0, 0, 0, 0);

  SpreadsheetApp.getUi().alert("Tomorrow's Date is")

  SpreadsheetApp.getUi().alert(TomorrowsDate)

   Logger.log("Row is " + Row);

   Logger.log("Today's Date is " + Today);

   Logger.log("Tomorrow's Date is " + Tomorrow);

  if (TomorrowsDate > TodaysDate) { 

Hopefully that works for you? Another tip is the .getValue() call you use can add a lot of time to your script execution. There is a good turorial here on the various ways you can read google sheet cell values quickly https://jeffreyeverhart.com/2019/03/01/retrieve-rows-from-google-spreadsheet-with-google-apps-script/

Using the tutorial another way to write your looping function would be:

function readRows(){

  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  rows.forEach(function(row, index) {

    if (index > 1) { // this skips header and row 2

      var Today = rows[index-1][0]; // using the previous row in the loop from the  source rows array 

      var TodaysDate = new Date(Today).setHours(0, 0, 0, 0); // this removes hours, minutes, seconds, milliseconds

      var Tomorrow = row[0]; // using the current row in the loop

      var TomorrowsDate = new Date(Tomorrow).setHours(0, 0, 0, 0);

      if (TomorrowsDate > TodaysDate) { 

        Logger.log("Today's Date is " + Today);

        Logger.log("Tomorrow's Date is " + Tomorrow);

        Logger.log('Row '+ (index+1) +' is more than one day between next row');

      }

    }

  });

}

[Nigel Hunt]

Thanks Martin, I'll certainly try the first solution.

I don't understand enough about the second one to know how to set it up for starting at a row of my choice using a previously determined value. Perhaps better that I use the one I understand even if it's technically slower. (Not a big issue tbh)

I will look at the info you've suggested & see if I can get my head round it.

I'm used to vba in Excel so this exercise is a bit of a leap in the dark for me!

bfn

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c1e2a5e5-e862-4133-b1e9-2073c6133e7cn%40googlegroups.com.

[Nigel Hunt]

First solution worked perfectly!

Many thanks for your help Martin.

[Martin Hawksey]

Hi Nigel - Rome wasn't build in a day and agree better to go with what you understand :) Glad the first solution worked out for you

Happy scripting!

Martin