解决Google Apps Script切换工作表后隐藏非活动表的异常问题

问题场景 #

通过Google Apps Script实现按分数切换工作表层级：用户点击「Check Answer」按钮，答对4题及以上进入下一层级，不足4题返回上一层级。设置目标活动工作表后执行hideAllSheets隐藏其他表时，偶尔抛出异常：Exception: You can't hide all sheets in a document，怀疑是缓存或执行顺序导致。

原代码 #

const UI = SpreadsheetApp.getUi()
const ss = SpreadsheetApp.getActiveSpreadsheet()

function checkAnswers() {
  const sheet = ss.getActiveSheet()
  var score = sheet.getRange('G1').getValue()
  const nextTest = sheet.getRange('H1').getValue()
  var nextSheet = ss.getSheetByName(nextTest);
  //console.log(score)

  if (score >= '4') {
    //Browser.msgBox('Go to the next level', Browser.Buttons.OK);
    ss.setActiveSheet(nextSheet)
    //Utilities.sleep(500)
    showAllSheets()
    hideAllSheets()
  }


/*   else if (score <= 3)
    //Browser.msgBox('Go back a level', Browser.Buttons.OK);
  ss.setActiveSheet(nextSheet)
  //showAllSheets()
  hideAllSheets() */
}

function hideAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheetId = ss.getActiveSheet().getSheetId();
  const sheets = ss.getSheets();
  sheets.filter(s => s.getSheetId() != currentSheetId)
    .forEach(s => s.hideSheet());
}

function showAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheetId = ss.getActiveSheet().getSheetId();
  const sheets = ss.getSheets();
  sheets.filter(s => s.getSheetId() != currentSheetId)
    .forEach(s => s.showSheet());
}

function startAssessments() {
ss.setActiveSheet(ss.getSheetByName('level1'), true);
Utilities.sleep(500)
hideAllSheets()
}

问题原因 #

1. 执行顺序延迟：Google Apps Script的电子表格操作默认批量延迟执行，切换活动工作表的操作可能还未生效，hideAllSheets就已执行，导致判断当前工作表时出错，甚至尝试隐藏所有表。
2. 冗余操作：原代码中showAllSheets + hideAllSheets的组合完全多余，反而增加出错概率。
3. 分支逻辑错误：else分支存在缩进错误，导致逻辑未正确执行。
4. 类型比较隐患：用字符串'4'和分数进行比较，可能引发类型不匹配问题。

修复后可正常运行的代码 #

const UI = SpreadsheetApp.getUi();
const ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen(){
  UI.createMenu("工具")
  .addItem("显示所有工作表","showAllSheets")
  .addItem("隐藏其他工作表","hideAllSheets")
  .addItem("重新开始","startOver")
  .addToUi()
}

function startAssessments() {
  ss.setActiveSheet(ss.getSheetByName('level1'), true);
}

function startOver() {
  ss.setActiveSheet(ss.getSheetByName('Start'), true);
  SpreadsheetApp.flush();
  hideAllSheets();
}

function checkAnswers() {
  const sheet = ss.getActiveSheet();
  var score = sheet.getRange('G1').getValue();
  const nextTest = sheet.getRange('H1').getValue();
  var nextSheet = ss.getSheetByName(nextTest);

  if (score >= '4') {
    Browser.msgBox('进入下一层级', Browser.Buttons.OK);
    ss.setActiveSheet(nextSheet);
    SpreadsheetApp.flush();
    hideAllSheets();

  } else if (score == 3) {
    Browser.msgBox('返回上一层级', Browser.Buttons.OK);
    ss.setActiveSheet(nextSheet);
    SpreadsheetApp.flush();
    hideAllSheets();
  } else {
    Browser.msgBox('感谢参与', Browser.Buttons.OK);
    ss.setActiveSheet(nextSheet);
    SpreadsheetApp.flush();
    hideAllSheets();
  }
}

function hideAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheetName = ss.getActiveSheet().getSheetName();
  const sheets = ss.getSheets();
  sheets.forEach((sh) => {
    if(sh.getSheetName() != currentSheetName) {
      sh.hideSheet();
    }
  });
}

function showAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheetName = ss.getActiveSheet().getSheetName();
  const sheets = ss.getSheets();
  sheets.filter(s => s.getSheetName() != currentSheetName)
    .forEach(s => s.showSheet());
}

修复要点 #

• 强制执行操作：使用SpreadsheetApp.flush()确保切换活动工作表的操作立即生效，避免后续函数基于旧状态执行。
• 完善分支逻辑：修正else分支缩进，覆盖所有分数场景。
• 优化菜单功能：添加onOpen自定义菜单，方便快速操作。
• 简化逻辑：移除冗余的showAllSheets调用和Utilities.sleep()，改用flush()保证执行顺序。
• 直观判断条件：改用工作表名称代替ID进行判断，逻辑更清晰。

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