Hide Rows Based on Check Boxes - Google App Script

Question

I'm trying to hide certain rows based on checkboxes that are checked in column 'C'.

Simply put if 'Option A' is checked, I only want to show rows 11 - 13, and row 22. If 'Option B' is checked. I only want to show rows 14 - 15, and row 22, etc.

I also would like to have them revert back after I uncheck. This is more of a learning exercise than anything, so I will get to 'checking multiple boxes' later.

I'm wondering if there should be some 'for' logic in there as well? I'm just not sure where...

Any help or recommendations would be greatly appreciated! Happy to get into more detail if need be.

Thanks a lot,

-M

Answer 1

function onEdit(e) {

    // Replace with the name of your sheet
    var sheetName = 'NameofYourSheet';

    // Align the trigger to the 4 checkboxes
    if (e.source.getActiveSheet().getName() != sheetName 
        || e.range.rowStart < 6 || e.range.rowStart > 11 || e.range.columnStart != 3) {
        return;
    }

    // Get checkbox values
    var sh = SpreadsheetApp.getActive().getSheetByName(sheetName),
        status = sh.getRange('C7:C10').getValues();

    // Crude helper func
    function showHideRow(v, i) {
        // [[row, numberRows]]
        var rows = [[11,3],[14,3],[17,3],[20,2]];

        return v[0]
               ? sh.showRows.apply(sh, rows[i])
               : sh.hideRows.apply(sh, rows[i])
    };

    status.forEach(showHideRow);
}

Answer 2

How about this modification? I think that there are several answers for your situation. So please think of this as one of them.

Modification points :

• Run the script using onEdit(). This was mentioned by @I'-'I.
• Retrieve the values of all checkboxes.
• the rows which correspond to each checkbox are shown and hidden.

Modified script :

When you use this modified script,

• Please copy and paste this script to your script editor, and save it. When you check to the checkbox on the sheet, the rows are shown and hidden.
  • Before you use this script, please confirm that there are no onEdit() functions in your project.
• When you modify the cell of checkbox and rows, please set cfg. For example, when there is a checkbox at "C7" and the rows are from row 11 to row 13, please set C7: {startRow: 11, endRow: 13}.

Script :

function onEdit(e) {
  var cfg = { // Please set this object.
    C7: {startRow: 11, endRow: 13},
    C8: {startRow: 14, endRow: 16},
    C9: {startRow: 17, endRow: 19},
    C10: {startRow: 20, endRow: 21}
  };

  var activeRange = e.range.getA1Notation();
  var ranges = Object.keys(cfg);
  if (cfg[activeRange]) {
    var sheet = e.source.getActiveSheet();
    var values = sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).getValues();
    values.forEach(function(e, i) {
      if (e[0]) {
        sheet.showRows(cfg[ranges[i]].startRow, cfg[ranges[i]].endRow - cfg[ranges[i]].startRow);
      } else {
        sheet.hideRows(cfg[ranges[i]].startRow, cfg[ranges[i]].endRow - cfg[ranges[i]].startRow);
      }
    });
  }
}

Note :

• In this modified script, if several checkboxes are true, the rows which correspond to each checkbox are shown.
• If you want to run the script at the specific sheet, please modify to if (cfg[activeRange] && e.source.getSheetName() == "### sheet name ###") {.

Rererence :

• Event Objects

If I misunderstand your question, please tell me. I would like to modify it.

Edit :

You want to show all rows without hiding, when users open the Spreadsheet. If my understanding is correct, how about this?

function onOpen(e) {
  var cfg = { // Please set this object.
    C7: {startRow: 11, endRow: 13},
    C8: {startRow: 14, endRow: 16},
    C9: {startRow: 17, endRow: 19},
    C10: {startRow: 20, endRow: 21}
  };
  var sheet = e.source.getSheetByName("### sheet name ##");
  var ranges = Object.keys(cfg);
  sheet.showRows(cfg[ranges[0]].startRow, cfg[ranges[ranges.length - 1]].endRow - cfg[ranges[0]].startRow);
  sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).setValue(true);
}

• When you use this, please copy and paste it in your script editor.
• onOpen() can be used for running when the spreadsheet is opened.
• You can also use cfg as a global variable.
• When true of sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).setValue(true); is modified to false, when the spreadsheet is opened, all checkboxes are no check and all rows are shown.