Google Sheets批量更新：如何同时保留单元格值与公式？

这个问题我之前帮不少开发者处理过，核心痛点就是getValues()只能获取单元格的计算结果，会丢失原有的公式；而要同时保留公式和值，我们可以结合getFormulas()和getValues()两个方法来实现批量读写的高效操作，具体步骤如下：

1. 批量读取公式与值 #

首先，一次性获取目标数据范围的公式数组和值数组：

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的工作表名称");
const targetRange = sheet.getDataRange(); // 或者指定具体范围如getRange("A1:Z100")

// 批量读取所有单元格的公式（非公式单元格返回空字符串）
const formulasArr = targetRange.getFormulas();
// 批量读取所有单元格的计算后的值
const valuesArr = targetRange.getValues();

2. 数组内容处理 #

遍历数组，根据你的外部条件决定每个单元格的处理逻辑：

• 如果单元格原本是公式（formulasArr[row][col]不为空），可以选择保留原公式，或者根据条件修改公式字符串；
• 如果单元格是普通值，则根据条件更新值。

举个示例场景：把所有值为100的普通单元格更新为200，同时保留所有原公式单元格：

for (let rowIndex = 0; rowIndex < formulasArr.length; rowIndex++) {
  for (let colIndex = 0; colIndex < formulasArr[rowIndex].length; colIndex++) {
    const currentFormula = formulasArr[rowIndex][colIndex];
    
    // 处理公式单元格：这里选择保留原公式，你也可以根据条件修改
    if (currentFormula !== "") {
      continue;
    }

    // 处理普通值单元格：按条件更新
    if (valuesArr[rowIndex][colIndex] === 100) {
      // 直接把新值赋值到formulas数组（setFormulas会自动识别为普通值）
      formulasArr[rowIndex][colIndex] = 200;
    }
  }
}

如果需要修改某些公式，比如把所有=SUM(A1:B1)替换为=AVERAGE(A1:B1)，可以在遍历中加入判断：

if (currentFormula === "=SUM(A1:B1)") {
  formulasArr[rowIndex][colIndex] = "=AVERAGE(A1:B1)";
}

3. 一次性写入处理后的数组 #

最后用setFormulas()方法批量写入，这个方法会自动识别内容：

• 如果是带=开头的字符串，会设置为单元格公式；
• 如果是普通数字、字符串等，会直接设置为单元格值。

代码如下：

targetRange.setFormulas(formulasArr);

为什么这个方案高效？ #

虽然我们做了两次批量读取（getFormulas()和getValues()），但这都是单次API调用操作，再加上一次批量写入，总调用次数远少于逐个单元格读写，完全符合你“最小化API调用、最大化效率”的需求。

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