Google Sheets Trigger updates values in a table, but does not leave old values unchanged

Question

I have programmed a macro in Google Sheets that displays a table to show the history of certain data that changes every day. Every day the macro inserts a new line with a new date into the table. This is done using a trigger. Problem: The trigger also changes the values (I don't mean the date, that works fine, I mean a value like 1,2,3,4,5,6) of the row created the day before. So every row contains the same data if the values change. However, I would like to display a history of the data. So how can I prevent this?

Currently my evaluation list is the first sheet in this file, but if I move it to the 6th position and put a trigger on it, the values are still written on the 1st sheet, not on the evaluation list, but on the first sheet (but I want them on the 6th sheet). What do I have to enter into the code so that the values are entered on the 6th sheet (evaluation)?

Right now it looks like this:

function zeitStempelPerTriggerSetzen(){ 
  var tabellenblatt=SpreadsheetApp.getActiveSheet();
  var aktuellerZeitpunkt=new Date(); 
  var zeitStempelFormat="dd.mm.yyyy hh:mm:ss";
  tabellenblatt.getRange(tabellenblatt.getLastRow()+1,15).setValue(aktuellerZeitpunkt).setNumberFormat(zeitStempelFormat);   
  tabellenblatt.getRange(tabellenblatt.getLastRow()+0,16).setFormula('=\' checklist\'!BG8'); 

So for example row 1: 2019-10-16 value is 1 (this should stay 1, because i want to see the progress) But when I activate the trigger it makes a new row 2: 2019-10-17 value is now 2 but the value in row 1 gets changed to 2 too. And i want the value in row 1 to stay at 1. How can I prevent this?

Answer 1

If you want to populate column 16 with the actual value in sheet checklist, cell BG8 and you do not want the values of previous rows in column 16 to update when BG8 updates - do not use formulas.

Instead modify

  tabellenblatt.getRange(tabellenblatt.getLastRow()+0,16).setFormula('=\' checklist\'!BG8'); 

to

  var checklist=SpreadsheetApp.getActive().getSheetByName("checklist");
  var currentValue=checklist.getRange("BG8").getValue();
  tabellenblatt.getRange(tabellenblatt.getLastRow()+0,16).setValue(currentValue);