在Gmail邮件中将Google Sheet范围（带或不带格式）通过电子邮件发送给HTML表格 [英] Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message
问题描述
因此，我有一个Google Spreadsheet，并希望通过使用Google Spreadsheet触发器将我的每日报告表中的一部分范围通过电子邮件发送到HTML表格来创建自动日报。
我在工作表中有一些条件格式，用于在列中绘制列中所有具有MAX（值）的单元格。
我已经管理创建下面的代码给我发电子邮件的范围，但Gmail不会将其识别为HTML表格，但纯文本。
我试过使用{htmlBody：htmltable}在MailApp.sendEmail函数中，但Gmail只是出错（[Ljava.lang.Object; @SOME_HASH。
）问题1：如何将表格作为电子邮件发送HTML，而不是纯文本，所有HTML标记都可见？
问题2：如何改进我的代码以获取Google表格单元格格式和将它应用于表格单元格，而不是使用特设格式仅仅为了让表格看起来好？
function sendMail（）{pre
var sh = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）
var data = sh.getRange（A2：O38）。getValues（）;
// var htmltable = [];
var tableFORMAT ='cellspacing =2cellpadding =2dir =ltrborder =1style =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>'）; （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>'）;
}
其他{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}）
}
解决方案对于问题1，它应该与不使用数组来保存HTML表的每一行一样简单。只需将它连接成一个字符串并发送，并且应该可以正常工作。
就问题2而言，我假设您必须检查单元格的某些条件以确定如何格式化表格。我不知道是否有一种可靠的直接复制所有格式的方法。
尽管这里有一个想法。可以将Google工作表作为HTML发布（查看文件选项卡下）。也许有一种方法可以通过url拉入HTML文件，然后解析你所需要的。我只是不知道它是否会继承任何单元格格式。值得调查。
编辑（连接）：
还添加了一个Logger.log，你可以看到最终的htmltable String对象是如何出来的。也许把这个值复制到一个典型的index.html页面中，看看它是如何加载的。
$ p $function sendMail（）{pre>
var sh = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）;
var data = sh.getRange（A2：O38）。getValues（）;
// var htmltable = [];
var tableFORMAT ='cellspacing =2cellpadding =2dir =ltrborder =1style =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>'; （col = 0; col< data [row] .length; col ++）{
if（data [row] [col] ===|| 0）{htmltable（
） + ='< td>'+'无'+'< / td>';}
else
if（row === 0）{
htmltable + ='< < + data [row] [col] +'< th>';
}
其他{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}）
}
编辑（已测试和正在运行，请参阅屏幕截图）：
更新（问题2的解决方案）：
从
p>
以下是实现此解决方案的一些代码（确保首先从上面的链接添加库）：
函数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 ='test@email.com';
var subject ='测试电子邮件';
convSheetAndEmail（dataRange，emailUser，subject）;
}
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 = 'test@email.com'; var subject = 'Test Email'; convSheetAndEmail(dataRange, emailUser, subject); }
这篇关于在Gmail邮件中将Google Sheet范围（带或不带格式）通过电子邮件发送给HTML表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
