How do I resolve the getrange error "The number of rows in the range must be at least 1"?

Question

The below line of code is throwing an error var FillDownRange = bp.getRange(2, 6, lr-1);

Error Message

Exception: The number of rows in the range must be at least 1.

Why would I be receiving this error message?

Here is the full function:

function ProcessBlueprint() {
  //Formula: What is the top query for the URL?
  bp.getRange("F2").setFormula("=IFERROR(VLOOKUP(C2,GSC!$A$2:$F,2,false),)");
  var FillDownRange = bp.getRange(2, 6, lr-1);
  bp.getRange("F2").copyTo(FillDownRange);
}

Your "lr" variable is probably 1 (or less). you can try using debugger or logger to check it. — MRB, Jan 29 '21 at 16:35

Marios · Answer 1 · 2021-01-29T16:47:42.663

Issue:

lr-1 is smaller than 1. Make sure your sheet has at least two rows.

Right now it has either 0 or 1, assuming that lr is calculated by getLastRow().

Solution:

You can avoid the issue with a simple if condition:

  bp.getRange("F2").setFormula("=IFERROR(VLOOKUP(C2,GSC!$A$2:$F,2,false),)");
  if(lr>1){
    var FillDownRange = bp.getRange(2, 6, lr-1);
    bp.getRange("F2").copyTo(FillDownRange);
  }

Another idea would be to use 2 if the last row is 0 or 1:

  bp.getRange("F2").setFormula("=IFERROR(VLOOKUP(C2,GSC!$A$2:$F,2,false),)");
  const fr = lr>1?lr:2;
  var FillDownRange = bp.getRange(2, 6, fr-1);
  bp.getRange("F2").copyTo(FillDownRange);

Make sure to understand what the arguments inside getRange represent:

Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?

How do I resolve the getrange error "The number of rows in the range must be at least 1"?

1 Answers1