调用SpreadsheetApp.flush()后Google Sheets隐藏工作表仍未更新？

问题根源 #

Google Sheets对隐藏工作表的公式计算有默认资源优化——隐藏工作表的公式不会像可见工作表那样实时触发重新计算。即使调用SpreadsheetApp.flush()，也可能仅同步可见工作表的更改，无法强制刷新隐藏工作表的公式缓存。再加上你使用的OFFSET属于易失性函数，在隐藏状态下更易出现缓存未更新的情况。

可行解决方案 #

• 临时切换工作表可见性
  删除DocketsToCreate首行后，先临时显示DocketSheet，触发计算后再重新隐藏，确保flush()能同步公式更新：

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const docketsToCreate = ss.getSheetByName("DocketsToCreate");
  const docketSheet = ss.getSheetByName("DocketSheet");
  
  // 删除首行（假设第1行是表头，数据从第2行开始）
  docketsToCreate.deleteRow(2);
  
  // 临时显示工作表触发计算，再重新隐藏
  docketSheet.showSheet();
  SpreadsheetApp.flush();
  docketSheet.hideSheet();
  

• 强制触发公式重新计算
  通过修改DocketSheet内的任意非关键单元格，强制触发公式重算后再执行flush()：

  const triggerCell = docketSheet.getRange("Z1"); // 选择不影响送货单内容的单元格
  const originalValue = triggerCell.getValue();
  triggerCell.setValue(originalValue + 1);
  SpreadsheetApp.flush();
  triggerCell.setValue(originalValue);
  SpreadsheetApp.flush();
  

• 替换OFFSET为非易失性函数
  OFFSET的易失性特性是缓存问题的诱因之一，可改用INDEX这类非易失性函数替代：
  原公式（引用A列首行数据）：=OFFSET(DocketsToCreate!A$2,0,0)
  替换为：=INDEX(DocketsToCreate!A:A,2)（若数据从第2行开始）
  动态获取首行数据的通用写法：=INDEX(DocketsToCreate!A:A,COUNTA(DocketsToCreate!A:A)-COUNTA(DocketsToCreate!A1:A1)+1)

• 严格控制脚本执行顺序
  确保删除行、触发计算、flush()三个步骤按顺序执行，且在生成下一份PDF前，给系统足够的计算同步时间，避免循环中连续操作导致的计算延迟。

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