Datetime Formatting in onEdit()

65 views
Skip to first unread message

Jenny

unread,
Oct 25, 2021, 10:23:08 AM10/25/21
to Google Apps Script Community

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

unread,
Oct 26, 2021, 12:30:39 AM10/26/21
to Google Apps Script Community

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

unread,
Oct 26, 2021, 12:42:56 AM10/26/21
to google-apps-sc...@googlegroups.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

unread,
Nov 26, 2022, 4:10:58 PM11/26/22
to Google Apps Script Community
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

unread,
Nov 26, 2022, 7:18:13 PM11/26/22
to Google Apps Script Community
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

Reply all
Reply to author
Forward
0 new messages