Trigger when user switches between sheets

Question

Is there a way on to run a script whenever the user switches between sheets in a Google Sheets Spreadsheet?

More or less like onOpen, but instead of running when the document is opened, it should fire every time the user switches to another sheet.

Answer 1

I have created a "Feature request" to implement "onSheetChange" trigger at google issue tracker. You can star it, so google now you want this: https://issuetracker.google.com/issues/72140210

And here is related question with possible workaround: How to capture change tab event in Google Spreadsheet?

Answer 2

UPDATE: On April 2020 Google added onSelectionChange(e) which could be used to check if the user switched between sheets aka tabs.

---

At this time there isn't a trigger related to switch for one sheet to another. To learn about the available triggers in Google Apps Script, please checkout Triggers and events - Google Apps Script Guides

As a workaround you could a custom menu to and SpreadsheetApp.setActivesheet to switch between sheets by using a script instead of using tabs and including in that script a call to the function to be run when switching from one sheet to another.

Answer 3

Here is my work-around for onSheetChange:

function saveActiveSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var actsheet = ss.getActiveSheet();
    // The onSelectionChange() function executes in a separate thread
    // which does not use any script global variables, so use the
    // PropertiesService to maintain the user global state.
    var userProperties = PropertiesService.getUserProperties();
    userProperties.setProperty('ACTIVE_SHEET', actsheet.getSheetName());
}

function onSheetChange(e) {
    // Do anything needed after a new sheet/tab selection

}

function onSelectionChange(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // Get current sheet name and compare to previously saved sheet
    var currentactsheet = ss.getActiveSheet();
    var currentactsheetname = currentactsheet.getSheetName();
    var userProperties = PropertiesService.getUserProperties();
    var actsheetname = userProperties.getProperty('ACTIVE_SHEET');
  
    if (currentactsheetname !== actsheetname) { // New sheet selected
        saveActiveSheet();
        onSheetChange(e);   // Call custom sheet change trigger
    }
    // Do anything needed when a different range is selected on the same sheet
    else {
        var range = e.range;

    }
}

function onOpen(e) {
    saveActiveSheet();
}

Answer 4

you can use

sheet1.showSheet();
sheet2.hideSheet();