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, 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. this 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 , 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. i.e 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 method next. Remember we're fetching values from the second row so, if the column is empty we have to consider this as well. findIndex() // find first non empty value  in the list 
    let firstNonEmptyCell = columnRawReverse.findIndex((el) => el !== ""); Here, we use the method to find the first non-empty value in the reversed list. findIndex() // 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 returns -1 if the condition is not satisfied and if so then, we'll return 2 as the last row. Else, just subtract the returned, with max rows, and then add 1( ). findIndex() index because we fetched the value from the second row Thank You This is Nibesh Khadka from Khadka's Coding Lounge. Find my blogs . Please do like and share if you like my work. Also, subscribe to get notified about the next upload. here I can make Google Add-Ons and  also write Google Apps Scripts for you. If you need my services let me know. Thank you for your time. Also Published Here

2022 - Tech Tutorial Teacher of the Year

Subscribe To Our News Letter For Free Contents

Hire Me

Newsletter

Buy Me a Coffee

Nominated for 2022 - Tech Tutorial Teacher of the Year

Developer 

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

How to Find the Last Non-Empty Row in a Column with Google Apps Script Accurately

Too Long; Didn't Read

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

How to Find the Last Non-Empty Row in a Column with Google Apps Script Accurately

Too Long; Didn't Read

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES