如何在运行脚本前强制刷新单元格以获取最新数值

问题根因 #

出现该问题的核心原因是：用户点击按钮触发脚本时，处于编辑状态的单元格输入内容仅保存在界面输入缓存中，还未提交到表格底层数据存储，因此脚本读取到的是上一次已提交的旧值。

可行解决方案 #

针对Excel VBA环境 #

• 在你的脚本所有逻辑最开头添加一行代码：SendKeys "{ENTER}"，这行代码会模拟按下回车键确认当前单元格输入，强制把缓存中的最新值同步到表格存储，后续读取的数值就会是最新输入的内容。
• 如果你是Mac系统下的Excel，替换为SendKeys Return即可生效。

针对Google Sheets Apps Script环境 #

• 在脚本所有逻辑最开头调用SpreadsheetApp.flush()方法，该方法会强制同步所有界面未提交的单元格变更到底层数据表，之后再执行读取逻辑就不会拿到旧值。
• 如果偶发还是读取到旧值，可以在flush后加一行Utilities.sleep(100)做100毫秒的延迟兜底，基本能覆盖所有异常场景。

通用兼容方案 #

如果不确定运行环境或者上述方案不适用，可以通过主动切换单元格的方式强制退出编辑模式，逻辑如下：

1. 脚本开头先选中一个用户几乎不会用到的空白单元格，比如当前工作表的最后一行最后一列的空白位置
2. 再切回用户原来选中的区域，整个过程用户无感知，但是可以强制提交当前编辑单元格的内容

VBA示例代码：

Dim originalRange As Range
Set originalRange = Selection
' 切换到空白单元格强制提交输入
ActiveSheet.Range("XFD1048576").Activate
' 切回用户原来选中的区域
originalRange.Activate

Google Apps Script示例代码：

const sheet = SpreadsheetApp.getActiveSheet();
const originalRange = sheet.getActiveRange();
// 切换到空白单元格
sheet.getRange(sheet.getMaxRows(), sheet.getMaxColumns()).activate();
SpreadsheetApp.flush();
// 切回原选中区域
originalRange.activate();

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