CODEWITHSUNDEEP
google-apps-script
MAbig11
MAbig11

Reputation: 51

How to show/hide rows in Google Sheet depending on whether check box is ticked

I'm trying to write the appscript to hide rows depending on whether a checkbox in a certain cell is checked.

I basically have 4 different checkboxes on one tab in cells D35, E35, F35, G35. I then have 4 different sections of questions on the second tab. Depending on which box/boxes are checked on the first tab I want to show/hide different row numbers on the second tab.

I've been following different scripts online and came up with the below but it isn't working.

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getActiveSheet();
  var inSheet = spreadsheet.getSheetByName('2');
  var outSheet = spreadsheet.getSheetByName('3');
  var a = inSheet.getRange('D35').getValue();
  var b = inSheet.getRange('E36').getValue();
  var c = inSheet.getRange('E37').getValue();
  var d = inSheet.getRange('E38').getValue();

  if(inSheet == activeSheet){
    var cell = spreadsheet.getActiveCell()
    var acell = cell.getValue();

    if(acell == a){
      if(acell.toString() == TRUE){
        outSheet.hideRows(25,3)  
      }
    }

    if(acell == b){
      if(acell.toString() == TRUE){
       outSheet.hideRows(32,3) 
      }
    }

   if(acell == c){
      if(acell.toString() == TRUE){
        outSheet.hideRows(39,3) 
      }
    }

   if(acell == d){
      if(acell.toString() == TRUE){
       outSheet.hideRows(46,3) 
      }
    }
 }}

Upvotes: 0

Views: 1477

Answers (1)

Iamblichus
Iamblichus

Reputation: 19319

  • You have 4 checkboxes in one sheet (I'll call it Origin), in cells D35, E35, F35, G35.
  • You have 4 groups of rows in another sheet (I'll call it Destination), which are 25-27, 32-34, 39-41, 46-48.
  • You want to hide/show a group of rows every time a checkbox is checked/unchecked.

An onEdit trigger can be used to accomplish all this. It would check whether the edited cell is one of the checkboxes, and if that's the case, it will hide/show the corresponding group of rows. So it checks (1) which sheet is edited, (2) which row is edited and (3) which column is edited.

Then, the code would check which specific checkbox was edited and hide/show the corresponding group of rows.

So, the code could be something on the following lines:

function onEdit(e) {
  var ss = e.source;
  var range = e.range;
  var editedSheet = e.source.getActiveSheet();
  var originSheetName = "Origin";
  var destSheet = e.source.getSheetByName("Destination");
  var editedRow = range.getRow();
  var editedCol = range.getColumn();
  if (editedSheet.getName() == originSheetName && editedRow == 35 && editedCol > 3 && editedCol < 8) {    
    var checkbox = range.getValue();
    if (editedCol == 4) {
      if (checkbox) {
        destSheet.hideRows(25, 3);
      } else {
        destSheet.showRows(25, 3);
      }
    }
    if (editedCol == 5) {
      if (checkbox) {
        destSheet.hideRows(32, 3);
      } else {
        destSheet.showRows(32, 3);
      }    
    }
    if (editedCol == 6) {
      if (checkbox) {
        destSheet.hideRows(39, 3);
      } else {
        destSheet.showRows(39, 3);
      }    
    }
    if (editedCol == 7) {
      if (checkbox) {
        destSheet.hideRows(46, 3);
      } else {
        destSheet.showRows(46, 3);
      }    
    }
  }
}

Now, this code has a fair amount of repetition, because it repeats the process for each of the 4 checkboxes. This could be improved and the code could be minimized significantly if the indexes of the different rows to hide/show followed a certain pattern. Fortunately, it looks like these rows do follow a certain pattern: each group of rows consists of 3 rows and there is a difference between each group and the next.

So, a shorter and more efficient alternative would be the following:

function onEdit(e) {
  var ss = e.source;
  var range = e.range;
  var editedSheet = e.source.getActiveSheet();
  var originSheetName = "Origin";
  var destSheet = e.source.getSheetByName("Destination");
  var editedRow = range.getRow();
  var editedCol = range.getColumn();
  if (editedSheet.getName() == originSheetName && editedRow == 35 && editedCol > 3 && editedCol < 8) {    
    var checkbox = range.getValue();
    var index = 7 * (editedCol - 4) + 25;
    if (checkbox) {
      destSheet.hideRows(index, 3);
    } else {
      destSheet.showRows(index, 3);
    }
  }
}

In this line, the index of the first row to hide/show is calculated based on the index of the edited column, so there is no need to repeat the process for all checkboxes:

var index = 7 * (editedCol - 4) + 25;

Bear in mind that I called the sheets Origin and Destination. Please change these according to your preferences.

I hope this is of any help.

Upvotes: 1

Related Questions