Problem with OnEdit code

[Emily Simmons]

Hi all!

I'm having trouble making my OnEdit function work properly. I have a trigger checkbox embedded into my spreadsheet as a Named Range, so that my team can "check" the trigger box in order to run the script, without having to dive into scripts at all. The Named Range button I created in my spreadsheet is called "MasterClear". 

However, when I attempt to run the OnEdit script, I am getting an error message in line 3. The functions work by themselves when I test them. But, the OnEdit trigger alone is not performing. I borrowed this line of code from a forum I found online, so I'm not able to decode or resolve it myself. Can anyone help?

SCRIPT

//

function onEdit(e) {

  var MasterClear = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("MasterClear").getA1Notation(); 

  var actv = e.range.getA1Notation();

  if (actv == MasterClear ) {

    clear1();

    clear2();

 

}

}

function clear1() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Team 3');

  sheet.getRange('T11:T300').clearContent() ;

 

}

function clear2() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Team 3');

  sheet.getRange('O11:O300').clearContent() ;

  

}

[Fco. Javier Lledó Fuster]

Hi,

with e.range.getA1Notation() you obtain the name of the range as an A1Notation, for example, B2, C5, F8,...that is the combination of the column and the row. If you want to check it as a named range, you have to obtain first with getRangeByName the A1Notation of the named range. Later, you can check it.

Pay attention if the named range take several cells, because the A1Notation of these would be G2:G7 for example. But when one cell is modified, the A1Notation result of e.range.getA1Notation() gives only one cell (G3 for example), and if you check directly G3 with G2:G7 return False. If you need to implement this situation, maybe it is possible to create one function to obtain all cells inside the range with a1Notation...

I give you a modified code of yours to check it. I hope it helps.

function onEdit(e) {
  RANGE_NAME = "MasterClear"
  A1NotationOfNamedRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(RANGE_NAME).getA1Notation();
  if (e.range.getA1Notation() == A1NotationOfNamedRange) {

    clear1();
    clear2();
  }
}

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Team 3');
  sheet.getRange('T11:T300').clearContent();
}

function clear2() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Team 3');
  sheet.getRange('O11:O300').clearContent();  
}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e4a1ac3a-8565-4f7d-acae-2a6ab46fcb2fo%40googlegroups.com.