Sidebar To Show/Hide Sheet With Apps Script

I have a spreadsheet with many sheets.

To make it easier to access, I tried to create a sidebar to choose which sheets I would hide or show.

Unfortunately, my script doesn't run perfectly. The sidebar can now appear, but when executed, the sheet cannot change to appear or be hidden according to what is selected in the sidebar.

// Function to display sidebar
 function showSheetSidebar() {
     var htmlOutput = HtmlService.createHtmlOutputFromFile('SheetSidebar')
                      .setWidth(300)
                      .setHeight(250);

      
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet List');
     var lastRow = sheet.getLastRow();
     var sheetIds = sheet.getRange('C9:C' + lastRow).getValues().flat();  
     var sheetNames = sheet.getRange('B9:B' + lastRow).getValues().flat();    

    var checkboxesHtml = sheetNames.map(function(name, index) {
        return '<input type="checkbox" id="sheet' + index + '" value="' + sheetIds[index] + '">' +
       '<label for="sheet' + index + '">' + name + '</label><br>';
       }).join('');

       htmlOutput.append('<script>document.getElementById("checkboxes").innerHTML = `' + checkboxesHtml + '`;</script>');

   SpreadsheetApp.getUi().showSidebar(htmlOutput);
   }

   // Function to display selected sheets and hide other sheets
  function showSelectedSheets(selectedSheets) {
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = spreadsheet.getSheetByName('Daftar Sheet');
      var lastRow = sheet.getLastRow();


     var sheetIds = sheet.getRange('C9:C' + lastRow).getValues().flat();  
     var sheetNames = sheet.getRange('B9:B' + lastRow).getValues().flat();  
 
     sheetIds.forEach(function(sheetId, index) {
     var targetSheet = spreadsheet.getSheetById(parseInt(sheetId));   

    if (targetSheet) {  
       if (selectedSheets.indexOf(sheetId) > -1) {
    
       targetSheet.showSheet(); 
        } else {
       targetSheet.hideSheet();  
        }
       }
     });
   }

The CSS/HTML display is as follows:

  <!DOCTYPE html>
  <html>
  <head>
  <base target="_top">
  </head>
  <body>
  <form id="sheetForm">
  <div>
      <p>Select the Sheet you want to display :</p>
      <div id="checkboxes"></div>
  </div>
  <input type="button" value="APPLY" onclick="applyChanges()" />
</form>

  <script>
     function applyChanges() {
        var checkboxes = document.querySelectorAll('input[type="checkbox"]');
        var selectedSheets = [];
       checkboxes.forEach(function (checkbox) {
        if (checkbox.checked) {
          selectedSheets.push(checkbox.value);
        }
       });
      
       google.script.run.withSuccessHandler(function(response) {
      
       }).showSelectedSheets(selectedSheets);

        google.script.host.close();  
       }
      </script>
   </body>
   </html>

Any suggestions for improvement? Your help would be greatly appreciated.

Solution

Modification points:

In the current stage, there is no method of getSheetById in Class Spreadsheet.
In your situation, I guessed that the sheet names can be used because each sheet name is a unique value in the Spreadsheet.

When these points are reflected in your script, how about the following modification?

Modified script:

Google Apps Script:

function showSheetSidebar() {
  var htmlOutput = HtmlService.createHtmlOutputFromFile('SheetSidebar').setWidth(300).setHeight(250);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet List');
  var lastRow = sheet.getLastRow();
  var sheetNames = sheet.getRange('B9:B' + lastRow).getValues().flat();
  var checkboxesHtml = sheetNames.map(function (name, index) {
    return '<input type="checkbox" id="sheet' + index + '" value="' + name + '">' +
      '<label for="sheet' + index + '">' + name + '</label><br>';
  }).join('');
  htmlOutput.append('<script>document.getElementById("checkboxes").innerHTML = `' + checkboxesHtml + '`;</script>');
  SpreadsheetApp.getUi().showSidebar(htmlOutput);
}

function showSelectedSheets(selectedSheets) {
  var { checked, unchecked } = selectedSheets;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  checked.forEach(e => {
    var sheet = spreadsheet.getSheetByName(e);
    if (sheet) {
      sheet.showSheet();
    }
  });
  unchecked.forEach(e => {
    var sheet = spreadsheet.getSheetByName(e);
    if (sheet) {
      sheet.hideSheet();
    }
  });
}

HTML:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <form id="sheetForm">
    <div>
      <p>Select the Sheet you want to display :</p>
      <div id="checkboxes"></div>
    </div>
    <input type="button" value="APPLY" onclick="applyChanges()" />
  </form>

  <script>
  function applyChanges() {
    var checkboxes = document.querySelectorAll('input[type="checkbox"]');
    var selectedSheets = {checked: [], unchecked: []};
    checkboxes.forEach(function (checkbox) {
      if (checkbox.checked) {
        selectedSheets.checked.push(checkbox.value);
      } else {
        selectedSheets.unchecked.push(checkbox.value);
      }
    });
    google.script.run.withSuccessHandler(function (response) {
      google.script.host.close();
    }).showSelectedSheets(selectedSheets);
  }
  </script>
</body>

</html>

Testing:

When you run the function showSheetSidebar, a sidebar is opened to the Spreadsheet. When you check some checkboxes and click "APPLY" button, the sheets of the checked checkboxes are shown, and the sheets of the unchecked checkboxes are hidden. If you want the opposite way, please modify if (checkbox.checked) of Javascript.

Note:

In this modification, it supposes that the sheet names retrieved by var sheetNames = sheet.getRange('B9:B' + lastRow).getValues().flat(); in the function showSheetSidebar are valid sheet names. Please be careful about this.

Reference:

Class Spreadsheet