Although I don't know whether this is a good method, how about this method? This method doesn't use the loops. Please check this as one of samples.
- Retrieve the column data that you want to know the number of last row.
- Import the column data to a new spreadsheet as a temporary sheet. (In this case, you can also add a new sheet to the spreadsheet you currently use and it can be used as a temporary.)
- Retrieve the number of last row using
getLastRow().
- Remove the temporary spreadsheet.
Sample Script :
var col = ##; // Here, please input a column number.
var ss = SpreadsheetApp.getActiveSheet();
var coldata = ss.getRange(1, col, ss.getLastRow(), 1).getValues();
var tempss = SpreadsheetApp.create("temporary_sheet");
tempss.getActiveSheet().getRange(1,1,coldata.length, coldata[0].length).setValues(coldata);
var last_row = tempss.getLastRow(); // The number of last row
Drive.Files.remove(tempss.getId()); // In this case, the file is removed using Drive API.
Note :
In above case, the number of last row can be retrieved, even if the column has null cells. If the column has no null cells, you can retrieve the number of last row for a specific column by following script. This doesn't create a temporary sheet.
var last_row = ss.getRange(1, col, ss.getLastRow(), 1).getValues().filter(String).length;
Updated at May 19, 2021:
In this case, I would like to approach with the following 2 patterns.
Retrieving 1st empty cell of specific column by searching from TOP of sheet
Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet
In order to achieve above, I think that the following 2 patterns, can be used.
Retrieve the values of the column and search the result using the loop.
Retrieve directly the result using the built-in methods of Google Apps Script.
I measured the process cost of them. As the result, it was found that the following 2 scripts are lowest of all methods.
1. Retrieving 1st empty cell of specific column by searching from TOP of sheet
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
const range = this.getRange(offsetRow, columnNumber, 2);
const values = range.getDisplayValues();
if (values[0][0] && values[1][0]) {
return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else if (values[0][0] && !values[1][0]) {
return offsetRow + 1;
}
return offsetRow;
};
// Please run this function.
function main() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const res = sheet.get1stEmptyRowFromTop(3);
console.log(res); // Retrieve the 1st empty row of column "C" by searching from TOP of sheet.
}
- Please give the sheet object and column number.
- In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as
const res = sheet.get1stEmptyRowFromTop(3, 2);.
2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet
In this question, I thought that this pattern might be suitable.
Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
return search ? search.getRow() : offsetRow;
};
// Please run this function.
function main() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const res = sheet.get1stNonEmptyRowFromBottom(3);
console.log(res); // Retrieve the 1st non empty row of column "C" by searching from BOTTOM of sheet.
}
- Please give the sheet object and column number.
- In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as
const res = sheet.get1stNonEmptyRowFromBottom(3, 2);.
Result:
When above script is used, the following result is obtained.
![enter image description here]()
When the script of "Retrieving 1st empty cell of specific column by searching from TOP of sheet" is used for the column "C", the row 6 is obtained.
When the script of "Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet" is used for the column "C", the row 9 is obtained.
Reference:
