求助：Google Sheets非空时将VLOOKUP公式转为纯文本

脚本代码 #

function convertVlookupToValue() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName("Main Table");
  
  // 替换成你实际用VLOOKUP的列，比如用E列就改成"E"
  const targetColumn = "D";
  const targetRange = mainSheet.getRange(`${targetColumn}2:${targetColumn}${mainSheet.getLastRow()}`);
  const formulas = targetRange.getFormulas();
  const values = targetRange.getValues();
  
  // 遍历单元格检查并转换
  for (let i = 0; i < formulas.length; i++) {
    const cellFormula = formulas[i][0];
    const cellValue = values[i][0];
    // 只处理含VLOOKUP且结果非空的单元格
    if (cellFormula.includes("VLOOKUP") && cellValue !== "") {
      const rowIndex = i + 2;
      const columnIndex = mainSheet.getRange(`${targetColumn}1`).getColumn();
      mainSheet.getRange(rowIndex, columnIndex).setValue(cellValue);
    }
  }
}

// 配置自动触发的触发器
function setupAutoTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 先清理旧触发器避免重复
  const existingTriggers = ScriptApp.getProjectTriggers();
  for (let trigger of existingTriggers) {
    if (trigger.getHandlerFunction() === "convertVlookupToValue") {
      ScriptApp.deleteTrigger(trigger);
    }
  }
  // 创建表格变更时触发的触发器
  ScriptApp.newTrigger("convertVlookupToValue")
    .forSpreadsheet(ss)
    .onChange()
    .create();
}

使用步骤 #

1. 打开你的Google表格，点击顶部菜单栏「扩展程序」→「Apps 脚本」进入编辑器
2. 清空默认代码，粘贴上面的脚本
3. 修改targetColumn变量为你实际用VLOOKUP的列（比如用F列就写"F"）
4. 点击编辑器顶部的运行按钮，先运行setupAutoTrigger函数，按提示完成授权（首次运行选「高级」→「转到脚本名称」即可继续）
5. 之后只要「Confirmation - GForms」新增客户确认记录，「Main Table」里对应返回绿点的VLOOKUP单元格会自动转成纯文本值

说明 #

脚本通过onChange触发器监听表格变动，一旦有新的确认记录添加，就自动检查「Main Table」中的VLOOKUP单元格，只要返回结果非空，就把公式替换成当前值，减少表格的公式计算负载。

内容的提问来源于stack exchange，提问作者Thurman Murman