Google Sheets Script Editor runs simple script slowly-CodePudding

I've done this simple script to tick checkboxes automatically upon data entry of certain names. It works well but it seems very slow (a full second, maybe more) for such a simple script. I was wondering if there was a more efficient/elegant/quicker way to write this, I'm an absolute beginner with JS and coding in general and I'd like advice on how to write this more efficiently for Google Scripts.

The two conditions are to prevent the macro from firing if the name is input in another column, and the else statement simply to correct quickly if the wrong name is entered, as the target cell is linked to a lot of conditional formatting.

The script is

function onEdit() {
  var current = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getCurrentCell();
  var name = current.getValue();
  var nameRow = current.getColumn();

  if(name == "Russell" && nameRow == 7) {
    current.offset(0,6).setValue(true);
  }
  else if(nameRow == 7) {
    current.offset(0,6).setValue(false);
  }
}

Thank you!

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, how about the following modified script?

Modified script:

function onEdit(e) {
  var { range, value } = e;
  if (range.rowStart != 7) return;
  range.offset(0, 6).setValue(value == "Russell" ? true : false);
}

When the event object is used, the process cost can be reduced a little. So, I modified your script using the event object. Ref
And, when the ternary operator is used, the process cost can be reduced a little. Ref

From The two conditions are to prevent the macro from firing if the name is input in another column, and the else statement simply to correct quickly if the wrong name is entered, as the target cell is linked to a lot of conditional formatting., in this case, the lock service might be suitable. When you use it, please test the following script.

  function onEdit(e) {
    var lock = LockService.getDocumentLock();
    if (lock.tryLock(10000)) {
      try {
        var { range, value } = e;
        if (range.rowStart != 7) return;
        range.offset(0, 6).setValue(value == "Russell" ? true : false);
      } catch(err) {
        throw new Error(err);
      } finally {
        lock.releaseLock();
      }
    }
  }

Note:

When you use this script, please edit the cell of the Spreadsheet. Because this modified script is run using the event object. When you directly run this script, an error occurs. Please be careful about this.

References:

CodePudding user response：

function onEdit(e) {
  const sh = e.range.getSheet();
  if (if sh.getName() == 'SheetName' && e.range.rowStart == 7) {
    if (e.value == "Russell") {
      e.range.offset(0, 6).setValue(true);
    } else {
      current.offset(0, 6).setValue(false);
    }
  }
}

Actually I've never found onEdit() to be particularly fast. If it's only taking a second to run then there's probably nothing you can do to improve it. It's a server based system not your own personal laptop or desktop.