Hello!, I wrote a blog on finding the last non-empty rows a couple of months ago. Since then, I have found a better, faster, and easier method, which works in most cases.
Find the Last Row & Column
You can copy and paste one of the following functions as per your needs.
FindLastRowNColBoundScript(), is meant for a bound script, is very easy to implement, and works on the currently active worksheet.FindLastRowNColIndependentScript()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.
/**
* 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 DataRange() to find the last row and columns works in most cases but few such as columns filled with checkboxes on their entire rows.
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.
Top comments (0)