Google App Script event value type for date field different from e.value and e.range.getValue()

Question

I've just started messing around with Google Apps Script and found something that has me puzzled. I have a spreadsheet with a cell A2 that contains a date. I have created an onEdit() event handler.

If I change the date value, e.value is a number, which is the number of days since 1/1/1900. e.range.getValue() is a date.

Here's a simple example:

function onEdit(e) {
   // This returns a number -- number of days since 1/1/1900
   SpreadsheetApp.getUi().alert('From event: '+e.value); 

   // This returns a date
   SpreadsheetApp.getUi().alert('From selected range: '+SpreadsheetApp.getActiveSheet().getRange(2, 1).getValue()); 

   // This also returns a date
   SpreadsheetApp.getUi().alert('From event range: '+e.range.getValue()); 
}

I'm sure there's a reason for this that I'm just missing. Can somebody clue me in? Thanks!

Max Makhrov Max Makhrov · Accepted Answer · 2016-08-03T06:38:41

e.Value

There is poor documentation about e.value, all it says is:

New cell value after the edit. Only available if the edited range is a single cell.

The real values of dates in spreadsheets are their serial numbers, starting from 1/1/1900. And only formattind is making dates look like dates in spreadsheets. So, I guess, this is a good feature that we can access this value with e.value

range.getValue()

There's more info about this function, it gives:

Number, Boolean, Date, or String depending on the value of the cell

So it works ok, giving a date. But Google Apps is using javascript which treats dates differently. And this may cause problems with understanding of dates.

Google App Script event value type for date field different from e.value and e.range.getValue()

1 Answers