如何修改Google Sheets脚本实现定时复制随机数至指定单元格

需求说明 #

现有代码可定时在Sheet「random」的A1单元格生成1-200的随机数，需扩展功能：

• A1生成随机数后，1分钟后复制该值到B1
• 2分钟后复制到C1
• 3分钟后复制到D1
  此前通过复制多份代码并设置4个触发器时，出现报错Exception: Too many simultaneous invocations，要求用单段代码+单个5分钟周期性触发器实现，保证同一随机数按延迟时间依次复制到对应单元格。

解决方案代码 #

function triggerAutoRefresh() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('random');
  const randomNum = getRandomInt(1, 200);
  // 生成随机数到A1
  sheet.getRange(1, 1).setValue(randomNum);

  // 创建延迟触发器，分别复制到B1、C1、D1
  createDelayedTrigger('copyToCell', 1, randomNum, 2); // 1分钟后复制到第2列(B1)
  createDelayedTrigger('copyToCell', 2, randomNum, 3); // 2分钟后复制到第3列(C1)
  createDelayedTrigger('copyToCell', 3, randomNum, 4); // 3分钟后复制到第4列(D1)
}

// 生成指定范围的整数随机数
function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

// 创建延迟执行的一次性触发器，并存储参数
function createDelayedTrigger(funcName, delayMinutes, value, colIndex) {
  const trigger = ScriptApp.newTrigger(funcName)
    .timeBased()
    .after(delayMinutes * 60 * 1000) // 转换为毫秒单位
    .create();
  
  // 用触发器ID关联存储参数，避免全局变量冲突
  PropertiesService.getScriptProperties().setProperty(
    trigger.getUniqueId(), 
    JSON.stringify({value, colIndex})
  );
}

// 执行单元格复制操作
function copyToCell(e) {
  const triggerId = e.triggerUid;
  const scriptProps = PropertiesService.getScriptProperties();
  const data = JSON.parse(scriptProps.getProperty(triggerId));
  
  const sheet = SpreadsheetApp.getActive().getSheetByName('random');
  sheet.getRange(1, data.colIndex).setValue(data.value);
  
  // 清理已触发的触发器和存储的参数
  ScriptApp.deleteTrigger(ScriptApp.getTriggerById(triggerId));
  scriptProps.deleteProperty(triggerId);
}

核心逻辑说明 #

1. 主触发器设置：仅需给triggerAutoRefresh设置一个每5分钟执行一次的周期性触发器，作为整个流程的入口
2. 延迟触发器创建：每次生成随机数后，创建三个一次性延迟触发器，分别对应1、2、3分钟后的复制任务，避免多周期性触发器的冲突
3. 参数存储：通过PropertiesService将随机数和目标列索引与触发器ID绑定，确保延迟执行时能获取正确的操作参数
4. 资源清理：每次复制操作完成后，删除已触发的一次性触发器并清理存储的参数，避免占用不必要的资源

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