Google Sheets脚本修改：如何将单元格区域内容插入邮件正文？

Absolutely! You can totally reference a cell range and include its content in your email body—here's how to tweak your script to make that happen smoothly:

Key Issue in Your Original Script #

Your current code uses sh.getRange('D28').getValue() which only pulls the value of a single cell. To include the entire D28:D40 range, we need to fetch all values in that range, format them into readable HTML (since your email uses htmlBody), and pass that formatted content to your email function.

Modified Script with Range Support #

Here's the updated code that handles the D28:D40 range and formats it nicely for your email:

function emailPdf(){ // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[3];
  var shName = sh.getName();
  
  // Fetch the D28:D40 range and process its values
  var range = sh.getRange('D28:D40');
  var rangeValues = range.getValues();
  
  // Convert the 2D array to a flat list, filter out empty cells, and format as HTML list items
  var formattedItems = rangeValues
    .flat() // Turns the 2D array [[val1], [val2]] into a 1D array [val1, val2]
    .filter(value => value !== '') // Removes any empty cells from the list
    .map(value => `<li>${HtmlService.createHtmlOutput(value).getContent()}</li>`); // Escapes special characters and wraps in list tags
  
  // Wrap the items in an unordered list for clean formatting
  var htmlBody = `<h3>Requested Content:</h3><ul>${formattedItems.join('')}</ul>`;
  
  // Pass the formatted HTML body to your send function
  sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), htmlBody);
}

function sendSpreadsheetToPdf(sheetNumber, pdfName, email, subject, date, htmlbody) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId();
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');
  var url_ext = 'export?exportFormat=pdf&amp;format=pdf' // export as pdf
    + (sheetId ? ('&amp;gid=' + sheetId) : ('&amp;id=' + spreadsheetId)) // following parameters are optional...
    + '&amp;size=A4' // paper size
    + '&amp;portrait=true' // orientation, false for landscape
    + '&amp;fitw=true' // fit to width, false for actual size
    + '&amp;sheetnames=true&amp;printtitle=false&amp;pagenumbers=true' // hide optional headers and footers
    + '&amp;gridlines=false' // hide gridlines
    + '&amp;fzr=false'; // do not repeat row headers (frozen rows) on each page
  var options = {
    headers: {
      'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
    }
  }
  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = { attachments:blob, htmlBody:htmlbody }
    MailApp.sendEmail( email, subject+" | "+date+" (" + pdfName +")", "html content only", mailOptions);
    MailApp.sendEmail( Session.getActiveUser().getEmail(), subject+" | "+date+" (" + pdfName +")", "html content only", mailOptions);
  }
}

What Changed? #

1. Fetching the Range: We use getRange('D28:D40').getValues() to get all values in the range. This returns a 2D array (each element is a single-cell array, e.g., [[value1], [value2]]).
2. Processing Values:
   • flat() converts the 2D array to a simple 1D array.
   • filter() removes any empty cells so your email doesn't show blank entries.
   • map() wraps each value in HTML list tags (<li>) and uses HtmlService.createHtmlOutput() to escape special characters (like <, >, or &) that could break your email's HTML structure.
3. Formatting the Body: We wrap the list items in an unordered list (<ul>) and add a header for clarity—you can adjust this to match your preferred email style.

Optional Formatting Adjustments #

If you prefer plain line breaks instead of a bullet list, replace the formattedItems and htmlBody lines with this:

var formattedItems = rangeValues
  .flat()
  .filter(value => value !== '')
  .map(value => HtmlService.createHtmlOutput(value).getContent())
  .join('<br>'); // Use line breaks instead of list items

var htmlBody = `<h3>Requested Content:</h3><br>${formattedItems}`;

Notes #

• Make sure your sheet index (ss.getSheets()[3]) is correct—remember sheets are zero-indexed, so [3] refers to the 4th sheet in your spreadsheet.
• If you want to include cell formatting (like bold or colors), you'll need to extend the script to fetch cell styles, but for plain text content, the above code works perfectly.

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