如何在Google Apps Script中实现定时循环与逐行取值逻辑

核心逻辑 #

1. 表单提交时先尝试为最新工单分配Backend的E列值，若Main最后一行B列不为空（工单已被处理），则启动循环任务
2. 用脚本属性记录当前要读取的Backend行号（范围E2-E11），循环时依次取值，到E11后回到E2
3. 每次循环执行后，若仍未满足条件，自动创建下一个3分钟触发器；满足条件则清除所有循环触发器

完整代码 #

// 表单提交触发函数
function onFormSubmit(e) {
  const ss = e.source;
  const mainSheet = ss.getSheetByName('Main');
  const backendSheet = ss.getSheetByName('Backend');
  
  // 获取最新提交的行
  const lastRow = mainSheet.getLastRow();
  const bColumnValue = mainSheet.getRange(lastRow, 2).getValue().toString().trim();
  
  // 先尝试一次赋值
  if (bColumnValue === '') {
    const agent = backendSheet.getRange(2, 5).getValue();
    mainSheet.getRange(lastRow, 1).setValue(agent);
  } else {
    // 条件不满足，初始化循环状态并启动第一个触发器
    const props = PropertiesService.getScriptProperties();
    props.setProperty('currentRow', '2'); // 从E2开始
    setupNextTrigger();
  }
}

// 核心赋值函数，处理循环时的分配逻辑
function assignNextAgent() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName('Main');
  const backendSheet = ss.getSheetByName('Backend');
  const props = PropertiesService.getScriptProperties();
  
  const lastRow = mainSheet.getLastRow();
  const bColumnValue = mainSheet.getRange(lastRow, 2).getValue().toString().trim();
  
  // 检查条件是否满足
  if (bColumnValue === '') {
    // 获取当前要读取的行号
    let currentRow = parseInt(props.getProperty('currentRow')) || 2;
    const agent = backendSheet.getRange(currentRow, 5).getValue();
    mainSheet.getRange(lastRow, 1).setValue(agent);
    
    // 满足条件，清除所有循环触发器
    clearExistingTriggers();
    props.deleteProperty('currentRow');
  } else {
    // 更新下一个要读取的行号（循环E2-E11）
    let currentRow = parseInt(props.getProperty('currentRow')) || 2;
    currentRow = currentRow >= 11 ? 2 : currentRow + 1;
    props.setProperty('currentRow', currentRow.toString());
    
    // 创建下一个3分钟触发器
    setupNextTrigger();
  }
}

// 创建下一个3分钟触发器
function setupNextTrigger() {
  // 先清除旧的触发器避免重复
  clearExistingTriggers();
  
  // 创建3分钟后的时间驱动触发器
  ScriptApp.newTrigger('assignNextAgent')
    .timeBased()
    .after(3 * 60 * 1000) // 3分钟，单位毫秒
    .create();
}

// 清除所有assignNextAgent的触发器
function clearExistingTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === 'assignNextAgent') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}

代码说明 #

• onFormSubmit: 表单提交时触发，先执行一次赋值检查，若条件不满足则初始化循环状态并启动第一个触发器
• assignNextAgent: 核心执行函数，读取当前记录的行号，尝试赋值；若条件满足则停止循环，否则更新行号并创建下一个触发器
• setupNextTrigger: 负责创建3分钟后的触发器，创建前会清除旧的同类型触发器，避免重复执行
• clearExistingTriggers: 清理所有针对assignNextAgent的触发器，防止任务堆积

使用注意事项 #

1. 确保脚本拥有足够的权限（首次运行时需要授权）
2. Backend工作表的E2-E11必须存在有效值，否则分配会为空
3. 脚本属性会持久化存储当前行号，即使脚本重启也能继续循环

内容的提问来源于stack exchange，提问作者Debz