Google Sheets Apps Script On Edit Remove Editors From Protected Range Very Slow / Not Completing

0
votes

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.

I am running into the time stamp not always persisting, the email not always sending, and, the protection taking forever.

Are there any more efficient ways to do this?

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() > 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 < 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 == "" && 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 == "" && 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 && 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 && 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 && 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");
    }
  }
}

EDITS:

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 = 
["[email protected]","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 < Len; i++) {
        protectionDescription.removeEditor(removeEditorsArray[i]);
    }
}
1
google-apps-scriptgoogle-sheets
You've posted a lot of code, and it's all a single function - I recommend at minimum splitting this one function into 3 functions, each dealing with the individual tasks you seek to perform. The function receiving the onEdit trigger would use logic to call the desired function, passing any relevant arguments. I also recommend accessing the event object available to onEdit triggered functions. – tehhowch
I have to second tehhowch here. Your active spreadsheet, active cell and even the value that was in the cell before and after the change are all accessible through the event object (you would write onEdit(e) for example, and e will contain what you need. Do split the code as it's a lot to go over all at once. A quick tip for the logic on which action to perform, use a switch instead of separate if statements – Vytautas
@tehhowch - Thank you for the suggestions! I have edited the code - I broke it down by function, and utilized the event object. Now, I am able to look at the Execution Transcript more clearly and 98% of the 80-90 seconds this script takes to run is on "Protection.removeEditor". I am using an array for this, but I have also tried "Protection.removeEditorS" with just a text field and it still takes about the same, large, chunk of time. I am basically trying to remove 2 group names (that have edit access to the sheet) from editor list a given protected range in the sheet. – Three-D
@Vytautas - Thank you! I edited and now use the switch function. Makes it easier to analyze what is going on, and when making edits. Also using (e). It is still taking a LONG time to remove protection from a range. Any ideas? – Three-D
@Three-D, definitely an improvement, good job. I'd go further (you erroneously reference e.source.getActiveSheet() instead of e.range.getSheet() - the user can change tabs while this function runs), and there are possible future issues with parameter passing. I'd move the protection variables down only where they are needed (inside changeSubmit, and as arguments to protectRanges). I also recommend only using the batch method removeEditors(String[]). How big is the protected area? – tehhowch

1 Answers

0
votes

Thanks to your help @tehhowch and @Vytautas, I was able to come up with a very efficient code, but did have to change the protection to a warning only, which is fine for the purposes of the sheet.

Please see below:

function onEditFunction(e) {

  if (e.range.getSheet().getSheetName() == "Updates") {
    // CHECKS FOR CHANGE IN "UPDATES" SHEET ONLY

    var protectedRange;
    var protectionDescription;

    var sheet = e.range.getSheet();
    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);
            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){
    // 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 l = e.range.getNumRows();

    if (l > 1){ // if pasted range is more than one row in size, then loop through and do validation on all impacted rows
      SpreadsheetApp.getActiveSpreadsheet().toast('Updating.....please wait.', 'Status',-1);
      }

    for (var x = 0; x < l; x++) {
          sheet.getRange(activeRow + x, 13).setValue(timestamp);
//          SpreadsheetApp.flush();

          protectedRange = 'A'+(activeRow + x) +':M'+(activeRow + x);
          protectionDescription = 'SubmitLock A'+(activeRow + x)+':M'+(activeRow + x);
          protectRanges(sheet, protectionDescription, protectedRange);
        }
    if (l > 1){ // if pasted range is more than one row in size, then loop through and do validation on all impacted rows
      SpreadsheetApp.getActiveSpreadsheet().toast('DONE', 'Status',2);
      }
}

function changeComplete(e, sheet, activeColumn){
  // if column O in Sheet is changed to TRUE (checked) then user email and timestamp columns Q and R 

    var activeRow = e.range.getRow();

    var newData = [];

    var email = Session.getActiveUser().getEmail();
    var timestamp = new Date();

    newData = [[email],[timestamp]];


    sheet.getRange(activeRow, 17, activeRow, 18).setValues(newData);


    protectedRange = 'N'+activeRow+':S'+activeRow;
    protectionDescription = 'CompleteLock N'+activeRow+':S'+activeRow;
    protectRanges(sheet, protectionDescription, protectedRange);
}

function changeItem(e, sheet, activeColumn){

    var activeRow = e.range.getRow();

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Updates');
    var sss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('LookUps');

    var email = Session.getActiveUser().getEmail();

    var l = e.range.getNumRows();
    var itemAttributes = sheet.getRange(activeRow, 1,(activeRow + l - 1),19).getValues();

    if (l > 1){ // if pasted range is more than one row in size, then loop through and do validation on all impacted rows
      SpreadsheetApp.getActiveSpreadsheet().toast('Updating.....please wait.', 'Status',-1);
      }

        for (var x = 0; x < l; x++) {
          if(itemAttributes[x][0] != ''){
            sheet.getRange(activeRow + x, 2,l, 13).clearContent(); // clears out row if new entry for item id
            sheet.getRange(activeRow + x, 12).setValue(email);  // stamps user in created by column
            }

        sss.getRange('G1').setValue(itemAttributes[x][0]);
        var dynamicList = sss.getRange('G2:G15');
        var arrayValues = dynamicList.getValues();
        var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
        ss.getRange(activeRow + x,2).setDataValidation(rangeRule);

          var NewItemCheck = sss.getRange('G2').getValues()

          if (NewItemCheck == "" && itemAttributes[x][0] != '') {
            sheet.getRange(activeRow + x, 10).setValue("NEW ITEM") 
            }
          }

    if (l > 1){ // if pasted range is more than one row in size, then loop through and do validation on all impacted rows
      SpreadsheetApp.getActiveSpreadsheet().toast('DONE', 'Status',2);
      }
}

function changeVendor(e, sheet, activeColumn){
     // if column B in Sheet UPDATES is changed, update adjacent drops downs

    var activeRow = e.range.getRow();

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Updates');
    var sss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('LookUps');

    var itemAttributes = sheet.getRange(activeRow, 1,activeRow,19).getValues();

    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);
} 

function changeDenied(e, sheet, activeColumn){
  // if column P in Sheet is checked (true) send email to original submitter in column M

    var activeRow = e.range.getRow();

    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();

      sheet.getRange(activeRow, 17).setValue(email);
      sheet.getRange(activeRow, 18).setValue(timestamp);

    protectedRange = 'N'+activeRow+':S'+activeRow;
    protectionDescription = 'CompleteLock N'+activeRow+':S'+activeRow;
    protectRanges(sheet, protectionDescription, protectedRange);

    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");
}

function protectRanges(sheet, protectionDescription, protectedRange) {

    protectedRange = sheet.getRange(protectedRange);
    protectionDescription = protectedRange.protect().setDescription(protectionDescription);
    protectionDescription.setWarningOnly(true);
    SpreadsheetApp.flush();
}