Google Sheets Scripts: Solving The Last Row Problem

If you ever tried to get the last filled-in row in a Google Sheets using a script, you probably know that the result can be confusing.

The method getLastRow() returns the last row with content in the sheet.

But this method is not useful when looking for the last row of a specific column.

Let’s look at the table below:

Row	A	B
1	Apple	20
2	Banana	30
3	Orange
4	Kiwi
5	Mango

Here we have no blank cells in the range of B:B. We’ll deal with an array with blank spaces later in this blog post.

In the below code we use the getLastRow() method:

function getLastRow() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  let sheet_last_row = ss.getLastRow()
  let column_B_last_row = ss.getRange('B:B').getLastRow();
  console.log(sheet_last_row) // <--- returns 5
  console.log(column_B_last_row) // <--- returns 1000
};

sheet_last_row returns 5, the last row in the sheet with a value.

column_B_last_row returns 1000 (the default number of rows in a sheet; it return the last row in the sheet, empty or filled-in). That’s not what we expected.

We need to find a way to get just the last filled-in row. To do so, we’ll use the JavaScript method indexOf and to find the first blank cell in our B:B range:

let column_B_last_row = ss.getRange('B:B')
    .getValues()
    .flat()
    .indexOf('');

Now, column_B_last_row returns 2.

Let’s break it down:

We specified the range we want to query with getRange(‘B:B’),
Then we get all the values with getValues().
We get an array of arrays: [ [ 20 ], [ 30 ], [ ” ], …]
We used the JavaScript method flat() to reduce our array of arrays to a simple array: [ 20, 30, ”,…]
We found the first blank cell with the method indexOf that returns the first index of a blank cell (”).

Get the Last Row In a Column With Blank Cells

What if we have a column with blank cells in-between values and we want to find the last filled-in cell in that column? Using indexOf(”) as we used before will not work for us now. This time we’ll get the last filled-in cell before the first blank cell, though there are filled-in cells down below.

See the table below:

Row	A	B
1	Apple	20
2	Banana
3	Orange	40
4	Kiwi	50
5	Mango

function getLastRow() {
 let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 let array = ss.getRange("B:B").getValues().flat();
  
  var lastRow;
  array.map((value, index) => {
    if (String(value).length > 0) {
      lastRow = index
    }
 })
 lastRow = lastRow +1;
 console.log(lastRow)
};

Let’s break it down:

array: we get all the values in the desired column, and then use the flat() method to reduce our array-of-arrays to a simple array.
We declare an empty variable of lastRow where we are going to store the result.
array.map – we use the map() method to go over each element in the array. The map method can get an optional argument of index. We are using it in our loop that comes right after:
If the value’s length is greater than 0 (not a blank cell), we set the lastRow as the index number. As our iteration continues, the lastRow always holds the greatest value. We convert the cell value to a string with String(value) to make sure our method works with both strings (“apple”) and numeric values (20).
We add 1 to the end result with lastRow = lastRow +1.
Why? Because JavaScript array is on a zero-scale (0,1,2…) while the rows in Google Sheets are on a one-scale (1,2,3…). To adjust the result, we add 1.

Hope these methods of finding the last cell in a column work for you. Just like we used these scripts to find the last value in a row, we can adjust the code to find the last value in a column.

Get the Last Row In a Column With Blank Cells

Leave a comment Cancel reply