电子邮件工作表预览，格式为HTML正文+附加工作表

Question

我已经将此解决方案应用于将谷歌工作表的行作为HTML主体的一部分通过电子邮件发送。

不幸的是，它出错了，因为HTML正文上设置了一个限制。我的数据中的行数是动态的。

超过限制：电子邮件正文大小。(第209行，文件"边栏"(

有办法解决这个问题吗？我可以在HTML正文上提供行的预览，比如说10行和所有列，然后提供一个查看其余行的链接。因为工作表上的内容发生了变化，所以链接不应该指向该工作表。相反，我想把工作表的副本作为一个新文件保存在他们自己的驱动器上，并链接到它。另一个选项是附加一个包含所有行的HTML文件。

这是我目前拥有的：

function emailBreakdown(emailUser, bodyAdd){
SpreadsheetApp.getActiveSpreadsheet().toast('Please wait while information is refreshed.');
if(emailUser == null){emailUser = '[e-mail]'}
if(bodyAdd == null){bodyAdd = 'Testing running of function on script editor'}
var ss = SpreadsheetApp.getActive();
var detailsSht = ss.getSheetByName("Details");
var dataRange = detailsSht.getDataRange();
var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy");
var subject = 'Summary - Exported Data ' + curDate;
var body = '<br />---------------------------------------------------------------<br />You have received an export of a dataset from the <a href="google.com">Summary</a> dashboard. Please see below:<br /><br />' //provide link to the whole dashboard.
convSheetAndEmail(dataRange, emailUser, body, bodyAdd, subject);
}
function convSheetAndEmail(rng, emailUser, body, bodyAdd, subject){
var HTML = SheetConverter.convertRange2html(rng);
MailApp.sendEmail(emailUser, subject, '', {htmlBody : bodyAdd + body + HTML});
}

Answer 1

以下是我通过进一步研究汇编的代码。它运行良好，满足了我的要求。它的作用如下：

• 附加图纸。在这种情况下，是一个xls文件。选择这个而不是HTML。允许用户在需要时在excel中进行操作
• 在电子邮件正文中以HTML形式提供10行的预览
• 预览和附加的文件将保留格式

它没有解决的问题：

• 将文件保存到用户的个人驱动器

开始：

function emailBreakdown(emailUser, bodyAdd){
SpreadsheetApp.getActiveSpreadsheet().toast('Please wait while information is refreshed.');
//If running on script editor the variables will not be transferred. Assign values below:
if(emailUser == null){emailUser = '[e-mail]'}
if(bodyAdd == null){bodyAdd = 'Testing running of function on script editor'}
var ss = SpreadsheetApp.getActive();
var detailsSht = ss.getSheetByName('Details');
var dataRange = detailsSht.getRange('A1:FS11'); //For the preview we are only looking at the first 10 rows of data.
var curDate = Utilities.formatDate(new Date(), 'GMT+1', 'MM/dd/yyyy');
//Gather data to convert specific sheet to excel document so it can be attached to the e-mail
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheetID = detailsSht.getSheetId().toString()
var requestData = {'method': "GET", 'headers':{'Authorization':'Bearer ' + ScriptApp.getOAuthToken()}};
var url = 'https://docs.google.com/spreadsheets/d/' + ssID + '/export?format=xlsx&id=' + ssID + '&gid=' + sheetID;
var result = UrlFetchApp.fetch(url , requestData);  
var contents = result.getContent();
//Assemble E-mail components
var subject = 'Summary - Exported Data ' + curDate;
var body = bodyAdd + 
'<br /><br /><hr style="border: none;border-top: 3px double #333;color: #333;overflow: visible;text-align: center;height: 5px;"><br />' +
'You have received an export of a dataset from the <a href="https://docs.google.com/spreadsheets/d/' + ssID + '/">Summary</a> dashboard. Below is a preview of the dataset:<br /><br />'
var afterBody = '<br /><br /><b>You can view the full dataset through the attached XLS file.</b>'
convSheetAndEmail(ss, contents, dataRange, emailUser, body, afterBody, subject);
};
function convSheetAndEmail(ss, contents, rng, emailUser, body, afterBody, subject){
var HTML = SheetConverter.convertRange2html(rng);
//Send email
MailApp.sendEmail(
emailUser, 
subject, 
'',{
htmlBody : body + HTML + afterBody,
name: 'Full Data Export',
attachments:[{fileName:'Export Data - ' + ss.getName() + '.xls', content:contents, mimeType:'application//xls'}]
}
);
};

除了问题中列出的资源外，我还从这里借用了代码。