基于Stock Code自动生成装箱单的App Script优化需求咨询

核心思路 #

1. 通过自定义菜单触发脚本，读取生产计划工作表中的新增数据
2. 从Master Key Sheet构建Stock Code与零件清单的映射关系
3. 将生产计划中的SO#、客户名称、地点、WO#等信息，关联到对应Stock Code的每个零件行
4. 动态生成装箱单内容并写入目标工作表，彻底解决预设固定行数导致的截断问题

完整代码实现 #

function onOpen() {
  // 给表格添加自定义操作菜单
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('装箱单工具')
    .addItem('一键生成装箱单', 'generatePackingList')
    .addToUi();
}

function generatePackingList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 替换为你实际的工作表名称
  const productionSheet = ss.getSheetByName('生产计划');
  const masterKeySheet = ss.getSheetByName('Master Key Sheet');
  const packingSheet = ss.getSheetByName('装箱单') || ss.insertSheet('装箱单');

  // 读取生产计划表头与数据
  const productionData = productionSheet.getDataRange().getValues();
  const header = productionData[0];
  // 定位各关键字段的列索引
  const soIdx = header.indexOf('销售订单号（SO#）');
  const customerIdx = header.indexOf('客户名称');
  const locationIdx = header.indexOf('地点');
  const woIdx = header.indexOf('工单编号（WO#）');
  const stockCodeIdx = header.indexOf('Stock Code');

  // 构建Stock Code到零件的映射表
  const masterData = masterKeySheet.getDataRange().getValues();
  const stockCodeMap = new Map();
  // 假设Master Key的列顺序为：Stock Code、零件名称、零件数量
  for (let i = 1; i < masterData.length; i++) {
    const code = masterData[i][0];
    const part = masterData[i][1];
    const qty = masterData[i][2];
    if (!stockCodeMap.has(code)) stockCodeMap.set(code, []);
    stockCodeMap.get(code).push({ part, qty });
  }

  // 初始化装箱单表头
  const packingHeader = ['销售订单号（SO#）', '客户名称', '地点', '工单编号（WO#）', 'Stock Code', '零件名称', '零件数量'];
  packingSheet.clear();
  packingSheet.appendRow(packingHeader);

  // 遍历生产计划数据，生成关联后的装箱单内容
  for (let i = 1; i < productionData.length; i++) {
    const row = productionData[i];
    const so = row[soIdx];
    const customer = row[customerIdx];
    const location = row[locationIdx];
    const wo = row[woIdx];
    const stockCode = row[stockCodeIdx];

    // 跳过空行或无Stock Code的无效行
    if (!stockCode) continue;

    // 匹配零件清单
    const parts = stockCodeMap.get(stockCode);
    if (!parts) {
      packingSheet.appendRow([so, customer, location, wo, stockCode, '无匹配零件数据', '']);
      continue;
    }

    // 将生产计划字段关联到每个零件行
    parts.forEach(item => {
      packingSheet.appendRow([so, customer, location, wo, stockCode, item.part, item.qty]);
    });
  }

  // 自动调整列宽优化显示
  packingSheet.autoResizeColumns(1, packingHeader.length);
  SpreadsheetApp.getUi().alert('装箱单生成完成！');
}

使用步骤 #

1. 打开目标Google表格，点击「扩展程序」>「Apps 脚本」，将上述代码粘贴到编辑器
2. 根据你的实际工作表名称，修改代码中productionSheet、masterKeySheet的名称
3. 确认Master Key Sheet的列顺序：第1列是Stock Code，第2列是零件名称，第3列是数量（若列顺序不同，调整代码中masterData[i][0]、masterData[i][1]、masterData[i][2]的索引值）
4. 保存脚本后刷新表格，顶部会出现「装箱单工具」菜单，点击「一键生成装箱单」即可执行
5. 脚本会自动创建「装箱单」工作表（若不存在），所有关联数据将动态写入，无需预设行数

可选优化 #

• 若需要自动监听粘贴操作生成装箱单，可替换onOpen为onEdit触发器，检测生产计划工作表的编辑事件自动执行
• 可添加批量数计算逻辑：如果生产计划包含生产数量列，将零件数量乘以生产批量后写入装箱单
• 新增重复生成时的覆盖确认提示，避免误操作覆盖已有数据

内容的提问来源于stack exchange，提问作者Emerald Coast Music