How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Filter out the Selected Value

Filtering is probably the easiest way to achieve this task, though it can be a little cumbersome. To do this you will have to deselect the value you want to hide. Every time you make a change you need to go back to the filter and select then click save and then deselect your value.

Let’s take a look at the steps:

Step 1 

Select the range you want to filter by and click the “Filter” button.

Filter Google Sheets

Step 2

Select the column you want to filter by and deselect value you want hidden. In our case, select Column F and deselect “Yes”.

Deselect Filter Google Sheets

Step 3

You can go ahead and change the values in the column you have selected.  When you are done, you will need to go to the drop-down for the filter of the column you are using, select the value and click “OK” then go in again and deselect the value and click “OK”. A little cumbersome, I know.

In our example, we can now change “No” or “Partial” to “Yes”. We will then go in and select “Yes” and then “OK” in the filter drop-down on column F. Then, we will go in again and deselect “Yes” and then “OK” in the filter drop-down.

Select and then unselect filter in Google Sheets

Hide the Selected Value with Google Apps Script onEdit() trigger

We can more efficiently hide selected values with a little code using Google Apps Script.

First, let’s take a look at what the code does.

Google Apps Script Hide if Value

As you can see, our code now takes care of the hiding process for us. Every time we change the values to “Yes” our Google Apps Script hides the values. You can see that they are hidden by the little up-down arrows on the far left of the screen. You can view these hidden rows again by clicking those little arrows.

The Code

If you haven’t created a Google Apps Script before, it’s probably a good idea to check out these tutorials to get an idea of how to setup a script:

Let’s take a look at the code:

In line 6-12 we set our global variables. The name of your sheet is “D&D Miniatures” so we set the variable SHEET to this.  The value we want to hide is “Yes” so VALUE is set to “Yes”. The column we want to check is column “F”. We need to convert this to a number value so that value will be 6.

On line 14 we start our onEdit function. The onEdit function is actually a special function called a trigger. This trigger, as the name suggests, will run every time an edit occurs (and of course, the parameters are met).

Lines 15-16 call the spreadsheet we are using and the active sheet we are using.

Line 19 checks to ensure that the active sheet matches the sheet we want to use, in our case, “D&D Miniatures”.

Line 20 calls the active cell and line 20 gets the cell value.

Line 24 checks to see if we are in the correct column, in our case, column 6.

Finally, in line 26 we check to see if the value of the active cell is the same as the value we want to hide. This mean that if we edit a cell in column F, or 6, to “Yes” then that will match our variable VALUE so we can then move on to line 27. Line 27 then grabs  the cell value and hides the row of the cell value with “Yes”.

Easy as that.

Update: Holiday Bonus!!!!!!

How To Hide a Row in a Different Sheet Tab Based on a Cell Value 

It’s the holidays and I have a bit of extra time. Almost as soon as I posted this tutorial I discovered a post from a Google Apps Script community asking if there is a way to Hide a Row in a Different Sheet(Tab). What a good question.

Here is the code:

The highlights in yellow mark the changes to the core code. Let’s look at the changes:

Line 9 adds the SHEET_TO_EDIT variable, this will bee the sheet we want to hide the rows in. We then need to call the sheet to edit in line 18. Next on line 23 we get the cell Notation (e.g. “A2”). Then on line 30 we get the range of that notation in the sheet to edit. Finally we use that range to hide the corresponding row in the SHEET_TO_EDIT.

Behold!!!

Hide row in another sheet Google Apps Script

Link to the Sheet here. Just make a copy to play with it.

 

 

 

Leave a Reply