在 Gmail 邮件中以 HTML 表格形式通过电子邮件发送 Google 表格范围(带格式或不带格式) [英] Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message

问题描述

因此，我有一个 Google 电子表格，并希望通过使用 Google 电子表格触发器将每日报告"表格中的一部分范围以 HTML 表格的形式通过电子邮件发送来创建自动每日报告.

我在工作表中有一些条件格式，以使其绘制列中具有 MAX(value) 的列中的所有单元格.

我设法创建了下面的代码，该代码将范围通过电子邮件发送给我，但 Gmail 无法将其识别为 HTML 表格，而是纯文本.

我尝试在 MailApp.sendEmail 函数中使用 {htmlBody: htmltable}，但 Gmail 只是出错 ([Ljava.lang.Object;@SOME_HASH.

问题 1:如何将表格以 HTML 格式而非纯文本形式通过电子邮件发送给所有 HTML 标记可见的表格?

问题 2:如何改进我的代码以获取 Google Sheets 单元格格式并将其应用于表格单元格，而不是使用临时格式来让表格看起来正常?

function sendMail(){var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()var data = sh.getRange("A2:O38").getValues();//var htmltable =[];var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:普通的;'var htmltable = [''];for (row = 0; row');for (col = 0;col

解决方案

对于问题 1，它应该像不使用数组来保存 HTML 表格的每一行一样简单.只需将它连接成一个字符串并通过它发送，应该可以正常工作.

至于问题 2，我假设您必须检查单元格的某些条件以确定如何格式化表格.我不知道是否有一种直接的方法可以复制所有格式.

不过，这是一个想法.可以将 Google 表格发布为 HTML(在文件选项卡下查看).也许有一种方法可以通过 url 拉入 HTML 文件，然后解析为您需要的内容.我只是不知道它是否会保留任何单元格格式.不过值得研究.

编辑(串联):

还添加了一个 Logger.log 以便您可以看到最终的 htmltable String 对象是如何出来的.或许可以将该值复制到典型的 index.html 页面中，然后查看它如何正确加载或是否正确加载.

function sendMail(){var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();var data = sh.getRange("A2:O38").getValues();//var htmltable =[];var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:普通的;'var htmltable = '';for (row = 0; row';}否则 {htmltable += ''+ 数据[行][列] + '</td>';}}htmltable += '</tr>';}htmltable += '</table>';Logger.log(数据);Logger.log(htmltable);MailApp.sendEmail(Session.getActiveUser().getEmail(), '每日报告','' ,{htmlBody: htmltable})}

编辑(经过测试和工作，见截图):

更新(问题 2 的解决方案):

从

以下是实现此解决方案的一些代码(确保首先从上面的链接添加库):

function convSheetAndEmail(rng, email, subj){var HTML = SheetConverter.convertRange2html(rng);MailApp.sendEmail(email, subj, '', {htmlBody : HTML});}

然后调用该函数:

函数 doGet(){//或指定一个范围，如 A1:D12 等.var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();var emailUser = '[e-mail]';var subject = '测试邮箱';convSheetAndEmail(dataRange, emailUser, 主题);}

So I have a Google Spreadsheet and want to create automated daily reports by emailing a portion of the range from my "Daily report" sheet as a HTML table using Google Spreadsheet triggers.

I have some conditional formatting in the sheet to get it to paint all cells in column that have MAX(value) in said column.

I've managed to create the code below which emails me the range but Gmail doesn't recognized it as a HTML table but plain text.

I've tried using {htmlBody: htmltable} in the MailApp.sendEmail function but Gmail just errors out ([Ljava.lang.Object;@SOME_HASH.

Question 1: How can I email the table as HTML and not plain text with all the HTML tags visible?

Question 2: How could I improve my code to get Google Sheets cell formatting and apply it to the table cells instead of using ad hoc formatting just to get the table to look OK?

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
 var data = sh.getRange("A2:O38").getValues();
  //var htmltable =[];

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = ['<table ' + TABLEFORMAT +' ">'];

for (row = 0; row<data.length; row++){

htmltable.push ('<tr>');

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable.push('<td>' + 'None' + '</td>');} 
  else
    if (row === 0)  {
      htmltable.push ('<th>' + data[row][col] + '</th>');
    }

  else {htmltable.push('<td>' + data[row][col] + '</td>');}
}

     htmltable.push('</tr>');
}

     htmltable.push ('</table>');
     Logger.log(data);
MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Daily report','' ,{htmlBody: htmltable})
}

解决方案

For question 1, it should be as simple as not using an array to hold each line of your HTML table. Just concatenate it into a string and send it on through and should work just fine.

As far as question 2, I'd assume that you'd have to check certain conditions of the cells to determine how to format the table. I don't know if there's a sure straightforward way to copy all formatting.

Here's an idea though. It's possible to publish a Google sheet as HTML (look under the file tab). Maybe there's a way to pull in the HTML file via url, then parse to what you need. I just have no idea if it'll carry over any cell formatting. Worth looking into though.

Edit (concatenation):

Also added a Logger.log so that you can see how the final htmltable String object comes out. Perhaps copy that value into a typical index.html page and see how or if it loads properly.

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var data = sh.getRange("A2:O38").getValues();
  //var htmltable =[];

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<data.length; row++){

htmltable += '<tr>';

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable += '<td>' + 'None' + '</td>';} 
  else
    if (row === 0)  {
      htmltable += '<th>' + data[row][col] + '</th>';
    }

  else {htmltable += '<td>' + data[row][col] + '</td>';}
}

     htmltable += '</tr>';
}

     htmltable += '</table>';
     Logger.log(data);
     Logger.log(htmltable);
MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Daily report','' ,{htmlBody: htmltable})
}

Edit (tested and working, see screenshots):

Update (solution for question 2):

After checking out the library SheetConverter from here pointed out by your comment below, I was able to send a perfectly formatted email that matched my sheet exactly! See the screenshots below.

Here's some code to that implements this solution (make sure to add the library first from the link above):

function convSheetAndEmail(rng, email, subj)
{
  var HTML = SheetConverter.convertRange2html(rng);
  MailApp.sendEmail(email, subj, '', {htmlBody : HTML});
}

Then call that function:

function doGet()
{
  // or Specify a range like A1:D12, etc.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();

  var emailUser = '[e-mail]';

  var subject = 'Test Email';

  convSheetAndEmail(dataRange, emailUser, subject);
}

这篇关于在 Gmail 邮件中以 HTML 表格形式通过电子邮件发送 Google 表格范围(带格式或不带格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！