0

I have this script running for a file that I share with 10 other colleagues.

Problem: Occasionally the script failed to generate a timestamp in Col22 although Col17 is filled in manually. What could be the issue?

  function onEdit(e){
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const sheetname = "Intake";
  const currentDate = new Date();
  const value = e.range.getValue();
  const Email = Session.getActiveUser().getEmail();
  const lock = LockService.getScriptLock();
  var success = lock.tryLock(10000);

  if(success) {
    if(col===17 && row > 1 && e.source.getActiveSheet().getName() === sheetname && value != "" ){
      if(e.source.getActiveSheet().getRange(row,22).getValue() == ""){
    e.source.getActiveSheet().getRange(row,22).setValue(currentDate);
      }
    
    }
    if(col===20 && row > 1 && e.source.getActiveSheet().getName() === sheetname && value === "Pass"){
      if(e.source.getActiveSheet().getRange(row,21).getValue() == ""){
      e.source.getActiveSheet().getRange(row,21).setValue(currentDate) &&
      e.source.getActiveSheet().getRange(row,24).setValue(Email);
      }
    }
    if ((e.source.getActiveSheet().getName() === sheetname) &&
      (col === 15 && row > 1 && value !="") ||
      (col === 16 && row > 1 && value !="")||
      (col === 20 && row > 1 && value === "Pass")) {
      let protection = e.range.protect();
      protection.addEditor("xxx@yyyy.com");
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  }
  lock.releaseLock();
}

I read that I need to include EventObject which I did. Other than that I didnt do anything. Thank you!

otw
  • 1
  • 1
  • Can you provide your script as text instead of an image? From your situation, how about using LockService? [Ref](https://developers.google.com/apps-script/reference/lock) – Tanaike Feb 27 '23 at 12:11
  • [format code](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) – Cooper Feb 27 '23 at 16:18
  • Hi @Tanaike thanks! I have edited my post and added my script above. I will read about the LockService and apply it. I will let you know if it works or not. thanks again! I really appreciate it. – otw Mar 13 '23 at 09:27
  • Thank you for replying. About LockService, if you want more information, I think that Stackoverflow has several sample scripts related to LockService, and those will help understanding LockService. – Tanaike Mar 13 '23 at 12:39
  • `value != ""` and `getValue() == ""` Are you testing for a blank cell? I suggest you look at `isblank()` as well as research the difference between `!=` vs `!==`. – Tedinoz Mar 14 '23 at 11:24
  • Hi @Tanaike I incorporated LockService in my script above. But no luck. Surely I did something not correct. Does LockService also help in preventing concurrency from the same user? So for example if I fill in some cells by dragging down/copying the cell above it, will LockService help? – otw Mar 17 '23 at 17:30
  • Some suggestions: 1) you must do some trouble-shooting with "logger.log()" statements at strategic points in your script to confirm whether the actual outcomes are the same as the outcomes that you expect. 2) [tryLock](https://developers.google.com/apps-script/reference/lock/lock#trylocktimeoutinmillis) uses `if (!success)` which is, I think, a more certain outcome than `if(success)`. 3) you use `!= ""` but `!== ""` is the correct operator. I suggest you read [How do I check for an empty/undefined/null string in JavaScript?](https://stackoverflow.com/a/154068/1330560). – Tedinoz Mar 18 '23 at 04:30

0 Answers0