Datetime Formatting in onEdit()

[Jenny]

Hi Community,

I want to format datetime input value on onEdit event.

ex) 2021/10/25 15:00 -> Oct 25, 2021 3:00 PM

I put the same value (2021/10/25 15:00) on the sheet.

1. When I get value by using SpreadsheetApp Class, it comes in with normal datetime value.

   SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(6, 6).getValue()
   
   result : Oct 25 15:00:00 GMT+09:00 2021

2. But when using event object on onEdit, it comes in Num type value.

   onEdit(e){Logger.log(e.value)}
   
   result : 44494.625

Is there any way to convert Number to Datetime?

[Jenny]

I got the answer from Stack Overflow.

Using e.value cannot return Date data type, so it gets converted into Number.

You could use e.range.getValue() instead.

function onEdit(e) { 

   Logger.log(e.value);   //44494.625

   Logger.log(e.range.getValue());   //Oct 25 15:00:00 GMT+09:00 2021

}

[cbmserv...@gmail.com]

Jenny,

 

The value returned by e.value is not incorrect, it is just not formatted correctly to be seen as a Date.

 

You could use the following to set it up as a Date:

 

  Var dateValue = new Date(e.value);

 

The advantage of using the e params over using getValue() is that it is much faster as it does not require a poke at the spreadsheet. This is only of concern of course if you have a large amount of processing to be done and are concerned by how long the script takes to complete.

 

Hope this helps.

--
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/2c1fe55d-67c8-4a27-ac72-9fb9326bc097n%40googlegroups.com.

[Fred Richardson]

Using Date(e.value) gave me some very strange values.  

To get a useable Date object out of the values stored in e.value and e.oldValue, I had to do the following conversion.  Google got me most of the way here and I was able to verify the conversion, but be aware of you time zone which you will have to account for when using "Date.UTC()":

Logger.log(d.value);     // The logged output is " 44725.0" --  this was "6/13/2022" in my Sheet

d = new Date(Date.UTC(1899, 11, 30, 4, 0, e.value * 86400));  // Date in days (converted to seconds) after Dec 30 00:00:00 GMT-04:00 1899

Logger.log(d);      // The logged output is "Mon Jun 13 00:00:00 GMT-04:00 2022" 

Logger.log(e.range.getValue());      // The logged output is also "Mon Jun 13 00:00:00 GMT-04:00 2022" 

So I think the number stored in e.value reflects my time zone (GMT-04:00), so I have to enter 4 hours into the UTC conversion above and I suspect that number would have to change for a different time zone,.

-Fred

[Fred Richardson]

Well, I did find yet another wrinkle...  It looks like Daylight Time and Standard Time effect the number of seconds in e.value...

So I need to add a condition to figure out whether the offset needs to 4 hours (GMT-4 for EDT) or 5 hours (GMT-5 for EST).  *Sigh*...

-Fred