This function works well, thanks to Michael the Temp. But how can it work if embedded inside of an IF statement like so: =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)
}
...
hey @user17712179 welcome! you can try this:
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');
};
}
Let me know!
Depending on your needs, you can also make AJ formula more reusable by adding params to the custom function and passing cell data to your formula.
/**
* 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'
};
}