How to get the last row that has a value from a specific column which don't have the same amount of rows with values as other columns

Question

	A	B
1	A	Yes
2	B
3	C	No
4	D	Yes
5	E
6	F
7
8

What script can be used to find the last row ID for the "B" column? I can't use the "getLastRow()" because it will return the ID of the "A" column (in this case because A has more rows with values) I can't use "filter(String).length" because "B" column has multiple empty cells

The correct cell that should be return is B4

Answer 1

You can try doing a reverse search only on B column values.

The idea is to fetch the range values, work with them in memory then find the index of the last populated cell.

With this index, you can get the A1 notation from the Range instance.

Sample code:

function findLastPopulatedA1Ref(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var bcolRange = ss.getRange("B2:B30"); //Getting B column range, arbitrary size. Modify it accordingly
    var bcolVals = bcolRange.getValues(); 

    var i; //declaring the index outside loop to reuse it later
    for (i = bcolVals.length - 1; i >= 0; i--) { //reversing the loop (end to start) to find the first non-empty cell
        if(bcolVals[i] != ""){
          console.log(`${i}: bcol[i] == ${bcolVals[i]}`); //(optional) Just for debugging purposes
          break; // Forcing the end of the for loop, thus keeping the desired index value in i
        }
    }
    if (i == -1){
      console.log("Warning: B column is empty"); //(optional) Just for debugging purposes
      return null; //B Column is empty
    }
    var result = bcolRange.getCell(i+1, 1).getA1Notation(); //getCell starts at (1,1) and js arrays starts at 0. That's the reason to add 1.
    console.log(result); //(optional) Just for debugging purposes
    return result;
}