Help needed: Google Sheets script to show only visible sheets

Other Questions Google Sheets
1

I’m new to Google Sheets scripts and I’m stuck. I have a script that shows a popup when the sheet opens. It lists all the sheets and lets you pick one to go to. But it’s showing hidden and protected sheets too. I only want it to show the visible sheets that everyone can access.

Here’s what I’ve got so far:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function showSheetPicker() {
  var ui = SpreadsheetApp.getUi();
  var sheets = ss.getSheets();
  var sheetNames = sheets.map(sheet => sheet.getName());
  
  var result = ui.prompt(
    'Pick a sheet',
    'Choose a sheet to go to:',
    ui.ButtonSet.OK_CANCEL
  );

  if (result.getSelectedButton() == ui.Button.OK) {
    var sheetName = result.getResponseText();
    if (sheetNames.includes(sheetName)) {
      ss.setActiveSheet(ss.getSheetByName(sheetName));
    } else {
      ui.alert('Sheet not found');
    }
  }
}

How can I change this to only show visible sheets? I can’t test it much outside of work. Any ideas would be great. Thanks!

2

I’ve encountered this issue before. To filter out hidden and protected sheets, you’ll need to modify your code slightly. Here’s a suggestion:

var visibleSheets = ss.getSheets().filter(function(sheet) {
  return !sheet.isSheetHidden() && sheet.getProtection() == null;
});
var sheetNames = visibleSheets.map(function(sheet) {
  return sheet.getName();
});

This will create an array of only visible and unprotected sheets. Then, in your prompt, you can use sheetNames to display the options. Remember to update your sheet activation logic to use the visibleSheets array instead of ss.getSheetByName(). This should solve your problem. Let me know if you need any clarification.

3

As someone who’s been wrestling with Google Sheets scripts for a while, I can tell you that dealing with sheet visibility can be tricky. Here’s a tweak that might help:

Instead of getting all sheets, try using getVisibleSheets() method. It’ll automatically filter out the hidden ones. Then, you can add a quick check for protection:

var visibleSheets = ss.getVisibleSheets().filter(sheet => !sheet.getProtection());
var sheetNames = visibleSheets.map(sheet => sheet.getName());

This should give you only the visible, unprotected sheets. You’ll need to adjust your sheet activation logic too, but this should get you on the right track.

One heads up - if you’re dealing with lots of sheets, this might slow things down a bit. In that case, you might want to cache the results if possible. Hope this helps!

4

hey tom, i’ve dealt with similar stuff before. try adding a check for sheet visibility in ur code. something like:

var visibleSheets = sheets.filter(sheet => !sheet.isSheetHidden());
var sheetNames = visibleSheets.map(sheet => sheet.getName());

this should filter out hidden sheets. for protected ones, u might need to check permissions too. good luck!