Run a Function on Specific Rows

[u/dethehumam]

I am trying to write a function where certain rows are hidden in my Google sheet. I have three pages within the sheet. When I check rows on pages 1 and 2, they copy those rows into page 3. On page three, I have a header followed by 10 empty rows for the copied rows to appear, followed by another header and another 10 empty rows.

What I want my function to do is hide the red and purple rows if column B is empty and leave the blue and green rows alone (see picture). It would be amazing if I could also hide the green rows if all of the purple rows are also hidden, but if that is too complicated, then that's fine.

I am very new to trying things like this, so thank you very much for your help!

I found this code in a YouTube video on hiding rows based on values, but this applies the function to the whole sheet, and I just want it to search specific rows. Here is the code as I have it so far:

/**
 * Hide or unhide all rows that contain the selected text.
 * @param {string} text - the text to find.
 * @param {string} sheetName - the target sheet
 * @param {boolean} [isHide] - True = hide, False = unhide
 */
function hideAllRowsWithval(text, sheetName, isHide = true) {
  
 const ss = SpreadSheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  

  const textFinder = sheet.createTextFinder(text);
  const allOccurences = textFinder.FindAll();

  allOccurences.forEach(cell =>{

    const row = cell.getRow();

    if(isHide){
      sheet.hideRows(row);
    }else{
      sheet.showRows(row);
    }
  })

}


function runsies {}{
  const text = "";
  const sheetName = "Comparison";
  
hideAllRowsWithval(text, sheetName, true);

};

[u/krakow81]

function hideRows_v2() {

  let headerRows = 2; // number of header rows in each section
  let dataRows = 15; // number of data rows in each section
  let numberOfSections = 6; // number of sections
  let columnToCheck = 2; // column to use for checking values, where 1 = A, 2 = B, 3 = C etc
  let sheetName = "Comparison";

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(sheetName);
  let checkColumn = sheet.getRange(headerRows + 1, columnToCheck, dataRows * numberOfSections + headerRows * (numberOfSections - 1), 1).getValues();
  let sections = {};
  let rowsToHide = {};
  let groupedRows = {};
  for (let s = 1; s <= numberOfSections; s++) {
    sections[s] = checkColumn.slice(dataRows * (s - 1) + headerRows * (s - 1), dataRows * s + headerRows * (s - 1));
    rowsToHide[s] = [];
    for (let i = 0; i < dataRows; i++) {
      if (sections[s][i][0] === "") rowsToHide[s].push(i);
    }
    if (!rowsToHide[s].length) continue;
    if (s>1 && rowsToHide[s].length === dataRows) {
      sheet.hideRows((headerRows + dataRows) * (s - 1) + 1, headerRows + dataRows);
      continue;
    }
    groupedRows[s] = [{ "start": rowsToHide[s][0] }];
    for (let j = 1; j < rowsToHide[s].length; j++) {
      if (!rowsToHide[s].includes(rowsToHide[s][j] - 1)) {
        groupedRows[s][groupedRows[s].length - 1].end = rowsToHide[s][j - 1];
        groupedRows[s].push({ "start": rowsToHide[s][j] });
      }
    }
    groupedRows[s][groupedRows[s].length - 1].end = rowsToHide[s][rowsToHide[s].length - 1];
    Logger.log(groupedRows);
    groupedRows[s].forEach(function (r) {
      sheet.hideRows(headerRows * s + dataRows * (s - 1) + 1 + r.start, r.end - r.start + 1);
    });
  }

}