I have a Google spreadsheet that is used concurrently by a team of people located in the Philippines, South America and the U.S. The load on the spreadsheet is usually no more than ten people at a time. The spreadsheet has two sheets, one being a "Submissions" page and the other is named "Archive". There is existing data in the spreadsheet but I don’t believe it’s enough to cause these issues.

The logic is pretty simple. There is a dropdown in Column 14 that has the values "Assignment", "Processing", "Closed", "Duplicate" and "Saved". If the selection is "Assignment" or "Processing" there are timestamps captured at columns 10 and 11. If the selection is "Closed", "Duplicate" or "Saved" there is a timestamp captured on row 12, it checks to make sure the timestamp is captured and the row is moved to the "Archived" tab.

I’m having issues with the script not being consistent capturing the timestamps or moving the row if there are more than one person using the dropdown. Sometimes it works perfectly and captures the timestamps as needed and moves the data as it should. It seems that always if there’s more than one person working in the spreadsheet it has issues.

The other issue is that it’s very slow. It could be because of some of my attempts to make it consistent with locks and retries.

The errors always seem to be either a "Exceeded max execution time" or "Exception: Lock timeout: another process was holding the lock for too long."

I’ve rewritten this and tried different logic and I’m still having issues. My priority is that the script works consistently in capturing timestamps and moving the data, secondly is the speed of the script. My code is below, please overlook any logic that may be incorrect, kind of a newbie with Apps Script.

function onEdit(e) {
  if (!e || !e.range) {
    Logger.log('Event object is invalid or undefined.');
    return;
  }

  var lock = LockService.getScriptLock();
  var maxRetries = 5; // Increased number of retries for high contention
  var retryDelay = 10000; // 10 seconds delay between retries
  var attempt = 0;

  while (attempt < maxRetries) {
    try {
      // Attempt to acquire the lock.
      lock.waitLock(15000); // Wait for up to 15 seconds

      var range = e.range;
      var sheet = range.getSheet();
      var value = range.getValue();

      // Only proceed if the edit is in 'Submissions' and in column 14.
      if (sheet.getName() !== 'Submissions' || range.getColumn() !== 14) {
        return;
      }

      var timestampColumn = getTimestampColumn(value);
      if (timestampColumn === null) {
        return;
      }

      // Check if a timestamp is already present; if so, exit
      var existingTimestamp = sheet.getRange(range.getRow(), timestampColumn).getValue();
      if (existingTimestamp) {
        lock.releaseLock();
        return;
      }

      // Write the timestamp
      var timestamp = new Date();
      sheet.getRange(range.getRow(), timestampColumn).setValue(timestamp);

      // Move row to 'Archived' sheet if needed
      if (['Ticket closed', 'Unnecessary', 'Saved'].includes(value)) {
        moveRowToCompleted(sheet, range.getRow());
      }

      break; // Exit loop if successful

    } catch (e) {
      Logger.log('Error: ' + e.toString());
      attempt++;
      if (attempt < maxRetries) {
        Utilities.sleep(retryDelay); // Wait before retrying
      } else {
        Logger.log('Exceeded maximum retries. Giving up.');
      }
    } finally {
      lock.releaseLock(); // Ensure the lock is always released
    }
  }
}

function getTimestampColumn(value) {
  switch (value) {
    case 'Assignment':
      return 10;
    case 'Processing':
      return 11;
    case 'Closed':
    case 'Duplicate':
    case 'Saved':
      return 12;
    default:
      return null;
  }
}

function moveRowToCompleted(sheet, rowIndex) {
  var numColumns = sheet.getMaxColumns();
  var rowValues = sheet.getRange(rowIndex, 1, 1, numColumns).getValues();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('Archived');
  var targetLastRow = targetSheet.getLastRow();
  targetSheet.getRange(targetLastRow + 1, 1, 1, numColumns).setValues(rowValues);
  
  // Delete the row after moving to avoid concurrent modification issues.
  sheet.deleteRow(rowIndex);
}

I tried with and without locks and retries. I tried with and without Google’s Api. I am currently trying to clear some data out of the spreadsheet and maybe try to take care of some functionality in the Sheet itself if possible.

Edit: I thought I had installed a trigger? I clicked the plus sign on the "Add Trigger" tab at the bottom right of the page. There was then a prompt with dropdowns for "On Edit"(which function), "Head"(what deployment), "From Spreadsheet"(event Source) and "In Open"(event type). When looking at the "Executions" tab, sometimes the exectutions say "Simple Trigger" others just "Trigger". I may have done something wrong setting it up or trying to use it?