0

I am using the below code to hide and unhide rows which I got from the below question posted in Stakeoverflow community.

Hide Blank Rows In Google Sheets Based On Selection

The code runs when I change the selection in "NamedRange1" i.e. Cell No. A1 of Sheet3. If I select A in this section then blank rows in Sheet2 will get hide and if I select B then the rows will get unhide.

The code runs fine and hides unhides the rows but I am getting issue in below case:

I selected A in NamedRange1 so blank rows in Sheet2 will get hide, then I manually hide one row (Say row No.7) in Sheet2 which is having data and now if I select B in NamedRange1 then the script will unhide hidden rows except Row No. 7. I want to update the script so it can unhide all the hidden rows.

For reference am sharing the link of the sheet which is having the sample data as well as the code.

https://docs.google.com/spreadsheets/d/1s0El5dqirom3UMX2tGihYbdZM8ldWku9F_xqQXi31Ws/edit#gid=1228899550

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) {
//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)
    }
  }
});
}
}

Any help on above will be appreciated.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Edyphant
  • 155
  • 2
  • 9
  • About `but automatic by importrange function, the OnEdit trigger is not getting activated, is then any trigger which identifies the data changed by importrange function and runs the trigger.`, in your situation, how is the original range of "importrange function" edited? – Tanaike Jul 08 '22 at 08:54
  • @ Tanaike Thanks for reply... The original rang is in another spreadsheet and it is being edited manually. – Edyphant Jul 08 '22 at 09:16
  • Thank you for replying. In that case, for example, how about executing the script on the sheet of original range? In this case, you can run the script by the installed OnEdit trigger. But, if this was not your expected direction, I apologize. – Tanaike Jul 08 '22 at 09:40
  • @ Tanaike thanks for the reply, I want to hide the rows in the second sheet not in the original sheet, so if I run the script on the original sheet the rows will get hide on the original sheet, I want to hide the rows in the second sheet. – Edyphant Jul 08 '22 at 11:00
  • Thank you for replying. I have to apologize for my poor English skill. In my proposal, when your 2nd sheet is edited, your current script will be used. But, I thought that when the original range of "importrange" is edited at the original sheet, in order to trigger the function of `onEdit` in 2nd sheet, the installable OnEdit trigger might be able to be used at the original sheet. I apologize again if I can't still explain it well – Tanaike Jul 08 '22 at 12:02
  • @ Tanaike thanks for the reply, I will try that out, as regards 1st issue.. sh2.showRows(i + 1) this command runs to unhide the hidden rows, it unhides only blank rows, I want to update it to unhide all the rows whether blank or whether having data. Can you guide in undating this command so it can unhide all the rows. – Edyphant Jul 08 '22 at 12:22
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't understand `Can you guide in undating this command so it can unhide all the rows.`. – Tanaike Jul 08 '22 at 12:27
  • In my script I am using below command to hide and unhide (1) To hide "sh2.hideRows(i + 1)"; and (2) To unhide "else {sh2.showRows(i + 1)}", the 2nd part sh2.showRows(i + 1) will unhide blank rows, what should I use in place of sh2.showRows(i + 1) to unhide all the rows. – Edyphant Jul 08 '22 at 12:45
  • Ask only one question per post. – TheMaster Jul 08 '22 at 13:07
  • @ TheMaster thanks for the great suggestion, I will post the query in a separate question. – Edyphant Jul 08 '22 at 13:17
  • 2
    Your question still contains 2 specific questions/changes. Provide more focus. If I focus on first, I would say your conclusion is wrong. showRows always shows all the rows. There is no filter based on whether that row has data or not. I would do extensive debugging to figure out the actual issue. Provide screenshots, tables, before/after results, the formula used and so on. – TheMaster Jul 08 '22 at 15:22
  • @ TheMaster Thanks for the reply, I am sharing the link of the sheet. If you select A in Sheet3 cell No. A1 then blank rows in Sheet2 will get hide. If you select B in Sheet3 cell A1 then hidden rows will get unhide. Now after selecting A, if I manually hide any more rows in sheet2 and then select B in sheet3 then all the rows will not get unhide. https://docs.google.com/spreadsheets/d/1s0El5dqirom3UMX2tGihYbdZM8ldWku9F_xqQXi31Ws/edit#gid=1228899550 – Edyphant Jul 08 '22 at 16:36
  • (1/2)Your question seems like a completely new question now and would benefit as a new question with tables. But regarding your old question, *sh2.showRows(i + 1) will unhide blank rows, what should I use in place of sh2.showRows(i + 1) to unhide all the rows*, you just need to remove this line `if (r.every(e => e == '')) {` and the corresponding `}` ( that line is the blank checker) – TheMaster Jul 30 '22 at 21:07
  • (2/2)Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Jul 30 '22 at 21:07
  • @ TheMaster Thanks for your valuable solution and precious suggestion of posting a new question and of using tables in place of sheets. Special thanks for bringing to notice the security aspects of sharing a sheet. – Edyphant Aug 01 '22 at 06:27
  • If the solution worked, you can rollback your question and add a answer here. – TheMaster Aug 01 '22 at 07:14
  • It partially worked i.e. it un-hides all the rows i.e. blank as well as rows having data but on the same hand it hides all the rows i.e. blank as well as rows having data. To keep the discussion clear and more helpful to the community, as suggested by you I will post a new question. Once again thanks for the support and valuable inputs. – Edyphant Aug 01 '22 at 11:54

0 Answers0