Google sheet get fasten array for loop

Question

Is it possible to fasten the hide of many rows when range.length > 300 rows ?

I also can't succeed moving the focus to the top of the sheet once the rows are hidden, I can only get the focus on another sheet.

Here is my code (french parameters), I'm not sure I need to show my spreadsheet. Thank you very much.

var LastRow = sheet.getLastRow()-1;
var ToHide = [];
for (var i=1 ; i < LastRow +1 ; i++){
    if ( sheet.getRange(i,1).isChecked() == null){ ToHide.push(i); }
}

for (var j=0 ; j<ToHide.length ; j++){ MyActiveSheet.hideRows(ToHide[j+1],1); }

ToHide.forEach(function (d){ FeuilleActive.hideRows(d); }); // also tried .hideRows(d,1)

SpreadsheetApp.getActive().getSheets()[5].getRange(1,1).activate(); //

score 1 · Accepted Answer · answered Jun 11 '21 at 23:02

To lessen the number of loops, you can group it by determining the series of consecutive numbers in your Array. Which you can use to determine the index and number of rows to hide. The number of .hideRows() execution will be determined by the number of series in your array. Thus, lesser runtime.

Example Code:

function myFunction() {
  var a = [1,2,3,4,6,7,8,9,11,13,14];

  const result = a.reduce((r, n) => {
    const lastSubArray = r[r.length - 1];
    if(!lastSubArray || lastSubArray[lastSubArray.length - 1] !== n - 1) {
      r.push([]);
    } 
    r[r.length - 1].push(n);
    return r;  
  }, []);
   
  //result output: [[1.0, 2.0, 3.0, 4.0], [6.0, 7.0, 8.0, 9.0], [11.0], [13.0, 14.0]]
  result.forEach(e => {
    var index = e[0];
    var numRows = e.length;
    Logger.log("Index: "+ index);
    Logger.log("numRows: "+numRows);
    // MyActiveSheet.hideRows(index,numRows);
  })  
}

Output:

Google sheet get fasten array for loop

1 Answers1

References: