Embed function inside of IF statement Google sheets-CodePudding

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)
   }
...

CodePudding user response：

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!

CodePudding user response：

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