Hide a sheet when I am active on another sheet

I am trying to automatically hide a sheet called “Add expense” when I am not active on it.

For example.

I have the sheet “BD Expenses”, the sheet “BD Tokens”, the sheet “BD Income”, the sheet “Add expense”, the sheet “Add income”.

What I want is that when I am active in any of the sheets that is not called “Enter expense”, then the script runs completely automatically (without having to click on any button) to hide the sheet called “Add expense”.

I currently have this code:

//Ocultar todas las hojas excepto la activa
function Ocultar_formulario_ANADIR_GASTO() {
  var sheets = SpreadsheetApp.getActiveSheet();  

  sheets.forEach(function(sheet) {
      if (sheet.getSheetByName('Añadir gasto') != SpreadsheetApp.getActiveSheet().getName())
        sheet.hideSheet();
  });
};   

I have tried various methods but without success.

I know there is a function called onSelectionChange (e) but since I am so new to Javascript I don’t really know how to make it work. Hence I have created my code differently.

I have looked at the reference from https://developers.google.com/apps-script/reference/spreadsheet/sheet#hidesheet

I’ve also googled and stackoverflow, but can’t find a solution to this problem.

Currently when running the script from the editor, I get the error “TypeError: sheets.forEach is not a function”.

I can’t get it to work.

I would really appreciate if someone can take a look at my code and offer me a little help.

Thank you very much.

Answer

It looks like there is no specific Google Scripts event for when you change sheets.

But there is a workaround mentioned in the issue tracker ticket – scroll to the bottom of the ticket to see it. It involves using onSelectionChange(e) to track which sheets you moved out of and into.

The following code adapts that workaround to your case:

The code assumes your Google spreadsheet has 2 or more sheets – and one of those sheets is called Secret Sheet. You can change this name to whatever you want (see the first line of the code).

When you move from the Secret Sheet to any other sheet, the Secret Sheet will automatically be hidden.

var secretSheetName = 'Secret Sheet'; // change this to whatever you prefer.
var prevSheetProperty = 'PREVIOUS_SHEET';

function saveActiveSheet() {
  var activesheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty(prevSheetProperty, activesheet.getSheetName());
}

function onSheetChange(e) {
  var sheetToHide = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(secretSheetName);
  sheetToHide.hideSheet();
}

function onSelectionChange(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Get current sheet name and compare to previously saved sheet
  var currentSheetName = ss.getActiveSheet().getSheetName();
  var userProperties = PropertiesService.getUserProperties();
  var previousSheetName = userProperties.getProperty(prevSheetProperty);

  if (currentSheetName !== previousSheetName) {
    saveActiveSheet(); // this becomes the new "previous sheet".
    if (previousSheetName === secretSheetName) { 
      // you have moved out of the secret sheet - so, hide it:
      onSheetChange(e);   // Call custom sheet change trigger
    }
  }
}

function onOpen(e) {
  saveActiveSheet();
}

The script works by keeping track of which sheet is the currently active sheet – and which was the (different) previously active sheet before that.

It stores the “previous” sheet name in a user property.

You will see that to use the onSelectionChange(e) event, you simply have to add that function to your script:

function onSelectionChange(e) { ... }

Google Scripts automatically recognizes this as an event function. You can read more about this, with examples, here.