Apps Script中setFormula()小数符号逗号变点的问题咨询

云原生阿岚

2026-7-16

问题背景

我用Apps Script从CSV文件生成不同数据集，设有存储元数据的「主表」，可筛选待分析特征，还能粘贴特定参数的阈值公式。流程如下：

在工作表选择特征/条件并运行脚本；
脚本遍历特征数据库，筛选出相关CSV文件；
提取所选参数值并粘贴到新电子表格，新表首为含特征和阈值公式的主表，为每个参数新建工作表，左侧列存CSV文件ID，同行对应参数数据；
脚本在ID与数据间插入新列，粘贴对应参数的阈值公式；
部分公式含{a}这类占位符，粘贴前会替换为单元格引用，再用setFormula()写入。

问题

整体运行正常，但部分静态阈值（如0,4）并非公式，主表中显示的是0,4，经脚本处理后变成=0.4（小数点替代逗号）。由于使用瑞典区域设置（Locale），表格无法解析这个公式。调试发现，用getRange().getValues()读取值时，逗号就已经变成点；但读取公式时，逗号能被正确解析。

疑问

为什么会出现这种情况？有没有更简便的方法获取带正确小数符号的输出？

阈值公式导入代码

var ssNewId = Drive.Files.insert(resource);
var ssNew = SpreadsheetApp.openById(ssNewId.id);
var threshholdFormulas = ss.getSheetByName("Acceptance levels").getRange("A1:E19").getValues(); // 就是这行读取时，逗号变成了点号
var thresholdstoPush = [];
for (var i = 0; i < parameters.length; i++) {
  var index = parameters[i][1]+1;
  if (index >= 0 && index < threshholdFormulas.length) {
    thresholdstoPush.push(threshholdFormulas[index]);
  }
}
const thObj = arrayToNestedObject(threshholdFormulas);

function copyAndReplaceFormulas(thObj, placeholderObj, ssNew) {
  var resultObject = thObj
  var newSheet = ssNew.getActiveSheet();

  var currentSheetName = newSheet.getName();
  var currentFormulas = resultObject[currentSheetName];

  if (!currentFormulas) {
    // 当前工作表名称与对象中的任何参数都不匹配
    Logger.log("No match");
    return;
  }

  // 获取当前参数的键数量
  var numKeys = Object.keys(currentFormulas).length;
  if (numKeys == 0){
    Logger.log("The parameter does not have any thresholds");
    return;
  }
  // 根据键的数量在新工作表上插入新列
  newSheet.insertColumnsAfter(2, numKeys);
  newSheet.insertRowBefore(1);
  var currentRange = newSheet.getRange(1, 3, 1, numKeys);
  var currentKeys = Object.keys(currentFormulas);
  currentRange.setValues([currentKeys]);

  // 创建存储占位符引用的对象
  var placeholderReferences = placeholderObj;

  // 将公式中的占位符替换为单元格引用
  var currentValues = currentRange.getValues()[0];
  for (var i = 0; i < currentKeys.length; i++) {
    var formula = currentFormulas[currentValues[i]].toString();

    // 将公式中的占位符替换为单元格引用
    for (var placeholder in placeholderReferences) {
      if (formula.indexOf(placeholder) !== -1) {
        formula = formula.replace(new RegExp(placeholder, 'g'), placeholderReferences[placeholder]);
      }
    }
    //newSheet.getRange(1,i+1).setValue(currentValues[i])
    newSheet.getRange(2, i + 3).setFormula(formula);
    var valuerange = newSheet.getRange(3, i+3, newSheet.getLastRow())
    newSheet.getRange(2,i+3).copyTo(valuerange);
  }
}

临时解决方案

我给静态值用setValue()替代setFormula()做了临时处理，虽然能用但不够优雅，还是想知道原因和更优解法。

var currentValues = currentRange.getValues()[0];
for (var i = 0; i < currentKeys.length; i++) {
  var formula = currentFormulas[currentValues[i]].toString();
  let formvar = 0;
  // 将公式中的占位符替换为单元格引用
  for (var placeholder in placeholderReferences) {
    if (formula.indexOf(placeholder) !== -1) {
      formula = formula.replace(new RegExp(placeholder, 'g'), placeholderReferences[placeholder]);
      formvar++
    }
  }

  if(formvar == 0){
    formula=formula.replace(".", ",");
    newSheet.getRange(2, i + 3).setValue(formula);
  } else {
    newSheet.getRange(2, i + 3).setFormula(formula);
  }
  var valuerange = newSheet.getRange(3, i+3, newSheet.getLastRow()-2)
  newSheet.getRange(2,i + 3).copyTo(valuerange);
}

原因解析与最优解法

原因

getValues()方法会将单元格的数值转换为JavaScript原生的Number类型，而JS中的小数统一用点号（.）作为分隔符，不管原表格的区域设置。所以即使主表中显示的是0,4（瑞典语系的小数符号），getValues()读取后会变成0.4，后续转成字符串也会保留点号。而读取公式时用的是getFormulas()，它会直接获取单元格的公式文本（包含原区域设置的符号），所以逗号能被正确保留。

最优解法

区分数值和公式，分别读取：
用getFormulas()获取单元格的公式文本，用getDisplayValues()获取单元格显示的文本（包含正确的区域小数符号），而非getValues()。这样静态阈值会以带逗号的字符串形式被读取，不会被转换为JS的Number类型。

修改读取主表的代码：

// 获取公式文本和显示值
var thresholdFormulas = ss.getSheetByName("Acceptance levels").getRange("A1:E19").getFormulas();
var thresholdDisplayValues = ss.getSheetByName("Acceptance levels").getRange("A1:E19").getDisplayValues();

// 合并数据：如果是公式则用公式文本，否则用显示值
var thresholdData = [];
for (var row = 0; row < thresholdFormulas.length; row++) {
  var currentRow = [];
  for (var col = 0; col < thresholdFormulas[row].length; col++) {
    currentRow.push(thresholdFormulas[row][col] || thresholdDisplayValues[row][col]);
  }
  thresholdData.push(currentRow);
}
// 后续用thresholdData替代原来的threshholdFormulas

使用区域兼容的公式写入方法：
对于需要写入的内容，如果是公式（包含占位符替换后的），用setFormula()；如果是静态数值，直接用setValue()写入字符串格式的带逗号数值，或者将字符串转换为符合区域设置的数值后写入。另外，也可以用setFormulaLocal()方法，它会根据目标表格的区域设置解析公式，适配性更强。

内容的提问来源于stack exchange，提问作者I Have No Idea What I am Doing

Apps Script中setFormula()小数符号逗号变点的问题咨询

原因 #

最优解法 #

原因

最优解法