appendRow in 2 If Statements

Question

I have the following problem. I want to have a value written to a line when a certain condition is met. The whole thing works currently also here my code:

function onEdit(e) {
  addTimestamp(e);
}

function addTimestamp(e) {
  
  var ui = SpreadsheetApp.getUi();
  var ws = "Tabellenblatt2"; 
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1");
  var range = targetSheet.getRange(3, 2, 1000,1); 
  var currentDate = new Date();
  
  if(e.source.getActiveSheet().getName() === ws && range != "") {
    var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
    var val = cell.getValue();

    if (val != "") {
      let rowToAdd = [val, "", currentDate, ""]
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1").appendRow(rowToAdd);
      ui.alert("Test2");
    } 

    if (val === ""){
      let rowToAdd = ["", "", "", currentDate]
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1").appendRow(rowToAdd);
      ui.alert("Test3");
    }
  }
}

I want the second If statement to put the value in the same line as in the first If statement, so that no new line is created. Does somebody has any idea?

Can I ask you about I want the second If statement to put the value in the same line as in the first If statement, so that no new line is created.? About the same line as in the first If statement, how do you check this? For example, when there are several rows by the first If statement, where do you want to put the value as the same line as in the first If statement? — Tanaike
Yes, that's a problem too, I don't really know how to tie the line to it so that it works with multiple lines. the whole thing should be used as a time tracker. The first if statement should set the start time and the second if statement the "end time" — LamaFahrrad
Thank you for replying. About how to tie the line to it so that it works with multiple lines, I cannot understand about your goal. So in order to correctly understand about your goal, can you provide the sample situation including the input and output you expect? By this, I would like to confirm it. — Tanaike
The main task is to measure time. Someone enters a cell, then this time (start time) should be entered in another worksheet with the first if statement. Should he "unsubscribe" again, i.e. empty the cell, the currentdate should also be written next to the start time. — LamaFahrrad
Thank you for replying and adding more information. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your goal, I apologize. — Tanaike

Tanaike Tanaike · Accepted Answer · 2021-03-06T03:30:08

From your following replying,

I don't really know how to tie the line to it so that it works with multiple lines. the whole thing should be used as a time tracker. The first if statement should set the start time and the second if statement the "end time"

The main task is to measure time. Someone enters a cell, then this time (start time) should be entered in another worksheet with the first if statement. Should he "unsubscribe" again, i.e. empty the cell, the currentdate should also be written next to the start time.

In this case, I thought that the current last row might be the cell you want to put the values of ["", "", "", currentDate]. If my understanding is correct, how about the following modification?

Modified script:

Please modify addTimestamp as follows.

function addTimestamp(e) {
  var ui = SpreadsheetApp.getUi();
  var ws = "Tabellenblatt2";
  var ss = e.source;
  var targetSheet = ss.getSheetByName("Tabellenblatt1");
  var range = targetSheet.getRange(3, 2, 1000, 1);
  var currentDate = new Date();
  if (e.source.getActiveSheet().getName() === ws && range != "") {
    var cell = ss.getActiveCell();
    var val = cell.getValue();
    if (val != "") {
      let rowToAdd = [val, "", currentDate, ""]
      ss.getSheetByName("Tabellenblatt1").appendRow(rowToAdd);
      ui.alert("Test2");
    } else {
      var sheet = ss.getSheetByName("Tabellenblatt1");
      sheet.getRange(sheet.getLastRow(), 4).setValue(currentDate);
      ui.alert("Test3");
    }
  }
}

As the additional modification point, SpreadsheetApp.getActiveSpreadsheet() can be replaced with e.source using the event object.

appendRow in 2 If Statements

1 Answers

Modified script:

References: