Hello! I wrote a blog on finding the a couple of months ago. Since then, I have found a better, faster, and easier method, which works in . last non-empty rows most cases Find the Last Row & Column You can copy and paste one of the following functions as per your needs. is meant for a bound script, is very easy to implement, and works on the currently active worksheet. FindLastRowNColBoundScript(), should be used with an independent script. The given code is very simple. You can modify this function and pass variables such as ID and Worksheet names as parameters to make this function more dynamic. FindLastRowNColIndependentScript() /**
 * Function should be used for bound script.
 * Function use getDataRange().getValues() method to 
 * find lastRow and LastColumn of that Spreadhseet.
 */
function findLastRowNColBoundScript() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;
  const lastCol = data[0].length;

  console.log(lastRow);
  console.log(lastCol);

  return [lastRow, lastCol];
}

/**
 * Function should be used for independent script.
 * Function use getDataRange().getValues() method to 
 * find lastRow and LastColumn of that Spreadhseet
 */
function findLastRowNColIndependentScript() {
  const ss = SpreadsheetApp.openById("<Your Spreadsheet's ID>");
  const sheet = ss.getSheetByName("<Name of the worksheet>");

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;
  const lastCol = data[0].length;

  console.log(lastRow);
  console.log(lastCol);

  return [lastRow, lastCol];
}


/**
 * This script is for finding last non empty rows and columns in a spreadsheet. 
 * This works in most cases but not in some cases such as columns with checkboxes
 * It is created in such a way that it only works with bound script.
 *
 * Created by: Nibesh Khadka.
 * linkedin: https://www.linkedin.com/in/nibesh-khadka/
 * website: https://nibeshkhadka.com
 */ This strategy of using () to find the last row and columns works in most cases but few such as columns filled with checkboxes on their entire rows. DataRange Thank You for Your Time My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. If you need my services, let me . know Don’t forget to like and share this blog. Also published . here

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

How to Get the Last Non-empty Row of a Column of a Spreadsheet Accurately

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps