如何通过AppScript导出Google Sheets指定范围A1:AX86为PDF并邮件发送

我通过Unity应用将数据同步至Google Sheets表单，再同步到模板表，复制到新表格后转换为PDF并通过邮件发送。目前遇到问题：导出的PDF包含所有行（共7页），但我仅需导出A1:AX86范围（1页），尝试过隐藏行的方法但未解决。手动下载Sheets为PDF时有导出范围选项，想了解如何通过AppScript完整实现该功能。

function sendPdfEmailWithLatestData() {
  // Constants
  const SPREADSHEET_ID = SpreadsheetApp.getActiveSpreadsheet().getId();
  const FORM_RESPONSE_SHEET_NAME = 'FormResponses';
  const PERIODONTAL_CHART_SHEET_NAME = 'Periodontal Chart';
  const EMAIL_SUBJECT = 'Periodontal Chart';
  const EMAIL_BODY = 'Attached is the Periodontal Chart for your review.';

  // Get the active spreadsheet and the sheets by their name
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const formResponseSheet = activeSpreadsheet.getSheetByName(FORM_RESPONSE_SHEET_NAME);
  const periodontalChartSheet = activeSpreadsheet.getSheetByName(PERIODONTAL_CHART_SHEET_NAME);

  // Get the range of all data in the FormResponses sheet, including the header row
  const formResponseDataRange = formResponseSheet.getDataRange();
  const numRows = formResponseDataRange.getNumRows();
  const numColumns = formResponseDataRange.getNumColumns();
  const values = formResponseDataRange.getValues();

  // Get the values from the latest row in the FormResponses sheet
  const headerRow = values[0]; // first row is the header row
  const latestRow = values[numRows - 1]; // last row is the latest row
  const patientLastName = latestRow[headerRow.indexOf('Last Name')];
  const patientFirstName = latestRow[headerRow.indexOf('First Name')];
  const patientDateOfBirth = latestRow[headerRow.indexOf('Date of Birth')];
  const appointmentDate = latestRow[headerRow.indexOf('Date')];
  const appointmentTime = latestRow[headerRow.indexOf('Time')];
  const appointmentVisit = latestRow[headerRow.indexOf('Visit')];
  const patientEmail = latestRow[headerRow.indexOf('Email')];

  // Update the values in the Periodontal Chart sheet
  const columnNames = ['E7:L7', 'Q7:X7', 'AC7:AJ7', 'AM7:AO7', 'AR7:AT7', 'AW7:AX7'];
  columnNames.forEach(function(columnName) {
  // Clear the contents of the cells in the specified column
  periodontalChartSheet.getRange(columnName).clearContent();
});
  const valuesToSet = [patientLastName, patientFirstName, patientDateOfBirth, appointmentDate, appointmentTime, appointmentVisit];
  columnNames.forEach((columnName, index) => {
    periodontalChartSheet.getRange(columnName).setValue(valuesToSet[index]);
  });

  // Convert the active spreadsheet to a PDF file and save it to Google Drive
  const pdf = DriveApp.createFile(activeSpreadsheet.getAs('application/pdf'));

  pdf.setName('Periodontal Chart.pdf');

  // Send the email with the PDF file attached
  //GmailApp.sendEmail(patientEmail, EMAIL_SUBJECT, EMAIL_BODY, {attachments: [pdf]});

  // Delete the PDF file from Google Drive
  pdf.setTrashed(true);

  console.log(`Latest row: ${latestRow}`);
copySheetAndSendEmail(SpreadsheetApp.getActiveSpreadsheet(), patientEmail, "Periodontal Chart", "Attached is the Periodontal Chart for your review.",PERIODONTAL_CHART_SHEET_NAME);

}

function copySheetAndSendEmail(sourceSpreadsheet, emailAddress, emailSubject, emailBody, PERIODONTAL_CHART_SHEET_NAME) {

  // Get the source sheet
  const periodontalChartSheet = sourceSpreadsheet.getSheetByName(PERIODONTAL_CHART_SHEET_NAME);
  // Create a new spreadsheet
  var newSpreadsheet = SpreadsheetApp.create("Periodontal Chart");

  // Copy the sheet to the new spreadsheet, including data and formatting
  periodontalChartSheet.copyTo(newSpreadsheet).setName("Periodontal Chart.pdf");

  // Get all sheets in the newSpreadsheet
  var sheets = newSpreadsheet.getSheets();

  // Loop through all sheets
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];

    // If the sheet is not the Periodontal Chart sheet, delete it
    if (sheet.getName() != "Periodontal Chart.pdf") {
      newSpreadsheet.deleteSheet(sheet);
    }
  }

var sheet = SpreadsheetApp.getActiveSheet();
for(var i=87;i<=sheet.getLastRow();i++){
  sheet.hideRow(i);
}

  // Convert the specified range of cells to a PDF file and save it to Google Drive
  const pdf2 = DriveApp.createFile(newSpreadsheet.getAs('application/pdf'));

  // Send the email with the PDF file attached
  GmailApp.sendEmail(emailAddress, emailSubject, emailBody, {attachments: [pdf2]});

  // Delete the new spreadsheet
  pdf2.setTrashed(true);
}

问题根源是getAs('application/pdf')默认导出整个工作表，隐藏行的方法可能因缓存或格式设置失效。要精准导出指定范围，需使用Google Sheets的导出URL参数来控制导出内容。

修改copySheetAndSendEmail函数，替换原PDF生成逻辑，以下是完整修改后的函数：

function copySheetAndSendEmail(sourceSpreadsheet, emailAddress, emailSubject, emailBody, PERIODONTAL_CHART_SHEET_NAME) {
  // 获取源工作表并创建临时表格
  const periodontalChartSheet = sourceSpreadsheet.getSheetByName(PERIODONTAL_CHART_SHEET_NAME);
  const newSpreadsheet = SpreadsheetApp.create("Periodontal Chart");
  
  // 复制目标工作表到临时表格并删除默认Sheet
  const copiedSheet = periodontalChartSheet.copyTo(newSpreadsheet).setName("Periodontal Chart");
  const defaultSheet = newSpreadsheet.getSheetByName('Sheet1');
  if (defaultSheet) newSpreadsheet.deleteSheet(defaultSheet);

  // 构造指定范围的PDF导出URL
  const spreadsheetId = newSpreadsheet.getId();
  const sheetId = copiedSheet.getSheetId();
  const exportRange = 'A1:AX86'; // 你需要的导出范围

  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?` +
    `format=pdf` +
    `&gid=${sheetId}` +
    `&range=${encodeURIComponent(exportRange)}` +
    `&portrait=true` + // 纵向排版
    `&fitw=true` + // 内容适应页面宽度
    `&sheetnames=false` + // 不显示工作表名称
    `&printtitle=false` + // 不显示文档标题
    `&pagenumbers=false` + // 不显示页码
    `&gridlines=false`; // 不显示网格线

  // 获取OAuth授权令牌并请求PDF内容
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': `Bearer ${token}` }
  });

  // 创建PDF文件并发送邮件
  const pdfBlob = response.getBlob().setName('Periodontal Chart.pdf');
  GmailApp.sendEmail(emailAddress, emailSubject, emailBody, {attachments: [pdfBlob]});

  // 删除临时表格释放空间
  DriveApp.getFileById(spreadsheetId).setTrashed(true);
}

关键说明 #

• 使用encodeURIComponent处理范围参数，避免特殊字符导致URL解析错误
• URL参数可按需调整：比如portrait=false改为横向排版，gridlines=true显示网格线
• 最后删除临时表格，避免占用Google Drive存储空间

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