Protect specific range by getting last row containing data with possible empty rows in between

Question

I'm trying to protect a specific range until the last row, but after trying to consider the possibility of having empty rows in the way, I couldn't get the expected result.

In my example: I'm trying to protect a range of A:D and since the last row containing data is C24, the script should protect A2:D24. Column F (and anything else) is ignored in my script.

Screenshot:

I'm getting the following error: Exception: The number of rows in the range must be at least 1

for line 30 corresponding to var rangeToProtect = ss.getRange(2,1,(lr-1), 4);

My script:

function getLastRow(sheet,rangeString) {

  var rng = sheet.getRange(rangeString).getValues();
  var lrIndex;

  for(var i = rng.length-1;i>=0;i--){

    lrIndex = i;

    if(!rng[i].every(function(c){ return c == ""; })){

      break;

  }
  }

  var lr = lrIndex + 1

  Logger.log(lr);

}


function protectData(){

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var curDate = Utilities.formatDate(new Date(), "GMT", "EEE d MMM yyyy HH:mm:ss")
  var lr = getLastRow(ss,"A2:D");
  var rangeToProtect = ss.getRange(2,1,(lr-1), 4);
  var protection = rangeToProtect.protect().setDescription(curDate);
  protection.removeEditors(protection.getEditors());

  }

// Function edited with suggestions:

function protectImportedData(){

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var curDate = Utilities.formatDate(new Date(), "GMT+3", "EEE d MMM yyyy HH:mm:ss")

  var Direction = SpreadsheetApp.Direction;
  var aLast =ss.getRange("A1:D"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

  var rangeToProtect = ss.getRange(2,1,(aLast-1), 4);
  var protection = rangeToProtect.protect().setDescription(curDate);
  protection.removeEditors(protection.getEditors());

  }

@TheMaster - Isn't that going to remove blanks that aren't at the end and then report a lower number than the actual height. — Cooper
This var Avals = ss.getRange("A1:A").getValues(); var Alast = Avals.filter(String).length; — Cooper
@Cooper OP is expected to read and test all the answers. Not just the top voted ones and choose the ones best suited for his situation. Specifically, I believe this should work. — TheMaster

Nabnub Nabnub · Accepted Answer · 2020-04-02T21:32:35

Thanks to @TheMaster for pointing out the error, the Function getLastRow was not returning anything,

The code works if we change this line:

var lr = lrIndex + 1

to:

return lrIndex + 1

Also we don't need the -1 in:

var rangeToProtect = ss.getRange(2,1,(lr-1), 4);

Protect specific range by getting last row containing data with possible empty rows in between

1 Answers