Google Sheet Hide Blank Row But Exclude Specific Rows

Question

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.

About `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 your showing script, the data range of "Sheet2" is retrieved. If the rows the row 80 and after row 80 is empty, the data range is "A1:B79". In this case, I think that your showing script doesn't check row 80. Can I ask you about the detail of your goal? — Tanaike, Jun 24 '22 at 07:07
@ Tanaike thanks for your reply.... My goal is to hide all the blank rows in the entire sheet except few specific rows... — Edyphant, Jun 24 '22 at 10:41
In another sheet say for example Sheet3 I had define Cell A1 as NameRange1 and when I change the selection in A1 the trigger of onEdit will run. For your reference I am sharing the sheet. https://docs.google.com/spreadsheets/d/1s0El5dqirom3UMX2tGihYbdZM8ldWku9F_xqQXi31Ws/edit#gid=1735319681 — Edyphant, Jun 24 '22 at 11:29

score 1 · Accepted Answer · answered Jun 24 '22 at 11:54

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.

Google Sheet Hide Blank Row But Exclude Specific Rows

1 Answers1

Modified script: