Google Sheets表单提交后公式行锁定失效求助：FORMULATEXT适配问题

方案1：自定义函数替代FORMULATEXT，配合ARRAYFORMULA批量处理 #

原生FORMULATEXT不支持数组运算，既没法批量处理，新行插入时还容易出现引用偏移。可以写个自定义函数实现批量提取公式/值的功能：

1. 创建自定义函数 #

打开Google Sheets的「工具」→「脚本编辑器」，粘贴以下代码后保存：

function GETFORMULA(range) {
  if (range.map) {
    return range.map(row => row.map(cell => cell.getFormula() || cell.getValue()));
  } else {
    return range.getFormula() || range.getValue();
  }
}

这个函数优先返回单元格的原公式，没有公式就返回单元格的值，同时支持数组批量处理。

2. 批量转换公式 #

在目标工作表的首行（比如A1）输入以下公式，无需手动下拉，新表单提交的行会自动处理：

=ARRAYFORMULA(IF(ROW(answers!N:N)=1, "转换结果", 
  IF(ISTEXT(answers!N:N), answers!N:N, 
    TO_TEXT(IF(ISNUMBER(answers!N:N), answers!N:N, 
      IFERROR(GETFORMULA(answers!N:N), answers!N:N)
    ))
  )
))

公式逻辑和你原来的一致，但通过自定义函数实现了数组批量处理，直接引用整列的方式也能避免新行插入时的引用偏移。

方案2：用INDIRECT+CELL函数实现无脚本批量处理 #

如果不想用脚本，可以用CELL("formula")提取公式，结合INDIRECT锁定每一行的引用：

=ARRAYFORMULA(IF(ROW(A:A)=1, "转换结果", 
  LET(current_row, ROW(A:A)-1,
    target_cell, "answers!N"&current_row,
    cell_content, INDIRECT(target_cell),
    IF(ISTEXT(cell_content), cell_content,
      TO_TEXT(IF(ISNUMBER(cell_content), cell_content,
        IFERROR(REGEXEXTRACT(CELL("formula", INDIRECT(target_cell)), "=(.*)"), cell_content)
      ))
    )
  )
))

这里用ROW(A:A)-1对应表单响应表的行号，INDIRECT会精准定位到每一行的单元格，新行插入时自动匹配，不会偏移。REGEXEXTRACT用来去掉公式前面的等号，还原+5 months这类原始内容。

额外建议：从源头减少错误触发 #

如果想降低#ERROR!的出现概率，可以在表单响应表的N列设置数据验证：

• 选中N列，打开「数据」→「数据验证」
• 允许类型选「文本」，添加输入提示告知用户避免输入带前置运算符的内容（比如+5 months），或者直接要求输入5 months这类格式，从根源避免错误触发。

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