Google Apps Script设置单元格公式后粘贴为值失效如何解决

问题原因 #

你原有代码失效的核心原因是：setFormula写入公式后，Google Sheets的公式计算是异步执行的，不等计算完成，后续的copyTo粘贴值操作就已经执行了，此时单元格还没有生成公式返回的数值，粘贴操作等同于用空值/未完成计算的内容覆盖了公式，自然没有效果。
CacheService不适用于该场景，它是用来存储脚本运行时的临时缓存数据的，和表格公式计算的等待逻辑无关。

解决方案 #

方案1：保留原有逻辑，添加强制刷新语句 #

只需要在设置公式和粘贴值之间添加SpreadsheetApp.flush()，该方法会强制脚本等待前面所有对表格的修改（包括公式计算）全部执行完成后，再运行后续代码，修改后代码如下：

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var rangeA2 = spreadsheet.getRange('A2');
  rangeA2.setFormula('=max(A3:A)+1');
  // 强制等待表格完成所有计算和更新
  SpreadsheetApp.flush();
  rangeA2.copyTo(rangeA2, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

方案2：优化逻辑，直接用脚本计算值写入（更稳定高效） #

完全不需要写入公式再粘贴值，直接在脚本中完成最大值的计算，直接把结果写入单元格即可，避免了公式计算的异步问题：

function myFunctionOptimized() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  // 提取A列从第三行开始的所有数值
  var colAValues = sheet.getRange('A3:A').getValues()
    .flat()
    .filter(item => typeof item === 'number' && !isNaN(item));
  // 计算最大值，无有效数值时默认取0
  var maxVal = colAValues.length ? Math.max(...colAValues) : 0;
  // 直接写入计算结果
  sheet.getRange('A2').setValue(maxVal + 1);
}

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