How do you Show/Hide Rows based on another cell? (Google Apps Script/Sheets)

Question

Goal: Click a button/select an option to hide/reveal a number of rows. Example, click/select on cell A4 that hides rows A5-A8 and another A45 that hides rows A46-A59. A button would be preferred but I'll take whatever. Bonus points if I can use a custom image.

I have a function that hides rows but it only works sometimes, could be slow (takes anywhere from 5s or 20s+, although I have 900 rows) or sometimes only hides some of the list regardless of length. I copied the function from somewhere else and I probably didn't implemented it in the best way, which might explain some problems, but here's how it works.

On B8 there's a dropdown list (data validation, list of items). There are two options, one is a blank (technically an inivisible character) and the other is "H". In column C, I copy this code in the specific cells I want to hide:

=IF(B8="H","Hide","Show")

And here is the script:

/** HideRow */
function onEdit()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  for( i=1 ; i<=lastRow ; i++) { // i <= lastRow

    var status = sheet.getRange("C"+i).getValue();  // C = the column that writes Show/Hide

    if (status == "Hide") {                                           // status == "Hide" 
         sheet.hideRows(i);
       } 
    if (status == "Show"){                                           // status == "Show" 
     sheet.showRows(i); 
    }
   }
}

I will be adding more rows over time and I don't know if the script keeps track of the cell when it moves, which is why I wrote a line of code in individual cells (maybe an array would fix slowdown due to redundancy?). Although if it works better in the script than by all means do it. Would a named range be useful in this case?

I've looked at some others and haven't had luck, mostly because other than the variables I'm not sure what else I would need to change.

Cooper Cooper · Accepted Answer · 2020-06-12T20:01:00

The onSelectionChange Trigger

You just have to make new selection and select either A4 or A45 and this will toggle the visibility of the specified rows. The point is that if you are already on one of the cells and you select it again that doesn't work there has to be a change in the selected range.

The good new is that you don't need any buttons or check boxes.

function onSelectionChange(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast('Entry');
  const C=['A4','A45'];//trigger ranges
  const R=[{row:5,count:4},{row:46,count:14}];//hide/show ranges with row and count for each
  const sh=e.range.getSheet();
  const idx=C.indexOf(e.range.getA1Notation());
  if(idx!=-1) {
    //e.source.toast('idx= ' + idx);
    if(sh.isRowHiddenByUser(R[idx].row)) {
      sh.showRows(R[idx].row,R[idx].count);
    }else{
      sh.hideRows(R[idx].row,R[idx].count);      
    }
  }
}

This version uses the trigger strings ~1~ and ~2~ and works with a relative offset of 1 row which is configurable

function onSelectionChange(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast('Entry');
  const T=['~1~','~2~'];
  const R=[{row:0,offset:1,count:4},{row:0,offset:1,count:14}];
  const sh=e.range.getSheet();
  const idx=T.indexOf(e.range.getValue());
  R[idx].row=e.range.getRow()+R[idx].offset
  if(idx!=-1) {
    //e.source.toast('idx= ' + idx);
    if(sh.isRowHiddenByUser(R[idx].row)) {
      sh.showRows(R[idx].row,R[idx].count);
    }else{
      sh.hideRows(R[idx].row,R[idx].count);      
    }
  }
}

another version:

function onSelectionChange(e) {
  e.source.toast('Entry');
  const obj={T:[],R:[new shrows(1,5,'~1~'),new shrows(1,5,'~2~'),new shrows(1,5,'~3~'),new shrows(1,5,'~4~')]};
  obj.R.forEach(function(e,i){obj.T.push(e.tstring)});
  const sh=e.range.getSheet();
  const idx=obj.T.indexOf(e.range.getValue());
  obj.R[idx].row=e.range.getRow()+obj.R[idx].offset
  if(idx!=-1) {
    //e.source.toast('idx= ' + idx);
    if(sh.isRowHiddenByUser(obj.R[idx].row)) {
      sh.showRows(obj.R[idx].row,obj.R[idx].count);
    }else{
      sh.hideRows(obj.R[idx].row,obj.R[idx].count);      
    }
  }
}

function shrows(offset,count,tstring) {
  this.offset=offset;
  this.count=count;
  this.tstring=tstring;
  this.row=0;
}

The bad news about this trigger is that there doesn't appear to be a installable version yet so you can't perform operations that require permission.

onSelectionChange()

How do you Show/Hide Rows based on another cell? (Google Apps Script/Sheets)

1 Answers