TIMESTAMP for function NOW() is updating when I open the Google Sheet to check the file

Question

I have an Excel file that is distributed to the company's retail stores and their purpose is to check the completion of some tasks and for the managers to be able to track the time when the tasks were completed. For convenience and time saving by employees there is a list of options for the status of work. The list of options is in column D and in column E the formula,

which I copied from this website, is:

=IF(D4<>"";IF(E4<>"";E4;NOW());"")

The problem I'm having is when I open the files to check them the time changes to the time I opened them.

I also tried another script I copied from some answer on a similar topic:

function TIMESTAMP() {
    var today = new Date();
    var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
    var date = today.getDate()+'/'+(today.getMonth()+1)+'/'+today.getFullYear();
    var dateTime = time+'-'+date;
    var timestamp_format = "dd-MM";
    return dateTime;
}

Using it in the formula:

=IF(D4<>"";TIMESTAMP();"")

but unfortunately, that didn't work either!

Is there a way someone can help me fix the formula or write a script that I can use inside column E that doesn't change the time and date?

Answer 1

From your question, I believe your goal is as follows.

• When the value of column "D" is not empty by editing the cell, you want to put the date retrieved by your function TIMESTAMP into the same row of column "E".
• You want to fix the cell value.

In this case, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set your sheet name, and save the script.

In this modification, the date value from your function TIMESTAMP is put into the cell of column "E" using the simple trigger of OnEdit. So, in this case, the cell value of the date is put without the formula. The value is directly put into the cell. By this, the cell value is fixed.

When you use this script, please edit the cell of column "D". From your formula =IF(D4<>"";TIMESTAMP();""), when the column "D" is not empty, the date is put into the column "E".

// This is from your showing script.
function TIMESTAMP() {
  var today = new Date();
  var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
  var date = today.getDate() + '/' + (today.getMonth() + 1) + '/' + today.getFullYear();
  var dateTime = time + '-' + date;
  var timestamp_format = "dd-MM";
  return dateTime;
}

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 4 || range.rowStart == 1) return;
  range.offset(0, 1).setValue(TIMESTAMP());
}

• In this modified script, the value of your function TIMESTAMP is used. So, it supposes that the value of your function TIMESTAMP is your expected value. Please be careful about this.

• In the above script, when column "D" is edited, the value of TIMESTAMP() is put into column "E" as the overwrite even when the column "E" is not empty. If you want to put the value of TIMESTAMP() into column "E" when the column "E" is empty, please modify onEdit as follows.

  function onEdit(e) {
    const sheetName = "Sheet1"; // Please set your sheet name.
  
    const { range } = e;
    const sheet = range.getSheet();
    if (sheet.getSheetName() != sheetName || range.columnStart != 4 || range.rowStart == 1) return;
    const rightSide = range.offset(0, 1);
    if (rightSide.isBlank()) rightSide.setValue(TIMESTAMP());
  }
  

Note:

• Please don't directly run onEdit function. When you directly run the function onEdit, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs because of no event object. Please be careful about this.

Reference:

• Simple Triggers