Lock-range code for Google Scripts/Sheets

Question

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 = ["[email protected]", "[email protected]"];
 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!

Answer 1

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