Google Sheets票务代码生成自动化：公式还是Apps Script？

关于方案选择 #

公式方案确实不是最优解——你需要手动维护动态范围、处理累计偏移量，跨多个工作表的公式不仅容易出错，操作复杂度甚至超过手动整理。Apps Script是更合适的选择，它能根据Quantities表的配置自动完成所有票种的代码分配，完全避免硬编码数值的问题。

自定义Apps Script代码 #

以下脚本可以实现你需要的功能，且能灵活适应Quantities表的更新：

function distributeCodes() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const quantitiesSheet = ss.getSheetByName('Quantities');
  const codesSheet = ss.getSheetByName('Codes');
  
  // 读取Quantities表的票种和数量（A列=票种，B列=数量，从第2行开始）
  const quantityData = quantitiesSheet.getRange(2, 1, quantitiesSheet.getLastRow()-1, 2).getValues();
  // 读取Codes表的所有代码
  const allCodes = codesSheet.getRange(1, 1, codesSheet.getLastRow(), 1).getValues().flat();
  
  let currentCodePosition = 0;
  
  quantityData.forEach(([ticketType, codeCount]) => {
    // 跳过空行或无效数量
    if (!ticketType || codeCount <= 0) return;
    
    // 获取或创建票种对应的工作表
    let targetSheet = ss.getSheetByName(`${ticketType} Codes`);
    if (!targetSheet) {
      targetSheet = ss.insertSheet(`${ticketType} Codes`);
    }
    
    // 清空工作表原有内容，避免重复数据
    targetSheet.clearContents();
    
    // 生成第一列：票种名称+编号
    const ticketNumbers = Array.from({length: codeCount}, (_, index) => [`${ticketType} ${index + 1}`]);
    // 提取对应数量的连续代码
    const selectedCodes = allCodes.slice(currentCodePosition, currentCodePosition + codeCount).map(code => [code]);
    
    // 批量写入数据到目标工作表
    targetSheet.getRange(1, 1, codeCount, 1).setValues(ticketNumbers);
    targetSheet.getRange(1, 2, codeCount, 1).setValues(selectedCodes);
    
    // 更新下一个票种的代码起始位置
    currentCodePosition += codeCount;
  });
  
  // 完成提示
  SpreadsheetApp.getUi().alert('代码分配已完成！');
}

脚本优势对比录制的宏 #

• 无需硬编码任何数量或行号，完全依赖Quantities表的配置
• 自动处理新增票种（会自动创建对应的工作表）
• 批量写入数据，比录制宏的逐行操作效率更高
• 自动清空原有数据，避免重复分配

使用步骤 #

1. 打开你的Google表格
2. 点击顶部菜单栏「扩展程序」→「Apps脚本」
3. 删除编辑器中的默认代码，粘贴上述脚本
4. 点击保存按钮，给脚本命名（例如distributeCodes）
5. 点击运行按钮，首次运行需完成权限授权（按照提示操作即可）
6. 运行完成后，所有票种工作表会自动更新

如果后续修改Quantities表的数量或新增票种，只需重新运行脚本即可。

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