Search code examples
google-apps-script

Automatically Hide and Show Sheets Using Sheet Names From Cell Reference In Google Script

I am trying to fix a script that will automatically show and then hide the sheets upon clicking a button. First click, it will show the sheets then if you click it again, it will hide the sheets, then third click it will show the sheets again, so on and so forth. The sheet names that I want to show and hide automatically are listed in a cell in "Etc" sheet from Cells O3:O8.

function OpenHideMultipleSheetsFromCellRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWithNames = ss.getSheetByName('Etc'); // The sheet where your sheet names are stored
  
  // Adjust the range (e.g., 'A1:A10') to the cells where the sheet names are listed
  var range = sheetWithNames.getRange('O3:O8'); 
  
  // Get all the sheet names from the specified range
  var sheetNames = range.getValues();
  
  for (var i = 0; i < sheetNames.length; i++) {
    var sheetName = sheetNames[i][0]; // Get the sheet name from each cell in the range
    
    var sheet = ss.getSheetByName(sheetName);
    
    if (sheet) {
      ss.setActiveSheet(sheet);
      // Optional delay between opening each sheet
      Utilities.sleep(2000);  // Pause for 2 seconds (adjust as needed)
    } else {
      Logger.log("Sheet '" + sheetName + "' not found.");
    }
  }
}
Solution

  • In your situation, how about the following modification?

    Modified script:

    function OpenHideMultipleSheetsFromCellRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWithNames = ss.getSheetByName('Etc');
  var range = sheetWithNames.getRange('O3:O8');

  // I modified the below script.
  range.getDisplayValues()
    .forEach(([name]) => {
      const sheet = ss.getSheetByName(name);
      if (sheet) {
        sheet[sheet.isSheetHidden() ? "showSheet" : "hideSheet"]();
      }
    });
}

    When this script is run, the sheet names are retrieved from "Etc!O3:O8". When the sheets are shown, those are hidden, and vice versa.

    Note:

    • If you want to run the script by clicking a button on Spreadsheet, please assign the function "OpenHideMultipleSheetsFromCellRange" to the button.

    • As additional information, in this modification, it supposes that all sheets of "O3:O8" are in the same situation as "show" or "hide". If the situations "show" and "hide" are mixtures, in order to achieve your goal, it might set the initial situation. If you want it, please run the following script. By this, all sheets of "O3:O8" are shown. By this, when you run the above script, the situation of the sheet is switched between "show" and "hide".

      function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWithNames = ss.getSheetByName('Etc');
  var range = sheetWithNames.getRange('O3:O8');
  range.getDisplayValues()
    .forEach(([name]) => {
      const sheet = ss.getSheetByName(name);
      if (sheet) {
        sheet.showSheet();
      }
    });
}

    References: