Javascript – Google Apps Script Race Conditions Handling

I have a spreadsheet that multiple people have access to edit. As part of the spreadsheet, I have a script that copies some data from one sheet to another when a checkbox on that row is set to true to begin an internal tracking process.

function onEdit(e){
  //get the sheet where the edit occurs
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = e.range.getSheet();  
  var sourceRange = e.range;
  var sourceRow = sourceRange.getRow();
  var sourceColumn = sourceRange.getColumn();

  if((sourceSheet.getName() == "ScriptTestingSource") && 
  e.range.columnStart==18 && e.range.rowStart >= 2){
    if(e.value == "TRUE"){
      var targetSheet = ss.getSheetByName("ScriptTestingTarget");

      var sourceValues = sourceSheet.getRange(sourceRow,1,1,11).getValues();
      //splice off unneeded values
      sourceValues[0].splice(7,1)
      sourceValues[0].splice(3,2)
      sourceValues[0].splice(0,1)
    
      //insert new row on on target sheet
      var targetRows = targetSheet.getDataRange().getNumRows()
      targetSheet.insertRowsAfter(targetRows,1);
      targetSheet.getRange(targetRows+1,1,1,7).setValues(sourceValues)
    }
  }
}

Now this works really well for me at doing exactly that single task of copying a necessary data from a row, trimming off the rest, and inserting it into a new row on a different sheet. However, I’m trying to expand the functionality, and solve some issues with use-cases, such as if someone marks multiple rows as true quickly. When this happens, it will create a new row (sometimes multiple) on the target sheet, but then it will just overwrite the first new row with the copied data repeatedly.

I was thinking that perhaps instead of having the work done within the onEdit function, it would instead simply push the "work order" to an array that would function as a queue that would pop the work orders off the array upon completion. Hopefully this would allow more work to be added to the queue, as it works.

var workQueue = [];
var working = false;

function onEdit(e){
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = e.range.getSheet();  
  var sourceRange = e.range;
  var sourceRow = sourceRange.getRow();
  var sourceColumn = sourceRange.getColumn();
  if((sourceSheet.getName() == "ScriptTestingSource") && 
  e.range.columnStart==18 && e.range.rowStart >= 2){
    if(e.value == "TRUE"){
      workQueue.push([0,sourceSheet,sourceRange,sourceRow,sourceColumn])
      if(workQueue.length<1 && working==false){
        doWork();
      }
    }
  }
}
function doWork(){
  working=true;
  while(workQueue.length>0){
    workOrder = workQueue[0]
    switch(workOrder[0]){
      //0: copy data for carrier tracking to sheet
      case 0:
        //copy data to sheet order
        var sourceSheet = workOrder[1];
        var sourceRange = workOrder[2];
        var sourceRow = workOrder[3];
        var sourceColumn = workOrder[4];
      

        //get values from sheet
        var sourceValues = sourceSheet(sourceRow,1,1,11);
        //trim and clean data
        sourceValues[0].splice(7,1);
        sourceValues[0].splice(3,2);
        sourceValues[0].splice(0,1);

        //insert new row in target sheet
        var targetSheet = ss.getSheetByName("ScriptTestingTarget");
        var targetRows = targetSheet.getDataRange().getNumRows();
        targetSheet.insertRowsAfter(targetRows,1);
        targetSheet.getRange(targeRows+1,1,1,7).setValues(sourceValues);
        workQueue.pop();
        break;
      default:
        break;
    }
  }
  working=false;
}

Something like this? though the example above doesn’t actually work. The switch-case block is to add modularity as I add new functionality to the script, such as removing a row from the sheet. E.g.: someone set the value to true accidentally so they set it back to false, rather than going and deleting the row manually, I want to delete the row using an identifying Key-value. This would be added to the queue to be done in sequence forcing a bottleneck.