使用CopyTo生成日志文件时VLOOKUP出现#REF!错误的解决方法

问题原因 #

你当前的代码先复制Log和Index工作表到目标文件，再执行重命名操作。复制过程中，Log表内的VLOOKUP公式会自动引用临时生成的Copy of Index工作表，重命名后公式不会自动更新引用路径，导致系统找不到Index表，触发#REF!错误。手动回车是强制触发公式重新解析，所以能修复问题，但需要实现自动化处理。

方案一：调整复制顺序（最简方案） #

先复制Index工作表到目标文件并立即重命名为Index，再复制Log工作表。此时Log表的VLOOKUP会直接引用已存在的Index表，从根源避免临时名称引发的引用错误：

var ss = SpreadsheetApp.openById('');
var sheet = ss.getSheetByName('Log');
var index = ss.getSheetByName("Index");

// 先复制Index工作表并完成重命名
var copiedIndex = index.copyTo(log_file);
copiedIndex.setName("Index");

// 再复制Log工作表并完成重命名
var copiedLog = sheet.copyTo(log_file);
copiedLog.setName("Log");

// 删除默认Sheet1
log_file.deleteSheet(log_file.getSheetByName('Sheet1'));

方案二：批量替换公式中的引用（适配无法调整顺序的场景） #

如果必须保持原复制顺序，可在重命名后遍历Log表的所有公式单元格，手动替换引用的临时工作表名称：

var ss = SpreadsheetApp.openById('');
var sheet = ss.getSheetByName('Log');
var index = ss.getSheetByName("Index");

// 复制两个工作表到目标文件
sheet.copyTo(log_file);
index.copyTo(log_file);

// 删除默认Sheet1
log_file.deleteSheet(log_file.getSheetByName('Sheet1'));

// 重命名工作表
var logSheet = log_file.getSheetByName("Copy of Log");
logSheet.setName("Log");
log_file.getSheetByName("Copy of Index").setName("Index");

// 遍历Log表所有单元格，替换公式中的临时引用
var range = logSheet.getDataRange();
var formulas = range.getFormulas();
for (var i = 0; i < formulas.length; i++) {
  for (var j = 0; j < formulas[i].length; j++) {
    if (formulas[i][j]) {
      // 同时处理带单引号和不带单引号的引用格式
      var newFormula = formulas[i][j]
        .replace(/'Copy of Index'/g, "'Index'")
        .replace(/Copy of Index/g, "Index");
      logSheet.getRange(i+1, j+1).setFormula(newFormula);
    }
  }
}

// 强制触发数据刷新
SpreadsheetApp.flush();

补充说明 #

• 方案一利用了Google Sheets的引用解析逻辑：复制带跨表引用的工作表时，会优先匹配目标文件中已存在的同名工作表，彻底避免临时名称问题。
• 方案二通过批量替换公式文本实现引用更新，覆盖了公式中带单引号（如工作表名称含特殊字符）和不带单引号的两种引用格式。

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