I have a google spreadsheet for requirement gathering with multiple sheets in it.

I want to hide some sheets based on a condition in a spreadsheet.

Example:

There are 4 sheets in a spreadsheet- sheet1, sheet2, sheet3 and sheet4

by default all sheets should be hidden

sheet1 has the values as conditions

Now, if only if sheet2 is marked Yesthen the sheet2 should unhide and sheets sheet3 and sheet4 should be hidden.

Similarly, only the Yes against each sheet name should unhide the respective sheets.

How can we do this in Google sheets with clicks or with a Google apps script.

I searched for a lot but only got answers for hiding cell ranges not sheets/tabs. Please help

CodePudding user response：

Try this:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  var sheet3 = ss.getSheetByName("Sheet3");
  var sheet4 = ss.getSheetByName("Sheet4");
  var cellv = sheet3.getRange('B4');
 
  if (cellv.getValue() == 'No') {
    sheet1.hideSheet();
  }

  if (cellv.getValue() == 'Yes') {
    sheet1.showSheet();
  }

  var cellc = sheet3.getRange('B5');

  if (cellc.getValue() == 'No') {
    sheet2.hideSheet();
  }

  if (cellc.getValue() == 'Yes') {
    sheet2.showSheet();
  }

  var cellb = sheet3.getRange('B7');

  if (cellb.getValue() == 'No') {
    sheet4.hideSheet();
  }

  if (cellb.getValue() == 'Yes') {
    sheet4.showSheet();
  }
}

The function will run based on the values displayed on the cells, the code could be improved by retrieving the range values from B4:B7 but if you will only use 4 sheets then this should do the work.

This is how it will work:

In my testing scenario I was using sheet3 to retrieve the cell value but you can modify it as you need.