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'))
.addItem('Column Finish', 'doColumnFinish')
.addItem('Glass Decoration', 'doGlassDecoration')

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

function doDiameter() {
function doSquare() {
function doRectangle() {
function doOval() {
function doBarrel() {
function do2Pc() {
function doColumnFinish() {
function doGlassDecoration() {

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.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.


Author: zastil

Web Developer and Director of Cheap Property Spain Ltd. Loves gaming, youtube creations and swimming and tenvis.

Leave a Reply

Your email address will not be published. Required fields are marked *