Search code examples
google-sheetsgoogle-sheets-formula

Can I filter or hide columns based on data in a specific row?

I need to find a way to either hide or sort the columns whose box in row 2 is not checked. I only want to see the columns that have a checked box in row 2. I can accomplish this with a script, but getting everyone in my organization to give permissions to a script is not likely.

I'm curious if there is a creative solution to this problem or if there is a way to give my whole organization permission in a way that doesn't look super sketchy. Right now, Google says that what I am doing is very dangerous and warns me multiple times to not give permission to this script. I won't be able to get people in my company to approve the script if that is what it does.

I've been trying to find a solution to this for a couple days and haven't really made a ton of progress.

Below is an example of my sheet. TRUE is a checked box and FALSE is an unchecked box.

This is what my sheet looks like

Column 1    Column 2    Column 3    Column 4
TRUE         FALSE       TRUE        FALSE
text         text        text        text
text         text        text        text
text         text        text        text
654354       654354      654354      654354
34245        34245       34245       34245
FALSE        FALSE       FALSE       FALSE
FALSE        FALSE       FALSE       FALSE

This is what I want it to look like with the unchecked(FALSE) boxes being last or hidden all together.

Column 1    Column 3    Column 4    Column 4
TRUE         TRUE        FALSE       FALSE
text         text        text        text
text         text        text        text
text         text        text        text
654354       654354      654354      654354
34245        34245       34245       34245
FALSE        FALSE       FALSE       FALSE
FALSE        FALSE       FALSE       FALSE

EDIT-- I used an 'on edit' script so that I dont need to give permission to every user.

here is an example of my script that did was I was looking for.

function onEdit(e) {
  var sheetNames = ['Tab1', 'Tab2', 'Tab3'];
  
  var sheet = e.source.getSheetByName(e.range.getSheet().getName());
  if (sheetNames.indexOf(sheet.getName()) === -1) return;

  var range = e.range;
  var b3Value = sheet.getRange('B3').getValue();
  
  if (range.getA1Notation() === 'B3' && (b3Value === 'Hide' || b3Value === 'Unhide')) {
    var checkboxes = sheet.getRange('E2:R2').getValues()[0];
    
    for (var i = 0; i < checkboxes.length; i++) {
      if (checkboxes[i] === false) {
        if (b3Value === 'Hide') {
          sheet.hideColumns(i + 5); // Column E is the 5th column
        } else if (b3Value === 'Unhide') {
          sheet.showColumns(i + 5);
        }
      }
    }
  }
}
Solution

  • SUGGESTION

    I totally understand that you have a working solution on your end that uses a script, but can I ask if you have tried using the onEdit trigger?

    I'll provide this sample code that uses the onEdit trigger that hides any columns that has the checkbox on the 2nd row unchecked (as per your specifications):

    function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const range = e.range;
  const col = range.getColumn();
  var data = range.getValue();

  if (!data) {
    sheet.hideColumns(col, 1);
  }
}

    And as per testing on my end, it no longer asked for any permissions since the onEdit trigger runs automatically as soon as it detects any changes made on the sheet. Thus, it hid all columns that had the checkbox unchecked, without getting a prompt for permissions; the same works for anyone who has access to the said file.

    OUTPUT

    image

    RESOURCES

    Nonetheless, let me know if you have any questions regarding this and I'll be glad to discuss further.