如何通过Google Apps Script将谷歌表格可变行数表格嵌入邮件？

问题原因 #

你之前的代码里，sheet.getRange('B8')只获取了单个单元格对象，直接拼接进邮件会输出对象的字符串（比如Range），而非实际数据。另外你需要的是动态行数的2列表格，得先获取完整的数据范围，再转换成适合纯文本邮件的格式。

修正后的完整代码 #

function ClosedPOsEmail() {
  // version 1.2, written by --Hyde, 13 January 2021
  //  - add |range| in |e.range.rowStart|
  // version 1.1, written by --Hyde, 13 January 2021
  //  - see https://support.google.com/docs/thread/93209480
  // version 1.0, written by --Hyde, 26 September 2020
  //  - initial version
  //  - see https://support.google.com/docs/thread/73381933?msgid=73395384

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Cancelled POs");
  var cancelledPOs = sheet.getRange("B6").getValue();
  var eaches = sheet.getRange("D6").getValue();
  var date = sheet.getRange("A1").getValue(); // 变量名避开内置对象Date，避免冲突

  // 获取动态2列表格数据（从B8开始，覆盖2列到最后一行有内容的行）
  var lastRow = sheet.getRange("B:B").getValues().filter(String).length; // 定位B列最后一行有数据的行号
  var poBreakdownData = sheet.getRange(8, 2, lastRow - 7, 2).getValues(); // 取B8到C列的完整数据范围

  // 将二维数组转换成纯文本表格格式
  let poBreakdownText = "";
  if (poBreakdownData.length > 0) {
    // 添加表头（可根据实际表格结构调整）
    poBreakdownText += "PO编号\t数量\n"; // 用制表符分隔列，纯文本中对齐效果清晰
    poBreakdownText += "--------\t--------\n";
    // 遍历每行数据拼接成文本
    poBreakdownData.forEach(row => {
      poBreakdownText += `${row[0]}\t${row[1]}\n`;
    });
  } else {
    poBreakdownText = "暂无取消的PO明细\n";
  }

  const emailAddress = 'toby.berry@email.com';
  const emailSubject = 'CONFIRMATION OF CLOSED PURCHASE ORDERS - '+ date;
  let emailContents = 'Dear all,\n';
  emailContents += 'This is to confirm that ' + cancelledPOs + ' POs have been cancelled which consisted of ' + eaches+ ' eaches.\n\n';
  emailContents += "PO明细:\n";
  emailContents += poBreakdownText + '\n\n';
  emailContents += 'Kind regards,\n';
  emailContents += 'Toby';
  
  MailApp.sendEmail(emailAddress, emailSubject, emailContents);
  Browser.msgBox("The Cancelled POs email has now been sent. Thank you!");
}

关键修改说明 #

• 动态获取数据范围：通过筛选B列非空值的数量得到最后一行行号，再用getRange(行号, 列号, 行数, 列数)精准获取2列完整数据。
• 纯文本表格转换：用制表符(\t)分隔列，搭配表头和分隔线，让纯文本邮件里的内容呈现清晰的表格结构。
• 变量名优化：将Date改为date，避免与JavaScript内置的Date对象冲突。

可选：发送HTML格式邮件（更美观的表格） #

如果想要更规整的表格样式，可以改用HTML格式发送邮件，替换原代码中MailApp.sendEmail相关部分：

// 转换为HTML表格
let poBreakdownHtml = "";
if (poBreakdownData.length > 0) {
  poBreakdownHtml += "<table border='1' cellpadding='4' cellspacing='0'>";
  poBreakdownHtml += "<tr><th>PO编号</th><th>数量</th></tr>";
  poBreakdownData.forEach(row => {
    poBreakdownHtml += `<tr><td>${row[0]}</td><td>${row[1]}</td></tr>`;
  });
  poBreakdownHtml += "</table>";
} else {
  poBreakdownHtml = "<p>暂无取消的PO明细</p>";
}

// 构造HTML邮件内容
let emailHtml = `<p>Dear all,</p>
<p>This is to confirm that ${cancelledPOs} POs have been cancelled which consisted of ${eaches} eaches.</p>
<h4>PO明细:</h4>
${poBreakdownHtml}
<p>Kind regards,</p>
<p>Toby</p>`;

// 发送HTML邮件（同时保留纯文本备用）
MailApp.sendEmail({
  to: emailAddress,
  subject: emailSubject,
  htmlBody: emailHtml,
  body: emailContents
});

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