为Google Sheets Apps Script添加适配新增行的自动VLOOKUP功能

实现思路 #

借助Google Apps Script的onEdit简单触发器，监听Sheet1中A列的编辑事件（包括新增行后在A列输入内容、现有A列单元格更新），自动为对应行的目标列插入VLOOKUP公式。同时保留原有自定义菜单功能，覆盖两种添加行的场景。

完整脚本代码 #

function onOpen() {
  SpreadsheetApp.getUi() 
     .createMenu('custom menu')
     .addItem('Add rows','duplicateLastRow')
     .addSeparator()
     .addItem('Help','showSidebar')
     .addToUi();
}

function duplicateLastRow(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  ss.insertRowsAfter(ss.getLastRow(),10);
  ss.getRange(ss.getLastRow(),1,1,ss.getLastColumn()).copyTo(ss.getRange(ss.getLastRow()+1,1,10));
}

// 监听A列编辑，自动添加VLOOKUP公式
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  // 仅处理Sheet1的A列（列索引为1）
  if (sheet.getName() !== "Sheet1" || e.range.getColumn() !== 1) return;
  
  var targetCol = 3; // 假设VLOOKUP公式放在C列，可根据实际需求修改列索引
  var row = e.range.getRow();
  
  // 根据A列内容动态设置公式，空值则清空目标列
  var formula = e.value > 0 ? `=if(A${row} > 0,VLOOKUP(A${row},Sheet2!A$2:G,3,false),"")` : "";
  sheet.getRange(row, targetCol).setFormula(formula);
}

关键逻辑说明 #

1. onEdit触发器：这是Google Sheets内置的简单触发器，无需手动授权，表格有编辑操作时自动触发。
2. 范围过滤：先判断当前编辑的是Sheet1的A列，避免干扰其他工作表或列的操作。
3. 动态公式生成：根据编辑行的行号生成对应公式，确保引用当前行的A列单元格（如A2、A3）。
4. 空值兼容：若A列单元格被清空，目标列公式同步清空，保持表格整洁。

可选批量补全优化 #

如果需要为已有行一次性补全公式，可添加以下函数并更新自定义菜单：

function fillAllVLOOKUPFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getLastRow();
  var targetCol = 3;
  
  // 遍历A列第2行到最后一行（假设第1行是表头）
  for (var row = 2; row <= lastRow; row++) {
    var aValue = ss.getRange(row, 1).getValue();
    var formula = aValue > 0 ? `=if(A${row} > 0,VLOOKUP(A${row},Sheet2!A$2:G,3,false),"")` : "";
    ss.getRange(row, targetCol).setFormula(formula);
  }
}

// 更新onOpen函数，添加批量处理菜单
function onOpen() {
  SpreadsheetApp.getUi() 
     .createMenu('custom menu')
     .addItem('Add rows','duplicateLastRow')
     .addSeparator()
     .addItem('批量补全公式','fillAllVLOOKUPFormulas')
     .addSeparator()
     .addItem('Help','showSidebar')
     .addToUi();
}

内容的提问来源于stack exchange，提问作者Russell Parrott