How do i automatically hide rows in google sheets?

I created a custom menu in App Scripts with 3 sub-menus:

1. hide a specific set of rows
2. hide another set of rows
3. unhide everything

Basically, people can click on any of them for better visibility. The issue with my current script is that if I put a filter, the rows won't be matching the ones I want to hide...

Is there any ways to hide rows based on the value of one of the cell instead of row number? So that no matter what data we are filtering, the needed rows will remain hidden.

Below the part of the code I believe I need to change:

function hideRowsC() {
  const obj = [{ sheetName: "Keyword Report", hide: [3,7,20,25,37,43,49,53,57,60,68,77 ] }];
  sample_(obj);
}

My entire script below if it helps solving my issue.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom View')
    .addItem('Keywords Only', 'hideRowsC')
    .addItem('Categories Only', 'hideRowsK')
    .addItem('View all', 'showRows')
    .addToUi();
}

function showRows() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  showRowsInAllSheets_(sheets);
}

function hideRowsC() {
  const obj = [{ sheetName: "Keyword Report", hide: [3,7,20,25,37,43,49,53,57,60,68,77 ] }];
  sample_(obj);
}

function hideRowsK() {
  const obj = [{ sheetName: "Keyword Report", hide: [4,5,6,8,19,24,26,27,28,50,52,58,59,61,76,80,81,82 ] }];
  sample_(obj);
}

function sample_(obj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  showRowsInAllSheets_(sheets);
  const sheetObj = sheets.reduce((o, s) => (o[s.getSheetName()] = s, o), {});
  obj.forEach(({ sheetName, hide }) => {
    if (sheetObj[sheetName]) {
      hide.forEach(h => sheetObj[sheetName].hideRows(h, 1));
    }
  });
}

function showRowsInAllSheets_(sheets) {
  sheets.forEach(s => s.showRows(1, s.getMaxRows()));
}

You can hide rows based on cell value using the Apps Script feature of Google Sheets. This is one way of filtering out data from your spreadsheet.

Table of Contents

1. What is Apps Script in Google Sheets?
2. A Real Example of Hiding Rows Using Apps Script
3. How to Hide Rows Based on Cell Value using Apps Script in Google Sheets
4. How to Hide or Show Rows using the Custom Menu

When working with a spreadsheet with lots of data, there are times that you need to hide some records based on cell value. These could be records that have been processed or those that are no longer needed. Google Sheets offers various ways to do this, and the most common is to create a filter that you can use to filter out your records manually.

While this technique gets the job done, it could be cumbersome to use it over time as you make changes to your spreadsheet. There’s actually a more seamless way to achieve this—using the Apps Script extension of Google Sheets.

Apps Script is a Google Sheets extension that lets you create your own programs. You can use Apps Script to create menus, dialogs, and custom functions to automate processes. This extension is completely free, and it’s available out of the box when you launch Google Sheets.

A Real Example of Hiding Rows Using Apps Script

For you to appreciate what Apps Script can do, let’s use it in a real-world scenario. Let’s take the spreadsheet below as an example.

Based on the data, we can assume that the spreadsheet serves as a tracker for people who have a remaining balance. Suppose we want to hide the records that have been paid, we can create a custom menu similar to the one below using the Apps Script extension.

Ideally, when the Filter rows command is clicked, it will automatically hide all those whose remaining balance has been paid.

Notice that some of the records above, particularly those that were checked earlier, have been hidden from the spreadsheet.

Hiding rows based on cell value is just one of the many things you can do in Google Sheets using its Apps Script extension. In the next section, we’ll tackle the step-by-step process on how we can achieve this. Click the link below to make a copy of our example spreadsheet.

How to Hide Rows Based on Cell Value using Apps Script in Google Sheets

For this tutorial, you’ll learn how to create a custom menu that contains two commands using Apps Script. The first command filters out or hides the records that have been marked as paid, while the other command shows all the records.

1. Start by opening a copy of the example spreadsheet provided earlier. Upon opening, you should have this dataset:
   
   
   
2. You’ll notice that the third column (column C) already contains checkboxes. Perhaps you’re already aware that when you use a checkbox in Google Sheets, you’ll be dealing with two states—check and uncheck. Behind the scenes, Google Sheets treat them as True and False. So, when you check a checkbox, Google Sheets will tag it as True, and False if otherwise. Keep this information in mind as we will use it later for this tutorial.
3. Next, take note also of the name of the sheet where you intend to place the custom menu. In our case below, it’s ‘Sheet1’.
   
4. At this point, we are now ready to create our custom menu. To do this, click the Extensions menu, and choose Apps Script.
   
5. Upon clicking, your browser will open a new tab that looks like this:
   
   
   As mentioned earlier, Apps Script is used for creating programs, so, expect that we’ll be dealing with codes for the succeeding steps. Don’t worry if you’re not quite familiar with coding in Apps Scripts as you’ll be guided every step of the way.
6. Within the Apps Script interface, you’ll see that there’s already a premade function named ‘myFunction()’.
   
   
   In the world of programming, a function is simply a block of codes that can be used anytime to accomplish a specific task. You’ll notice that the default function doesn’t contain anything, so it’s useless now. Delete the default function to make space for the functions that we will create.
   
7. Since we aim for two commands (hiding and showing records), we’ll need to create two separate functions. Each function will have its own set of codes that will do a specific task.
8. For the first function, we’ll name it ‘filterRows()’. This function will hide or filter out the records that are tagged as paid.
   Type or copy the following codes on your editor:

   function filterRows() {  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1")
   
     var data = sheet.getDataRange().getValues();
   
     for(var x=1; x
   
       if (data[x][2]===true) {
   
         sheet.hideRows(x+1);
   
       }
   
     }
   
   }

   Once you’re done typing or pasting, your editor should already look like this:
   

   
   So, what does this block of codes means?
   For the first line, we referenced the Sheet’s name, Sheet1, and assigned it to a variable named ‘sheet’. This is where we want our function to operate.
   
   
   Next, we tried to get all the values from Sheet1 and assigned them to the variable called ‘data’.
   
   
   
   Afterwards, we used the for loop to iterate through all the elements or rows of our data. Within this part, we tested if the third column of each element contains ‘true’, which is signified by a check mark on its checkbox. If it does, the ‘hideRows()’ method automatically hides the entire row from the spreadsheet.
   
   
   We are now done with the function to hide a row based on the value of its checkbox.

9. Now, let’s create another function that we can call anytime we want to unhide all rows and name it as showAll(). Below the function we have created earlier, type or copy the following codes:

   function showAll() {  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
   
     sheet.showRows(1, sheet.getMaxRows());
   
   }

   Your editor should now look like this:
   

   
   You might be wondering, how does the ‘showAll()’ function work?

   Like our previous function, we referenced the sheet where we want the ‘showAll()’ function to work in the first line.
   

   
   After that, we used the ‘showRows()’ method to unhide all rows within the sheet.
   

   
   Good job! We are now done with our two functions. But, there’s one more thing we need to do. We need to develop a way for users to access these functions. In our case, the best way to achieve this is to have a custom menu. That’s what we are going to do in the next steps.

10. In order for our spreadsheet to have a custom menu, we’ll need to create another function, called ‘onOpen()’. This function will contain all the codes needed to create a functional menu. Below the two functions we created earlier, copy or type the following codes:

    function onOpen() {  SpreadsheetApp.getUi().createMenu("Filter Rows")
    
        .addItem("Filter rows", "filterRows")
    
        .addItem("Show all", "showAll")
    
        .addToUi();
    
    }

    Your editor should now be similar to this:
    

11. At this point, you should now have three functions in your editor. Save all your changes by clicking the Save project button located on top of your editor.
    

Great! We just created a simple program that will hide rows based on a cell value and show them anytime we want. In the next section, you will learn how to use this program.

It’s time to test the Custom Menu program we have created earlier to hide or show rows in our spreadsheet.

1. First, make the Remaining Balance spreadsheet active by clicking its tab on your browser.
2. Then, check if your spreadsheet already has the Filter Rows custom menu, as shown below. If it doesn’t exist yet, try refreshing your web page.
   
3. To hide rows based on cell value, simply click the Filter Rows menu, and choose the Filter Rows command.
   
   
   As you click on the command, notice that Google Sheets will automatically hide the records that have been tagged as paid on your spreadsheet.
   
4. If you want to unhide all rows, click the Filter Rows menu, and select Show All.
   
   
   
   Upon clicking, Google Sheets will show all hidden rows in your spreadsheet.
   
   
   

Perfect! Now you know how to hide rows based on a cell value and show them anytime using Apps Script in Google Sheets.

Learn more awesome techniques by checking out our other articles about Google Sheets.

Subscribe to our newsletter if you want to receive more useful articles like this one about Google Sheets.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

As an IT professional, Google Sheets and MS Excel are included in my arsenal of skills. In fact, they are my go-to tools for organizing and analyzing data. I deal with large data sets every day, but I don't have to worry since these tools have me covered. I would love to share everything I know about them, so, make sure to catch up!

How do you automatically hide rows based on cell value?

How do you quickly hide rows in Google Sheets?

Is there a hide function in Google Sheets?