基于两种规则的Google Sheets自动任务分配脚本开发求助

需求核心 #

• 按总部英雄人数平均分配哥谭市、大都会两地任务
• 任务与英雄随机打乱匹配
• 每日通过IMPORTRANGE自动同步外部表格数据

分步实现（可逐段操作） #

第一步：配置数据自动同步 #

在表格空白工作表（命名为「英雄数据」「任务数据」）中，分别输入以下公式同步外部数据（替换为你的表格实际ID和区域）：

# 「英雄数据」工作表同步英雄名单
=IMPORTRANGE("你的英雄表格ID", "英雄名单!A:B")

# 「任务数据」工作表同步任务列表
=IMPORTRANGE("你的任务表格ID", "任务列表!A:C")

首次使用会弹出授权请求，点击「允许访问」即可完成数据同步。

第二步：编写可拆解的分配脚本 #

1. 打开Google Sheets，点击「扩展程序」→「Apps 脚本」
2. 清空默认代码，粘贴以下带注释的分段脚本：

// 【模块1】读取同步后的英雄、任务数据
function getSyncData() {
  const heroSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("英雄数据");
  const taskSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("任务数据");
  
  // 提取有效数据（跳过表头行）
  const heroes = heroSheet.getDataRange().getValues().slice(1);
  const tasks = taskSheet.getDataRange().getValues().slice(1);
  
  return {heroes, tasks};
}

// 【模块2】按英雄人数比例拆分任务
function splitTasksByHeroCount(heroes, tasks) {
  // 统计两地英雄数量
  const gothamHeroes = heroes.filter(h => h[1] === "哥谭");
  const metropolisHeroes = heroes.filter(h => h[1] === "大都会");
  const totalHeroes = heroes.length;
  
  // 计算任务分配数量并随机打乱任务
  const gothamTaskCount = Math.round(tasks.length * gothamHeroes.length / totalHeroes);
  const shuffledTasks = tasks.sort(() => Math.random() - 0.5);
  const gothamTasks = shuffledTasks.slice(0, gothamTaskCount);
  const metropolisTasks = shuffledTasks.slice(gothamTaskCount);
  
  return {gothamHeroes, metropolisHeroes, gothamTasks, metropolisTasks};
}

// 【模块3】随机匹配英雄与任务并输出结果
function assignTasks() {
  const {heroes, tasks} = getSyncData();
  const {gothamHeroes, metropolisHeroes, gothamTasks, metropolisTasks} = splitTasksByHeroCount(heroes, tasks);
  
  // 随机打乱英雄排序
  const shuffledGotham = gothamHeroes.sort(() => Math.random() - 0.5);
  const shuffledMetropolis = metropolisHeroes.sort(() => Math.random() - 0.5);
  
  // 整理分配结果（含表头）
  const result = [["英雄姓名", "所属总部", "任务名称", "任务地点"]];
  
  // 匹配哥谭英雄与任务（任务数多于英雄时循环分配）
  shuffledGotham.forEach((hero, index) => {
    const task = gothamTasks[index % gothamTasks.length];
    result.push([hero[0], hero[1], task[0], task[1]]);
  });
  
  // 匹配大都会英雄与任务
  shuffledMetropolis.forEach((hero, index) => {
    const task = metropolisTasks[index % metropolisTasks.length];
    result.push([hero[0], hero[1], task[0], task[1]]);
  });
  
  // 将结果写入「分配结果」工作表
  const resultSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("分配结果");
  resultSheet.clear();
  resultSheet.getRange(1, 1, result.length, result[0].length).setValues(result);
}

第三步：设置每日自动更新 #

1. 在脚本编辑器界面，点击左侧「触发器」图标
2. 点击「添加触发器」，配置：
   • 运行函数：assignTasks
   • 触发类型：「时间驱动」
   • 时间设置：「日计时器」+ 选择每日更新的时间段（如凌晨2点）
   • 点击「保存」完成配置

注意事项 #

• 提前创建「英雄数据」「任务数据」「分配结果」三个工作表，对应数据同步、存储和结果输出
• 脚本中所有注释已标注每步作用，可根据自身表格结构调整工作表名称和列位置

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