CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
VScode06
VScode06

Reputation: 11

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

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 -

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

Upvotes: 1

Views: 61

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

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.

Upvotes: 0

Related Questions