请求优化Google Sheets脚本：自动识别页面高度重复添加表头生成PDF

问题背景 #

我正在制作一个「估价单生成器」，但遇到功能瓶颈：Google Sheets不支持添加带Logo的自定义页眉，需要实现脚本让“Maker”工作表内容逐行写入“PDF Maker”工作表，当已写入行总高度达到PDF打印页面最大尺寸时，自动重复添加表头行，继续写入数据后下一页满了再重复加表头。每天要处理数百份订单，这个功能能大幅提升自动化效率。每份订单有自定义表头，明细行格式和行高可能变化，所以脚本得先识别页面尺寸再加表头。

目前编写的脚本仅能写入第一个表头，无法识别页面高度，只会逐行写数据，不会添加后续表头。打印PDF用固定纸张尺寸，恳请优化。

原尝试脚本 #

function generatePDFWithConsistentHeaders() {
  var sourceSheetName = "Maker"; 
  var destinationSheetName = "PDF Maker"; 
  var headerRange = "A1:G2"; // 
  var dataRange = "A1:E200"; // 

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = spreadsheet.getSheetByName(sourceSheetName);
  var destinationSheet = spreadsheet.getSheetByName(destinationSheetName);

  // Clear the destination sheet before copying new data
  destinationSheet.clearContents();

  // Copy and paste the header to the top of each page
  var headerRangeCopy = sourceSheet.getRange(headerRange);
  var pageCount = Math.ceil(sourceSheet.getLastRow() / destinationSheet.getMaxRows());

  for (var page = 0; page < pageCount; page++) {
    var startRow = page * destinationSheet.getMaxRows() + 1;
    var destinationRange = destinationSheet.getRange(startRow, 1, headerRangeCopy.getNumRows(), headerRangeCopy.getNumColumns());
    headerRangeCopy.copyTo(destinationRange);
  }

  // Copy the data row by row
  var dataRangeCopy = sourceSheet.getRange(dataRange);
  var destinationRow = headerRangeCopy.getNumRows() + 1;

  for (var i = 1; i <= dataRangeCopy.getNumRows(); i++) {
    var sourceRow = dataRangeCopy.getRow() + i - 1;
    var sourceValues = dataRangeCopy.getSheet().getRange(sourceRow, dataRangeCopy.getColumn(), 1, dataRangeCopy.getNumColumns()).getDisplayValues()[0];
    var destinationRange = destinationSheet.getRange(destinationRow, 1, 1, sourceValues.length);
    destinationRange.setValues([sourceValues]);
    destinationRow++;

    // Insert a new header row at the start of each page
    if (destinationRow % destinationSheet.getMaxRows() === 1 && destinationRow <= destinationSheet.getMaxRows() * pageCount) {
      var headerRangeCopy = sourceSheet.getRange(headerRange);
      var destinationRange = destinationSheet.getRange(destinationRow, 1, headerRangeCopy.getNumRows(), headerRangeCopy.getNumColumns());
      headerRangeCopy.copyTo(destinationRange);
      destinationRow++;
    }
  }
}

优化后的脚本 #

以下脚本会根据打印页面实际可用高度，实时计算已写入内容总高度，达到页面上限时自动插入带完整格式的表头，适配行高变化的明细行：

function generatePDFWithDynamicHeaders() {
  const sourceSheetName = "Maker";
  const destinationSheetName = "PDF Maker";
  const headerRange = "A1:G2"; // 自定义表头范围
  const dataStartRow = 3; // Maker工作表中数据起始行（表头之后的第一行）

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const destSheet = ss.getSheetByName(destinationSheetName);

  // 清空目标工作表
  destSheet.clear();

  // 获取表头区域和表头行高总和
  const headerSource = sourceSheet.getRange(headerRange);
  const headerRowCount = headerSource.getNumRows();
  let headerTotalHeight = 0;
  for (let i = 0; i < headerRowCount; i++) {
    headerTotalHeight += sourceSheet.getRowHeight(headerSource.getRow() + i);
  }

  // 获取打印页面的可用高度（页面高度 - 上下边距）
  const printSettings = destSheet.getPageSetup();
  const pageHeight = printSettings.getPaperHeight();
  const topMargin = printSettings.getTopMargin();
  const bottomMargin = printSettings.getBottomMargin();
  const usablePageHeight = pageHeight - topMargin - bottomMargin;

  // 初始化目标工作表状态
  let currentDestRow = 1;
  let currentPageUsedHeight = 0;

  // 先插入第一个表头
  headerSource.copyTo(destSheet.getRange(currentDestRow, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
  currentDestRow += headerRowCount;
  currentPageUsedHeight += headerTotalHeight;

  // 获取所有数据行
  const lastDataRow = sourceSheet.getLastRow();
  if (lastDataRow < dataStartRow) return; // 无数据时直接退出

  // 逐行处理数据
  for (let sourceRow = dataStartRow; sourceRow <= lastDataRow; sourceRow++) {
    const dataRowHeight = sourceSheet.getRowHeight(sourceRow);
    
    // 检查当前页面剩余空间是否能放下此行数据
    if (currentPageUsedHeight + dataRowHeight > usablePageHeight) {
      // 插入新表头
      headerSource.copyTo(destSheet.getRange(currentDestRow, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
      currentDestRow += headerRowCount;
      currentPageUsedHeight = headerTotalHeight; // 重置页面已用高度为表头高度
    }

    // 复制数据行（包含格式）
    const sourceDataRange = sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn());
    sourceDataRange.copyTo(destSheet.getRange(currentDestRow, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
    
    // 更新状态
    currentDestRow++;
    currentPageUsedHeight += dataRowHeight;
  }
}

关键优化点 #

• 精准计算页面可用高度：通过getPageSetup()获取打印设置的纸张高度和边距，算出实际可用于内容的高度，适配固定打印尺寸
• 实时行高累加判断：每处理一行数据就累加行高，当超过页面可用高度时自动插入表头，适配行高变化的明细行
• 完整复制表头格式：使用PASTE_ALL复制表头的所有格式（包括Logo、字体、样式），确保每页表头一致
• 动态状态管理：跟踪当前目标行位置和页面已用高度，确保表头插入时机准确

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