0

I want to update the next rows in a spreadsheet with some data and for that, I need to find the last row. However, I need to get the last value in column A and not other columns. The function below works well and gets me the last row in column A that has value but returns that one and I need the row below the one with the value.

function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A" + lastRow);

  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
   }              
}

I am new to both JavaScript (python user) as well as Google Apps Script so I could really use some help with this as I have been trying all evening to get the first row in column A that is empty after the one with the value. For example if A20 has the last value, I want to return 21 and not 20.

David Dostal
  • 711
  • 13
  • 17
Matija Kordic
  • 3
  • 3
  • I use [getColumnHeight](https://stackoverflow.com/a/55425107/7215091) – Cooper Sep 17 '21 at 23:13
  • How about this thread? https://stackoverflow.com/q/44562592 – Tanaike Sep 18 '21 at 02:53
  • Hi Tanaike, thanks but I already have a way of finding the first non-empty cell searching from bottom. I just don't know how to get the next row, the one that is empty. It is that specific part I am interested in. You are giving me a valid solution that could be used but not really an answer I am asking for. Still, thank you. – Matija Kordic Sep 19 '21 at 18:42
  • 1
    The solution I used was quite simple but very ugly, it is instead of var target = "A" + getLastDataRow ( sheet ); I used var target = "A" + (getLastDataRow ( sheet )+1); – Matija Kordic Sep 19 '21 at 18:47

1 Answers1

0

Basically, I was using the following code to get a range and update the cells with new content:

if (projdoc.match(regExp)) { 
projdocnum = regExp.exec(projdoc)[1]; 
var target = "A" + (getLastDataRow ( sheet )+1); 
var regExp2 = new RegExp("[A-Za-z]+([0-9]+)","g"); 
var targetnum = regExp2.exec(target)[1]; var rangesheet = sheet.getRange(target+":D"+targetnum);
rangesheet.setValues([[ID, projectname, projdoc, projdocnum]]);
}

The only thing I changed was in earlier versions I had :

var target = "A" + (getLastDataRow ( sheet )); and I just added +1 to it to get the next row.
Matija Kordic
  • 3
  • 3