-1

I have designed below script from solution given in one of the question posted from the Stakeoverflow community (Show and hide rows and columns based on different dropdowns).

In the solution given in the above link, specific rows/columns are being hide by using the script, I want to hide all the blank rows in place of specific rows.

Script which I am using

function onEdit(e) {
  const n1=e.source.getRangeByName('Sheet3!NamedRange1').getValue();
  const sh2=e.source.getSheetByName("Sheet2");
  if (n1=="A"){sh2.showRows(15,10);}else{sh2.hideRows(15,10);}
}

Any help on above will be greatly appreciated.

Edyphant
  • 155
  • 2
  • 9
  • What sheet to do wish to hide all blank rows on and upon what sort of user edit would you like it to occur. – Cooper Jun 23 '22 at 16:21
  • In Sheet3 I had designed a NamedRange3 i.e. const n1=e.source.getRangeByName('Sheet3!NamedRange1').getValue(); if A is selected in this range then show blank rows and in case of other selection hide blank rows. So onEdit trigger will run on changing the selection in this range. – Edyphant Jun 23 '22 at 16:27

1 Answers1

1

Hide and Show Row based on value in NamedRang1

function onEdit(e) {
  //e.source.toast("Entry")
  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) {
    //e.source.toast("Flag1")
    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)
        }
      }
    });
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • @ Copper Thanks for your reply, the solution given by you works fine. After testing the same I found one more requirement and for the same I am posting a separate question. – Edyphant Jun 24 '22 at 06:20
  • @ Copper, the script was running fine but today I came to one situation in which am facing issue. If i manually hide any row with data the script will not unhide these rows as they are having data so in place of sh2.showRows(i + 1) I want to change this to run command to unhide all hidden rows whether with data or without data. – Edyphant Jul 07 '22 at 15:38
  • Feel free to change it any way that you wish. – Cooper Jul 07 '22 at 15:40
  • @ Copper thanks for your reply, can you share the updated part of script to cover all rows i.e. updated part for this sh2.showRows(i + 1) – Edyphant Jul 07 '22 at 15:42