多用户并发下Google Sheets Apps Script运行慢且不稳定求助

场景与核心需求 #

• 共享Google表格，供菲律宾、南美、美国团队成员并发使用（同时在线≤10人），包含「Submissions」（提交表）和「Archive」（归档表）两个工作表，现有数据量不大
• 核心逻辑：第14列设下拉菜单（选项：Assignment/Processing/Closed/Duplicate/Saved）
  • 选择Assignment或Processing时，自动在第10、11列对应位置生成时间戳
  • 选择Closed/Duplicate/Saved时，自动在第12列生成时间戳，随后将该行移至「Archive」工作表

当前遇到的问题 #

• 多用户操作下拉菜单时，时间戳捕获、行移动操作不稳定，有时正常有时失效
• 脚本运行极慢，频繁出现两类报错：
  • Exceeded max execution time（超出最大执行时间）
  • Exception: Lock timeout: another process was holding the lock for too long.（锁超时：其他进程持有锁时间过长）

已尝试的解决方案（均无效） #

• 添加/移除锁与重试逻辑
• 尝试使用/不使用Google API
• 清理表格数据
• 当前优先级：先保障时间戳捕获与行移动的稳定性，其次优化运行速度

现有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);
}

触发器设置疑问 #

• 手动添加触发器时，选择函数为onEdit，部署版本为「Head」，事件源「From Spreadsheet」，事件类型「On Open」
• 但在「Executions」标签中，部分执行显示为「Simple Trigger」，部分显示为「Trigger」，怀疑触发器配置有误

内容的提问来源于stack exchange，提问作者The Dude Abides