Can You Prevent onSelectionChange() from Firing on Every Cell Clicked?-CodePudding

I am using onSelectionChange to navigate between a menu of Units and Tenants for a selected property. When you click on a given unit on the Unit Load Panel on the right, it loads the unit/tenant information into the form to the left of the panel.

It works fine but from viewing the Executions log, I noticed that the onSelectionChange trigger is fired every time a cell is clicked anywhere, on any sheet in the entire spreadsheet. This makes sense because, as the code is written, it needs to check if the cells clicked are in the range specified in the if loop every time. However, this seems to be inefficient to me and unnecessary. Is there a way to restrict onSelectionChange to only fire if a cell is clicked in a specific range of cells?

Here is the code:

function onSelectionChange(e) {
  var app = SpreadsheetApp; 
  var activeSheet = app.getActiveSpreadsheet(); //Get current active spreadsheet
  var sheet = e.source.getActiveSheet().getName(); //Gets name of sheet where cell was clicked

  //row and column index of active (clicked) cell: 
  var range = e.range; //Activates range of clicked cell
  var row = range.getRow(); //Gets row # of clicked cell
  var col = range.getColumn(); // Gets col # of clicked cell
  
  // UNIT SHEET TRIGGERS:
  if(sheet == "Units"){
    var sheetUnits = activeSheet.getSheetByName('Units');
  
    //Variables for Units Load Panel (to switch between Units)
    var unitsRange = sheetUnits.getRange('L7:M41'); //highlights unit number list AND tenant name list on unit form tab
    var UnitID = sheetUnits.getRange('B10')// Sets Unit ID to be cell B10
    var UnitIDFirstCol = sheetUnits.getRange('N7').getColumn(); //Returns 14
    
    
        if( !sheetUnits.getRange(row,UnitIDFirstCol).isBlank() && col >= unitsRange.getColumn() && col <= unitsRange.getLastColumn() && row >= unitsRange.getRow() && row <= unitsRange.getLastRow()){

        UnitID.setValue(sheetUnits.getRange(row,UnitIDFirstCol).getValue()); //sets UnitID at top of unit form to be unit selected
        Unit_Load(); //Function that loads selected unit/tenant into form to the left
        }
      
  }

CodePudding user response：

You might be able to limit how you respond to the trigger by using the event object show below:

{"authMode":"LIMITED","user":{"email":"","nickname":""},"source":{},"range": 
{"columnEnd":3,"columnStart":3,"rowEnd":6,"rowStart":6}}

CodePudding user response：

If you're referring to not log the onSelectionChange at all on the Executions section of the Apps Script editor, then this seems like it is not possible as also commented by @Kos.

Suggestion

However, there's a workaround where you can add to your current if condition to only run your code when a specific range (rows & columns) is selected like:

E.g. if you only want to run your code if cells within A5:A10 are selected

  if (sheet == 'Units' && row>4 && row<11 && col==1){ 
        //run code
  }else{
    return; //Automatically end your script
  }

This script was derived from this similar answer.

CodePudding user response：

It's not possible to "Prevent onSelectionChange() from Firing on Every Cell Clicked?" but you could use a JavaScript control statement to limit when certain parts of your code are executed.

function onSelectionChange(e){
  if(e.range.rowStart === 1 && e.range.columnStart === 1){
    // This part will be executed when A1 is selected
  } else {
    // This part will be executed when any cell but not A1 is selected
  }
}