Gfrktyl

This code locks a range and adds a timestamp. More details below. I'd like to learn how to make this code more efficient (minimize code/variables and reduce redundancy). Any thoughts on the areas I can improve?

function lockEdits(e) {



  // delcare initial col variable

  var colCheck = e.range.getLastColumn();



  // exit function if the col edited was not 11, 17, 20

      if (colCheck != 11 && colCheck != 17 && colCheck != 20) {

        return;

      }



  //  // delcare remaining variables

  var ss = e.source.getActiveSheet();

  var thisRow = e.range.getRow();

  var rngHeight = e.range.getHeight();

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

  var owners = ["owner1@test.com", "owner2@test.com"];

  var checkEmpty = ss.getRange(e.range.getRow(), colCheck).getValue();

  var rejectCheck = ss.getRange(e.range.getRow(), 17).getValue();



  // if change in col 11, enter user email and timestamp, then protect range

  if (colCheck == 11 && checkEmpty !== '') {

    var protection = ss.getRange(thisRow, 2, rngHeight, 10).protect().setDescription('Lock Range:');

    var nEmail = ss.getRange(thisRow, 21, rngHeight, 1);

    var nStamp = ss.getRange(thisRow, 22, rngHeight, 1);

    nEmail.setValue(email); // print email

    nStamp.setValue(new Date()); // print timestamp

    SpreadsheetApp.flush();

    protection.removeEditors(protection.getEditors()); // protect range

    if (protection.canDomainEdit()) {

      protection.setDomainEdit(false);

    }

    protection.addEditors(owners);

    SpreadsheetApp.flush();



    // if change in col 20, enter email and timestamp, then protect range

  } else if (colCheck == 20 && checkEmpty !== '') {

    var protection = ss.getRange(thisRow, 17, rngHeight, 4).protect().setDescription('Lock Range:');

    var vEmail = ss.getRange(thisRow, 23, rngHeight, 1);

    var vStamp = ss.getRange(thisRow, 24, rngHeight, 1);

    vEmail.setValue(email); // print email

    vStamp.setValue(new Date()); // print timestamp

    SpreadsheetApp.flush();

    protection.removeEditors(protection.getEditors()); // protect range

    if (protection.canDomainEdit()) {

      protection.setDomainEdit(false);

    }

    protection.addEditors(owners);

    SpreadsheetApp.flush();



    // if rejection in col 17, enter email and timestamp

  } else if (colCheck == 17 && rejectCheck == "Rejected") {

    var vEmail = ss.getRange(thisRow, 23, rngHeight, 1);

    var vStamp = ss.getRange(thisRow, 24, rngHeight, 1);

    vEmail.setValue(email); // print email

    vStamp.setValue(new Date()); // print timestamp

    SpreadsheetApp.flush();

  }

}

So a few notes on what this does:

This script is set up as an onedit trigger and this sheet is shared with multiple users.

• If a user edits a cell in column K (11), then lock that row/range from columns B-K. Then also add the users email in column U and a timestamp in column V.


• If a user edits a cell in column T (20), then lock that row/range from columns Q-T. Then also add the users email in column W and a timestamp in column X.


• If a user edits a cell in column Q (17) to "Rejected", then just add the users email in column W and a timestamp in column X.

This works as-is, I'm just not sure this is the most efficient way to do it and I'm hoping those of you with more knowledge can help me fine-tune this a bit.

Let me know if any other info would be helpful in sorting this out!

1. It's not necessary to include SpreadsheetApp.flush() as the last command of the if blocks because there aren't any command to be executed after those blocks and Google Apps Script submits all the changes to the spreadsheet when the script execution ends. If you remove them, you will be saving three code lines.


2. On some scenarios using one sheet.getDataRange().getValues() is faster than having several sheet.getRange(...).getValue().


3. Using e.range.columnStart is faster than e.range.getLastColumn();