title: Google Sheets Apps Script On Edit Remove Editors From Protected Range Very Slow / Not Completing
body: <p>I am relatively new to Google Apps Script and am having difficulties with my code below.  Basically, it is bound to a sheet that acts as a workflow.  I use the installed onEdit trigger, it loops through to see what cells have changed, then either timestamps, sends an email, or protects a range, given the cells that are changed.</p>
  
  <p>I am running into the time stamp not always persisting, the email not always sending, and, the protection taking forever.</p>
  
  <p>Are there any more efficient ways to do this?</p>
  
  <pre><code>function onEditFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();
  
    // CHECKS FOR CHANGE IN "UPDATES" SHEET ONLY
    if (sheet.getSheetName() == "Updates") {
      var activeRange = sheet.getActiveCell();
      var activeRow = activeRange.getRow();
      var activeColumn = activeRange.getColumn();
  
      var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Updates');
      var sss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('LookUps');
  
      var email = Session.getActiveUser().getEmail();
      var timestamp = new Date();
  
      var itemAttributes = sheet.getRange(activeRow, 1,activeRow,19).getValues();
  
    // CHECKS FOR CHANGE IN ITEM COLUMN (A)
      // if column A clear out the other cells
      if (activeColumn == 1) { 
        var r = sheet.getActiveRange();
  
        // CHECKS IF NUMBER OF ROWS INDICATES A PASTED RANGE
        // if pasted range is more than one row in size, then loop through and do validation on all impacted rows
        if (r.getNumRows() &gt; 1) {  
          SpreadsheetApp.getActiveSpreadsheet().toast('Updating.....please wait.', 'Status', -1);
          var l = r.getNumRows()
          sheet.getRange(activeRow, 2, l, 13).clearContent();
          for (var x = 0; x &lt; l; x++) {
            var ac = activeRow + x
            if(ss.getRange(ac, 1).getvalue != '') {
              sheet.getRange(ac, 12).setValue(email);
            }
            ss.getRange(ac, 1).copyTo(sss.getRange('G1'), {contentsOnly:true})    
            var dynamicList = sss.getRange('G2:G15');   // set to your sheet and range
            var arrayValues = dynamicList.getValues();
            var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
            ss.getRange(ac, 2).setDataValidation(rangeRule); // set range to your range
  
            //SpreadsheetApp.flush();
            var NewItemCheck = sss.getRange('G2').getValues()
            if (NewItemCheck == "" &amp;&amp; ss.getRange(ac, 1).getvalue != '') {
              sheet.getRange(ac, 10).setValue("NEW ITEM") 
            }
          }  // END FOR LOOP THROUGH ROWS OF RANGE
  
          SpreadsheetApp.getActiveSpreadsheet().toast('DONE', 'Status', 2);
        }  // END IF RANGE IS GREATER THAN 1 ROW
        else
        {
          sheet.getRange(activeRow, 2, 1, 13).clearContent();
          if(itemAttributes[0][0] != '') {
            sheet.getRange(activeRow, 12).setValue(email);
          }
          sss.getRange('G1').setValue(itemAttributes[0][0]);
          var dynamicList = sss.getRange('G2:G15');
          var arrayValues = dynamicList.getValues();
          var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
          ss.getRange(activeRow, 2).setDataValidation(rangeRule);
          //SpreadsheetApp.flush();
          var NewItemCheck = sss.getRange('G2').getValues()
          if (NewItemCheck == "" &amp;&amp; itemAttributes[0][0] != '') {
            sheet.getRange(activeRow, 10).setValue("NEW ITEM") 
          }
        }
      }
  
    // CHANGE IN Vendor Name / ID - LOC COLUMN (B)
      if (activeColumn == 2) {
        // if column B in Sheet UPDATES is changed, update adjacent drops downs
        sheet.getRange(activeRow, 5).clearContent();
        sss.getRange('H1').setValue(itemAttributes[0][1])
  
        var dynamicList = sss.getRange('H2:H15');
  
        var arrayValues = dynamicList.getValues();
        var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
        ss.getRange(activeRow, 5).setDataValidation(rangeRule);
  
        var a = itemAttributes[0][1].slice(-9);
        var b = a.split(" ");
        var c = itemAttributes[0][1].slice(-2);
  
        var vendLocCombo = [[b,c]];
        var vendLocComboRange = ss.getRange(activeRow, 3, 1, 2);
        vendLocComboRange.setValues(vendLocCombo);
      } 
  
      // SUBMITTED
      if (activeColumn == 11 &amp;&amp; itemAttributes[0][10] ){
        // if column K in Sheet UPDATES is changed to TRUE (checked), email
        // cost analysts in var CostEmail, and protect submitted info
        sheet.getRange(activeRow, 13).setValue(timestamp);
        SpreadsheetApp.flush();
  
        var Prange = sheet.getRange('A'+activeRow+':M'+activeRow);
        var protection = Prange.protect().setDescription('SubmitLock A'+activeRow+':M'+activeRow);
        protection.removeEditors(protection.getEditors());
        protection.addEditors(['emailhere.com','emailhere.com']);
  
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      }
  
      // COMPLETED
      if (activeColumn == 15 &amp;&amp; itemAttributes[0][14]) {
        // if column O in Sheet is changed to TRUE (checked) then user email and timestamp columns Q and R 
        sheet.getRange(activeRow, 17).setValue(email);
        SpreadsheetApp.flush();
        sheet.getRange(activeRow, 18).setValue(timestamp);
        SpreadsheetApp.flush();
        var Prange = sheet.getRange('N'+activeRow+':S'+activeRow);
        var protection = Prange.protect().setDescription('CompleteLock N'+activeRow+':S'+activeRow);
        protection.removeEditors(protection.getEditors());
        protection.addEditors(['emailhere.com','emailhere.com']);
  
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      }
  
      // DENIED
      if (activeColumn == 16 &amp;&amp; itemAttributes[0][15]) {
        // if column P in Sheet is checked (true) send email to original submitter in column M
        sheet.getRange(activeRow, 17).setValue(email);
        sheet.getRange(activeRow, 18).setValue(timestamp);
  
        var Prange = sheet.getRange('O'+activeRow+':T'+activeRow);
        var protection = Prange.protect().setDescription('CompleteLock O'+activeRow+':T'+activeRow);
        protection.removeEditors(protection.getEditors());
        protection.addEditors(['emailhere.com','emailhere.com']);
  
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
  
        var DeniedEmailAddress = itemAttributes[0][11];
        var message = "  Comments :     "+itemAttributes[0][13];
        var subject = "[Denied - Contracted Cost Request] "+itemAttributes[0][0];
  
        MailApp.sendEmail(DeniedEmailAddress, subject, message,{
            name: 'Contracted Cost Update',
            attachments: []
        });
  
        sheet.getRange(activeRow, 19).setValue("X");
      }
    }
  }
  </code></pre>
  
  <p>EDITS:</p>
  
  <pre><code>function onEditFunction(e) {
  
    if (e.source.getActiveSheet().getSheetName() == "Updates") {
      // CHECKS FOR CHANGE IN "UPDATES" SHEET ONLY
  
      var sheet = e.source.getActiveSheet();
      var protectionDescription;
      var protectedRange;
      var i;
      var protection;
  
      var removeEditorsArray = 
  ["group1@email.com","group2@email"];
  
      var activeColumn = e.range.getColumn();
  
      switch(activeColumn){
          case 1: //Item
              changeItem(e, sheet, activeColumn);
              break;
          case 2: //Vendor Name/ID - Loc 
              changeVendor(e, sheet, activeColumn);
              break;
          case 11: //SUBMIT check box
              changeSubmit(e, sheet, activeColumn, protectionDescription, protectedRange, removeEditorsArray);
              break;
          case 15: //COMPLETE check box
              changeComplete(e, sheet, activeColumn);
              break;
          case 16: //DENIED check box
              changeDenied(e, sheet, activeColumn);
              break;
      }
    }
  }
  
  
  function changeSubmit(e, sheet, activeColumn, protectionDescription, protectedRange, removeEditorsArray){
      // if column K in Sheet UPDATES is changed to TRUE (checked), enter a  timestamp and protect submitted info 
  
      var activeRow = e.range.getRow();
      var timestamp = new Date();
      var removeLen = removeEditorsArray.length
  
      sheet.getRange(activeRow, 13).setValue(timestamp);  
  
      protectedRange = 'A'+activeRow+':M'+activeRow;
      protectionDescription = 'SubmitLock A'+activeRow+':M'+activeRow;
  
      protectRanges(sheet, protectionDescription, protectedRange, removeEditorsArray)
  }
  
  function protectRanges(sheet, protectionDescription, protectedRange, removeEditorsArray) {
  
      var i=0;
      var Len = removeEditorsArray.length;
  
      protectedRange = sheet.getRange(protectedRange);
      protectionDescription = protectedRange.protect().setDescription(protectionDescription);
  
      for (i; i &lt; Len; i++) {
          protectionDescription.removeEditor(removeEditorsArray[i]);
      }
  }
  </code></pre>