Value of Date Cell returns a ntp time Google Apps Script

Question

i have a Spreadsheet with a liste of date.

When i use this code to get the value:

function run() {
var ss = SpreadsheetApp.getActive().getSheetByName("Planning Template");
var values = ss.getRange("G3:ADS3").getValues();
var value = ss.getRange("A1").getValue();
var date = new Date (value);

  for(var column in values[0]){
  var test = values[0][column];
  if( test ==  value){
   var i = column; 
  }
}}

This is the result from getValues():

[[42370, "", 42371, "", 42372, "", 42373, "", 42740, "", 42741, "", 42742, "", 42743, "", 42378, "", 42379, "", 42380, "", 42381, "", 42382, "", 42383, "", 42384, "", 42385, "", 42386, "", 42387...

And this is the result from getValue(): Sun Jan 01 2017 00:00:00 GMT+0100 (CET)

I dont understand why Spreadsheet return a ntp time.

Edit: That is the value normally i expect that when i call getValue i get this value but i get this 42373. Its formated but the value is still a date why do i get a formated number? That is the shown value as i exoect i get 3 when i call getDisplayValue.

Have you tried formatting the date columns in the spreadsheet to be the same date format? You can also use formatDate found here: developers.google.com/apps-script/reference/utilities/… for consistency. — utphx
I did try it but when i use formatDate it says that the date is invalid. I think the reason for that is that ntp starts at 1900 and unix at 1970. Formatdate use new date as input but new date cant create a date with the value 4370. — Taaut

Unknown Unknown · Accepted Answer · 2016-12-21T21:03:24

The methods getValue and getValue may return "Number, Boolean, Date, or String depending on the value of the cell." Specifically, you get

a number if your cell is formatted as a number.
a JavaScript date object if your cell is formatted as a date, datetime, time, or duration (for example, time value 3:00pm will become a JS datetime, with the date part being December 30, 1899.)
a string if the content is plain text (empty string if the cell is blank)
Boolean if the content is TRUE or FALSE

In your case, you should make sure that the columns you are comparing have the same kind of formatting, probably the date formatting is what you want.

Value of Date Cell returns a ntp time Google Apps Script

1 Answers