How to hide all sheets but the active sheet which is set by the script itself without getting the &#-CodePudding

I am using a script to go up and down a level (different tabs) based on the score of the tab you are on. So the script sets the active sheet to the correct sheet, then runs hideAllTabs script which sometimes works and other times will throw the error

Exception: You can't hide all the sheets in a document'

The script is run when the user clicks a 'Check Answer' button. If they get 4 or more answers correct, they move to the next level, if less than 4 they move back a level.

This is what I have so far. And it works sometimes, but sometimes throw the error. Is it a question of clearing the cache? Maybe the order that I have?

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()
}

CodePudding user response：

I think the issue is you're not showing your new actual sheet. You shouldn't filter in that step or it won't be shown

CodePudding user response：

Simplest solution: Add SpreadsheetApp.flush(); after ss.setActiveSheet(nextSheet);

For a more robust solution, whenever is possible avoid the use of "active" methods as it's not possible to completely block the user interaction with the Google Sheets UI while a script is running. In this case your script could pass the sheet name from the main function to the hideAllSheets function and make this function use the sheet name instead of the sheet id. This will also make your script a bit faster because it will reduce the number of calls to Google Apps Script methods.

P.S. Add ; at the end of all statements as reliying on the automatic semicolon insertion might make the code a bit harder to read and debug.