如何让Duplicate Sheet函数执行完成后再触发GetSheetName自定义函数？

问题描述 #

我通过点击绘图触发duplicateSheet函数复制模板工作表，同时在单元格A1中使用自定义函数GetSheetName获取当前表名。但复制后新工作表的A1单元格显示的是默认的“Copy of Template”，而非弹窗输入的自定义名称，尝试过添加延迟等方法均无效。

相关代码 #

var nameofSheet; // 全局变量，用于跨函数传递工作表名称

function duplicateSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var response = SpreadsheetApp.getUi().prompt("新工作表", "请输入新工作表的名称：", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() == SpreadsheetApp.getUi().Button.OK && response.getResponseText() == "") {
    // 输入为空时递归调用重新输入
    duplicateSheet();
  } else if (response.getSelectedButton() == SpreadsheetApp.getUi().Button.OK && response.getResponseText() != "") {
    nameofSheet = response.getResponseText();
    console.log("工作表名称：" + nameofSheet);

    // 创建新工作表并设置名称
    var newSheet = sheet.copyTo(sheet.getParent()).setName(nameofSheet);
    
    // 复制值和格式
    sheet.getDataRange().copyTo(newSheet.getRange(1, 1));
    
    // 复制合并单元格（原代码存在变量覆盖问题）
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var range = sheet.getRange("A1:F50");
    var mergedRanges = range.getMergedRanges();

    for (var i = 0; i < mergedRanges.length; i++) {
      var mergedRange = mergedRanges[i];
      var newMergedRange = newSheet.getRange(
        mergedRange.getRow(),
        mergedRange.getColumn(),
        mergedRange.getNumRows(),
        mergedRange.getNumColumns()
      );
      newMergedRange.merge();
      console.log(mergedRanges[i].getA1Notation());
      console.log(mergedRanges[i].getDisplayValue());
    }
  } else {
    throw("已取消创建新工作表");
  }
}

/**
 * 获取当前工作表名称
 * 
 * @customfunction
 */
function getSheetName() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  console.log("工作表名称：" + sheet.getName())
  return sheet.getName();
}

解决方案 #

问题根源 #

1. 自定义函数缓存与上下文限制：Google Sheets的自定义函数会缓存计算结果，且getActiveSheet()在脚本执行期间的上下文可能未及时切换到新创建的工作表，导致返回旧名称。
2. 代码变量覆盖：原代码中重新定义了sheet变量，导致合并单元格复制逻辑错误地引用了第一个工作表而非模板表。

修复方案 #

最可靠的方式是跳过自定义函数，直接给新工作表的A1单元格赋值，彻底避免依赖自定义函数的上下文和缓存：

在duplicateSheet函数中，创建新表并复制内容后，添加一行代码直接设置A1的值：

newSheet.getRange("A1").setValue(nameofSheet);

修改后的完整代码 #

var nameofSheet;

function duplicateSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getSheetByName("Template");

  var response = SpreadsheetApp.getUi().prompt("新工作表", "请输入新工作表的名称：", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() == SpreadsheetApp.getUi().Button.OK && response.getResponseText() == "") {
    duplicateSheet();
  } else if (response.getSelectedButton() == SpreadsheetApp.getUi().Button.OK && response.getResponseText() != "") {
    nameofSheet = response.getResponseText();
    console.log("工作表名称：" + nameofSheet);

    // 创建新工作表并命名
    var newSheet = templateSheet.copyTo(ss).setName(nameofSheet);
    
    // 复制值、格式和公式
    templateSheet.getDataRange().copyTo(newSheet.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
    
    // 直接设置A1为新表名称，替代自定义函数
    newSheet.getRange("A1").setValue(nameofSheet);
    
    // 复制合并单元格
    var mergedRanges = templateSheet.getRange("A1:F50").getMergedRanges();
    for (var i = 0; i < mergedRanges.length; i++) {
      var mergedRange = mergedRanges[i];
      newSheet.getRange(
        mergedRange.getRow(),
        mergedRange.getColumn(),
        mergedRange.getNumRows(),
        mergedRange.getNumColumns()
      ).merge();
    }
  } else {
    throw("已取消创建新工作表");
  }
}

// 如需保留自定义函数可保留，但建议用上述直接赋值的方式
/**
 * 获取当前工作表名称
 * 
 * @customfunction
 */
function getSheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

说明 #

• 直接赋值A1单元格彻底规避了自定义函数的缓存和上下文问题，是最稳定的解决方案。
• 修复了原代码中的变量覆盖问题，确保合并单元格复制逻辑正确引用模板表。
• 如果一定要保留自定义函数，可以尝试在复制完成后调用SpreadsheetApp.flush()强制刷新，但效果不如直接赋值可靠。

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