如何用Google Apps Script自动生成Google Sheets生产表格

需求说明 #

现有两张表格：

• 批次表格（Batch Sheet）：记录生产批次基础信息

• 配方表格（Recipe Sheet）：记录产品原料配比

需要生成生产表格（Production Sheet），关联批次与配方，计算实际原料用量：

实现步骤 #

1. 打开脚本编辑器 #

打开目标Google表格，点击顶部菜单栏「扩展程序」→「Apps Script」，进入脚本编辑界面。

2. 粘贴执行代码 #

function generateProductionSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 获取三个工作表对象
  const batchSheet = ss.getSheetByName("Batch Sheet");
  const recipeSheet = ss.getSheetByName("Recipe Sheet");
  const productionSheet = ss.getSheetByName("Production Sheet");
  
  // 读取数据（跳过表头行）
  const batchData = batchSheet.getDataRange().getValues().slice(1);
  const recipeData = recipeSheet.getDataRange().getValues().slice(1);
  
  // 构建配方映射：按Product ID分组存储原料信息
  const recipeMap = {};
  recipeData.forEach(row => {
    const productId = row[1];
    const ingredientId = row[2];
    const recipeQty = parseFloat(row[3].replace(/[^\d.]/g, ""));
    
    if (!recipeMap[productId]) recipeMap[productId] = [];
    recipeMap[productId].push({ ingredient: ingredientId, qty: recipeQty });
  });
  
  // 生成生产表格数据
  const productionData = [["Batch ID", "Product ID", "Ingredient ID", "Quantity Used", "Date"]];
  
  batchData.forEach(batchRow => {
    const batchId = batchRow[0];
    const productId = batchRow[1];
    const batchQtyStr = batchRow[2];
    const date = batchRow[3];
    
    const recipes = recipeMap[productId];
    if (!recipes) return;
    
    // 提取数值和单位
    const batchQty = parseFloat(batchQtyStr.replace(/[^\d.]/g, ""));
    const unit = batchQtyStr.replace(/[\d.]/g, "").trim();
    
    // 计算并生成每条生产记录
    recipes.forEach(recipe => {
      // 示例逻辑：批次产品量与配方基准量比例为10:1，原料用量同步放大
      const usedQty = (batchQty / recipe.qty) * recipe.qty;
      const usedQtyStr = `${usedQty}${unit}`;
      productionData.push([batchId, productId, recipe.ingredient, usedQtyStr, date]);
    });
  });
  
  // 写入生产表格
  productionSheet.clearContents();
  productionSheet.getRange(1, 1, productionData.length, productionData[0].length).setValues(productionData);
  
  // 格式化表头
  const headerRange = productionSheet.getRange(1, 1, 1, productionData[0].length);
  headerRange.setFontWeight("bold").setHorizontalAlignment("center");
}

3. 代码说明 #

• 配方映射：将配方数据按Product ID分组，避免重复查找，提升效率。
• 用量计算：提取数值部分计算比例，示例中批次产品量是配方基准的10倍，因此原料用量同步放大；若配方为「每单位产品的原料用量」，可将计算逻辑改为batchQty × recipe.qty。
• 数据写入：清空生产表格原有内容后写入新数据，同时格式化表头增强可读性。

4. 运行脚本 #

点击脚本编辑器的「运行」按钮，首次运行需完成权限授权，按照提示操作即可。运行完成后切换到「Production Sheet」即可查看生成的生产表格。

注意事项 #

• 确保三个工作表名称与代码中完全一致（"Batch Sheet"、"Recipe Sheet"、"Production Sheet"）。
• 批次和配方的用量单位需统一，否则计算结果会出错。
• 若一个产品对应多个原料，代码会自动生成多条生产记录。

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