如何在App Script中设置支持工作表内手动编辑的公式

核心问题 #

需要通过App Script批量填充公式（典型场景为百分比列表配合INDEX+MATCH匹配标准值），但部分场景存在自动计算值和实际值不符的情况，需要支持手动录入调整内容，且手动调整的内容不能被脚本后续运行覆盖。

可落地方案 #

方案1：脚本写入逻辑加空值判断（最小改动方案） #

脚本批量写入公式前，先遍历目标范围的所有单元格，仅对完全为空的单元格写入公式，已有内容（不管是手动输入的值还是之前留存的公式）一律跳过，从根源上避免覆盖手动调整的内容。
参考代码如下，替换对应工作表名、范围、公式即可直接使用：

function fillFormulasWithoutOverwrite() {
  // 替换为你自己的工作表名称
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("数据页");
  // 替换为你需要填充公式的目标范围
  const targetRange = sheet.getRange("D2:D200");
  const cellValues = targetRange.getValues();
  const cellFormulas = targetRange.getFormulas();

  // 遍历单元格，仅空单元格写入公式
  for (let row = 0; row < cellValues.length; row++) {
    for (let col = 0; col < cellValues[row].length; col++) {
      if (cellValues[row][col] === "" && cellFormulas[row][col] === "") {
        // 替换为你需要写入的公式，注意动态拼接行号
        const currentRowNum = row + 2; // 范围从第2行开始，所以起始行号是2
        cellFormulas[row][col] = `=INDEX(标准值!$B$2:$B$50,MATCH(B${currentRowNum},标准值!$A$2:$A$50,0))`;
      }
    }
  }
  targetRange.setFormulas(cellFormulas);
}

注意：绝对不要直接对整列/大范围直接调用setFormula()或setValues()做全量覆盖，这类操作会直接清空范围内所有手动修改的内容。

方案2：列拆分隔离自动值和手动值（最稳妥零冲突方案） #

通过列拆分把自动计算和手动调整的内容完全隔离，不存在覆盖风险：

• 预留单独的「自动计算列」：这一列由脚本批量写入公式，日常不需要手动编辑，脚本每次运行可以全量刷新这一列的公式，不会影响其他内容
• 预留单独的「手动调整列」：平时保持为空，遇到自动计算值不符合实际场景的情况，直接在这一列录入调整后的正确值
• 新增「最终取值列」：写入固定判断公式=IF(手动调整列对应行<>"",手动调整列对应行,自动计算列对应行)，后续所有数据统计、跨表引用都取这一列的结果即可
  这种方案逻辑完全透明，哪怕后续脚本逻辑调整、协作者操作，也不会出现手动调整内容丢失的问题。

方案3：配合范围保护降低误操作概率 #

如果担心协作者误改自动计算列的公式，可以给自动计算列加轻量编辑保护，仅给脚本所属账号开放编辑权限；允许手动编辑的列不设保护，协作者可以随时录入调整值，配合方案1的空值写入逻辑，使用体验和直接在公式列修改没有区别。

内容的提问来源于stack exchange，提问作者A.S.