Automatically conceal rows with 'no' entries across multiple Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m working on a project with one main Google Sheet and four smaller ones that pull data from it. When there’s no relevant info, the smaller sheets show ‘no’ in all cells. I want to hide these rows automatically.

I’ve cobbled together a script that kind of works, but I can’t get it to run on all four sheets. I’ve tried using getSheets() and getSheetByName(name) with all the sheet names, but no luck.

Here’s my current code:

function updateSheetVisibility() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = workbook.getSheets();
  
  allSheets.forEach(sheet => {
    const dataRange = sheet.getRange('C:C');
    const values = dataRange.getValues();
    
    values.forEach((value, index) => {
      if (value[0].toLowerCase() === 'no') {
        sheet.hideRows(index + 1);
      } else {
        sheet.showRows(index + 1);
      }
    });
  });
}

Any ideas on how to make this work across all sheets? I’m stumped!

2

hey alexj, have u tried using a named range for the ‘no’ column? might make it easier to reference across sheets. also, u could use getSheetById() instead of getSheetByName() if ur havin trouble with names. just a thought, hope it helps!

3

I’ve faced a similar challenge with multiple sheets in Google Sheets. Your approach is on the right track, but there might be a few tweaks needed to make it work across all sheets.

First, ensure you’re running the script with the correct permissions. Sometimes, scripts won’t work on all sheets due to permission issues.

Secondly, consider adding error handling. I’ve found that sometimes certain sheets might not have the expected structure, causing the script to fail.

Lastly, you might want to add a trigger to run this script automatically whenever changes are made to the main sheet. This way, you don’t have to manually run it each time.

Here’s a modified version of your script that might help:

function updateSheetVisibility() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = workbook.getSheets();
  
  allSheets.forEach(sheet => {
    try {
      const dataRange = sheet.getRange('C:C');
      const values = dataRange.getValues();
      
      values.forEach((value, index) => {
        if (value[0].toString().toLowerCase() === 'no') {
          sheet.hideRows(index + 1);
        } else {
          sheet.showRows(index + 1);
        }
      });
    } catch (error) {
      Logger.log('Error in sheet: ' + sheet.getName() + '. ' + error.toString());
    }
  });
}

Hope this helps! Let me know if you need any clarification.

4

Your approach is sound, but there might be a performance issue when dealing with multiple sheets. Instead of iterating through each row, consider using a batch update to improve efficiency. Here’s a modified version that should work faster:

function updateSheetVisibility() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = workbook.getSheets();
  
  allSheets.forEach(sheet => {
    const dataRange = sheet.getRange('C:C');
    const values = dataRange.getValues();
    const rowsToHide = [];
    
    values.forEach((value, index) => {
      if (value[0].toString().toLowerCase() === 'no') {
        rowsToHide.push(index + 1);
      }
    });
    
    sheet.showRows(1, sheet.getMaxRows());
    if (rowsToHide.length > 0) {
      sheet.hideRows(rowsToHide[0], rowsToHide.length);
    }
  });
}

This version first collects all rows to hide, then applies the changes in one go. It should significantly speed up the process when dealing with large datasets across multiple sheets.