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