Google Sheets导出PDF脚本报错：数据与范围行数不匹配

问题背景 #

接手前同事的任务，修复一款将Google Sheets编译为PDF的脚本，运行时出现以下错误：

Exception: The number of rows in the data does not match the number of rows in the range. The data has 49 but the range has 71.

已更新所有工作表名称，但问题仍未解决，同时不清楚代码中DriveApp.getRootFolder();的用途。

原脚本代码 #

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Create Cash Proposal PDF', 'createCashProposal')
    .addToUi()
}
function createCashProposal() {
  /* 
      Author: http://haw.productions 
      Date Created: October 2019
      Updated by Amaya Maya February 2023 
  */
  let sourceSpreadsheet = SpreadsheetApp.getActive();
  let pdfName = sourceSpreadsheet.getName();
  
  var sheetName1 = "B1. Cash Proposal Cover";
  var sheetName2 = "B2. Who We Are";
  var sheetName3 = "B3. Comparison";
  var sheetName4 = "B4. What We Found";
  var sheetName5 = "B5. How We Can Help";
  var sheetName6 = "B6. Project Estimate";
  var sheetName7 = "B7. Cash Flow Solution";
  var sheetName8 = "B8. Accounting Summary";
  var sheetName9 = "B9. Work Order";

  var sourceSheet1 = sourceSpreadsheet.getSheetByName(sheetName1);
  var sourceSheet2 = sourceSpreadsheet.getSheetByName(sheetName2);
  var sourceSheet3 = sourceSpreadsheet.getSheetByName(sheetName3);
  var sourceSheet4 = sourceSpreadsheet.getSheetByName(sheetName4);
  var sourceSheet5 = sourceSpreadsheet.getSheetByName(sheetName5);
  var sourceSheet6 = sourceSpreadsheet.getSheetByName(sheetName6);
  var sourceSheet7 = sourceSpreadsheet.getSheetByName(sheetName7);
  var sourceSheet8 = sourceSpreadsheet.getSheetByName(sheetName8);
  var sourceSheet9 = sourceSpreadsheet.getSheetByName(sheetName9);

  
  var folder = DriveApp.getFolderById('1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv'); 
  DriveApp.getRootFolder();
 
  Logger.log("getActive: ", sourceSpreadsheet.getId());
  
  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  var destSheet1 = destSpreadsheet.getSheetByName(sheetName1);
  var destSheet2 = destSpreadsheet.getSheetByName(sheetName2);
  var destSheet3 = destSpreadsheet.getSheetByName(sheetName3);
  var destSheet4 = destSpreadsheet.getSheetByName(sheetName4);
  var destSheet5 = destSpreadsheet.getSheetByName(sheetName5);
  var destSheet6 = destSpreadsheet.getSheetByName(sheetName6);
  var destSheet7 = destSpreadsheet.getSheetByName(sheetName7);
  var destSheet8 = destSpreadsheet.getSheetByName(sheetName8);
  var destSheet9 = destSpreadsheet.getSheetByName(sheetName9);
  
  //repace cell values with text (to avoid broken references) 
  var sourceRange1 = sourceSheet1.getRange(1,1,sourceSheet1.getMaxRows(),sourceSheet1.getMaxColumns());
  var sourceRange2 = sourceSheet2.getRange(1,1,sourceSheet2.getMaxRows(),sourceSheet2.getMaxColumns());
  var sourceRange3 = sourceSheet3.getRange(1,1,sourceSheet3.getMaxRows(),sourceSheet3.getMaxColumns());
  var sourceRange4 = sourceSheet4.getRange(1,1,sourceSheet4.getMaxRows(),sourceSheet4.getMaxColumns());
  var sourceRange5 = sourceSheet5.getRange(1,1,sourceSheet5.getMaxRows(),sourceSheet5.getMaxColumns());
  var sourceRange6 = sourceSheet6.getRange(1,1,sourceSheet6.getMaxRows(),sourceSheet6.getMaxColumns());
  var sourceRange7 = sourceSheet7.getRange(1,1,sourceSheet7.getMaxRows(),sourceSheet7.getMaxColumns());
  var sourceRange8 = sourceSheet8.getRange(1,1,sourceSheet8.getMaxRows(),sourceSheet8.getMaxColumns());
  var sourceRange9 = sourceSheet9.getRange(1,1,sourceSheet9.getMaxRows(),sourceSheet9.getMaxColumns());
  
  var sourcevalues1 = sourceRange1.getValues();
  var sourcevalues2 = sourceRange2.getValues();
  var sourcevalues3 = sourceRange3.getValues();
  var sourcevalues4 = sourceRange4.getValues();
  var sourcevalues5 = sourceRange5.getValues();
  var sourcevalues6 = sourceRange6.getValues();
  var sourcevalues7 = sourceRange7.getValues();
  var sourcevalues8 = sourceRange8.getValues();
  var sourcevalues9 = sourceRange9.getValues();

  
  var destRange1 = destSheet1.getRange(1,1,destSheet1.getMaxRows(),destSheet1.getMaxColumns());
  var destRange2 = destSheet2.getRange(1,1,destSheet2.getMaxRows(),destSheet2.getMaxColumns());
  var destRange3 = destSheet3.getRange(1,1,destSheet3.getMaxRows(),destSheet3.getMaxColumns());
  var destRange4 = destSheet4.getRange(1,1,destSheet4.getMaxRows(),destSheet4.getMaxColumns());
  var destRange5 = destSheet5.getRange(1,1,destSheet5.getMaxRows(),destSheet5.getMaxColumns());
  var destRange6 = destSheet6.getRange(1,1,destSheet6.getMaxRows(),destSheet6.getMaxColumns());
  var destRange7 = destSheet7.getRange(1,1,destSheet7.getMaxRows(),destSheet7.getMaxColumns());
  var destRange8 = destSheet8.getRange(1,1,destSheet8.getMaxRows(),destSheet8.getMaxColumns());
  var destRange9 = destSheet9.getRange(1,1,destSheet9.getMaxRows(),destSheet9.getMaxColumns());
  
  destRange1.setValues(sourcevalues1);
  destRange2.setValues(sourcevalues2);
  destRange3.setValues(sourcevalues3);
  destRange4.setValues(sourcevalues4);
  destRange5.setValues(sourcevalues5);
  destRange6.setValues(sourcevalues6);
  destRange7.setValues(sourcevalues7);
  destRange8.setValues(sourcevalues8);
  destRange9.setValues(sourcevalues9);

  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName1 && 
        sheets[i].getSheetName() != sheetName2 && 
        sheets[i].getSheetName() != sheetName3 && 
        sheets[i].getSheetName() != sheetName4 && 
        sheets[i].getSheetName() != sheetName5 && 
        sheets[i].getSheetName() != sheetName6 &&
        sheets[i].getSheetName() != sheetName7 &&
        sheets[i].getSheetName() != sheetName8 &&
        sheets[i].getSheetName() != sheetName9){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }  

  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);
  
  // Display a modal dialog box with custom HtmlService content.
  const htmlOutput = HtmlService
  .createHtmlOutput('<p style="font-family:arial;font-weight:bold">Click to open <a href="' + newFile.getUrl() + '" target="_blank">' + pdfName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')  

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

错误原因 #

脚本中分别使用getMaxRows()获取源表和目标表的行数，但复制后的临时表（目标表）可能因格式调整、历史删除行等操作，导致行数和源表不一致。setValues()要求数据的行列数必须和目标范围完全匹配，因此出现行不匹配的报错。

修复方案 #

1. 统一行列范围定义 #

修改所有目标范围的获取逻辑，基于源表的行列数来定义目标表的范围，确保数据和范围的行列数完全一致。

将原代码中所有类似：

var destRange1 = destSheet1.getRange(1,1,destSheet1.getMaxRows(),destSheet1.getMaxColumns());

的代码，替换为：

var destRange1 = destSheet1.getRange(1,1,sourceSheet1.getMaxRows(),sourceSheet1.getMaxColumns());

对9个工作表的目标范围全部做此修改。

2. 删除无用代码 #

DriveApp.getRootFolder();这行代码仅获取根文件夹对象，但未赋值给变量或进行任何操作，属于冗余代码，直接删除即可。

3. 可选：简化重复代码（优化） #

原代码对9个工作表的操作重复度极高，可改用数组循环简化，减少代码冗余：

function createCashProposal() {
  /* 
      Author: http://haw.productions 
      Date Created: October 2019
      Updated by Amaya Maya February 2023 
  */
  let sourceSpreadsheet = SpreadsheetApp.getActive();
  let pdfName = sourceSpreadsheet.getName();
  
  const sheetNames = [
    "B1. Cash Proposal Cover",
    "B2. Who We Are",
    "B3. Comparison",
    "B4. What We Found",
    "B5. How We Can Help",
    "B6. Project Estimate",
    "B7. Cash Flow Solution",
    "B8. Accounting Summary",
    "B9. Work Order"
  ];

  var folder = DriveApp.getFolderById('1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv'); 
 
  Logger.log("getActive: ", sourceSpreadsheet.getId());
  
  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //替换单元格值为文本（避免无效引用）
  sheetNames.forEach(name => {
    const sourceSheet = sourceSpreadsheet.getSheetByName(name);
    const destSheet = destSpreadsheet.getSheetByName(name);
    const sourceRange = sourceSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns());
    const sourceValues = sourceRange.getValues();
    const destRange = destSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns());
    destRange.setValues(sourceValues);
  });

  //删除冗余工作表
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (!sheetNames.includes(sheets[i].getSheetName())) {
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }  

  //保存为PDF
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);
  
  //显示导出成功弹窗
  const htmlOutput = HtmlService
  .createHtmlOutput('<p style="font-family:arial;font-weight:bold">点击打开 <a href="' + newFile.getUrl() + '" target="_blank">' + pdfName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, '导出成功')  

  //删除临时表
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

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