Use getLastRow independently of a cell

Question

Hello I am currently working on a time tracking system. With the following code I track the time how long a value was in a cell. This time is recorded in another worksheet and this is done continuously by appendRow (). Now I have the problem, if several cells have one value, I only ever get the date + time in the last one. Does it work that it inserts the last value regardless of the cell?

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

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");
    }
  }
}

To explain my problem more clearly, two pictures of how the script is currently running.

If Name1 (C11) is now unsubscribed, the last date is not entered for Name1 in the first worksheet, but for Name2.

Not sure what you mean, but this might help you: https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column — Marios, Mar 08 '21 at 13:14
Unfortunately that doesn't help me any further. I try to formulate my problem more clearly. — LamaFahrrad, Mar 08 '21 at 13:21
If I understand you correctly, a new row is appended whenever a certain sheet is edited. Looking at your code, I don't see how different cells having the same name can cause the date not to be entered. Would you consider providing a copy of the spreadsheet you are working on, in order to clarify this? Also, what do you mean by `unsubscribed`? — Iamblichus, Mar 08 '21 at 14:11
Yes sure https://docs.google.com/spreadsheets/d/1rhNHb8CK_OYdvGiLa8S1EeWMLWT3c5WIIRHxokehm_U/edit?usp=sharing By that I mean that the value is deleted, i.e. the cell is empty again. — LamaFahrrad, Mar 08 '21 at 14:22
Hi, I posted an answer so that this works when deleting the cell directly (no need to select its content first). Let me know if that works for you. — Iamblichus, Mar 09 '21 at 13:11

score 2 · Answer 1 · answered Mar 08 '21 at 14:27

Explanation:

You can use the TextFinder class to search for the name that was removed and find the row of the specific name.

To find the old value you can use e.oldValue but that has a restriction.

Solution:

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

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 val = e.range.getValue();
    if (val != "") {
      let rowToAdd = [val, "", currentDate, ""]
      ss.getSheetByName("Tabellenblatt1").appendRow(rowToAdd);
      ui.alert("Test2");
    } else {
      var sheet = ss.getSheetByName("Tabellenblatt1");
      var dataFinder = sheet.createTextFinder(e.oldValue);
      var nameRow = dataFinder.findAll()[0].getRow();
      sheet.getRange(nameRow, 4).setValue(currentDate);
      ui.alert("Test3");
    }
  }
}

Restrictions:

The e.oldValue value is undefined if you delete the content of the cell and therefore the aforementioned solution won't work.

To get the old value you need to replace it with an empty string. To do that, left click on the cell (name) you want to delete, select the text in the formula area:

and press delete to delete the text.

Last but not least, the solution assumes the names are unique.

Thanks for the help, but it doesn't seem to help me with my problem, because I would like to do the whole thing completely automatically and via the script editor. As you said, the solution is not going to work out right. — LamaFahrrad, Mar 08 '21 at 14:39
@LamaFahrrad I didn't say it won't work, otherwise I wouldn't post it as an answer . I said that if you want to it to work, you need to select the cell, and remove the text from the formula editor. You can't simply delete the content of the cell with the delete button in your keyboard, because `e.oldValue` will return undefined. — Marios, Mar 08 '21 at 14:42
Yes, you are right, it works very well, I will then open a new question. Maybe someone knows a solution that also works with the delete key, because that would actually be very important for me. — LamaFahrrad, Mar 08 '21 at 14:48

score 1 · Accepted Answer · answered Mar 09 '21 at 13:10

Issue:

Since, as Mario has commented, e.oldValue is not populated when a cell content is directly removed (instead of first selecting it and then delete the content), keeping track of which value has been deleted becomes troublesome.

You need to find a way to keep track of which rows in the source sheet (Tabellenblatt2) correspond to which rows in the target sheet (Tabellenblatt1).

Solution:

You can use PropertiesService for this:

Every time a new item is written to the source sheet, use setProperty() to store a property whose key is the index of the currently edited row, and value is the index of the row appended to the target sheet.
Every time an item is removed from the source sheet, use getProperty(key) (using the current row index) to retrieve the corresponding row index in the target sheet (where the second timestamp should be written).
Write the timestamp to the row that has just been retrieved.

Code sample:

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();
  var scriptProperties = PropertiesService.getScriptProperties();
  if (e.source.getActiveSheet().getName() === ws) {
    var cell = ss.getActiveCell();
    var val = cell.getValue();
    var sourceRowIndex = cell.getRow();
    if (val != "") {
      let rowToAdd = [val, "", currentDate, ""]
      targetSheet.appendRow(rowToAdd);
      scriptProperties.setProperty(sourceRowIndex, targetSheet.getLastRow());
    } else {
      var rowIndex = Number(scriptProperties.getProperty(sourceRowIndex));
      if (rowIndex) targetSheet.getRange(rowIndex, 4).setValue(currentDate);
    }
  }
}

Note:

IMPORTANT: properties won't be stored for previously existing values. Either create those properties manually, or remove all existing values and start from scratch.
In the example above, script properties is used, but document properties and user properties could also be appropriate, depending on your current situation).

Thanks for your answer, because your solution worked even better than Mario's, I'll mark the whole thing as well. — LamaFahrrad, Mar 09 '21 at 18:28

Use getLastRow independently of a cell

2 Answers2

Explanation:

Solution:

Restrictions:

Issue:

Solution:

Code sample:

Note: