1

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?

Community
  • 1
  • 1
greenster10
  • 79
  • 1
  • 9
  • Are you talking about the values in column 16? You populate this column with a formula, so if the value of all rows changes, this is because the content of `BG8` changes. How is this content generated? – ziganotschka Oct 17 '19 at 14:48
  • yes the values in column 16 change, but I want them to stay untouched. So how do I solve this? In the moment I have 3 dates in my table and I dont want the values (column 16) of the dates (column 15) to change anymore, they should stay at value 1 when it was 1 at this day and don't change to value 2 for example when a new row with a new date gets added and the value change to 2 for the new date. – greenster10 Oct 17 '19 at 14:56

2 Answers2

0

Assuming your macro is assigning the value =today(), you can work around this by using the utility in app-scripts var today = Utilities.formatDate(new Date(), "dd/MM/yyyy").

Yaakov Bressler
  • 9,056
  • 2
  • 45
  • 69
  • Can you post this in your Q? – Yaakov Bressler Oct 17 '19 at 14:25
  • So the problem is that the values come from another table and the values in this other table change from day to day. And when i activate the trigger it changes the old values, which i want to safe to see the progress/history of the values – greenster10 Oct 17 '19 at 14:29
  • Ah. Have you considered writing the new data to a new sheet? That should solve your difficulty. – Yaakov Bressler Oct 17 '19 at 14:42
  • Yes the data i reference to is in table checklist and I made a new table which name is Evaluation (there is my history/progress table) because I only want specific data in my history/progress table. But when a value in chchklist changes, it changes in my history table for all days that are already in my table, so the result is that I simply have no history... There are always the new data, just with different dates and that is the problem – greenster10 Oct 17 '19 at 14:44
  • 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)? – greenster10 Nov 07 '19 at 14:21
  • Elaborate further in the Q @greenster10 – Yaakov Bressler Nov 08 '19 at 09:42
  • I did, can you help me? – greenster10 Nov 08 '19 at 09:47
  • Sure! Gimme a day or so. – Yaakov Bressler Nov 08 '19 at 10:48
0

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);
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • 1 moment I try this! – greenster10 Oct 17 '19 at 14:58
  • It says: Invalid or unexpected token – greenster10 Oct 17 '19 at 15:05
  • TypeError: SpreadsheetApp.getActive(...).getSheetbyName is not a function – greenster10 Oct 17 '19 at 15:09
  • sorry, it is meant to be `getSheetByName` (capital B). Also make sure that "checklist" is spelled exactly the same way as the name of your sheet. – ziganotschka Oct 17 '19 at 15:27
  • Glad to hear. If you are happy with my solution, I would appreciate it if you could mark it as "accepted". – ziganotschka Oct 18 '19 at 07:58
  • 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 in order to write the values on the 6th sheet (evaluation)? – greenster10 Nov 07 '19 at 14:20
  • u have an idea? – greenster10 Nov 08 '19 at 07:51
  • Instead of defining ` var tabellenblatt=SpreadsheetApp.getActiveSheet();`, you can modify your code to ` var tabellenblatt=SpreadsheetApp.getSheetByName(name);` and specify the name of your evaluation sheet. https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename – ziganotschka Dec 02 '19 at 11:59
  • https://stackoverflow.com/questions/59900660/creating-a-data-history-with-excel-vba-using-lastrow-time-stamp-and-workbook-sh could you please help me here? same problem but I basically need this in excel VBA now – greenster10 Jan 25 '20 at 09:36