如果单元格中的值为日期，则运行自定义函数 [英] Run custom function if value in cell is date

问题描述

我是 Google 脚本的新手.我想检查单元格中的值是否是日期，如果是，更改日期的默认格式，然后返回新的格式化日期值.

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.

这是我想要的示例:

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

这可能吗?

推荐答案

使用下面的 isDate() 函数来完成您的要求.可以更改日期格式以满足其他需要.有关详细信息，请参阅 [Utilities.formatDate()][3].

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;
}

在您的脚本中，您使用了 Range.getValues() 函数，该函数将返回给定范围内数据的二维数组.(您的整个工作表，就您而言.)因此，您需要循环读取值并在您感兴趣的任何单元格上调用此函数，然后将信息写回电子表格.在 Google 的文档和 Stackoverflow 上都有很多这样的例子.

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.

这篇关于如果单元格中的值为日期，则运行自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！