5

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.
Tabs in a spreadsheet
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:

3

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.

  • 1
    Another (better?) approach could be to use 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. – Rubén Apr 12 '16 at 21:58
  • @Vidar S. Ramel, Yes, you pointed out a good option. But it is like a cron job which checks every minute and runs the script accordingly. But this does not fit my requirement. May I know, whether we can restrict viewing a specific sheet from others in a multisheet spredasheet ? I know this is entirely different from my actual question. But I am just asking. :) – Uvais Ibrahim Apr 13 '16 at 6:17
  • 1
    You should post that as a separate question. And search for it first, it might already have an answer. – Vidar S. Ramdal Apr 13 '16 at 6:25
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.