Google Sheets脚本粘贴值时出现Loading...问题求助

我写了下面这个Google Apps Script函数：

function copyValues(range) {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange(range).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

多数时候运行正常，但偶尔会粘贴出Loading...值。

问题大概率和我创建的自定义命名函数有关：

USD2INR=Summary!$K$8*usd_amount

其中Summary工作表的K8单元格公式是：

=GOOGLEFINANCE("CURRENCY:USDINR")*0.995*1

另外还有部分单元格用了类似=-GOOGLEFINANCE("VOOG") * 2.983的公式。

我猜测是GOOGLEFINANCE函数加载数据的时候，脚本刚好执行了复制粘贴操作导致的。需要解决这个问题。

我是通过宏菜单调用copyValues函数的，关联的完整脚本如下：

function copyValues(range) {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange(range).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
}

function moveRight() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(0, 1).activate();
}

function moveTo(sheet, address) {
  sheet.setCurrentCell(sheet.getRange(address)).activate()
}

function setValue(text) {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setValue(text);
}

function setFormula(address, formula) {
  var spreadsheet = SpreadsheetApp.getActive();
  moveTo(spreadsheet, address);
  spreadsheet.getCurrentCell().setFormula(formula);
}

function goToLastRow(sheet) {
  sheet.setActiveRange(sheet.getRange("A" + (sheet.getLastRow() + 1)));
}

function createAssetTimeSeries() {
  
  var spreadsheet = SpreadsheetApp.getActive();

  var timeSeriesSheet = spreadsheet.getSheetByName('Asset Timeseries');
  spreadsheet.setActiveSheet(timeSeriesSheet);

  goToLastRow(spreadsheet);
  var startCell = spreadsheet.getCurrentCell();
  var rows = startCell.getRow();

  spreadsheet.getCurrentCell().setValue(new Date()); // Set Date
  moveRight();
  
  copyValues('Summary!C18');  
  moveRight();
  copyValues('Summary!C19');  
  moveRight();
  copyValues('Summary!C20');  
  moveRight();
  copyValues('Summary!D18');  
  moveRight();
  copyValues('Summary!D19');  
  moveRight();
  copyValues('Summary!D20');  
};

解决方案 #

方法1：直接读取单元格值再写入（最稳妥）

修改copyValues函数，不再依赖copyTo，而是直接获取目标单元格的计算后数值再写入。Google Apps Script的getValue()方法会自动等待公式计算完成，不会返回Loading...状态。

修改后的函数：

function copyValues(range) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourceValue = spreadsheet.getRange(range).getValue();
  spreadsheet.getActiveRange().setValue(sourceValue);
}

方法2：批量获取值后一次性写入（更高效）

针对createAssetTimeSeries里的批量复制场景，可以改成一次性获取所有需要的单元格值，再批量写入目标行，减少多次操作的延迟，彻底规避加载时机问题。

修改后的createAssetTimeSeries函数：

function createAssetTimeSeries() {
  var spreadsheet = SpreadsheetApp.getActive();
  var timeSeriesSheet = spreadsheet.getSheetByName('Asset Timeseries');
  spreadsheet.setActiveSheet(timeSeriesSheet);

  goToLastRow(spreadsheet);
  var targetRow = spreadsheet.getCurrentCell().getRow();

  // 批量获取所有需要的单元格值
  var sourceAddresses = [
    'Summary!C18', 'Summary!C19', 'Summary!C20',
    'Summary!D18', 'Summary!D19', 'Summary!D20'
  ];
  var values = sourceAddresses.map(addr => spreadsheet.getRange(addr).getValue());

  // 写入日期和所有数据
  timeSeriesSheet.getRange(targetRow, 1).setValue(new Date());
  timeSeriesSheet.getRange(targetRow, 2, 1, values.length).setValues([values]);
}

这种方法不需要再调用moveRight和copyValues，执行效率更高，也避免了频繁切换单元格的操作。

方法3：添加等待机制（兼容原有逻辑）

如果要保留copyTo的逻辑，可以添加循环等待，直到目标单元格不再显示Loading...再执行复制：

修改后的copyValues函数：

function copyValues(range) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourceRange = spreadsheet.getRange(range);
  
  // 最多等待10秒，避免无限等待
  var maxWaitSeconds = 10;
  var waitCount = 0;
  
  while (sourceRange.getValue() === 'Loading...' && waitCount < maxWaitSeconds) {
    Utilities.sleep(1000); // 每次等待1秒
    waitCount++;
    sourceRange = spreadsheet.getRange(range); // 刷新单元格值
  }
  
  sourceRange.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

注意：这种方法可能会增加脚本执行时间，若GOOGLEFINANCE加载超时，仍可能复制Loading...，建议优先选择前两种方法。

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