CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
William Toscano
William Toscano

Reputation: 225

Hide all sheets that are not listed in named range

To translate my code into English: For each sheet name, check if the sheet name is in the named range "sheetsAlwaysShow". If the iterator J reaches a cell that is blank (""), then the sheet needs to be hidden as it is not listed in the named range. This function works. But is there a faster and more efficient way to approach this?

function hideOtherSheets()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Quick Facts");

  var codeNameRange = ss.getRangeByName("sheetsAlwaysShow");
  var codeNameRangeCol = codeNameRange.getColumn();
  
  var sheets = ss.getSheets();

  //var codeNameRange = ss.getRangeByName("participantCodeNameList");

      //Logger.log("Current Cell Value: " + currentCellVal);
        Outer:
        for (i = 0; i < sheets.length; i++) 
        {

          //Logger.log("Current Cell Value: " + currentCellVal);

          Logger.log("Current Sheet: " + sheets[i].getSheetName());
          Inner:
          for (var j =  s.getFrozenRows(); j <= s.getLastRow(); j++)
          {
            
            var currentCellRange = s.getRange(j, codeNameRangeCol);
            var currentCellVal = currentCellRange.getValue().toString();
            var currentSheetName = ss.getSheetByName(currentCellVal);

            Logger.log("Current Sheet Name: " + currentCellVal);
            

            if (currentCellVal != "" || currentCellVal != "" || currentSheetName != null)
            {

              if (sheets[i].getSheetName() == currentCellVal)
              {
                Logger.log("In Iff");
                //sheets[i].hideSheet();
                break Inner;
              }
       
            }
            Logger.log("Current Iteration for J: " + j)

            if (currentCellVal == "")
            {
              Logger.log("Now Hiding: " + sheets[i].getSheetName());
              sheets[i].hideSheet();
              break Inner;
            }

         
            


          }
        }

}

Upvotes: 0

Views: 337

Answers (1)

Cooper
Cooper

Reputation: 64042

Hide sheets not in list

function hidesheetsnotinlist() {
  const ss = SpreadsheetApp.getActive();
  const list = ss.getRangeByName("sheetsAlwaysShow").getDisplayValues().flat();//assumed only one column
  ss.getSheets().filter(sh => !~list.indexOf(sh.getName())).forEach(sh => sh.hideSheet());
}

bitwise not

Upvotes: 2

Related Questions