Google App脚本将工作表中的数据分组到电子邮件

Question

我有一个行动项目谷歌表，上面有这些栏：

• A-ID-A系列#1、2、3等
• B-分配-分配操作项的日期
• C-product-项目或区域的短文本名称
• D-操作项-操作项的文本说明
• 电子所有者-项目分配给谁，这通常只是一个像Bob L这样的名字，或者有时是多个人Bob/Ted
• F-到期日
• G-状态-挂起、正在处理等
• h-上次更新-由onEdit脚本更新
• i-上次编辑者-由onEdit脚本更新
• j-owner email-vlookup到数据范围以获取所有者的电子邮件地址
• k-上次发送的提醒-上次发送电子邮件的日期由脚本更新

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;  // First row of data to process
  //var numRows = 12;   // Number of rows to process                     *CHANGE AS NEEDED*

  //var numItems = getRowsData(sheet, OpenItems);
   var numItems = SpreadsheetApp.getActiveSheet().getRange("OpenItems").getValues();


  var numRows = numItems[0]

  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 11) // must encompass Column K (11) 
  // Fetch values for each row in the Range.

  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {

    var row = data[i];
    var actionitemid = row[0];
    var assignedon = Utilities.formatDate(new Date(row[1]), "GMT-05:00", "MM/dd/yy");
    var assignedto = row[4];
    var project = row[2];
    var actionitem = row[3];
    var duedate = Utilities.formatDate(new Date(row[5]), "GMT-05:00", "MM/dd/yy");
    if (duedate == "12/31/69") { // no due date
      var duedate = "TBD - please update!!" ;}
    var status = row[6];
    var emailAddress = row[9];  // Column J = 9 (starts at A=0

    var subject = "Action Item Reminder - Project: " + project;
    var sheetURL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
    var body = "A reminder to do the following:\n\n    Project: "+project + "\n        Due: "+duedate+"\nAssigned: "+assignedon+"\n    Owner: "+assignedto+"\n    Status: " + status + "\n\nACTION ITEM: "+actionitemid+"\n=============================\n"+actionitem+"\n=============================\n\nPlease advise if you will not meet the due date ASAP.\n\nAll action items can be found in the Action Item sheet:\n"+sheetURL+"."; 
    var emailSent = row[11];     // Column D = 3


 //   if (oktosend == "Y") {  // Send only for marked rows
    var ok = emailAddress.length

    if ( emailAddress.length > 0) {

// Send email
 //     MailApp.sendEmail(emailAddress, subject, body);
 //     MailApp.sendEmail(emailAddress, "TEST", body);
      MailApp.sendEmail({
        to: emailAddress, 
        cc: "[e-mail]",
        subject: subject, 
        body: body
      });
       // Update EmailSent
           var time = new Date();
           time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yy, hh:mm");
           sheet.getRange(startRow + i, 11).setValue(time);  // Use column for EmailSent + 1 here getRange(startRow + i, X)
       // Update oktosend to N
 //          sheet.getRange(startRow + i, 5).setValue('N');  // Use column for oktosend + 1 here getRange(startRow + i, X)
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    } //oktosend<>N
  }
}

项目：项目X
到期时间：3/1/14
分配时间：02/01/14
所有者：Rick D
状态：挂起

操作项：1

如果你方不能按时交货，请尽快通知。

所有动作项目都可以在动作项目单中找到：结束输出

在Gmail中寻找关于分组和生成表的帮助。

Answer 1

您可以将表放在消息的正文中，然后通过GmailApp服务而不是mailapp发送它。

var body = '<table><tr><td>col</td><td>val</td></tr></table>';
GmailApp.sendMail('[e-mail]', 'subject', '', 
      { cc: '[e-mail]',
        htmlBody: body 
      });