Can we hide sheets according to the content page row?

Question

Is it possible to hide the sheets according to the register row? I can get it done in excel sheet not sure if google sheet can do so.

I have a register showing the list of activities and the location. Each activity shown on the register is link to a google sheet and the sheets name is according to cell B4:B. The register will show the relevant row according to the drop down list in cell G2. onEdit(e) function already created to hide row when there is a change in the drop down list.

Sample of register: Imgur Picture tab name: Imgur

If the drop down list is change to hub 01 Imgur, only the relevant sheets will be shown, irrelevant sheets will remain hidden.

The first 5 sheets must be shown at all time. I try to follow the example Hide Sheets Based on a Cell Value and create a function to hide the sheet. But it hide every sheets.

 function HideSheet()
{
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheets = ss.getSheets();

for ( var i=0; i<sheets.length; i++) 
{
var name = sheets[i].getName();
if (name != "Site Location" ,"Risk Register", "RM team member", "Risk Matrix", "Register History")
{ 
var value = ss.getSheetByName('Risk Register').getRange('B4:B').getValue();
if (name.match(new RegExp(value, 'gi')))
sheets[i].showSheet();
else
sheets[i].hideSheet();
}
}
}

Tanaike Tanaike · Accepted Answer · 2020-05-27T05:52:44

I believe your goal as follows.

You want to show the sheets with the sheet names retrieving from the column "B4:B".
- The rows are shown by the basic filter. You want to hide sheets with the sheet names of rows hidden by the basic filter.
- You always want to show the sheets of "Site Location" ,"Risk Register", "RM team member", "Risk Matrix", "Register History"`.

For this, how about this answer?

Modification points:

In your script,
- if (name != "Site Location" ,"Risk Register", "RM team member", "Risk Matrix", "Register History") is not correct. In this case, it is requried to be if (name != "Site Location" && name != "Risk Register" ,,,.
- var value = ss.getSheetByName('Risk Register').getRange('B4:B').getValue() is the value of "B4".
- The filtered rows cannot be checked. So all values are always used.

When above points are reflected to your script, it becomes as follows. The flow of this modified script is as follows.

Create object for searching the shown sheet names.
Create object for searching the current sheets.
Retrieve values from the column "B".
Hide or show the sheets using the created objects by checking the filtered row.

Modified script:

function HideSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. Create object for searching the shown sheet names.
  var shownSheetNames = ["Site Location" ,"Risk Register", "RM team member", "Risk Matrix", "Register History"];
  var obj = shownSheetNames.reduce((o, e) => Object.assign(o, {[e]: true}), {});

  // 2. Create object for searching the current sheets.
  var sheetsObj = ss.getSheets().reduce((o, e) => Object.assign(o, {[e.getSheetName().toLowerCase()]: e}), {});

  // 3. Retrieve values from the column "B".
  var sheet = ss.getSheetByName("Risk register");
  var values = sheet.getRange("B1:B" + sheet.getLastRow()).getValues();

  // 4. Hide or show the sheets using the created objects by checking the filtered row.
  for (var r = 4; r <= sheet.getLastRow(); r++) {
    var sheetName = values[r - 1][0].toLowerCase();
    if (sheetName && sheetsObj[sheetName]) {  // <--- Modified
      if (sheet.isRowHiddenByFilter(r)) {
        if (!obj[sheetName]) sheetsObj[sheetName].hideSheet();
      } else {
        sheetsObj[sheetName].showSheet();
      }
    }
  }
}

In this modification, you can also use this script by putting HideSheet() to the function of onEdit as your question saying.

Can we hide sheets according to the content page row?

1 Answers

Modification points:

Modified script:

References: