Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

178
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda