Run script on Sheet(tab) selection/change

Question

I would like to know if there's a way to automatically trigger a script every time a specific sheet, inside a worksheet, is selected.

Looking around I found the onSelectionChange function but I have not been able to use it for my scope. Script draft below.

function onSelectionChange(e) 
{ // Run update script when sheet is selected 
 var sheet = e.sheet; 
 var ssname = SpreadsheetApp.getActiveSheet().getName(); 
 if(ssname === "Projects") {
    UpdateProjects(); 
    }
} 

Any suggestion would be much appreciated. Thanks!

Answer 1

I believe your goal as follows.

• You want to run a script when the specific sheets are activated.
• You want to achieve this using Google Apps Script.

I thought that in this case, your goal can be achieve by modifying the sample script of this thread.

Usage

1. Please copy and paste the following sample script to the container-bound script of Google Spreadsheet, and save the script.

2. Please reopen the Google Spreadsheet.

   • By this, onOpen is run and the current sheet is put to PropertiesService.
   • Unfortunately, in the current stage, it seems that the event object of onSelectionChange has no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.

3. Then, please select other sheet on Google Spreadsheet.

   • By this, onSelectionChange is run by the onSelectionChange event trigger, and when the activated sheet is included in specificSheetNames, the script of e.source.toast("Run script"); is run.

Sample script:

function onOpen(e) {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = e.range.getSheet().getSheetName();
  prop.setProperty("previousSheet", sheetName);
}

function onSelectionChange(e) {
  const specificSheetNames = ["Projects"]; // Please set the sheet names you want to run the script.

  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const a1Notation = range.getA1Notation();
  const sheetName = range.getSheet().getSheetName();
  if (!specificSheetNames.includes(sheetName)) return;
  
  // When the specifc sheet names are activated, this script is run.
  e.source.toast("run script"); // This is a sample script.

  prop.setProperty("previousSheet", sheetName);
}

• In this sample script, when the sheet is changed to "Projects", the script in the if statement is run.

Note:

• From your script, unfortunately, I cannot understand about UpdateProjects() in your script. In the current stage, all methods cannot be run with onSelectionChange because of the authorization. Because this trigger is the simple trigger. So please be careful this. So as a simple test for checking above script, at first, please use e.source.toast("run script");.

References:

• onSelectionChange(e)
• Related thread
  • Is there a google spreadsheet trigger that fires after a tab is changed?