CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Shironats
Shironats

Reputation: 137

Google Apps Script takes very long (or doesn't run at all)

I have a simple script that checks the cell contents for how many dashes it contains and changes the cell colour accordingly. The function trigger is set to onEdit().

For some reason I don't know, this script takes at least a few seconds to run, if it even runs at all. I would like to ask for help to explain why this takes very long to execute. Thank you in advance.

function checkSix() {
var sheet = SpreadsheetApp.getActiveSheet();
var currentCell = sheet.getCurrentCell();
var contents = currentCell.getValue();

var amt = contents.replace(/[^-]/g, "").length;
if(amt >= 6)
  currentCell.setBackground('red');
else if(amt == 5)
  currentCell.setBackground('yellow');
else
  currentCell.setBackground('white');  
}

Upvotes: 0

Views: 66

Answers (2)

Tanaike
Tanaike

Reputation: 201388

I thought that your script is correct. When I tested it, the process time was about 1 second. Although I'm not sure whether this is the direct solution of your issue, can you test the following modification?

Modification points:

  1. In your script, the if statement of the multiple conditional branches is used.
    • From the benchmark, when the ternary operator is used, a process cost can be reduced a little.
  2. Your function of checkSix() is executed by OnEdit trigger of onEdit.
    • From the benchmark, when the event object is used, a process cost can be reduced a little.
  3. From or doesn't run at all, I thought that several users might use Google Spreadsheet.
    • In this case, I would like to propose to use LockService.

When above points are reflected to your script, it becomes as follows.

Modified script:

From your question, it supposes that in your situation, checkSix is called from onEdit as follows. Please be careful this. When the lock service is not used, the script becomes as follows.

function onEdit(e) {
  checkSix(e);
}

function checkSix(e) {
  var currentCell = e.range;
  var amt = e.range.getValue().replace(/[^-]/g, "").length;
  currentCell.setBackground(amt >= 6 ? 'red' : amt == 5 ? 'yellow' : 'white');
}

When the lock service is used, the script becomes as follows.

function onEdit(e) {
  checkSix(e);
}

function checkSix(e) {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      var currentCell = e.range;
      var amt = e.range.getValue().replace(/[^-]/g, "").length;
      currentCell.setBackground(amt >= 6 ? 'red' : amt == 5 ? 'yellow' : 'white');
    } catch(er) {
      throw new Error(er);
    } finally {
      lock.releaseLock();
    }
  }
}

Note:

  • In this answer, it supposes that your script is only the script shown in your question. If you run other functions when onEdit is run, my proposal might not be useful. Please be careful this.

  • If above modification was not the direct solution of your issue, as a test case, I would like to propose to try to create new Spreadsheet and test it again.

References:

Upvotes: 3

Cooper
Cooper

Reputation: 64062

function checkSix(e) {
const sh = e.range.getSheet();
if(sh.getName() == 'Your Sheet Name' && e.value ) {
  let contents = e.value;
  let l = contents.replace(/[^-]/g,'').length;
  if(l>=6) {
    e.range.setBackground('red');
  }else if(l==5) {
    e.range.setBackground('yellow');
  } else {
    e.range.setBackground('while');
  }
  
}

Since you changed the name you will require an installable onEdit trigger. Or call it from onEdit(e) function.

Upvotes: 0

Related Questions