CODEWITHSUNDEEP
datedatetimegoogle-apps-scriptgoogle-sheetsjavascript-objects
Craig Ferguson
Craig Ferguson

Reputation: 13

Google script not recognising date

I'm trying to write some code to check values in a spreadsheet column to see if they are valid dates. However, even if I put in a date in a cell with the format set to date, it doesn't seem to recognise it as a date. When I debug the date objects are listed as "new Date" but if I attempt to ask Logger to getDate() I receive the following error :

TypeError: Cannot find function getDate in object Sat Jun 05 2010 01:00:00 GMT+0100 (BST). (line 15, file "myCode")

I can set my objects as dates by calling 'new Date()' which means that they are recognised as dates but this changes all the objects to dates whether they should be or not. Here is my code:

function onMyPress(){
 var sheet = SpreadsheetApp.getActive().getActiveSheet()
 var myRange = sheet.getRange(2,2,8,1)
 var myValues = myRange.getValues()
 for (i=0; i<8; i++){
 var testDate = myValues[i]
 if (isDate(testDate)) {sheet.getRange(i+2,3,1,1).setValue("Date")
 }
 else{
 sheet.getRange(i+2,3,1,1).setValue("Not Date")
 }
 Logger.log(testDate.getDate())
 }
 }
 function isDate(testDate) {
 if (Object.prototype.toString.call(testDate) !== "[object Date]") {
 return false; 
 }
 else {
 return true;
 }
 }

Thanks for looking.

Upvotes: 1

Views: 2372

Answers (2)

Wicket
Wicket

Reputation: 38160

The problem is that getValues() returns a 2D array and you script assigns a "row" (a JavaScript array) instead of cell value (a JavaScript string, Date, etc.) to testDate on the following line

var testDate = myValues[i]

One alternative is to replace the above code line by by something like the following:

var testDate = myValues[i][0]

assuming that your dates are on the first column.

Upvotes: 1

Alix Bergeret
Alix Bergeret

Reputation: 106

"Cannot find function getDate in object" means that you are calling getDate() on an object that is NOT a Date object.

I am guessing "testDate" is a String object? If you step through the code, does it go into the "else" clause?

You need to:

  1. Check that your String object contains a correctly formatted date (which seems to be the case)
  2. Then CONVERT it to a Date object, like this: var realDate = new Date(testDate);

See working example here: https://jsfiddle.net/uyowax8o/

// Convert to Date object
var realDate = new Date(testDate);

// This now works
alert(realDate.getDate());

Upvotes: 0

Related Questions