Google Sheet Hide Blank Row But Exclude Specific Rows-CodePudding

I am trying to hide blank rows in Google Sheet based on edit i.e. change of selection in 'Sheet3!NamedRange1'. I had taken support of these two questions posted in Stakeoverflow community for the same (Hide and Unhide Specific Blank Rows With the Same Button) and (Hide Blank Rows In Google Sheets Based On Selection).

I am using below code to get the desired result. The code hides blank rows but only upto the last row having data i.e. if last row with data is Row No. 80 then after row no. 80 the script will not hide the rows even if the rows are blank. I want to hide all the blank rows.

In addition to above I want to keep specific blank rows unhide based on the row no. E.g. Donot hide Row No. 12, 15 and 18 even if they are blank.

function onEdit(e) {
  const sh = e.range.getSheet();
  const rg = e.source.getRangeByName("NamedRange1");
  const sr = rg.getRow();
  const sc = rg.getColumn();
  const er = sr   rg.getHeight() - 1;
  const ec = sc   rg.getWidth() - 1;
  if (sh.getName() == "Sheet3" && e.range.columnStart >= sc && e.range.columnStart <=
    ec && e.range.rowStart >= sr && e.range.rowStart <= er && e.value) {
    const sh2 = e.source.getSheetByName("Sheet2");
    const vs = sh2.getDataRange().getValues();
    vs.forEach((r, i) => {
      if (r.every(e => e == '')) {
        if (e.value == "A") {
          sh2.hideRows(i   1);
        } else {
          sh2.showRows(i   1)
        }
      }
    });
  }
}

Any help on above will be appreciated.

CodePudding user response：

I believe your goal is as follows.

You want to hide all empty rows in "Sheet2" when the value of cell "A1" of "Sheet3" is A.
In this case, you want to exclude the specific rows from the hidden rows.

In this case, how about the following modification?

Modified script:

Before you use this script, please show all rows, and test the script. Please set the excluded rows to excludeRows. In this sample, rows 12, 15, and 18 are excluded from the hidden rows.

function onEdit(e) {
  const excludeRows = [12, 15, 18]; // Please set excluded row numbers.

  // Ref: https://gist.github.com/tanaikech/5a43281964b739ead2b7ae2401400630
  const compilingNumbers = ar => {
    const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce((o, e, i, a) => {
      if (o.temp.length == 0 || (o.temp.length > 0 && e == o.temp[o.temp.length - 1]   1)) {
        o.temp.push(e);
      } else {
        if (o.temp.length > 0) {
          o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
        }
        o.temp = [e];
      }
      if (i == a.length - 1) {
        o.values.push(o.temp.length > 1 ? { start: o.temp[0], end: o.temp[o.temp.length - 1] } : { start: e, end: e });
      }
      return o;
    }, { temp: [], values: [] });
    return values;
  };
  const sh = e.range.getSheet();
  const rg = e.source.getRangeByName("NamedRange1");
  const sr = rg.getRow();
  const sc = rg.getColumn();
  const er = sr   rg.getHeight() - 1;
  const ec = sc   rg.getWidth() - 1;
  if (sh.getName() == "Sheet3" && e.range.columnStart >= sc && e.range.columnStart <=
    ec && e.range.rowStart >= sr && e.range.rowStart <= er && e.value) {
    const sh2 = e.source.getSheetByName("Sheet2");
    const vs = sh2.getRange(1, 1, sh2.getMaxRows(), sh2.getMaxColumns()).getDisplayValues();
    const rows = vs.reduce((ar, r, i) => {
      if (!excludeRows.includes(i   1) && r.join("") == "") ar.push(i   1);
      return ar;
    }, []);
    const method = e.value == "A" ? "hideRows" : "showRows";
    compilingNumbers(rows).forEach(({ start, end }) => sh2[method](start, end - start   1));
  }
}

In this script, when the value of cell "A1" of "Sheet3" is A, the empty rows in "Sheet2" are hidden by excluding the specific rows.