Concealing Rows Across Multiple Google Sheets When Cell Value Equals "no"

Other Questions Google Sheets
1

I have a main Google Sheets document that feeds data to 4 different product sheets. When there’s no data to pull, these sheets show “no” in every cell. I need to automatically hide these rows containing “no”.

I put together some code that works partially, but I can’t make it function across all 4 sheets at once. I tried using getSheets() and getSheetByName() methods but nothing seems to work properly.

Here’s my current script:

function onOpen() {
  var workbook = SpreadsheetApp.getActive();
  var allSheets = workbook.getSheets();
  var totalSheets = workbook.getNumSheets();

  // display all rows first
  worksheet.showRows(1, totalRows);

  // fetch data from column C
  var columnData = worksheet.getRange('C:C').getValues();

  // loop through each row
  for(var row = 0; row < columnData.length; row++){
    // check if cell contains 'no', then hide the row
    if(columnData[row][0] == 'no'){
      worksheet.hideRows(row + 1);
    }
  }
}

How can I modify this to work on multiple sheets simultaneously?

2

Your script’s on the right track, but you’re missing the loop to go through all sheets. You created the allSheets variable but never actually used it.

Here’s the fix:

function onOpen() {
  var workbook = SpreadsheetApp.getActive();
  var allSheets = workbook.getSheets();
  
  allSheets.forEach(function(worksheet) {
    var totalRows = worksheet.getMaxRows();
    worksheet.showRows(1, totalRows);
    
    var columnData = worksheet.getRange('C:C').getValues();
    
    for(var row = 0; row < columnData.length; row++){
      if(columnData[row][0] == 'no'){
        worksheet.hideRows(row + 1);
      }
    }
  });
}

This’ll work, but Google Apps Script gets messy fast for this stuff. Every time someone opens the sheet, it has to process all that data again.

I’d set up a proper automation workflow instead. Latenode can monitor your main sheet for changes and automatically update visibility rules across all product sheets in real time. No manual triggers needed.

With Latenode, you can create a workflow that watches for data changes, processes the “no” values, and manages row visibility across multiple sheets at once. Way more reliable than running scripts every time someone opens the file.

You can also add notifications when rows get hidden or set up alternative actions instead of just hiding rows.

3

Running scripts on sheet open is a nightmare. You’ll hit execution limits and timeouts with multiple sheets every time.

I fixed this by moving everything outside Google Sheets. Set up automation that watches your main sheet and handles row visibility automatically.

No onOpen triggers or manual runs needed. Main sheet updates with “no” values? Automation catches it instantly and hides rows across all 4 sheets.

You can add extras like hiding rows only during business hours, alerts when rows disappear, or archiving hidden data for later.

Way more reliable than Apps Script timeouts and won’t slow your sheet loading. Users see clean sheets without the processing mess.

Latenode works great with Google Sheets - whole workflow takes maybe 10 minutes to set up. No coding needed.

4

You’re referencing undefined variables. Your code declares allSheets but then tries to use worksheet and totalRows which don’t exist. Here’s what works:

function onOpen() {
  var workbook = SpreadsheetApp.getActive();
  var allSheets = workbook.getSheets();
  
  for (var i = 0; i < allSheets.length; i++) {
    var currentSheet = allSheets[i];
    var lastRow = currentSheet.getLastRow();
    
    if (lastRow > 0) {
      currentSheet.showRows(1, lastRow);
      var columnData = currentSheet.getRange('C1:C' + lastRow).getValues();
      
      for (var row = 0; row < columnData.length; row++) {
        if (columnData[row][0] === 'no') {
          currentSheet.hideRows(row + 1);
        }
      }
    }
  }
}

I use this same approach for automation stuff. Main fixes: define the worksheet variable inside the loop and use getLastRow() instead of processing everything. Way faster with big datasets.

5

two problems here: you’re not looping thru ur sheets array, and onOpen() fires every time someone opens the file - super annoying. switch to a time-driven trigger that runs every few minutes. won’t slow down loading and does the job automatically.

6

you didn’t define worksheet and totalRows in your code. try using a for loop instead of forEach - it’s usually more reliable with the sheets API.

7

Had this exact problem building a dashboard with multiple product sheets. It’s not just the undefined variables - performance kills you too. Your script will timeout on large sheets since you’re processing entire columns.

Here’s what actually worked:

function hideNoRows() {
  var workbook = SpreadsheetApp.getActive();
  var sheets = workbook.getSheets();
  
  sheets.forEach(function(sheet) {
    var lastRow = sheet.getLastRow();
    if (lastRow === 0) return;
    
    // Process in batches to avoid timeout
    var batchSize = 100;
    for (var startRow = 1; startRow <= lastRow; startRow += batchSize) {
      var endRow = Math.min(startRow + batchSize - 1, lastRow);
      var range = sheet.getRange('C' + startRow + ':C' + endRow);
      var values = range.getValues();
      
      for (var i = 0; i < values.length; i++) {
        if (values[i][0] === 'no') {
          sheet.hideRows(startRow + i);
        }
      }
    }
  });
}

Skip onOpen() for this - it makes everything slow to load. Use a manual trigger or onChange() for automation. If you’ve got tons of rows, batch operations with RangeList.hide() work better.