Apps Script自动写入表格公式时出现formula parse error报错

问题描述 #

• 开发了指定行数据插入自定义组件：用户通过Modal Dialog弹窗输入目标行号、选择待添加数据后提交写入
• 业务规则：所选数据需通过公式关联BDD工作表的对应关联值，插入行后自动在对应行B列写入匹配查找公式
• 异常表现：提交后A列数据可正常写入指定行，但B列自动写入的公式提示formula parse error错误；写入的公式语法和表格内手动输入、正常运行的公式完全一致，手动编辑该公式（任意修改内容后改回原公式）即可正常计算，无法直接定位问题根源

原有实现代码 #

服务端Apps Script代码 #

function include(filename){
   return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function widget() {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getActiveSheet();
  const ui = SpreadsheetApp.getUi();
  var widget;
  widget = HtmlService.createTemplateFromFile("widget.html").evaluate();
  ui.showModalDialog(widget, "Add new Row");
  widget.setWidth(600);
  widget.setHeight(600);
}

function getData(){
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName("BDD");
  var services = feuille.getRange("A2:A").getValues().filter(d =>d[0] !== "");
  return services.map(d => "<option>" + d[0] + "</option>").join("")
}

function ajoutLigne(row,data) {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getActiveSheet();
  index = feuille.getActiveRange().getRow();
  feuille.insertRowBefore(row);
  feuille.getRange("A"+row).setValue(data);
  feuille.getRange("B"+row).setValue("=RECHERCHEV(A"+row+";BDD!A:B;2;FAUX)");
}

组件前端widget.html代码 #

<!DOCTYPE html>
 <html>
   <head>
     <base target="_top">
     <?!= include('JavaScript'); ?>
   </head>
   <body>
     <p>Row : <input type="text" id="row"></p>
     <br>
     <p><i>Select data :</i>
       <select id="data" name="data" class="form-control" required>
         <option disabled selected>Choose ...</option>
         <?!=getData()?>
       </select>
     </p>

     <input type="button" class="button" value="SUBMIT" onclick="addRow();">
     <input type="button" class="button" value="CLOSE" onclick="google.script.host.close();">

   </body>
 </html>

前端交互JavaScript代码 #

<script>
   function addRow(){
     var row = document.getElementById("row").value;
     var data = document.getElementById("data").value;
     google.script.run.ajoutLigne(row,data);
   }
</script>

问题根因 #

Google Apps Script通过Range.setValue()写入公式时，仅识别英文函数名+英文逗号作为参数分隔符的标准公式语法，不识别本地化（如法语环境）的函数名、分号参数分隔符。
手动在表格界面输入公式时，Google Sheets会自动适配当前表格的区域设置，支持本地化函数和分隔符；手动修改脚本写入的异常公式时，相当于触发了界面层的公式解析转换，因此改回原内容也能正常运行。

修复方案 #

1. 将脚本中写入公式的代码替换为标准英文语法版本，Google Sheets会自动根据表格区域设置转换为界面对应的本地化显示形式：

// 原错误写法
// feuille.getRange("B"+row).setValue("=RECHERCHEV(A"+row+";BDD!A:B;2;FAUX)");
// 修复后写法
feuille.getRange("B"+row).setValue("=VLOOKUP(A"+row+",BDD!A:B,2,FALSE)");

2. 额外优化：前端传入的行号为字符串类型，写入前建议转为数字避免类型异常；删除代码中定义后未使用的index冗余变量，优化后的ajoutLigne函数如下：

function ajoutLigne(row,data) {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getActiveSheet();
  const targetRow = Number(row);
  feuille.insertRowBefore(targetRow);
  feuille.getRange("A"+targetRow).setValue(data);
  feuille.getRange("B"+targetRow).setValue("=VLOOKUP(A"+targetRow+",BDD!A:B,2,FALSE)");
}

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