CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kate Bedrii
Kate Bedrii

Reputation: 103

OnEdit(e) to be triggered by edits in multiple ranges

I have a script that clears values in several named ranges if they're edited. I managed to configure OnEdit(e) trigger to fire when a single named range is edited but cannot figure out how to expand it to fire out when any of the ranges is edited. I suppose that I can set several variables for the named ranges (e.g, MyRange2, MyRange3, etc.) but in this case, I`ll need to duplicate IF statement for each variable which I am trying to avoid.

I also tried to define myRange as an array but it looks like it contradicts with IF: var myRange = [ss.getRangeByName('total1), ss.getRangeByName('total2'), ss.getRangeByName('total3')];

function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
  var myRange = SpreadsheetApp.getActiveSheet().getRange('total1');
 
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { 
        var name_arr = ['total1', 'total2', 'total3'];
        for (var name of name_arr) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var range = ss.getRangeByName(name).clearContent();
        }    ;
      } else {
        return;
      }
    };

Upvotes: 0

Views: 684

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  • Make sure you take full advantage of the event object. You want to spend as less resources as possible and put all the absolute necessary code to be executed upon every edit before the main if condition that can end the execution of the code. For example var myRange = SpreadsheetApp.getActiveSheet().getRange('total1') does not need to be executed for every edit.

  • I assume you want to run the script for a particular sheet. In my code, I used Sheet1 and I added an if condition to check whether the sheet you edited is Sheet1. If you don't want this behaviour, remove this if condition and the enclosing brackets if(as.getName()=="Sheet1"). Then your code will automatically work for every sheet in the document (as soon as there are name ranges in this sheet that matches your criterion).

  • Define a list of the names of the named ranges ["total1","total2","total3"] and the script will iterate over all the named ranges and make sure to execute a block of code, only for these named ranges.

  • If all the aforementioned if conditions evaluate to true, clear the content of the name range.

Solution:

function onEdit(e) {
  const arng = e.range; // // get active range
  const as = arng.getSheet(); // get active sheet
  if(as.getName()=="Sheet1"){
      const nranges = ["total1","total2","total3"]; // add here the names of the ranges
      //Let's get the row & column indexes of the active cell
      const namedRanges = as.getNamedRanges(); // get all named ranges
      const row = arng.getRow();
      const col = arng.getColumn();
      namedRanges.forEach(nr=>{
        if (nranges.includes(nr.getName())){
          let nrange = nr.getRange();
          if (col >= nrange.getColumn() && col <= nrange.getLastColumn() 
              && row >= nrange.getRow() && row <= nrange.getLastRow()) { 
               nrange.clearContent(); // clear the range of the named range
          }
        }
      });     
  }
}

Upvotes: 2

Related Questions