Google Apps Script生成PDF报错：行数不匹配，需添加非空行处理规则

问题分析 #

你遇到的Exception: The number of rows in the data does not match the number of rows in the range错误，核心原因有两个：

1. 代码存在语法错误：getDisplayValues()后多了一个多余的括号
2. 读取的数据包含空行，既会导致处理时行数不匹配，也会给createPDF函数传递无效参数引发后续问题

修正后的代码 #

function createBulkPDF() {
  const docFile = DriveApp.getFileById("1AhTfn-nrV91ewNfdQpRl2L-G2-KNXltoLmYq_YABTF8");
  const tempFolder = DriveApp.getFolderById("1fM_PcKV59Q9sOSYWRzxA6Ugk65sT-Ktq");
  const pdfFolder = DriveApp.getFolderById("1R-yQz8DtY69EbbwxFTZm0uLFLZ6JUc-z");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("results");
  const currentNewSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultsdata");
  
  // 修正语法错误，同时过滤空行
  let data = currentSheet.getRange(2, 1, currentSheet.getLastRow() - 1, 10).getDisplayValues();
  // 仅保留至少有一个非空单元格的行，可根据需求调整过滤规则
  data = data.filter(row => row.some(cell => cell.trim() !== ''));

  let errors = [];
  data.forEach(row => {
    try {
      createPDF(row[1], row[0], row[2], row[9], row[3], row[4], row[5], row[6], row[7], row[8], row[0] + " " + row[1], docFile, tempFolder, pdfFolder);
      errors.push(["Complete"]);
    } catch(err) {
      errors.push(["Failed"]);
    }
  });

  // 确保结果行数与目标范围行数匹配
  currentNewSheet.getRange(2, 11, errors.length, 1).setValues(errors);
}

function createPDF(name, platoon, d, oap, pr, cp, mn, vps, dap, sr, pdfName, docFile, tempFolder, pdfFolder) {
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  
  body.replaceText("{name}", name);
  body.replaceText("{platoon}", platoon);
  body.replaceText("{d}", d);
  body.replaceText("{oap}", oap);
  body.replaceText("{pr}", pr);
  body.replaceText("{cp}", cp);
  body.replaceText("{mn}", mn);
  body.replaceText("{vps}", vps);
  body.replaceText("{dap}", dap);
  body.replaceText("{sr}", sr);
  
  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(pdfName);
  tempFile.setTrashed(true);
}

关键修改说明 #

• 语法错误修复：删除getDisplayValues()末尾多余的括号，消除语法异常
• 空行过滤逻辑：使用filter+some方法筛选有效行，如果你需要更严格的校验（比如必须姓名、班组字段非空），可以替换为：

  data = data.filter(row => row[0].trim() !== '' && row[1].trim() !== '');
  

• 行数匹配修复：设置结果范围时用errors.length替代currentNewSheet.getLastRow()-1，保证数据行数与目标范围完全匹配，避免触发行数不匹配异常

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