如何在Google Forms提交新条目时自动填充VLOOKUP公式到Google Sheets？

我需要实现：每次通过Google Forms提交新条目后，Google Sheets对应行的B列自动填充公式=VLOOKUP(K:K,Lookup!C:F,4,true)。

已尝试的方法及问题 #

• 直接在表单预填公式：Forms会自动在=前添加'，导致公式变为文本，无法生效。
• 尝试以下三个脚本，均未解决问题：

脚本1：onEdit触发器 #

function onEdit(e) {
  var editedRange = e.range;
  var sheet = editedRange.getSheet();
  
  // Check if the edited range is in column A
  if (editedRange.getColumn() === 1) {
    var row = editedRange.getRow();
    var value = editedRange.getValue();
    var formulaRange = sheet.getRange(row, 2);
    
    // Check if the corresponding cell in column A is not empty
    if (value !== '') {
      var currentFormula = formulaRange.getFormula();
      var targetFormula = '=VLOOKUP(K:K,Lookup!C:F,4,true)';
      
      // Set the formula in the formula range if it is not already set
      if (currentFormula !== targetFormula) {
        formulaRange.setFormula(targetFormula);
      }
    } else {
      // Clear the formula in the formula range
      formulaRange.clearContent();
    }
  }
}

问题：手动在A列输入内容时，B列能正常填充公式，但通过Forms提交时完全无效。

脚本2：onFormSubmit触发器 #

function onFormSubmit(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var value = e.values[0]; // Assuming the form response is in the first column (column A)
  var formulaRange = sheet.getRange(row, 2); // Corresponding cell in column B
  
  // Check if the corresponding cell in column A is not empty
  if (value !== '') {
    var currentFormula = formulaRange.getFormula();
    var targetFormula = '=VLOOKUP(K:K,Lookup!C:F,4,true)';
    
    // Set the formula in the formula range if it is not already set
    if (currentFormula !== targetFormula) {
      formulaRange.setFormula(targetFormula);
    }
  } else {
    // Clear the formula in the formula range
    formulaRange.clearContent();
  }
}

问题：无法正常工作，表单提交后B列无任何变化。

脚本3：遍历表单响应 #

function autoFillFormula() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();

  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];
    var itemResponses = formResponse.getItemResponses();

    // Assuming the timestamp is in the first column (column A)
    var timestamp = formResponse.getTimestamp().getTime();

    // Find the corresponding row based on the timestamp in column A
    var row = sheet.createTextFinder(String(timestamp)).findNext().getRow();
    var formulaRange = sheet.getRange(row, 2); // Corresponding cell in column B

    // Check if the cell in column B is empty
    if (formulaRange.getValue() === '') {
      formulaRange.setFormula('=VLOOKUP(K:K,Lookup!C:F,4,true)');
    }
  }
}

问题：无法正常工作，运行后B列没有填充公式。

我不擅长编程，之前用ChatGPT生成的这些脚本都没能解决问题，希望能得到有效的解决方案。

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