getLastRow() affecting other cells

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 ().

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

Now one Picture to show my Problem:

The problem is that the cells should start in row 1, is that possible with getLastRow ()?

score 2 · Accepted Answer · answered Mar 09 '21 at 19:44

2

Determine the last row with content based on another column (e.g. column A):

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();
  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, ""]
      let rowA=targetSheet.getRange("A1:A").getValues().filter(String).length+1; // new code
      targetSheet.getRange(rowA,1,1,rowToAdd.length).setValues([rowToAdd]); // new code
      scriptProperties.setProperty(sourceRowIndex, targetSheet.getLastRow());
    } else {
      var rowIndex = Number(scriptProperties.getProperty(sourceRowIndex));
      if (rowIndex) targetSheet.getRange(rowIndex, 4).setValue(currentDate);
    }
  }
}

answered Mar 09 '21 at 19:44

Marios

26,333
8
32
52

Works fine, except that the last date is entered again below and not next to the "start date". [link] (https://gyazo.com/18cd1dc6fd8fa1b576d6716acdba5db3) As you can see in this Picture. – LamaFahrrad Mar 09 '21 at 20:01
@LamaFahrrad hmm are you sure that date wasn't there before ? The code is not supposed to add the date there. Remove it and try again . – Marios Mar 09 '21 at 20:07
Does it work for you? I tried it again with the same problem. – LamaFahrrad Mar 09 '21 at 20:18
@LamaFahrrad replace `rowIndex` with `rowA`. – Marios Mar 09 '21 at 20:38
Ok in which line? – LamaFahrrad Mar 09 '21 at 20:49
@LamaFahrrad I would say in every place . – Marios Mar 09 '21 at 20:52
unfortunately that doesn't change anything. – LamaFahrrad Mar 09 '21 at 21:23
1

@LamaFahrrad replace also `targetSheet.getLastRow()` with `rowA-1` . If this does not work share a copy of your sheet – Marios Mar 09 '21 at 21:32
1

Now its working but only with rowA not -1. Thx – LamaFahrrad Mar 09 '21 at 21:39

score 0 · Answer 2 · answered Mar 09 '21 at 19:13

0

it would be easier to change the formula for Time to not display anything at all unless there was an entry, then getLastRow() will work just fine. For help with that, share the formula that's calculating the time.

answered Mar 09 '21 at 19:13

MattKing

7,373
8
13

Formula to calculate the time is just =D2-C2 and so on in the remaining lines. – LamaFahrrad Mar 09 '21 at 19:22
Try this instead: =IF(D2,D2-C2,) – MattKing Mar 09 '21 at 20:45
unfortunately that doesn't change anything – LamaFahrrad Mar 09 '21 at 21:23

getLastRow() affecting other cells

2 Answers2