Value of Date Cell returns a ntp time Google Apps Script

1.5k views Asked by At

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? enter image description here That is the shown value as i exoect i get 3 when i call getDisplayValue. enter image description here

1

There are 1 answers

2
AudioBubble On

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.