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.

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:

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

79 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!

    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();

      }

      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.

        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

          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”;

      1. Hi Bradley,

        Yes, you can do that. You could take a look at clock triggers to read your date column each day and hide the necessary ones. Or you could use the OnOpen trigger to hide relevant days as the sheet loads.

        Here is a tutorial I wrote a little while ago that incorporates clock triggers.
        Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

        Let me know how you get on.

        Cheers,
        Yagi

    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);};

        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.

    2. Hi, I hope this could help

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

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

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

    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.

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

      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();

        };
        }

        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,

  4. 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();

    };
    }

    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.

  5. 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();

    };
    }

    1. Hi Amanda,

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

      to

      Happy coding.

      ~Yagi

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

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

    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

  7. 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();

    };
    }

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

    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 == “”).

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

  9. 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);
    };
    };

    };
    }
    }

    1. Hi Cecilia,

      Thanks for the kind words.

      Did you set our global VALUE variable to true?

      var VALUE = true;

      Cheers,
      Yagi

  10. 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);
    }
    };

    };
    };

    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,

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

    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,

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

    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?

  13. 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();

    };
    }

    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.

  14. 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();

    };
    }

    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

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

    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

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

  17. 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();

    };
    }

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

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

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

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

    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

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

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

    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

      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();

        };
        }

        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

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

    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

  24. 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);
    };
    };

    };
    }

    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

      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.

        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

          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!

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

    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

  26. Hi Yagi!

    Thank you so much for creating this! It is truly amazing!

    I have copied and pasted the code into Apps Script and for some reason I cannot get it to run. I have attached the link of my spreadsheet below. I have also clicked save an run. I am so sorry for the inconvenience.

    https://docs.google.com/spreadsheets/d/1PMrD5ErpF06Rri7Fi7aKdWm1bmzxsxz_G93nbVgov2g/edit#gid=0

    Thank you for any assistance in advance!

    Pattilynne Ouellette.

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

    1. Hi Katy,

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

      Cheers,

      Yagi

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

    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

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

    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

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

    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

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

Leave a Reply