Filter a google spreadsheet by named ranges, but don't hide frozen rows or columns.

filter-by-named-ranges-gas.js

function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu('Custom Filter');
  menu
  .addItem('Show All', 'showAll')
  .addToUi();
}

function onEdit(e) {
  var cellRef = e.range.getA1Notation();
  
  if (cellRef == 'B2') {
    if (e.value) {
      filterByNamedRange(e.value);
    } else {
      filterByNamedRange(e.oldValue);
    }
  }
}

function filterByNamedRange(namedRange) {
  if (namedRange == 'all') {
    showAll();
  } else {
    var sheet = SpreadsheetApp.getActiveSheet();
    var namedRanges = sheet.getNamedRanges();
    var names = []
    
    names.push('all')
    
    Logger.log(namedRange);
    
    for (var i = 0; i < namedRanges.length; i++) {
      names.push(namedRanges[i].getName());
    }
    
    for (var i = 0; i < names.length; i++) {
      if (names[i] == namedRange) {
        var colCount = sheet.getMaxColumns();
        var rowCount = sheet.getMaxRows();
        var startColumn = sheet.getRange(names[i]).getColumn();
        var endColumn = sheet.getRange(names[i]).getLastColumn();
        var startRow = sheet.getRange(names[i]).getRow();
        var endRow = sheet.getRange(names[i]).getLastRow();
        var frozenColumns = sheet.getFrozenColumns();
        var frozenRows = sheet.getFrozenRows();
        
        showAll();
        
        // The if condition checks if the number of rows/columns to hide is more than 0, since GAS has an annoying
        // bug where this method will silently fail (and stop execution of the rest of the script) if calling hideColumns or
        // hideRows and passing 0 for the number of rows/columns to hide.
        
        // Hide everything between any frozen columns and the first column of the named area
        if (startColumn-(frozenColumns+1) > 0) sheet.hideColumns(frozenColumns+1, startColumn-(frozenColumns+1));  
        
        // Hide everything between the last column of the named area up to and including the last column of the sheet
        if (colCount-endColumn > 0) sheet.hideColumns(endColumn+1, colCount-endColumn);
        
        // Hide everything between any frozen rows and the first row of the named area
        if (startRow-(frozenRows+1) > 0) sheet.hideRows(frozenRows+1, startRow-(frozenRows+1));
        
        // Hide everything between the last row of the named area up to and including the last row of the sheet
        if (rowCount-endRow > 0) sheet.hideRows(endRow+1, rowCount-endRow);
        break;
      }
    }
  }
}

function showAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var colCount = sheet.getMaxColumns();
  var rowCount = sheet.getMaxRows();
  sheet.showColumns(1, colCount);
  sheet.showRows(1, rowCount);
}

// Usage: In order to use this script you will need to create a data validation dropdown menu inside a target cell
// which contains a list of items that match all the named ranges you've created - and then add the "all" value to it too.
// If you're lazy (like I am) just add a Logger.log(names) inside this script after the names array is populated.

• added if condition to get around annoying GAS bug
• added ability to hide both rows and columns