Google Sheet Filter by Column using NamedRanges

You may have found out by now that you cannot filter by columns only by rows which is very annoying, if you have a Google sheet that has 100 or more columns then you will end up scrolling horizontally hundreds of times which takes up a lot of time.

Well I have wrote a google sheet script and here it is.

First part of the codes is to make it easy to run the individual filters by adding a “Filter Columns” menu item to our sheet.


function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Filter Columns')
.addSubMenu(ui.createMenu('Table Top Size')
.addItem('Round', 'doDiameter')
.addItem('Square', 'doSquare')
.addItem('Rectangle', 'doRectangle')
.addItem('Oval', 'doOval')
.addItem('Barrel', 'doBarrel')
.addItem('2Pc', 'do2Pc'))
.addSeparator()
.addItem('Column Finish', 'doColumnFinish')
.addItem('Glass Decoration', 'doGlassDecoration')
.addToUi();
}

Now we need to create functions for each Named Range we want to filter by.

function doDiameter() {
FilterColumns("DIAMETER");
}
function doSquare() {
FilterColumns("SQUARE");
}
function doRectangle() {
FilterColumns("RECTANGLE");
}
function doOval() {
FilterColumns("OVAL");
}
function doBarrel() {
FilterColumns("BARREL");
}
function do2Pc() {
FilterColumns("TWOPIECE");
}
function doColumnFinish() {
FilterColumns("COLUMNFINISH");
}
function doGlassDecoration() {
FilterColumns("GLASSDECORATION");
}

Now the powerful part of the script this will show all columns then loop each named range and hide all that are not in the selected Named Range.


function FilterColumns(name) {
var spreadsheet = SpreadsheetApp.getActive();
var settingsSheet = spreadsheet.getSheetByName('Pricing');
settingsSheet.activate();
settingsSheet.showColumns(1, settingsSheet.getLastColumn());
var namedRanges = settingsSheet.getNamedRanges();
for (var i = 0; i < namedRanges.length; i++) {
if (namedRanges[i].getName() != name) settingsSheet.hideColumn(namedRanges[i].getRange());
}
}

Now all you need to do is setup your named ranges and edit your menus passing the name of the named range to the FilterColumns(“{NameHere}”).

Any questions then please leave a comment below and will gladly assist you.