Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

175
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda