Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

173
Views
Why is this adding more rows than the values (2D Array) set when a cell is edited fast?

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: enter image description here

Apreciate your help!

about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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 Initial data

Error message Error message

Resulting data 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

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!