0

I am seeing some very strange behavior with reading a date from my sheet. Here is my code: 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1];

var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
SpreadsheetApp.getUi().alert(values[14][9]);

The cell at 14,9 has the following date 10/15/2016 but when I do the alert, I get the following value - 42658

I don't understand what's going on. Can someone help me with this?

This is resolved by doing 

var values = dataRange.getDisplayValues();
p0tta
  • 1,461
  • 6
  • 28
  • 49
  • 3
    Use var values = dataRange.getDisplayValues(); – Anees Hameed Mar 06 '17 at 17:08
  • @AneesHameed works like a charm, thanks! – p0tta Mar 06 '17 at 17:11
  • Possible duplicate of [Converting Google spreadsheet date into a JS Date object?](http://stackoverflow.com/questions/14363073/converting-google-spreadsheet-date-into-a-js-date-object) – Rubén Mar 07 '17 at 21:01
  • Also possible duplicate of [How to get a date format string from a sheet cell?](http://stackoverflow.com/questions/33809229/how-to-get-a-date-format-string-from-a-sheet-cell/) – Rubén Mar 07 '17 at 21:06

2 Answers2

0

This is resolved by doing

var values = dataRange.getDisplayValues();
p0tta
  • 1,461
  • 6
  • 28
  • 49
0

The value you're getting is the number of milliseconds since the beginning of January 1, 1970 UTC. You can use various methods, like getFullYear(), getMonth(), getDate(), getDay(), getHours(), and getMinutes() to parse the item if that's more helpful. pOtta's suggestion will get you the text string that matches what's displayed.

Russ Smith
  • 11
  • 1