Want to run same script on each active sheets of shared google spreadsheet

Question

I'm very new to coding and just trying to learn few things starting with Google sheets. I've a sample script that run on one sheet only and it will be shared spreadsheet. Here's my script

<!-- begin snippet: js hide: false console: true babel: false -->

var sheet =`enter code here` ["Main01"];
var optionList = ["Options"];
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionList);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues();
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;


//Begins - main functions

function myFunction() {
} //End of myFunction


function onEdit(e) {
    var activeCell = e.range;
    var val = activeCell.getValue();
    var r = activeCell.getRow();
    var c = activeCell.getColumn();
    var wsName = activeCell.getSheet().getName();
    
    if(wsName == sheet && c == firstLevelColumn && r > 1 ){
        firstLevelList(val,r);
    } else if (wsName == sheet && c == secondLevelColumn && r > 1 ) {
        secondLevelList (val,r);
      }
} //End of function - onEdit


function firstLevelList(val,r) {
    if(val == ""){
        ws.getRange(r,secondLevelColumn).clearContent();
        ws.getRange(r,secondLevelColumn).clearDataValidations();
        ws.getRange(r,thirdLevelColumn).clearContent();
        ws.getRange(r,thirdLevelColumn).clearDataValidations();
    } //End of inner if statement 
      else { 
        ws.getRange(r,secondLevelColumn).clearContent();
        ws.getRange(r,thirdLevelColumn).clearContent();
        ws.getRange(r,thirdLevelColumn).clearDataValidations();
        
        var filteredOptions = options.filter(function(op) { return op[0] == val });
        var applyList = filteredOptions.map(function(op) { return op[1] });
        var cell = ws.getRange(r,secondLevelColumn);
        
        applyCellValidation(applyList,cell);
      } //End of else statement
} //End of function - firstLevelList


function secondLevelList(val,r) {
    //similar to firstLevelList with next level validation
} //End of function - secondLevelList


function applyCellValidation(list,cell) {
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();

      cell.setDataValidation(rule);
} //End of function - CellValidation

Some tasks I want to achieve from the code -

Run script automatically when spreadsheet opened - maybe use onOpen() trigger. How no idea
Since it wil be shared spreadsheet, I want script to run for each of the active sheet whenever accesed by any user.

When you open a spreadsheet - the active sheet will alwas be the first one (Sheet1 - if not renamed). This means that a script that detects the active sheet onOpen will always detect the first sheet of the spreadsheet. Is it what you want? If you want the script to run again after the user manually changed the active sheet - that cannot happen onOpen. Also, currently your script is set up onEdit - would you like to maintain this functionality (the script should run both onOpen and onEdit) or only onOpen? — ziganotschka
@ziganotschka I would like to make it work, whenever any sheet tab opened and edited by user. Script to work for all the sheet tabs of spreadsheet in similar manner. Thanks — VScode06
In this case you need (in addition to your onEdit trigger) an onSelectionChange instead of onOpen trigger - see my answer. — ziganotschka

ziganotschka ziganotschka · Accepted Answer · 2021-05-24T13:56:43

To detect a change of the active sheet you need to use the `onSelectionChange` trigger

The onSelectionChange trigger allows you to estimate either the new selection belongs to the same sheet like the old selection - or to a different sheet.

There is an elegant implementation developed by @Diego.

Applying it to your case, you should add to your already existing script the following functions:

function onSelectionChange(e) {
  var activeCell = e.range;
  if (activeSheetChanged(activeCell.getSheet().getSheetId())) {
    var val = activeCell.getValue();
    var r = activeCell.getRow();
    var c = activeCell.getColumn();
    var wsName = activeCell.getSheet().getName();
    if(wsName == sheet && c == firstLevelColumn && r > 1 ){ 
        firstLevelList(val,r);
    } else if (wsName == sheet && c == secondLevelColumn && r > 1 ) {
        secondLevelList (val,r);
      }
   }
} //End of function 

function activeSheetChanged(newSheetId) {
  const key = 'activeSheetId';
  const cache = CacheService.getUserCache();
  let properties;
  const savedSheetId = getSavedSheetId();
  if (savedSheetId != newSheetId) {
    saveSheetId(newSheetId);
    return true;
  }
  return false;
  
  /**
   * Get the saved sheet ID from the Cache/Properties.
   * @returns {Number}
   */
  function getSavedSheetId() {
  console.log("called")
    let savedSheetId = cache.get(key);
    if (savedSheetId == null) {
      properties = getProperties();
      savedSheetId = properties.getProperty(key);
      cache.put(key, savedSheetId);
    }
    return cache.get(key);
  }
  
  /**
   * Save the sheet ID to the Cache & Properties
   */
  function saveSheetId(sheetId) {
    properties = properties ? properties : getProperties();
    properties.setProperty(key, sheetId);
    cache.put(key, sheetId);
  }
  
  /**
   * @returns {PropertiesService.Properties}
   */
  function getProperties() {
    return PropertiesService.getUserProperties();
  }
}

Also:

If you want to compare the sheet name to "Main01", you need to change your line 1 from var sheet = ["Main01"]; to var sheet = "Main01"; to make your script work.

Want to run same script on each active sheets of shared google spreadsheet

1 Answers

To detect a change of the active sheet you need to use the onSelectionChange trigger

To detect a change of the active sheet you need to use the `onSelectionChange` trigger