求编写Google Sheets AppScript：新增行时自动执行VLOOKUP并转值

核心思路 #

通过可安装触发器监听工作表的行插入或编辑事件，当API填充A-D列的新行后，自动为E、F列设置VLOOKUP公式，计算完成后将公式结果转为静态值，避免后续操作覆盖公式，确保消息推送流程能正常触发。

完整代码实现 #

function autoFillVlookupOnNewRow(e) {
  // 替换为你的主工作表（存储A-D列数据）和数据工作表（Sheet2）名称
  const MAIN_SHEET_NAME = "Sheet1";
  const DATA_SHEET_NAME = "Sheet2";

  // 仅处理行插入或A-D列的编辑事件
  if (!e || (e.changeType !== "INSERT_ROW" && e.changeType !== "EDIT")) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName(MAIN_SHEET_NAME);
  const dataSheet = ss.getSheetByName(DATA_SHEET_NAME);
  
  // 检查工作表是否存在，避免报错
  if (!mainSheet || !dataSheet) return;

  // 获取Sheet2的完整数据范围，用于VLOOKUP查找
  const dataRange = dataSheet.getDataRange();
  
  // 确定目标行：插入行时取最后一行；编辑时取当前编辑行
  let targetRow;
  if (e.changeType === "INSERT_ROW") {
    targetRow = mainSheet.getLastRow();
  } else {
    const editedRange = e.range;
    targetRow = editedRange.getRow();
    // 仅处理A-D列的编辑，且当前行A列不为空（确保是已填充的有效新行）
    const editedCol = editedRange.getColumn();
    if (editedCol < 1 || editedCol > 4 || mainSheet.getRange(targetRow, 1).getValue() === "") return;
  }

  // 跳过表头行（假设表头在第1行，若表头行数不同请修改此值）
  if (targetRow <= 1) return;

  // 构造E、F列的VLOOKUP公式
  const lookupValue = `B${targetRow}&C${targetRow}`;
  const eColumnFormula = `=VLOOKUP(${lookupValue}, ${dataRange.getA1Notation()}, 10, FALSE)`;
  const fColumnFormula = `=VLOOKUP(${lookupValue}, ${dataRange.getA1Notation()}, 20, FALSE)`;

  // 为E、F列设置公式
  mainSheet.getRange(targetRow, 5).setFormula(eColumnFormula);
  mainSheet.getRange(targetRow, 6).setFormula(fColumnFormula);

  // 强制刷新工作表，确保公式计算完成
  SpreadsheetApp.flush();

  // 将E、F列的公式结果转为静态值
  const efRange = mainSheet.getRange(targetRow, 5, 1, 2);
  efRange.setValues(efRange.getValues());
}

安装与配置步骤 #

1. 打开你的Google Sheets，点击顶部菜单栏的「扩展程序」→「Apps Script」；
2. 清空默认代码，粘贴上面的脚本；
3. 修改脚本开头的MAIN_SHEET_NAME和DATA_SHEET_NAME为你实际的工作表名称；
4. 点击左侧的「触发器」图标（时钟样式），选择「添加触发器」：
   • 选择要运行的函数：autoFillVlookupOnNewRow
   • 选择事件源：「电子表格」
   • 选择事件类型：「更改」
5. 保存触发器，按提示完成授权（需允许脚本访问你的表格数据）。

关键注意事项 #

• 确保Sheet2的第一列是「鞋款型号+编号」的组合值（即B&C的内容），否则VLOOKUP无法匹配到数据；
• 若你的表头不在第1行，修改脚本中if (targetRow <= 1) return;的判断值；
• 可安装触发器会自动监听API的批量行插入操作，无需手动触发。

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