So, the user ticks a checkbox in a sheet and the script:

1. gets that row's col A data as testNumber;
2. looks for it in a separate sheet;
3. modify some of its elements;
4. adds that as a new record to the bottom of the separate sheet.

The problem is when multiple checkboxes are ticked fast, it add the modified arrays correctly, but adds more empty rows to the bottom and I can't find what is causing that in the script below.

function onEdit(e) {
  const thisRow = e.range.getRow();
  const thisCol = e.range.getColumn();
  const cellVal = e.range.getValue()
  if (e.range.getSheet().getName() === 'Search By Name'
    && thisCol === 7
    && thisRow > 5
    && cellVal === true
    && e.range.offset(0, -4).getValue() != '') {

    const lock = LockService.getScriptLock();
    try {
      lock.waitLock(3000); // wait 03 seconds for others' use of the code section and lock to stop and then proceed
    } catch (e) {
      Logger.log('Could not obtain lock after 03 seconds.');
      return HtmlService.createHtmlOutput("<b> Server Busy. Please try after some time <p>");
    }

    const testNumber = e.range.offset(0, -6).getValue();

    const dbSheet = e.source.getSheetByName('Database')
    const data = dbSheet.getRange(2, 1, dbSheet.getLastRow(), 12).getValues();

    let filteredperson = data.filter(e => e[11] == testNumber);

    const timeStamp = Utilities.formatDate(new Date(), Session.getTimeZone(), "MM/dd/yyyy HH:mm:ss");
    let maxNumber = Math.max.apply(null, dbSheet.getRange(2, 12, dbSheet.getLastRow(), 1).getValues());
    maxNumber = maxNumber   1

    filteredperson[0][0] = timeStamp
    filteredperson[0][11] = maxNumber
    filteredperson[0][12] = ''
    filteredperson[0][13] = ''
    filteredperson[0][14] = ''
    const lastRow = lastRowForColumn(dbSheet, 1);

    dbSheet.getRange(lastRow   1, 1, 1, filteredperson[0].length).setValues(filteredperson);

    e.range.setValue(false);
    lock.releaseLock();
  }
}

Here's the weird behavior, in the last attempt:

Apreciate your help!

CodePudding user response：

Description

I do not think LockService is doing what you expect for simple trigger onEdit().

I created a simple onEdit() script and tried typing real fast in a spreadsheet. It works some of the times but then error out without doing what it was supposed to but then resumes.

Also return HTMLService() doesn't do anything in Spreadsheet context.

I believe multiple onEdit() can run asynchronously and ignore LockService.

Screen shots

Initial data

Error message

Resulting data

Script

function onEdit(e) {
  try {
    var lock = LockService.getScriptLock();
    try {
      lock.waitLock(5000);
    }
    catch(err) {
      SpreadsheetApp.getUi().alert(err);
      return;
    }
    Utilities.sleep(2000);
    e.range.offset(0,1).setValue(e.oldValue);
    lock.releaseLock();
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Reference