hide all except for current Sheet

Rookie_Js 0
Viewed 28

I have a container bound script with drawings that have functions assigned to them that change the current sheet to a predefined one.

Now i addition to switching sheets i'd also like to hide all sheets except the one sheet im on / going to.

if SwitchToSheet4 gets executed, hide all sheets except sheet4


Heres my code but this doesn't work since it hides all sheets:


function hideAllSheets() {
  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for(var i =0;i<sheets.length;i++){
    Logger.log(i);
    if(sheets[i].getName()!=null){
      sheets[i].hideSheet();
    }
  }
}


function SwitchToSheet1() {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet1'), true);
  hideAllSheets();
};
function SwitchToSheet2() {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet2'), true);
  hideAllSheets();
};
function SwitchToSheet3() {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet3'), true);
  hideAllSheets();
};
function SwitchToSheet4() {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet4'), true);
  hideAllSheets();
};
function SwitchToSheet5() {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet5'), true);
  hideAllSheets();
};
google-sheets google-apps-script
1 Answers

Solution:

  • Retrieve the active sheet using Spreadsheet.getActiveSheet().
  • Filter out this sheet from the array of sheets, for example using the sheet id to identify it (getSheetId).
  • Hide all the other sheets.

Code snippet:

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());
}
Iamblichus0
Related