Trigger script when only certain sheets are activated

Question

I have several sheets that begin with "Agent Report" followed by the name, like this: "Agent Report - John", "Agent Report - Adam", etc. I have a script that reads the name of the agent from a specific cell and retrieves the data from another spreadsheet for that person. I want to trigger the script when a sheet that begins with "Agent Report" is activated, so that when I move between the sheets, the sheet data are updated for each person in "Agent Report" sheets. So far I have this:

function onOpen(e) {
  makeMenu();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sname = ss.getActiveSheet().getName();
  if(sname.indexOf("Agent Report")>-1){
    master();
  }
}

Agent Report is not the first sheet, so the script correctly makes a custom menu (makeMenu) when I open the spreadsheet, but does not get triggered (master) when I switch to an "Agent Report" sheet. When I run the script manually from an "Agent Report" sheet, it runs fine.

My question is: Can I create a trigger that will run the script when I switch to a sheet with the name that begins with "Agent Report"? onOpen() doesn't seem to fit for that purpose.

If such a trigger is not possible, can there be a workaround - a loop that would go over every sheet, check the name and if it contains "Agent Report", run the script. Something like that:

function onOpen(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var numberOfSheets = ss.getSheets().length;     
  for (var i = 0; i<=numberOfSheets; i ++) {
    if(SOMETHING HERE.indexOf("Agent Report")[i] > -1){
      master();
    }
  }
}

Answer 1

Issue:

According to the official documentation:

The onOpen(e) trigger runs automatically when a user opens a spreadsheet, document, presentation, or form that they have permission to edit.

The problem with this approach is that onOpen is executed only the time you open the spreadsheet file. Therefore, even if you switch between sheets or do any other operations, the script won't be executed, unless you refresh/open the spreadsheet file again. In other words, onOpen will only be executed for the sheet that you open the first time.

---

Solution 1:

To execute a code when switching between different sheets you can use onSelectionChange(e):

  function onSelectionChange(e) {
    makeMenu();
    const as = e.source.getActiveSheet();
      if (as.getName().indexOf("Agent Report")>-1){ 
        master();
      }
    }

I don't recommend you to choose this approach because every time you make a selection change in the script, the code will be executed.

---

Solution 2 (recommended):

As you also proposed, to control when you want to execute the script I would advice you to use a regular function that iterates over all sheets and checks if the sheet name matches the criterion. Add also onOpen to execute makeMenu():

function allSheets(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  sheets.forEach(sh=>{                 
  if(sh.getName().indexOf("Agent Report")>-1){
  master();
  }});
}


function onOpen() {
  makeMenu();
}

You can execute this regular function in many different ways:

1. script editor
2. create a custom menu and execute it from the spreadsheet file
3. create an icon button within your spreadsheet file
4. create a time-driven trigger to execute on a particular time

---

Bonus information:

In both approaches, you can replace

if(as.getName().indexOf("Agent Report")>-1)

with

if(as.getName().includes("Agent Report"))

which does exactly the same thing, but it is more eye-friendly.

---

Related Issue:

As also pointed out by Iamblichus, an onSheetChange trigger function has already been requested by the community and it is reported in the IssueTracker. Go ahead and click on the star (★) button to the top left of the webpage to increase the chances of this feature to be implemented in the future.

Answer 2

The final solution was to use installable trigger (not a simple trigger, this is why the script did not run onOpen) with the following script:

function allSheets() {
  makeMenu();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.forEach(sh=>{
    SpreadsheetApp.setActiveSheet(sh);     
    if (sh.getName().includes("Agent Report")) {
      master();
    }
  })
  SpreadsheetApp.setActiveSheet(sheets[0]);
}

Thank you @Marios for all the helpful tips.