How to optimize script selecting and creating Named Ranges?

Question

I'm working on a section of code to create named ranges of rows in a sorted Google Sheet, grouped by the value in the "offset" column of each row. I'm looking for the more efficient way of selecting these ranges. Below is what I currently have, any advice is appreciated!

function nameRanges(input, sheet, ss, offset) {
  var maxrows = sheet.getMaxRows();
  var numRows = input.getNumRows();
  sheet.deleteRows(numRows+1, maxrows-numRows);
  var sheetname = sheet.getName();
  var numCol = input.getNumColumns();

  for (var j = 2, x=2, y=3; i <= numRows -1, j <= numRows, x <= numRows; x++, y++) { 
    var jvalue = sheet.getRange(j,offset).getValue();
    var xvalue = sheet.getRange(x,offset).getValue();
    var yvalue = sheet.getRange(y,offset).getValue();
    var start = j;

    if (xvalue != yvalue) {
      var rangesize = sheet.getRange(j, 1, (y-j) , numCol);
      Logger.log("New NamedRange "+sheetname + jvalue +" created from "+ start + " to " +y);
      ss.setNamedRange (sheetname + jvalue, rangesize);
      rangesize.setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
      j = y;
    }
  }
}

noogui noogui · Accepted Answer · 2017-08-26T10:52:43

I would advise you to not declare your variables inside for loops. Declare them outside, in the beginning. There are also several Best practices for Apps Scripts that's too long to post here.

How to optimize script selecting and creating Named Ranges?

1 Answers