How can I trigger a function when switching sheets?
Let's say I'm viewing a multi-sheet spreadsheet. Now I want a function to run whenever I switch from one sheet (tab) to another.
Is that possible?
There is the onOpen trigger, but that only runs when I first open the spreadsheet - not when opening other sheets within that spreadsheet:
As Rubén says, there's no such event built in. But you might have some (limited) success with this approach:
var PROP_NAME = "lastSheetIdx";
var userProperties = PropertiesService.getUserProperties();
function didSwitchSheets(from, to) {
Logger.log("Switched from " + from + " to " + to);
// Your code here
}
function timedEventHandler() {
var currentSheetIdx = SpreadsheetApp.getActiveSheet().getIndex()
var previousSheetIdx = parseInt(userProperties.getProperty(PROP_NAME));
if (currentSheetIdx !== previousSheetIdx) {
didSwitchSheets(previousSheetIdx, currentSheetIdx);
userProperties.setProperty(PROP_NAME, currentSheetIdx);
}
}
Paste this code, press the Run button to authorize the script (you only need to do this once).
Then, from the Resources menu, select Triggers for current script, and set up the timedEventHandler to run as often as you can - which currently seems to be "every minute".
That will cause the function to run every minute and check if the "current sheet" id has changed, and if so, the didSwitchSheets function will run.
Probably, one minute is not often enough. But it might look as though Google is about to implement the possibility to run a script every second. At least that comes up as a choice in my Triggers for current script dialog, but it does not seem to work when I select it - see Can I set a trigger to run every second?
I have created an example spreadsheet to demonstrate this. Not much to see there, but make a copy of it (File → Copy) to your own Google Drive, and you should be able to see and edit the script as much as you like.
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
