如何用Google Apps Script限制Google表单输入不超库存

首先明确：要在用户提交前就拦住超量申请，必须给数量输入字段动态绑定基于实时库存的验证规则——而且完全可以给已有的字段加，不用新建。

第一步：定位表单目标字段 #

假设你的表单结构是：

• 一个单选下拉选择题，让用户选择申请产品（对应库存表的产品名称）
• 一个文本输入框，让用户填写申请数量

第二步：编写动态验证代码 #

结合你已经写好的getStockMap()，用以下函数给现有数量字段添加验证：

/**
 * 给Google表单的数量输入字段设置库存限制验证
 */
function setupQuantityValidation() {
  // 替换成你的表单ID（可在表单URL中找到）
  const formId = "你的表单ID";
  const form = FormApp.openById(formId);
  
  // 替换成你表单里产品选择、数量输入的字段标题（需完全匹配）
  const productFieldTitle = "请选择申请产品";
  const quantityFieldTitle = "申请数量";
  
  // 遍历所有表单字段，找到目标项
  let productItem, quantityItem;
  form.getItems().forEach(item => {
    if (item.getTitle() === productFieldTitle) {
      productItem = item.asMultipleChoiceItem(); // 单选下拉用这个，复选框改asCheckboxItem
    }
    if (item.getTitle() === quantityFieldTitle) {
      quantityItem = item.asTextItem(); // 数量为文本输入框时用这个
    }
  });
  
  if (!productItem || !quantityItem) {
    Logger.log("未找到目标字段，请检查字段标题是否正确");
    return;
  }
  
  // 调用你的函数获取实时库存数据
  const stockMap = getStockMap();
  
  // 将库存Map转换为公式可用的二维数组
  const stockArray = Array.from(stockMap.entries());
  
  // 构建验证规则：必须是正整数，且不超过所选产品的库存
  const validationRule = FormApp.createTextValidation()
    .setHelpText("请输入1到对应产品库存之间的正整数")
    .requireNumber()
    .requireMinimum(1)
    // 用自定义公式关联产品选择与库存数据
    .requireFormulaSatisfied(`=(${quantityItem.getId()} <= VLOOKUP(${productItem.getId()}, ${JSON.stringify(stockArray)}, 2, FALSE))`)
    .build();
  
  // 给已有的数量字段设置验证（核心操作）
  quantityItem.setValidation(validationRule);
  
  Logger.log("库存验证规则已更新");
}

第三步：定时更新验证规则 #

因为库存会动态变化，需要设置定时触发器保证验证规则同步最新库存：

1. 打开Google Apps Script编辑器，点击左侧「触发器」图标
2. 添加新触发器：
   • 选择函数：setupQuantityValidation
   • 事件源：「时间驱动」
   • 事件类型：根据库存更新频率选择（比如「每小时计时器」）

对你现有代码的小优化 #

调整getStockMap()跳过表头，避免无效数据：

function getStockMap() {
    const spreadsheetId = "1J5twCP4L4Smy7FP4JbARD3qVB_mv8rnQ1fRN5y6LvaQ";
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName("Stock");
    // 从第二行开始取数据，跳过B1的表头
    const range = sheet.getRange('B2:D8');
    const data = range.getValues();
    const stockMap = new Map();

    for (let i = 0; i < data.length; i++) {
        const product = data[i][0];
        const quantity = data[i][2];
        // 只存入有效的产品和库存数值
        if (product && typeof quantity === 'number') {
            stockMap.set(product, quantity);
        }
    }

    for (const [product, quantity] of stockMap) {
        Logger.log(`${product}: ${quantity}`);
    }

    return stockMap;
}

注意事项 #

• 如果产品选择是复选框（允许多选），需要调整验证逻辑，因为VLOOKUP仅支持单值匹配
• 该验证规则会在用户输入时实时生效，不符合要求的表单无法提交，彻底避免了OnSubmit触发器事后删除错误数据的问题

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