Run custom function if value in cell is date

Question

I am new to Google scripting. I would like to check if the value in a cell is a date, and if so, to change the default format of the date, and then return the new formatted date value.

Here is an example of what I would like:

var activeCellValues = SpreadsheetApp.getActiveRange().getValues();
if ( activeCellValues = "Is_Type_Date")
       {   
          var activeCellValues = Utilities.formatDate(pubDateCell,"PST", "MM/dd/yyyy")
       }

Is this possible?

Crowell

Answer 1

Use the isDate() function, below, to do what you're asking. The date format can be changed to suit other needs. Refer to [Utilities.formatDate()][3] for more info.

// From http://stackoverflow.com/questions/1353684
// Returns 'true' if variable d is a date object.
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

// Test if value is a date and if so format
// otherwise, reflect input variable back as-is. 
function isDate(sDate) {
  if (isValidDate(sDate)) {
    sDate = Utilities.formatDate(new Date(sDate), "PST", "MM/dd/yyyy");
  }
  return sDate;
}

In your script, you've used the Range.getValues() function, which will return a two-dimensional array of the data in the given range. (Your whole sheet, in your case.) You will therefore need to loop over the read values and call this function on any cell that you're interested in, and then write the information back out to the spreadsheet. There are plenty of examples of this both in Google's documentation and on Stackoverflow.

Roofer

Is this isValidDate still the easiest way to test if a value is a Date object? Does Apps Script have a built-in function for this purpose now? – Indelible

Answer 2

A simpler method would be to use instanceof

if (data instanceof Date) {
  formattedDate = Utilities.formatDate(new Date(data), "PST", "MM/dd/yyyy");
}

Works