在IF语句Google表单中嵌入函数

Question

多亏了Michael the Temp，这个函数工作得很好。但是如果嵌入在if语句中，它怎么能工作呢？比如：=if(B2>5，TIMESTAMP((，"FALSE"(

function TIMESTAMP() 
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var addedDateAndTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss");
//Optional if you only want the date: var addedDate = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy");
//Optional if you only want the time: var addedTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "hh:mm:ss");
sheet.getRange(1,1).setValue(addedDateAndTime)
}
...

Answer 1

嘿@user17712179欢迎！你可以试试这个：

function TIMESTAMP() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var addedDateAndTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss");
//Optional if you only want the date: var addedDate = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy");
//Optional if you only want the time: var addedTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "hh:mm:ss");
if (sheet.getRange("B2").getValue() > 5) {
sheet.getRange(1,1).setValue(addedDateAndTime);
} else {
sheet.getRange(1,1).setValue('FALSE');
};
}

让我知道！

Answer 2

根据您的需要，您还可以通过向自定义函数添加params并将单元格数据传递给公式来提高AJ公式的可重用性。

/**
* Convert cell value in timestamp
*
* @param {cell} input The value to convert.
* @return Timestamp converted.
* @customfunction
*/
function TIMESTAMP(cell) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var addedDateAndTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss");
if (cell > 5) {
return addedDateAndTime
} else {
return 'FALSE'
};
}