Hide Sheets Based on a Cell Value

1
votes

I'm pretty new to learning app script and looked over/tried to edit this script, but I'm not getting my desired result. I have a sheet titled "Menu" where I'm wanting a user to select from three different drop down options in Cell A2 (e.g. Blue, Yellow, Green). I then want to hide the different sheets based on the selection. So if a user selects "Blue" I want only the sheets that start with the word "Blue" to be visible + the "Menu" sheet and the rest to be hidden. Same for Yellow and Green. As a note there are 13 sheets for each color.

Any help with this is much appreciated.

2
google-apps-scriptgoogle-sheetsvisibility
Welcome to Stack Overflow. Providing the link is useful for context. But please include the source code inside your question. Thanks! – Elletlar
@Shawn can you share yo spreadsheet please I'll make a pleasure seeing that with you on it. – JSmith
@Jsmith - Here's the sheet docs.google.com/spreadsheets/d/… – SCali

2 Answers

1
votes

try this code:

function onEdit(e)
{
  //filter the range
  if (e.range.getA1Notation() == "A2")
  {
    // get value of cell (yellow||green||...)
    onlySheet(e.value)
  }
}

function onlySheet(str)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //get all sheets
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++)
  {
    //get the sheet name
    var name = sheets[i].getName();
    // check if the sheet is not the "Menu" sheet
    if (name != "Menu")
    {
      // check if the name of the sheet contains the value of the cell, here str
      //if it does then show sheet if it doesn't hide sheet
      if (name.match(new RegExp(str, "gi")))
        sheets[i].showSheet();
      else
        sheets[i].hideSheet();
    }
  }
}
2
votes

This is an alternative implementation of @JSmith's answer, using the Sheets REST API to more efficiently hide & unhide a large number of sheets.

To use the Sheets REST API from Apps Script, you will first need to enable it, as it is an "advanced service."

The Sheets API approach enables you to work with the JavaScript representation of the data, rather than needing to interact with the Spreadsheet Service repeatedly (e.g. to check each sheet's name). Additionally, a batch API call is processed as one operation, so all visibility changes are reflected simultaneously, while the Spreadsheet Service's showSheet() and hideSheet() methods flush to the browser after each invocation.

var MENUSHEET = "Menu";
function onEdit(e) {
  if (!e) return; // No running this from the Script Editor.
  const edited = e.range,
        sheet = edited.getSheet();
  if (sheet.getName() === MENUSHEET && edited.getA1Notation() === "A2")
    hideUnselected_(e.source, e.value);
}

function hideUnselected_(wb, choice) {
  // Get all the sheets' gridids, titles, and hidden state:
  const initial = Sheets.Spreadsheets.get(wb.getId(), {
      fields: "sheets(properties(hidden,sheetId,title)),spreadsheetId"
  });
  // Prefixing the choice with `^` ensures "Red" will match "Reddish Balloons" but not "Sacred Texts"
  const pattern = new RegExp("^" + choice, "i");

  // Construct the batch request.
  const rqs = [];
  initial.sheets.forEach(function (s) {
    // s is a simple object, not an object of type `Sheet` with class methods
    // Create the basic request for this sheet, e.g. what to modify and which sheet we are referencing.
    var rq = { fields: "hidden", properties: {sheetId: s.properties.sheetId} };
    // The menu sheet and any sheet name that matches the pattern should be visible
    if (s.properties.title === MENUSHEET || pattern.test(s.properties.title))
      rq.properties.hidden = false;
    else
      rq.properties.hidden = true;
    // Only send the request if it would do something.
    if ((!!s.properties.hidden) !== (!!rq.properties.hidden))
      rqs.push( { updateSheetProperties: rq } );
  });
  if (rqs.length) {
    // Visibility changes will fail if they would hide the last visible sheet, even if a later request in the batch
    // would make one visible. Thus, sort the requests such that unhiding comes first.
    rqs.sort(function (a, b) { return a.updateSheetProperties.properties.hidden - b.updateSheetProperties.properties.hidden; });
    Sheets.Spreadsheets.batchUpdate({requests: rqs}, initial.spreadsheetId);
  }
}

There are a fair number of resources to be familiar with when working with Google's various REST APIs:

A little testing in a workbook with 54 sheets, in which I used the Sheets API to apply some changes and @JSmith's code to revert the changes, showed the API approach to be about 15x faster, as measured with console.time & console.timeEnd. API changes took from 0.4 to 1.1s (avg 1s), while the Spreadsheet Service method took between 15 and 42s (avg 20s).