javascript - 如何使用应用程序脚本在 html 模板中制作表格

Question

我正在尝试在电子邮件模板中创建一个表格，但我不确定发生了什么，但它没有在电子邮件模板中显示为表格。有人可以帮我把它转换成表格吗？信息逐行显示，但不在表格中。希望得到一些帮助。

这是我的代码。

function sendEmail2(template) {

 //setup function

 var ActiveSheet = SpreadsheetApp.openById("1naSWINA8_uxeLUsj0lFntqILyDj2nirb56uvkBel79Y").getSheetByName("Sheet3");
 var StartRow = 2;
 var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
 var AllValues = WholeRange.getValues();



 var message = "";
 //iterate loop

 for (var i in AllValues) {

 //set current row
 var CurrentRow = AllValues[i];

 //define column to check if sent
 //var EmailSent = CurrentRow[12];

   var check = CurrentRow[12];

   if ( check == "sent"){
     continue;
 } 

 //set HTML template for information
  message +=

      "<p><b>Timestamp by: </b>" + CurrentRow[1] + "</p>" +
      "<p><b>Requester Email: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>Star Rating: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Request Category: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Description: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>Label: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>Ticket ID: </b>" + CurrentRow[7] + "</p>" +
      "<p><b>Comment: </b>" + CurrentRow[8] + "</p>" +
      "<p><b>Status: </b>" + CurrentRow[9] + "</p><br><br>";

  //set the row to look at
  var setRow = parseInt(i) + StartRow;

  //mark row as "sent"
  //ActiveSheet.getRange(setRow, 13).setValue("sent");
}

 var SendTo = "someone@gmail.com";

 //set subject line
 var Subject = "CT IT feedback";

var htmlBody = HtmlService.createTemplateFromFile(template);
var htmlContent = htmlBody.evaluate().getContent();




  //send the actual email  
  MailApp.sendEmail({
      to: SendTo,
      cc: "",
      subject: Subject,
      htmlBody: htmlContent,
});



 };

Answer 1

试试这个:

function sendEmail2(template) {
  var ss=SpreadsheetApp.openById("1naSWINA8_uxeLUsj0lFntqILyDj2nirb56uvkBel79Y");
  var sh=ss.getSheetByName("Sheet3");
  var rg=sh.getRange(StartRow,1,sh.getLastRow()-1,11);
  var vA=rg.getValues();
  var html= '<style>th,td{border: 1px solid black;}</style><table>';
  for (var i=0;i<vA.length;i++) {
    var row=vA[i];
    if ( row[12]=="sent"){
      continue;
    } 
    html+=Utilities.formatString('<tr><td><strong>Timestamp by: </strong>%s</td><td>%s</td></tr>',row[1]);
    html+=Utilities.formatString('<tr><td><strong>Requester Email: </strong>%s</td><td>%s</td></tr>',row[2]);
    html+=Utilities.formatString('<tr><td><strong>Star Rating: </strong>%s</td><td>%s</td></tr>',row[3]);
    html+=Utilities.formatString('<tr><td><strong>Request Category: </strong>%s</td><td>%s</td></tr>',row[4]);
    html+=Utilities.formatString('<tr><td><strong>Description: </strong>%s</td><td>%s</td></tr>',row[5]);
    html+=Utilities.formatString('<tr><td><strong>Label: </strong>%s</td><td>%s</td></tr>',row[6]);
    html+=Utilities.formatString('<tr><td><strong>Ticket ID: </strong>%s</td><td>%s</td></tr>',row[7]);
    html+=Utilities.formatString('<tr><td><strong>Comment: </strong>%s</td><td>%s</td></tr>',row[8]);
    html+=Utilities.formatString('<tr><td><strong>Status: </strong>%s</td><td>%s</td></tr>',row[9]);    
  }
  html+='</table>';
  var SendTo = "someone@gmail.com";
  var Subject = "CT IT feedback";
  MailApp.sendEmail({to:SendTo,subject: Subject,htmlBody: html});
}