getLastRow() not working in my script. I am trying to return the last row that has data. But since the spreadsheet has empty rows below the rows filled with data, the script returns with all the rows but I want it to stop on the last row that has the data. Can someone tell me how to fix this? The data contains the array formula and here is the script:-

function Productivity() 
{
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Do you want to send emails to all PRICE ACCURACY resources now?', ui.ButtonSet.YES_NO);
  // Process the user's response.
  if (response == ui.Button.YES) 
  {
    
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Productivity Report Out')
    var range = ss.getDataRange().getValues()
    var lastrow = ss.getLastRow();
    //Logger.log(range.length)
    for(var i=1;i<=lastrow; i  ) //i<range.length

    {
      Logger.log("Index:" i " | [" range[i] "]")
      var status =  range[i][14]
      if(status !='No')
      {
        var name =  range[i][0]
        var ldap = range[i][1]
        var target = range[i][2]
        var new_mvt = range[i][3]
        var onboarding = range[i][4]
        var ubio_rates =  range[i][5]
        var violation = range[i][6]
        var reevaluation = range[i][7]
        var total = range[i][8]
        var current_productivity = range[i][9]
        var deficit = range[i][10]
        var remaining_checks = range[i][11]
        var week = range[i][12]
        var date = range[i][13]

CodePudding user response：

You define lastRow but don't use it.

I'm guessing this is what you're looking for:

for(var i=1; i<=lastRow; i  )

As designed, .getLastRow() returns the last row that has data. Or from documentation: Returns the position of the last row that has content.

• Sheet.getLastRow()

Side note: You're beginning your loop at index 1 (row 2), if you do not have a header row, you should use 0 (row 1).

CodePudding user response：

Getting blank rows in your data

if(status !='No') would be true for a blank line. Perhaps you are using something like an Array Formula that is causing the getLastRow() calculation to be incorrect. Do a Logger.log(lastRow) and see if it makes sense.

Try this function:

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
  //const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
  //Logger.log(h);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}