Intro

I am writing this short blog to share a solution to a problem that has been bugging me for a while, which is to find the last row in a column accurately.

Problem

Usually, when you use google apps script and want to find the last non-empty row we use something similar to the following code.

//...... 
// after certain codes
//last row
let lastRow = sheet
      .getRange(1, 1)
      .getNextDataCell(SpreadsheetApp.Direction.DOWN)
      .getRow();

If you've used this code then you know that this doesn't always work.

Helper Function

But first, a function to get all columns in a spreadsheet.

function getColumnNames() {
  let spreadsheet = SpreadsheetApp.openById(SPRD_ID);
  let activeSheet = spreadsheet.getSheetByName(SHEET_NAME);
  let lastCol = sheet
    .getRange(1, 1)
    .getNextDataCell(SpreadsheetApp.Direction.NEXT)
    .getColumn();

 // find the corrent index of url column
  let columns = activeSheet.getRange(1, 1, 1, lastCol).getValues().flat();
  console.log(columns)
  return columns;
}

---

Solution

So, this solution in stack overflow suggests using the logic of finding the last non-empty row starting from the bottom. I have modified this code to suit my needs.

Then the following code can calculate the last row of a column.

function getUrlColumnNRowNumber(colName = "Email") {

  try {
    let spreadsheet = SpreadsheetApp.openById(SPRD_ID);
    let activeSheet = spreadsheet.getSheetByName(SHEET_NAME);

    // find the corrent index of url column
    let columns = getColumnNames();
    let ind = columns.findIndex((val) => val.toLowerCase().trim() === colName.toLowerCase().trim());

    // check if -1 is returned else increase index by 1 to match with index system of spreadsheet
    let colIndex = ind === -1 ? null : ind + 1;
    if (colIndex === null) throw { "error": "No Column Name Found" };

    let maxRows = activeSheet.getMaxRows();
    // ger all the values with emtpy rows
    let columnRaw = activeSheet
      .getRange(2, colIndex, maxRows, 1)
      .getValues()
      .flat();

    let columnRawReverse = [...columnRaw].reverse();
    // find first non empty value  in the list 
    let firstNonEmptyCell = columnRawReverse.findIndex((el) => el !== "");

    // subtract it with maxrows and add 1 becuase range started from 2.
    //  if all rows are empty then return 2 
    let lastRowInCol = firstNonEmptyCell !== -1 ? maxRows - firstNonEmptyCell + 1 : 2;
    console.log([colIndex, lastRowInCol]);
    return [colIndex, lastRowInCol];
  }
  catch (e) {
    if (e.error === "No Column Name Found") {
      console.log("No column found");
      return [null, null]
    }

  }
}

If you've understood the code then no need to read further.

---

Explanation

Let's go over some minor details:

  // find the corrent index of url column
    let columns = getColumnNames();
    let ind = columns.findIndex((val) => val.toLowerCase().trim() === colName.toLowerCase().trim());

   // check if -1 is returned else increase index by 1 to match with index system of spreadsheet
    let colIndex = ind === -1 ? null : ind + 1;
    if (colIndex === null) throw { "error": "No Column Name Found" };

Here, we get column names i.e, the first row from the spreadsheet. Then, on the second line, we check if the column name exists in the spreadsheet if not we are throwing an error, however, if it exists then we return the column index.

---

    let maxRows = activeSheet.getMaxRows();
    // ger all the values with emtpy rows
    let columnRaw = activeSheet
      .getRange(2, colIndex, maxRows, 1)
      .getValues()
      .flat();

    let columnRawReverse = [...columnRaw].reverse();

Now, we get the max number of rows in the current spreadsheet. Get the values in the column we're checking. The reverse is done to implement the findIndex() method next. Remember we're fetching values from the second row so, if the column is empty we have to consider this as well.

---

  // find first non empty value  in the list 
    let firstNonEmptyCell = columnRawReverse.findIndex((el) => el !== "");

Here, we use the findIndex() method to find the first non-empty value in the reversed list.

---

  // subtract it with maxrows and add 1 becuase range started from 2.
    //  if all rows are empty then return 2 
    let lastRowInCol = firstNonEmptyCell !== -1 ? maxRows - firstNonEmptyCell + 1 : 2;

The findIndex() returns -1 if the condition is not satisfied and if so then, we'll return 2 as the last row. Else, just subtract the index returned, with max rows, and then add 1(because we fetched the value from the second row).

Message from the Future

I have written the second part or improved this blog, where I've provided a new way to find the last row and column in a spreadsheet. If you're interested check out this new blog.

Thank You

This is Nibesh Khadka from Khadka's Coding Lounge. Find my blogs here. Please do like and share if you like my work. Also, subscribe to get notified about the next upload.

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me know directly or from fiverr or upwork.

Thank you for your time.