Google Sheets脚本公式替换：修正数据范围定义问题

问题根源是原代码使用getDataRange()获取整个工作表范围，包含表头所在的第1行，而getFormulas()会将非公式单元格返回为空字符串，最终通过setFormulas()设置时会清空这些非公式的表头单元格。我们需要修改代码，仅处理第2行及以下的公式区域。

修改后的完整代码 #

function duplicateSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getActiveSheet();

  // 获取原工作表名称
  var originalSheetName = activeSheet.getName();

  // 复制活动工作表
  var newSheet = activeSheet.copyTo(spreadsheet);

  // 将原工作表重命名为带版本号的名称
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() === originalSheetName) {
      var versionNumber = sheets.length - 1; // 版本号为现有工作表数量-1
      
      // 检查原名称是否已包含版本号
      if (/_v\d+$/.test(originalSheetName)) {
        sheets[i].setName(originalSheetName.replace(/_v(\d+)$/, '_v' + versionNumber));
      } else {
        sheets[i].setName(originalSheetName + '_v' + versionNumber);
      }
      break;
    }
  }

  // 将新工作表重命名为原工作表名称
  newSheet.setName(originalSheetName);

  // 更新DB Export表中的公式引用
  var dbExportSheet = spreadsheet.getSheetByName('DB Export');
  if (dbExportSheet) {
    var dataRange = dbExportSheet.getDataRange();
    var totalRows = dataRange.getNumRows();
    var totalCols = dataRange.getNumColumns();

    // 仅处理第2行及以下的区域（跳过表头行）
    if (totalRows > 1) {
      // 获取第2行到最后一行的公式
      var formulas = dbExportSheet.getRange(2, 1, totalRows - 1, totalCols).getFormulas();
      
      // 遍历公式替换工作表名称
      for (var i = 0; i < formulas.length; i++) {
        for (var j = 0; j < formulas[i].length; j++) {
          formulas[i][j] = formulas[i][j].replace(originalSheetName, newSheet.getName());
        }
      }
      
      // 将修改后的公式设置回对应的区域
      dbExportSheet.getRange(2, 1, formulas.length, formulas[0].length).setFormulas(formulas);
    }
  }
}

关键修改说明 #

• 范围精准限定：用getRange(2, 1, totalRows - 1, totalCols)明确指定处理第2行到最后一行的区域，完全避开表头所在的第1行。
• 避免空值覆盖：仅操作数据行的公式，不会触碰表头的文本内容，从根源解决表头消失问题。
• 边界安全判断：增加if (totalRows > 1)的判断，防止当DB Export表只有表头行时执行无效操作。

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