容器绑定脚本切换工作表时如何隐藏当前/目标以外的所有工作表？

问题核心在于hideAllSheets函数的判断逻辑无效——原代码里的sheets[i].getName()!=null永远为真，导致所有工作表（包括刚激活的目标表）都被隐藏。以下是修正后的完整代码：

1. 修改隐藏函数，保留指定工作表 #

function hideAllSheetsExcept(targetSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  for(var i = 0; i < sheets.length; i++){
    var sheet = sheets[i];
    // 仅隐藏名称不等于目标表的工作表
    if(sheet.getName() !== targetSheetName){
      sheet.hideSheet();
    }
  }
}

2. 修正切换工作表的函数 #

原代码中spreadsheet变量未定义会引发报错，同时调用修改后的隐藏函数传入目标表名称：

function SwitchToSheet1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('sheet1');
  ss.setActiveSheet(targetSheet, true);
  hideAllSheetsExcept('sheet1');
};

function SwitchToSheet2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('sheet2');
  ss.setActiveSheet(targetSheet, true);
  hideAllSheetsExcept('sheet2');
};

function SwitchToSheet3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('sheet3');
  ss.setActiveSheet(targetSheet, true);
  hideAllSheetsExcept('sheet3');
};

function SwitchToSheet4() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('sheet4');
  ss.setActiveSheet(targetSheet, true);
  hideAllSheetsExcept('sheet4');
};

function SwitchToSheet5() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('sheet5');
  ss.setActiveSheet(targetSheet, true);
  hideAllSheetsExcept('sheet5');
};

可选优化：减少代码冗余 #

如果想避免重复代码，可以封装通用切换逻辑，再通过单独函数传递参数（Google Sheets绘图控件绑定函数时无法直接传参）：

// 通用切换逻辑
function switchToTargetSheet(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName(sheetName);
  if(targetSheet){
    ss.setActiveSheet(targetSheet, true);
    hideAllSheetsExcept(sheetName);
  }
}

// 供控件绑定的函数
function SwitchToSheet1() {
  switchToTargetSheet('sheet1');
};

function SwitchToSheet2() {
  switchToTargetSheet('sheet2');
};

// SwitchToSheet3/4/5同理

内容的提问来源于stack exchange，提问作者Rookie_Js