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, Apr 02 '20 at 19:24
This `var Avals = ss.getRange("A1:A").getValues(); var Alast = Avals.filter(String).length;` — Cooper, Apr 02 '20 at 19:30
@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](https://stackoverflow.com/a/51473352/) should work. — TheMaster, Apr 02 '20 at 19:31
@Cooper It's not a bother. Of the answers there, that [one](https://stackoverflow.com/a/51473352/) was actually the fastest of all(last i checked) and I recommend it. — TheMaster, Apr 02 '20 at 19:47
Thanks to you guys, I've come across this link yesterday and I've tried some of the suggestions, but I've skipped the one you're referring to. How do I reference range from A to D in: ss.getRange("A"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getRow(); — Nabnub, Apr 02 '20 at 19:54
Which I've tried before asking, but I'm getting the same error (The number of rows in the range must be at least 1) — Nabnub, Apr 02 '20 at 20:01
@TheMaster, I've tried something similar and tried your suggestion as well, but it stops at row 21, in another word it's considering only column A (Protected: Sheet1!A2:D21) — Nabnub, Apr 02 '20 at 20:24
@TheMaster, by the way you were right the function `getLastRow` was returning nothing, silly me!! I'm still curious to find out how your suggestion could be implemented — Nabnub, Apr 02 '20 at 20:37
Tested a bunch of things. it seems getNext* or getDataRegion* doesn't work for this. I'll undo my duplicate vote. Looping is probably the only way. If adding a `return` fixes your code, consider adding it as a answer. — TheMaster, Apr 02 '20 at 20:49

score 1 · Accepted Answer · answered Apr 02 '20 at 21:32

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 Answers1