如何让Google Apps Script的getValues()等待动态加载避免REF错误？

问题核心是动态函数（如IMPORTRANGE、QUERY等）需要时间完成计算，脚本执行时直接调用getDataRange().getValues()会拿到未加载完成的结果。以下是几种可行的解决思路：

1. 循环检查关键单元格状态，等待加载完成 #

写一个专用的等待函数，通过检查包含动态函数的关键单元格，确认工作表加载完成后再执行取值操作。

示例代码： #

// 等待工作表加载完成的辅助函数
function waitForSheetLoad(sheet, checkCellAddress, timeoutSeconds = 30) {
  const startTime = Date.now();
  const timeoutMs = timeoutSeconds * 1000;
  
  while (Date.now() - startTime < timeoutMs) {
    // 强制刷新所有待处理的计算
    SpreadsheetApp.flush();
    const cellValue = sheet.getRange(checkCellAddress).getValue();
    
    // 根据实际情况调整判断条件：排除REF错误和空白
    if (cellValue !== '#REF!' && cellValue !== '') {
      return true;
    }
    
    // 每2秒检查一次，避免频繁请求
    Utilities.sleep(2000);
  }
  
  // 超时返回失败
  Logger.log(`等待加载超时（${timeoutSeconds}秒）`);
  return false;
}

// 主复制函数
function copySheetData() {
  // 替换为你的源表和目标表ID、工作表名称
  const sourceSS = SpreadsheetApp.openById('你的源表ID');
  const sourceSheet = sourceSS.getSheetByName('源工作表');
  
  // 选择一个包含动态函数的关键单元格（比如A1）作为加载状态判断依据
  const loadSuccess = waitForSheetLoad(sourceSheet, 'A1');
  if (!loadSuccess) return;
  
  // 加载完成后获取数据
  const data = sourceSheet.getDataRange().getValues();
  
  // 写入目标表
  const targetSS = SpreadsheetApp.openById('你的目标表ID');
  const targetSheet = targetSS.getSheetByName('目标工作表');
  targetSheet.clear();
  targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

2. 先复制工作表再等待加载，最后转成值 #

如果直接从源表取值不稳定，可以先将整个工作表复制到目标表，等待复制后的工作表加载完成，再把公式转换为静态值：

示例代码： #

function copySheetAndConvertToValues() {
  const sourceSS = SpreadsheetApp.openById('你的源表ID');
  const sourceSheet = sourceSS.getSheetByName('源工作表');
  
  // 复制工作表到目标表
  const targetSS = SpreadsheetApp.openById('你的目标表ID');
  const copiedSheet = sourceSheet.copyTo(targetSS);
  
  // 等待复制后的工作表加载完成
  if (!waitForSheetLoad(copiedSheet, 'A1')) {
    targetSS.deleteSheet(copiedSheet);
    return;
  }
  
  // 将所有公式转换为静态值
  const dataRange = copiedSheet.getDataRange();
  dataRange.setValues(dataRange.getValues());
  
  // 可选：重命名复制后的工作表
  copiedSheet.setName(`复制数据_${new Date().toLocaleString()}`);
}

注意事项： #

• 选择的checkCellAddress必须是包含动态函数的单元格，确保其状态能代表整个工作表的加载进度。
• 超时时间建议设置为30秒以内，避免触发Google Apps Script的6分钟执行时间限制。
• SpreadsheetApp.flush()会强制Sheets完成所有待处理的计算和UI更新，配合Utilities.sleep()可以有效等待动态函数加载。

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