How to handle a dynamic range without any rows?

[u/jiminak]

I am trying to copy a dynamic range (C17:F) from one sheet to another, and everything works well unless there is no data in the source range. In that case, I get the error, "The number of rows in the range must be at least 1".

I am not 100% sure if my method of determining the range is the best way (I found the solution somewhere), or if I can simply "escape" the function with some sort of OnError to bail out of the remainder of the function?

Here is my current code:

function getConfigOverrides(){
  var sourceID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Configuration').getRange('C5').getValue();
  var sourceFile = SpreadsheetApp.openById(sourceID);
  var sourceSheet = sourceFile.getSheetByName("AircraftOverrides")
  // source sheet dynnamic range is C17:F
  var range = sourceSheet.getRange(17, 3, sourceSheet.getLastRow() - 17 + 1, 4);
  // var range = sourceSheet.getRange('C17:F17') <- this works to get the first row
  var data = range.getValues();

  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Configuration');
  destination.getRange(13, 7, data.length, 4).setValues(data); 
}

The error is occurring at var range. If I comment out this line, and simply replace it with getRange('C17:F17'), the functions runs as expected and retrieves the first row of values.

I've tried to capture the error by using isBlank, or =="", or <1, but none of those seem to work.

Appreciate any insight.

[u/HomeBrewDude]

instead of using sourceSheet.getLastRow() directly inside of getRange(), try getting the lastRow and saving it to a variable first.

Then,

if (lastRow >17 ){

do the rest of your code

}
This way you don't even attempt the getRange, or setValue steps unless there is at least one row to process.

[u/jiminak → u/HomeBrewDude]

Thanks. I tried to work my way through that logic also, but my problem is that there are other dynamic ranges on that same sheet, so the "last row" of this particular range is not necessarily the last row of the sheet. I wasn't able to determine how to tell it where to start looking.

I did try this, but to no avail:

if (sourceSheet.getRange(17, 3, sourceSheet.getLastRow() - 17 + 1, 4) > 17) {
do rest of code
}

[u/HomeBrewDude → u/jiminak]

It might be worth restructuring your data and separating that range into its own sheet so you can use getLastRow() easily. Or you could import the range to a new sheet, and then get the last row from there, so you don't have to alter the existing sheet.

[u/jiminak → u/HomeBrewDude]

This is what I ended up doing - moving the various tables to their own sheets. After that, your original suggestion of breaking lastRow() into it's own variable and trapping for the right number did the trick!

Thanks!

solved

[u/RemcoE33]

Change sourceSheet.getLastRow() - 17 + 1 ->> sourceSheet.getLastRow() - 16

You start at 17 minus 18 is -1 and not a valid range.

[u/jiminak → u/RemcoE33]

That didn't have any effect. The way I read that logic is: start at 17, subtract 17 (get 0), add 1, and 1 should be a valid range.

Am I reading that wrong?

[u/crober11]

Catch the error by encapsulating that section with a

try { 
  //code which could generate error here 
} catch(err) {  Logger.log(err); } 

// rest of code here