I have an issue with getLastRow(). I thought this function is supposed to return the last row that has data. But since the worksheet that I am working on has a lot more vacant rows below the rows filled with data, the functions seems to go in and select the last vacant row on its execution instead of selecting the last row which has data, which may not be the same. Can some one tell me how to fix this anomaly. Is this a google script bug or am I not clearly understanding what to do? Please ask further questions if I didn't make myself clear.
I have an issue with getLastRow(). I thought this function is supposed to return the last row that has data. But since the worksheet that I am working on has a lot more vacant rows below the rows filled with data, the functions seems to go in and select the last vacant row on its execution instead of selecting the last row which has data, which may not be the same. Can some one tell me how to fix this anomaly. Is this a google script bug or am I not clearly understanding what to do? Please ask further questions if I didn't make myself clear.
Share Improve this question edited Nov 26, 2018 at 2:29 Wicket 38.3k9 gold badges77 silver badges192 bronze badges asked Jun 4, 2012 at 19:56 Rijo SimonRijo Simon 7873 gold badges15 silver badges36 bronze badges4 Answers
Reset to default 11Do you have any "whole-column" formulae in the sheet? Either array formulae, or formulae that are copied all the way down? Even if these formulae are designed to return blank cells down the bottom, getLastRow() will still catch them.
If this is the case, try:
function getLastPopulatedRow(sheet) {
var data = sheet.getDataRange().getValues();
for (var i = data.length-1; i > 0; i--) {
for (var j = 0; j < data[0].length; j++) {
if (data[i][j]) return i+1;
}
}
return 0; // or 1 depending on your needs
}
Here is another version of the currently selected answer. This swaps out the inner loop for an array join with no spaces between items. This joins all characters on the row. If there is a character in any position it will kick out similar to the current answer.
I did some speed tests, and while I thought the chosen answer might be faster, for some reason this seems to process faster based on my tests. I believe I found this concept a few years ago here on stack overflow, but not seeing that reference now.
function getLastPopulatedRow(sheetX) {
var arrVals = sheetX.getDataRange().getValues();
for (var i = arrVals.length-1; i > 0; i--) {
if (arrVals[i].join('')){
return i+1
}
}
return 0;
}
A simpler way is
var lastRow = sheet.getDataRange().getValues().length ;
while Adams solution will work with some tweaking, I have e across another solution to this issue with some searching. I saw a code that was supposed to find the first empty row, and I figured that I could simply return the row behind the first empty row to get the last populated row. I am attaching the code here:
function getLastPopulatedRow(sheet) {
var cell = sheet.getRange('a1');
var ct = 0;
while ( cell.offset(ct, 0).getValue() != "" ) {
ct++;
}
return (ct--);
}