0

I have designed a spreadsheet that has about 150 hidden rows and only 3 blank rows shown for the user. Every time the user enters an input in any of the 3 blank rows, the onEdit trigger automatically unhides the first row of the hidden range, and so on.

Now, I am using the below code to hide the rows from C20 to C167 only if they are blank. And it is intended that the code leaves every time three empty rows as visible (after the last non-blank row).

The problem is that this script is too slow because it checks the whole range every time (Row-by-Row), although the majority of the rows is already hidden. is there a way to hide all the blank rows as (one bulk)? i.e. stored in the undo history as one action.

otherwise, is there a way to make the script skips the rows that are already found as hidden?

I think the idea maybe around something like:

If ( sheet. isRowHiddenByUser (C20:C167) == True) {return}

My script is as follows:

function onEdit(e) {
  const sh=e.range.getSheet();
  const hr=20;
  if(sh.getName()=='PM' && e.range.rowStart>hr){
  sh.showRows(e.range.rowStart+1);
  sh.showRows(e.range.rowStart+2);
  sh.showRows(e.range.rowStart+3);
  }
}

function hideRo() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var Sheet = ss.getSheetByName("PM");
    var range = Sheet.getRange("C20:C167")
    var LastRow = range.getLastRow();
    for (i = 20; i <= LastRow; i++) {
      var statusRange = Sheet.getRange("C"+i);
      if (statusRange.getValue() == 0) {
        Sheet.hideRows(i+3);
      }
      }
    }
Rubén
  • 34,714
  • 9
  • 70
  • 166
mahmoud hedaya
  • 1

1 Answers1

0

Your code is slow indeed because you're reading and updating your sheet repeatedly.

It should be more like this:

function hideRo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = ss.getSheetByName("PM");
  var range = Sheet.getRange("C20:C167")
  //create an array with the values in that range
  var arr = range.getValues();
  // abandon: var LastRow = range.getLastRow();
  //prepare an array for rownumbers to be hidden:
  var toHide = new Array();
  //in internal memory, count from row 0 to last row of arr:
  for (i = 0; i < arr.length; i++) {
    //first element on each row of arr needs to be checked if 0:
    if(arr[i][0] == 0) {
      //store rowNumber to be hidden in toHide array:
      toHide.push(i+20);
      }
    }
  //when arr is done, continue to hide rows:
  for(var i = 0; i < toHide.length; i++){
    //element with index i contains the rowNumber to be hidden:
    Sheet.hideRows(toHide[i])
  }

}

Hope it helps, I have tried this one out and works at my side.

user9303562
  • 38
  • 7