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

Google Sheets, Google Apps Script, onEdit

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

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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 means 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:

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


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

  1. Hi there — so helpful! Can we please find out how use this to filter rows in a completely different spreadsheet.

    Basically I have a spreadsheet where I am updating small chunks of data (credit card spend). So let’s say I’m manually tagging a particular spend at Kohl’s as “expense – gift”.

    I have another sheet that has a query from several years’ of data — in this I need to refer to what tagging I did for all Kohl’s spend in the past — “expense – gift”, “expense – school” etc. should show up.

    Thank you!

    Reply

    1. HI,

      I cant seem to get this to work when applying to a new spreadsheet.

      I think i have copied correctly and changed the bits I need to, any ideas?

      Thanks

      Tom

      var SHEET = “Test 2”;
      var VALUE = “Yes”;
      var COLUMN_NUMBER = 7

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var activeSheet = ss.getActiveSheet();

      if(SHEET == activeSheet.getName()){
      var cell = ss.getActiveCell()
      var cellValue = cell.getValue();

      }

      Reply

      1. Hi Tom,

        The code looks good to me. Would you like to share a “View” only link your spreadsheet? I might be able to help better from there. You can email via the Feedback if you are concerned with sharing.

        Cheers,

        Yagi.

        Reply

        1. Hi Yagi.

          With a bit of fiddling I seem to have maned to get it working.

          Now I am trying to figure out how to have multiple scripts running one after another?!?.

          Cheers

          Tom

          Reply

          1. Great to hear, Tom.

            If all of your scripts are affected by the onEdit(e) trigger function. You might like to create a function for each of your script and link it to the on edit function. For example:

            function onEdit(){
            var firstScript = scriptOne();

            var secondScript = scriptTwo();
            };

            function scriptOne(){
            //do some stuff in here
            };

            function scriptTwo(){
            //do different kinds of stuff in here
            };

    2. This works great, but do you know if there is a way to specify to hide a row if the date entered in the cell is before today? For instance, I have a budget workbook and one of the sheets keeps track of transactions. I would like for the rows with dates from 30 days prior to automatically hide once today’s date has triggered the 30 day expiration of the row. I figured it would be as simple as this…var VALUE = “>Today()-30”;

      Reply

    1. Hi Raheel,

      I suggest you change VALUE to an array of all the values you want to hide e.g. var VALUE ['Yes','Mistake','Almost']

      Then use a for loop to iterate through the VALUE array during this “if” statement if(cellValue == VALUE[i]){activeSheet.hideRow(cell);};

      Reply

      1. I seem to be having an issue with this in that it’s not working – am I missing something?

        Reply

        1. Hi Katie.
          It is difficult to say without seeing your code.
          If you post your code in the replies, we might be able to help better.

          Reply

    2. Hi, I hope this could help

      var COLUMN_NUMBER = [1, 2, 3, 4]

      if(COLUMN_NUMBER.indexOf(cell.getColumn()) >= 0){

      Reply

  3. Thank you for you Tutorial! What needs to be changed to handle a checkbox with TRUE or FALSE as the VALUE instead of a dropdown?

    Reply

    1. Hi Marko,
      Try changing the VALUE = true

      Reply

  4. Thanks for the post!

    Now, what if I want to hide rows with “yes” and “partial”?

    Reply

    1. Yes, you could use & in the if statement. If you want to meet both conditions or and | if you want to meet one condition or another.

      Reply

  5. Is there a way to simply have a button that will hide/unhide the rows automatically rather than having to click the little arrows on the right side to unhide the rows one by one?

    Reply

      1. I have tried to modify this code so that if I hit a button it will unhide all of my hidden rows but I can not get it to work.

        Code used to hide rows

        var SHEET = “Assignments”;
        var VALUE = “Done”;
        var COLUMN_NUMBER = 5

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var activeSheet = ss.getActiveSheet();

        //Ensure on correct sheet.
        if(SHEET == activeSheet.getName()){
        var cell = ss.getActiveCell()
        var cellValue = cell.getValue();

        };
        }

        Codes used to show hidden

        var SHEET = “Assignments”;
        var VALUE = “Done”;
        var COLUMN_NUMBER = 5

        function button() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var activeSheet = ss.getActiveSheet();

        //Ensure on correct sheet.
        if(SHEET == activeSheet.getName()){
        var cell = ss.getActiveCell()
        var cellValue = cell.getValue();

        };
        }

        Reply

        1. Hi MArnould,

          You will need to do something a little different for our Unhide All button. After you verify if the SHEET name matches the activeSheet like you did above, you will need to get the last row of the active sheet and store that in a variable.
          Next, use a ‘for’ loop to loop from row 1 through to the last row + 1. Inside this loop you want to check if the row isRowHiddenByUser. If it is, then get the range by concatenating ‘A’ to the current row number. Then unhideRow.

          Give that a try.

          Regards,

          Reply

  6. Thanks for sharing the article, it’s very informative. I’m trying to modify the code so that columns will appear or disappear based on the presence of a 1 or 0, respectively. Initially the code was working correctly but after moving cells around I receive an error in the getRange where the value is null. Let me know if you can spot an error in my code below. Thanks!

    /**
    * TITLE:
    * Hide a column if a value is inputted.
    */

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Master Product List”;
    var SHEET_TO_EDIT = “Raw Order Entry”;
    // The value that will cause the row to hide.
    var VALUE = “0”;
    var VALUE2= “1”;
    // The row we will be using
    var ROW_NUMBER = 1

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();
    var editingSheet = ss.getSheetByName(SHEET_TO_EDIT);

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();
    var cellNotation = cell.getA1Notation();

    };

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();
    var cellNotation = cell.getA1Notation();

    };
    }

    Reply

    1. Hi Dave,

      Thanks for the kind words.

      Your code seems to be working fine. I would encourage you not to use the same variables in your unhide process. Instead, just add an else if statement for VALUE2 (see below). The only other thing I can think of is that you may have copied in some weird quotations (” “). Just update them with quotations once you have pasted it in Google Script.

      Reply

  7. Hi, this appears to be the exact fix I’m look for but I’m having trouble getting it to work and am unsure how to fix it. The value is set to True as the cells in column F contain checkboxes. Here’s the script as I’ve changed it:

    var SHEET = “Passes”;
    var VALUE = “True”;
    var COLUMN_NUMBER = 6

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

    1. Hi Amanda,

      You almost had it. Your VALUE needs to be a boolean not a string.
      Change

      to

      Happy coding.

      ~Yagi

      Reply

      1. It worked; thank you! Would there be a way to get this to work and hide 6 rows at a time?

        Reply

  8. If “true” hides the row and if change it back to “false”, is there a way for the row to unhide its self to show “false”

    Reply

    1. Hi Josh,

      I’m not 100% sure what you mean, sorry.

      However, you would need some sort of indicator that would trigger the row to be unhidden. What conditions need to occur for the row to be unhidden?

      ~Yagi

      Reply

  9. Yagi,

    I used your code to hide a row once I have finished a task how ever I tried to create a button that would show the hidden rows to view if needed

    Here is the code that I used to hide the rows

    var SHEET = “Assignments”;
    var VALUE = “Done”;
    var COLUMN_NUMBER = 5

    function button() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    The code I used to show the hidden rows
    var SHEET = “Assignments”;
    // The value that will cause the row to hide.
    var VALUE = “Done”;
    // The column we will be using
    var COLUMN_NUMBER = 5

    function button() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

  10. Great article, thank you – and I am sure if I were smarter I could work this out for myself but if I can get any assistance I would really appreciate it.

    Is it possible to hide specific rows and columns after I delete contents of a cell?

    Specifically, if I delete the contents of Z1, I want to hide columns Z, AA, AB & AC as well as rows 9, 17, 25 & 33.

    Reply

    1. Hi Stuart,

      Yes, this is possible if you or your users are deleting the cell contents. You could use the isblank() object or simply have it equal to an empty string (cell == “”).

      Reply

      1. OK, thanks – I will do some research on what that is, sounds well beyond my capabilities just now.

        Reply

  11. your site is amazing thanks for all the tutorials.
    I am having issue to get the script working for a TRUE value that results from a formula (=$S90//Ensure we are looking at the correct column.
    if(cell.getColumn() == COLUMN_NUMBER){
    //If the cell matched the value we require,hide the row.
    if(cellValue == VALUE){
    activeSheet.hideRow(cell);
    };
    };

    };
    }
    }

    Reply

    1. Hi Cecilia,

      Thanks for the kind words.

      Did you set our global VALUE variable to true?

      var VALUE = true;

      Cheers,
      Yagi

      Reply

  12. I have tried to used this script and it runs without error but the rows DO NOT hide.
    Can anyone help me with a possible answer?

    This is my script.

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Lil Bubba Consolidated Sales”;
    var SHEET_TO_EDIT = “Lil Bubba Sales Tracking”;
    // The value that will cause the row to hide.
    var VALUE = “Yes”;
    // The column we will be using
    var COLUMN_NUMBER = 16
    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct cheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    //Ensure we are looking at the correct column.
    if(cell.getColumn() == COLUMN_NUMBER){
    //If the cell matched the value we require,hide the row.
    if(cellValue == VALUE){
    activeSheet.hideRow(cell);

    };
    };

    };
    }

    function button(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct cheet.
    if(SHEET == activeSheet.getName()){
    var lastRow = activeSheet.getLastRow();

    for(var row = 1; row < lastRow+1; row++){
    if(activeSheet.isRowHiddenByUser(row)){
    var range = activeSheet.getRange(“A”+row);
    activeSheet.unhideRow(range);
    }
    };

    };
    };

    Reply

    1. Hi Gordon,

      I can’t see an issue with your code. Can you share an example of the Google sheet you are working on?

      Cheers,

      Reply

  13. Hey Yagi, thx for sharing this script with us! I’m using it now to hide rows in a monthly sheet im creating. I wan’t to add a second criteria to the script and have no clue how to do it.

    I only want to hide the row if the cell for “outstanding amount” is zero (0), if not the row should stay visible even if i turn the “trigger value” to the status where it should hide the row.

    cheers mate!

    Reply

    1. Hi Dave,

      I’d first set the VALUE to your “trigger value” and the COLUMN_NUMBER to the col the trigger value is on. Then I would create two more variables VALUE1 and COLUMN_NUMBER1 and add in your outstanding amount data. Next, nest a second if statement under line 26. You would essentially duplicate lines 24 to 28 inside the other if statements and swap in the new variables.

      Give it a crack and if you get stuck, feel free to post the code and I will take a look.

      Cheer,

      Reply

  14. Hello Yagisanatode

    i’m finishing my google spreadsheet and i need some more javascript things to do
    at the age of 15 and that was 35 years ago and programmed at basic on my first home computers as a c64
    and i was also good in assembler for 6502 processor
    now i’m 50 and it seems like i’ve had too much beer in my life and smoked too many cigarettes
    and by no means can do javascript that works, only sometimes i succeed if i copy

    and need help how to run this script if the formula changed the value in cell N5
    this script is macro but i want to run it if the formula in N5 gave a value change

    function Printrekap () {
    var spreadsheet = SpreadsheetApp.getActive ();
    spreadsheet.getRange (‘M1’). Activate ();
    spreadsheet.getActiveSheet (). getFilter (). removeColumnFilterCriteria (13);
    var criteria = SpreadsheetApp.newFilterCriteria ()
    .setHiddenValues ​​([‘0’])
    .build ();
    spreadsheet.getActiveSheet (). getFilter (). setColumnFilterCriteria (13, criteria);
    };

    it’s a script I got from a macro because I wanted to refresh the filter
    i also tried your script filtering row if “yes”
    but from the formula I get a value of “1” to show the row and “0” to hide the row
    because the table must automatically show me a row according to that value from the formula
    the sum of all values ​​of “1” is in column N5 and when my number changes I should refresh the filter
    greetings from Croatia
    Please help

    Reply

    1. Hi Darko,

      Thanks for your message. I am not 100% certain of what you want to do here. My guess (please correct me if I am off base here) is that you want to hide values based on if a cell has changed AND if values in a column match a criteria. Does that sound right?

      Reply

  15. Hey Yagi,

    I am so close! I am trying to hide all rows with a 0 value in my Qty column in a quote sheet upon clicking a button. The script works, but it only works in the cell/row that I currently have highlighted. I’d like it to work no matter where I am selecting and also to look down the entire column and hide all of the rows with a Qty of 0. Code is below and I can share the sheet if needed.

    /**
    * TITLE: Hide and UnHide Zero Quant Rows IT Quote
    * Hide rows qty of 0 when clicking button.
    */

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Item Costs”;
    // The value that will cause the row to hide.
    var VALUE = “0”;
    // The column we will be using
    var COLUMN_NUMBER = 4

    function button() {
    var ui = SpreadsheetApp.getUi();
    var cell = SpreadsheetApp.getCurrentCell().getValue();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

    1. Hi Tommy,

      For a button, you won’t be able to use the getActiveCell method. Your best bet here is to getRange of the column with your marked value then getValues of that range. You could then make a custom variable to contain each row that has your 0. Then loop through that row storing the row number in that custom value. Then you can loop through that custom variable or row numbers and hide them.

      Reply

  16. Hey Yagi,

    Thanks for this, very comprehensive code.
    I am trying to make very similar use as you did for hiding rows that meet a criteria. Somehow it doesn’t seem to produce any outcome aside from generating the UI (used it from one other tutorial of yours). Would you be so kind to check it for me? Thanks in advance!

    Cheers,
    Pedro

    function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu(‘Custom’)
    .addItem(‘Hide obsolete rows’, ‘onEdit’)
    .addToUi();
    }

    // script to remove rows with obsolete (removed) events
    var SHEET = ‘Tab1’; // sheet name
    var VALUE = ‘removed’; // value that will cause the row to hide.
    var COLUMN_NUMBER = 2; // column from where value is read

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

    1. Hi Pedro,

      Yeah, the script in this tutorial works by reviewing the currently active cell. So if you run your code from the menu it will only look at whatever cell is active.

      What you will need to do is to get the range of the COLUMN_NUMBER column and loop through it(possibly in reverse) finding all the rows with your VALUE and hiding them as you go.

      Hope this points you in the right direction.

      ~Yagi

      Reply

  17. How do I hide rows 3-6 if cell A1 is empty? And unhide them if a value is entered in cell A1?

    I’m a complete newbie and would appreciate if you could help me with this.

    Reply

    1. Hi Fernando,

      Make a try at it based on what you have learnt from this tutorial. If you get stuck, feel free to post your code below and either I or one of the community might get a chance to take a look.

      ~Yagi

      Reply

  18. Hey Yagi,
    thanks for your work. I’m trying to hide a range of rows based on cell value in another sheet. So how can I set the range of hiding in the code.

    Reply

  19. Hi Yagi,

    I am not a programmer by any means and I am trying to get this completed. I’ve been able to do some scripting to completion but I am unable to hide one column upon the cell value “Yes”.
    I literally copied your code. I think it may have something to do with the global variables you used and my other functions, but I’m not certain.

    https://docs.google.com/forms/d/e/1FAIpQLSd15Lr75sq4-5daVeTcNFQxCQu63MSjYfTrbwuvXK_nBfuE7A/viewform?edit2=2_ABaOnufFh7eOiQeZ8eOwfhK7uv8kPejkfwO0QrPcVh2XxbkPsTr1t_R7mTbXhtfxsCbTr3E

    //Editing URLs
    function assignEditUrls() {
    var form = FormApp.openById(‘1MezJPFAqjkANrqz4iR5s0XM0NsytJhGbkV5O6Lpc8g0’);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘FD Database’);
    var data = sheet.getDataRange().getValues();
    var urlCol = 12;
    var responses = form.getResponses();
    var timestamps = [], urls = [], resultUrls = [];

    for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
    }
    for (var j = 1; j < data.length; j++) {

    }
    sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
    }

    //Trigger Function
    function scheduleTrigger() {
    var builder = ScriptApp.newTrigger(“assignEditUrls”).timeBased().everyMinutes(1);
    var trigger = builder.create();
    }

    //Hide Column
    var SHEET = “FD Database”;

    var VALUE = “Yes”;

    var COLUMN_NUMBER = 9

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

    2. Hi Quinn,

      Looking at your code, it seems that you want to hide a column based on a value change from a form. Unfortunately, will not be able to help you with that. You could use onOpen or a scheduled time trigger like you have in your code.

      Cheers,

      Yagi

      Reply

  20. Hi Yagi,

    Thanks for this guide! I want to create a week selector. So I have a drop down in a cell to select a number (between 1 and 10). Then in a Column I have =if($D$2=1,True,”Yes”) for all the rows that are data from week 1. I would have =if($D$2=2,True,”Yes”) for all week 2 rows. I am running into the issue where when changing the drop down value it does not filter out these cells. It works fine if I type Yes in but not if it is changed by the drop down and IF function. How would you change your code to look through all the column and update each cell based on the drop down change?

    Thanks 🙂

    Reply

  21. Hi Yagi,

    I have used the script and it works great, however is there a way to get rid of the ‘glitch’ were the hidden rows keep appearing/disappearing when something is edited?

    Thanks 🙂

    Reply

  22. Hi There,

    Thank you for all this great information. How can I make this same exact function work with onOpen as opposed to onEdit? I tried just changing the code from onEdit(e) to onOpen(e) but that did not work so I’m assuming there is something else I need to edit.

    Also, is there a way to selectively run the script by linking it to a custom menu selection (like the “OMG” in your Not Hello World example). Essentially, I want to be able to hide and unhide the rows (run the script and not run the script) by selecting this option. Is this possible? Thank you so much!

    Reply

    1. Hi mwinn14,

      Yes, you could launch a script onOpen() to hide certain rows based on cell values. However, this would take a complete rewrite of the script ☹. With your requirements to have a custom menu to run the code or not, I would look at onOpen() check to see if the script to hide the rows should run by using PropertiesService and adding a key:value pair like runHide:true or runHide:false. Then to run the script, getRange and getValues all the values in the column you want to hide and loop through them noting the columns that you want hidden. Then hide those selected columns.

      Some of this looks pretty interesting so I will add it to my list of tutorials to write in the near future.

      ~Yagi

      Reply

  23. How do you do the loop? I’m trying to get mine to automatically hide rows no matter if the cell is active or not.

    Reply

  24. Hey Yagi,

    Thanks a lot for the info. Very helpful and very cool of you to share it.

    I’m trying to do something I’m not quite sure anyone else has asked it here in the comments. Maybe I missed it…

    I want to hide/unhide (toggle) a range of rows and columns (for instance, A47:G83), based on a selection from a Yes/No dropdown. I would greatly appreciate your help with this one.

    Thank you so much!

    Reply

    1. Hi Jay,

      Thanks for the kind words.

      You can definitely do this. The basic concept is the same.

      Here, In your onEdit(e) function you could get the range of your Yes/No drop down with something like e.range.getA1Notation() and make sure that equal the cell you have your dropdown on e.g. “B1” with an “if” statement. If it does, get the active spreadsheet and then the active sheet from your e.range – confirm it is the correct sheet.

      Once all confirmed, go ahead and use the SpreadsheetApp class getActiveSheet() method to get the sheet. Then get the range you want to show or hide using show/hideRow like in the example in the tutorial.

      Hopefully this has pointed you in the right direction to get experimenting.

      Happy coding!

      Yagi

      Reply

      1. First off, thank you so much for responding so quickly and for providing a detailed answer – much appreciated! I tried to implement it and got lost in the explanation. I would appreciate your help here – Thank you! That’s the current script I have –

        /**
        * TITLE:
        * Hide a row if a value is inputted.
        */

        //GLOBALS
        // Sheet the data is on.
        var SHEET = “Sheet11”;
        // The value that will cause the row to hide.
        var VALUE = “No”;
        // The column we will be using
        var COLUMN_NUMBER = 1

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var activeSheet = ss.getActiveSheet();
        var IF(B1, value_if_true, value_if_false) = e.range.getA1Notation();

        //Ensure on correct sheet.
        if(SHEET == activeSheet.getName()){
        var cell = ss.getActiveCell()
        var cellValue = cell.getValue();

        };
        }

        Reply

        1. Hi Jay,

          From what you have explained, you only have one Yes/No cells for your range your example is A47:G83.

          GLOBALS
          If you only have one cell that has a yes or no variable then you will need to declare a variable with that cell reference e.g. “A1”.
          You won’t need a column variable so you can remove that.
          You will also need a variable to the range that you want to hide.

          I’m not 100% sure what was happening here: var IF(B1, value_if_true, value_if_false) = e.range.getA1Notation(); . I’m guessing you were trying to use an IF statement to check if the A1 notaiton of the selected range equalled the range for your Yes/No cell. Spend a bit of time exploring IF statements and then have a look at this portion of the code:

          var cell = ss.getActiveCell() //You can safely remove this row now
          var cellValue = cell.getValue(); //then you can use e.range.getValue()

          This will update these lines:
          if(cellValue == VALUE){
          activeSheet.hideRow(cell); // <<

          In the code below is where you will need to swap out comparing the columns to comparing if the Yes/No cell was edited.
          if(cell.getColumn() == COLUMN_NUMBER){
          You want to check if your Global for your Yes/No cell is equal to the e.range.getA1Notation()

          Work through this and give it a few tests. If you get stuck add your updated code to the comments below and I can give you a few more hints.

          ~Yagi

          Reply

          1. Great, thanks a lot for your help! I appreciate it.

  25. Hi Yagi –
    I have added the code and everything seems to be correct, but it is not actually doing anything when I save the code. Does it only apply to things that were input after I edited the code?

    https://docs.google.com/spreadsheets/d/1KkCp8zrr6UI34GEKGHyWCUTQyFHvpBBKt3NgsPfEqYs/edit?usp=sharing

    Here is the link to the sheet I’m working on. I have inputs being pulled from multiple sheets within the workbook, but I don’t want to see the rows that say ‘Click to Select’

    I appreciate the help!
    -Lindsay

    Reply

    1. Hi Lindsay,

      Drawing in dynamic data using something like IMPORTRANGE will not be considered an onEdit event. You may have to go with onOpen(). However, keep in mind that if you data rows change in your connected sheet then that hidden row will be wrong. One approach might be to create another Sheet tab and copy and paste (or update) in the currently dynamic values with Apps Script onOpen on a time trigger. Then update that sheet tab to hide or even remove the unwanted rows.

      I hope this points you in the right direction.

      Cheers,

      Yagi

      Reply

  26. Hey, trying to rack my brain around what is not working here. I have a checkbox in column A and I want it to hid the row if the checkbox is checked. But for some reason not doing so. I believe I’ve changed everything correctly any help would be great.

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Paragould Local”;
    // The value that will cause the row to hide.
    var VALUE = “TRUE”;
    // The column we will be using
    var COLUMN_NUMBER = 1

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    //Ensure we are looking at the correct column.
    if(cell.getColumn() == COLUMN_NUMBER){
    //If the cell matched the value we require,hide the row.
    if(cellValue == VALUE){
    activeSheet.hideRow(cell);
    };
    };

    };
    }

    Reply

    1. Hi Garrett,

      Values like true and false are reported as booleans in Google Apps Script rather than text so there is no need to put quotation marks around true or false in your VALUE variable. So value should look like this:
      var VALUE = true;

      Hope that helps.

      ~Yagi

      Reply

      1. I’ve changed it to true no “”. I had tried that before cause I knew it was boolean but that still doesn’t seem to do the trick. Any thoughts or would it help to possibly share a sheet.

        Reply

        1. Hi Garrett,

          I’m not sure what is causing your issue.

          Here is a copy of your updated code in a sample sheet that seems to be running fine.

          Sample Sheet

          Go to File > Make a copy. Then make sure you run the code onEdit() one time from the Apps Sript editor and go through the permissions.

          Cheers,

          Yagi

          Reply

          1. Sorry it took so long to get back to you. One apparently just taking out “” didn’t change the true variable to Boolean and I had to retype it. 2nd didn’t realize I had to run the script to have it go… Yikes… So it did work now the next thing I’ll have to do is make it where it unprotects the columns in the sheet then runs the code and then re-protects the columns. Hopefully that won’t be to bad. Thanks for the help.

          2. Hi Garrett,

            Great to hear you got it all working. Yeah, that initial test run in the editor can catch the best of us.

            Happy coding!

  27. Hi Yagi, I have successfully implemented this code and it works perfectly in its current format (a dropdown list to select YES/NO, with selecting YES hiding the row).

    My question is, if i change a bunch of cells to a YES value too quickly (including by dragging down, or selecting CTRL+D), the script doesn’t seem to be able to keep up – at best only hiding the first row.

    So, Is there anything I can do to improve the code so that if i change 10 values at once to a YES, it will hide all 10 rows?

    Reply

    1. Hi Sim,

      Yes the onEdit trigger can be a bit temperamental. You might want to look at the LOCKSERVICE as it is applied to the spreadsheet and maybe even FLUSH the code. If all else fails, you could implement a button and change your values and then click the button to update.

      Cheers,

      Yagi

      Reply

  29. Hi 🙂

    This is a great article! I’ve been trying to adapt the code to be able to hide columns based on ROW1 value

    Any help on figuring out this code will be greatly appreciated!

    Thanks in advance.

    Katy

    Reply

    1. Hi Katy,

      I’m glad you found the code useful. Feel free to share your attempt in the comments.

      Cheers,

      Yagi

      Reply

  30. Hello,

    Your script Is perfect but I require something maybe considered the opposite of what It does.
    I have a specific Cell off the side of all my data that acts as a search bar. I can select what Id number I want from the drop down which Is quite long or simply type the Id number of the product and it will find that product row and give It a different color background and make the font Bold to stand out against all other data. I believe the search script was done In excel but now that I have transferred the excel file to google sheets, I have no idea how I set that search option up and for some reason I can’t find the excel file either. It has been a year since I attempted to make this Stock Inventory file so I might have deleted the Excel file Since It successfully transferred over to Google Sheets with no Issues. I needed It online for other work colleges to work with It at the same time.

    Anyways, I would actually like for the search result rather than highlighting the row and making the font Bold, but also to hide all other rows temporally Until i delete what ever Is In the search Cell, which then returns all data back to visible.

    Is this possible In google sheets and biggest apologies for not offering any examples of coding as I’m the type of person to search for some code and just try to apply It. I really don’t know much at all about coding.

    If you could offer some assistance, that would be great, If this Is not possible I still thank you for your time In reading this post.

    Kind Regards,
    Rick

    Reply

    1. Hi Rick,

      Yes this is achievable in Google Apps Script. The onEdit Trigger can complete other actions in other cells, rows, columns, sheets based on your selection. You could even use a set of buttons along with your ID selector cell.
      As you mentioned, this is quite a bit outside the scope of this tutorial. I would recommend you look to Fiverr if you need to get some custom Google Apps Script written well and quickly. Here’s a link to the Fiverr page for Google Apps Script developers. The first one is an affiliate link if you want to support me – it won’t cost you any extra, but the little commission I get helps to pay for the running of this site. Otherwise, feel free to click that direct link if you want to go that direction:
      Fiverr (Support Yagi)
      Fiverr
      ~Yagi

      Reply

  31. Hi Yagi –

    Can this be run with formulas? For instance – if we have a COUNTIF formula in a cell and we want to hide the row if it hits 0, but unhide it if goes back above 0?

    ~Billy

    Reply

    1. Hi Billy,

      Not directly, but you could reference the input cell to check that it has been changed which will trigger the onEdit(e) function and then use something like offset to check if your COUNTIF cell has hit zero.

      Cheers,
      Yagi

      Reply

  32. Yagi –

    This is great stuff – very useful! Not a coder, but your explanations are very intuitive.

    Question: What if I’m not manually changing the cell value (i.e. the active cell), but rather the cell(s) are linked to a master data tab that updates via script. I have multiple rows that change status that I would need to hide. How would I modify the active cell code section to be able to hide multiple rows on the sheet that meet my criteria in a particular column?

    Thanks,
    JT

    Reply

    1. Hi JT,

      Unfortunately, the onEdit() trigger function, as you have probably discovered, only works with manual changes to the cell. My recommendation is that you elicit these changes when you run your master script. It will take a little bit of coding that is outside the scope of this tutorial but not too challenging.

      Other no code alternatives might be for you to use filter views and hide data based on your specific needs. I have a video tutorial on this here.

      Another option might be to use the QUERY function or FILTER function to display only what you need base on the Master data.

      ~Yagi

      Reply

  33. Hi Yagi!

    Your instructions is very easy to understand and very helpful! Thank you so much!

    However I tried to run the codes and change the values but for some reason it’s not working. Could you help me check what seems to be the error?

    https://docs.google.com/spreadsheets/d/1OeePKXXO0gbCuAi3HZovvxuwff_0WDPbuNh2wXyKOzw/edit#gid=80737719

    By the way here’s the details on what I’m trying to do.

    Goal: To create timesheet for 3 employees, when they go to their specific timesheet, they have the option select the date range they want and it will hide the rows that is they don’t want
    Also can you help me to do this with the 3 sheets? Cuz it seems your code will work in 1 sheet only.

    Thank you so much, Yagi! <3

    Reply

    1. Hi Jen, Sure if I get a chance, I can take a look at your code and see if I can guide you in the right direction.

      ~Yagi

      Reply

  34. Hey Yagi!

    I am trying to use the original script but on several sheets in the same file. Would you be able to help me out? Thank you!

    /**
    * TITLE:
    * Hide a row if a value is inputted.
    */

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Wells Fargo Checking”;
    // The value that will cause the row to hide.
    var VALUE = “C”;
    // The column we will be using
    var COLUMN_NUMBER = 5

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    };
    }

    Reply

    1. Hi Jon,

      Your best bet here would be to set up an array of objects like:
      const hideLocations = [
      {
      sheet: “Wells Fargo Checking”,
      value: “C”,
      colNum: 5
      }
      {
      sheet: “Vanguard”,
      value: “Limit”,
      colNum: 10
      }
      …
      etc
      …
      ]

      Make sure you reassign the variables to these values.

      Next, loop (for, while, forEach) through these options with the 3 ‘if’ statements inside.

      Give it a try. If you get stuck, you can always share and one of my readers or, if I have time, I can guide you further.

      ~Yagi

      Reply

  35. Hi Yagi,

    Need help with your code. This is exactly what I’m looking for (THANK YOU!). But not sure why it doesn’t work for me.

    Here’s an example sheet in view mode:
    https://docs.google.com/spreadsheets/d/1pVXasLdmiY1QcxclR1OmZU7Vw0veDc9H-j8U1-WUSUg/edit?usp=sharing

    And my goal with this is to hide rows when a split test has ended in column K. This is for marketing. In the original sheet, I added in data validations for Copywriter, Media Buyer, Variation Page Status,Test Status, Winning Test, and Significance Columns. So there would be a drop down of “Items” to choose from. I also added Conditional formatting for a row to change color. When the test is running it turns to a color. When the test has ended, it turns another color.

    I’m hesitant to share the actual sheet, since I might get in trouble. So hopefully, the example sheet will do.

    Also, here’s a quick video of what i’ve done, with the code, running the code, and refreshing the sheet, but it doesn’t hide the Ended Tests. https://app.usebubbles.com/tfvmXCUFvPnHh5MoHsnVbu/for-yagi

    Reply

  36. Hey Yagi,

    I don’t know why your code isn’t working for me. Here’s an example sheet I made: https://docs.google.com/spreadsheets/d/1pVXasLdmiY1QcxclR1OmZU7Vw0veDc9H-j8U1-WUSUg/edit?usp=sharing

    My goal is to hide rows when TEST STATUS has ENDED. This is for marketing, where we split test a page against a variation of the page, so we have a lot of data.

    Also, not sure if it matters but i put conditional formatting to color the row when the TEST STATUS has ENDED.

    Anyway, I also created this short video for you to see the code and what I did:
    https://app.usebubbles.com/tfvmXCUFvPnHh5MoHsnVbu/for-yagi

    Thank you for your help!

    Reply

    1. Hi Jose,

      It looks like you are referencing “Example Split Test Idea Log” as your SHEET main variable that you are using to find the change to ENDED status but you don’t have a sheet tab with this name.

      ~Yagi

      Reply

  37. hello yagi!

    i’m a real noob with google sheets and i’ve been searching for 2 days now how i can make a code for this.
    i really dont know how to share a sheet so i’ll try my best to describe what i need.

    if cell A1 is empty, i want columns B, C and D hidden.
    if there’s data on cell A1, columns B, C and D should be visible.

    hope you have a solution for me.
    thank you!

    Reply

  38. hi yagi, thanks for sharing,
    what if I want hidden rows in the D&D Miniatures sheet if the value is “yes” then it’s automatically hidden

    Reply

    1. Hi Rendra,

      I am a little confused about your question. Could you expand in it a little, please?

      ~Yagi

      Reply

  39. Hi Yagi,

    I was wondering if you could take a look and see if my code has a fixable problem. It was working with my modifications to your code, but then stopped. I’m trying to make it hide rows based on 2 out of 3 options and unhide the row on the third option. Thanks for any help you can provide! (And thanks for this tutorial!)

    [code]
    /**
    * TITLE:
    * Hide a row in another sheet(tab) if a value is inputted.
    */

    //GLOBALS
    // Sheet the data is on.
    var SHEET = “Raw Data”;
    var SHEET_TO_EDIT = “Pipeline”;
    // The value that will cause the row to hide.
    var VALUE = “Loss”;
    var VALUE2 = “Win”;
    var VALUE3 = “Other”;
    // The column we will be using
    var COLUMN_NUMBER = 10

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = ss.getActiveSheet();
    var editingSheet = ss.getSheetByName(SHEET_TO_EDIT)

    //Ensure on correct sheet.
    if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellNotation = cell.getA1Notation();
    var cellValue = cell.getValue();

    };
    }
    [/code]

    Reply

    1. Hi Susan,

      A couple of questions:

      Are you receiving an error in your code? If so, what is it?

      If no error in the code, what issue are you facing? What is working and what isn’t?

      Do you have a copy of the Google Sheet with the code connected that I can take a look at (View only – as a sample without any sensitive details).

      Cheers,

      Yagi

      Reply

    1. I fixed the error! Simple thing. It turns out that I changed the name of a tab, but not in the code.

      Reply

      1. Fantastic, I was just about to take a look this morning. Always satisfying to figure out a bug yourself. Glad you got it all sorted.

        ~Yagi

        Reply

  41. do you have similar code for this that work in grouping rows or columns automatically when selecting a value in dropdown list? thanks!

    Reply

    1. Hi 1ntr0v3rt3ch,

      No, I don’t have anything like that at the moment. Have a look a the Group child class of SpreadsheetApp.

      ~Yagi

      Reply

Leave a Reply