I have an onEdit() function that hosts 3 different onEdit() functions. I know the question has been asked several times and I have tested the solutions of many of these answers but I don't understand why it doesn't work.

In my script, which I wrote based on the most relevant answer, onEdit1(event) and onEdit2(event) work but not onEdit3(event). I have the impression that there is a problem with "event.range" but I can't solve it.

Can you tell me what I did wrong?

Here is my script:

function onEdit(event) {
  onEdit1(event);
  onEdit2(event);
  onEdit3(event);
}

//ADD COLOR MPR MASTER TAB
function onEdit1(event) {
  var sss = event.source;
  var sourceSheet = sss.getActiveSheet();
  var shName = sourceSheet.getName();
  var crange = event.range;
  var crow = crange.getRow();
  var ccol = crange.getColumn();
  
  if ((shName == "MASTER" ) && (ccol >= 38)){
    var cvalue = crange.getValue();
    var validationSheet = sss.getSheetByName("CALCULS");
    var optionColor = validationSheet.createTextFinder(cvalue).findNext().getBackground();
    crange.setBackground(optionColor);

    var sourceOptionColor = sourceSheet.getRange(crow, 38).getBackground();
    destSheet.getRange(destRow,38).setBackground(sourceOptionColor);
  }
}

//MERGE BANQUE TAB
function onEdit2(event) {
  let sh = event.source.getActiveSheet()
  let onglets = ['BANQUE']
  let shCol = event.range.getColumn()
  let shRow = event.range.getRow()
  let upRows = [25]
  let upCols = [14]
   console.log(shCol)
   console.log(shRow)
  if (onglets.indexOf(sh.getName()!=-1) && upRows.includes(shRow) && upCols.includes(shCol)){
   mergeBanque()
  }
}

//LOCK ROWS MASTER TAB
function onEdit3(event) {
  let range = event.range; //get the range of edited cell
  let row = range.getRow(); //get the row
  let col = range.getColumn(); //get the column 
  let value = event.value; //get the new value of edited cell
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MASTER'); //get the sheet where edit is made
  
  if(col == 69 && row >= 6 && value == 'TRUE'){ //check if the edited cell is BM and row is equal or greater than 5 and value is X
    let lock_range = `B${row}:BQ${row}`; //set lock range using row
    let protection = sheet.getRange(lock_range).protect() //set protection
      .setDescription(`Ligne ${row}`)  //add description
    protection.addEditor(kamaramamedia);
    protection.removeEditors(protection.getEditors()); //remove editors
  }else if(col == 69 && row >= 6 && value == 'FALSE'){ //check if the edited cell is BL and row is equal or greater than 5 and value is O
    var protectedRange = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); //get all protection with type range
    for (var i = 0; i < protectedRange.length; i  ) { //loop 
      if (protectedRange[i].getDescription() == `Ligne ${row}`) { //row matching
        protectedRange[i].remove(); //remove protection
      }
    }  
  }
}

CodePudding user response：

I rewrote them as below:

function onEdit(e) {
  onEdit1(e);
  onEdit2(e);
  onEdit3(e);
}

//ADD COLOR MPR MASTER TAB
function onEdit1(e) {
  const sh = e.range.getSheet();  
  if ((sh.getName() == "MASTER" ) && (e.range.columnStart >= 38)){
    var validationSheet = e.source.getSheetByName("CALCULS");
    var optionColor = validationSheet.createTextFinder(e.value).findNext().getBackground();
    e.range.setBackground(optionColor);
    var sourceOptionColor = sh.getRange(e.range.rowStart, 38).getBackground();
    destSheet.getRange(destRow,38).setBackground(sourceOptionColor);
  }
}

//MERGE BANQUE TAB
function onEdit2(e) {
  let sh = e.range.getSheet();
  let onglets = ['BANQUE']
  let upRows = [25]
  let upCols = [14]
  if (~onglets.indexOf(sh.getName()) && upRows.includes(e.range.rowStart) && upCols.includes(e.range.columnStart)){
   mergeBanque()//undefined function so I cannot comment
  }
}

• The one requires permissions so it needs to be an installable trigger

    //LOCK ROWS MASTER TAB
    function onEdit3(e) {
      let sheet = e.source.getSheetByName('MASTER'); 
      if(e.range.columnStart == 69 && e.range.rowStart > 5 && e.value == 'TRUE'){ 
        let lock_range = `B${e.range.rowStart}:BQ${e.range.rowStart}`; 
        let protection = sheet.getRange(lock_range).protect().setDescription(`Ligne ${e.range.rowStart}`);

• This line has an undefined variable

    protection.addEditor(kamaramamedia);
  
    protection.removeEditors(protection.getEditors());
  }else if(e.range.columnStart == 69 && e.range.rowStart > 5 && e.value == 'FALSE'){ 
    var protectedRange = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protectedRange.length; i  ) { 
      if (protectedRange[i].getDescription() == `Ligne ${e.range.rowStart}`) { 
        protectedRange[i].remove(); 
      }
    }  
  }
  

  }

The other possibility is that number 3 just takes to long because simple triggers must finish in 30 seconds. Try using installable trigger.