Run custom function if value in cell is date

问题:

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?

回答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.