我一直在尝试创建一个脚本,将单元格数组作为表格发送(保持格式),但是经过数小时和大约30种测试代码的变化,我一直碰壁...
到目前为止,我要么: 1.收到一封空电子邮件 2.收到一封包含我所有数据但没有格式的电子邮件(所有数字一个接一个,而不是一个表) 3.在电子邮件中以HTML代码编写的表格,而不是表格本身。 4.一封带有[对象]作为消息的电子邮件
我通过使用以下代码获得结果2:
var conv = SheetConverter.init(ss.getSpreadsheetTimeZone(),ss.getSpreadsheetLocale());
var html = conv.convertRange2html(dataRange);
最后,我的最后一次尝试仍然失败了,但似乎很有希望,它是以下内容。我目前认为的与此相关的问题是sendEmail函数未调用其上方的getHtmlTable:
function getHtmlTable(range){
var sheet = Spreadsheetapp.getactiveSpreadsheet().getSheetByName("Sheet1");
startRow = "6";
startCol = "B";
lastRow = "18";
lastCol = "E";
// Read table contents
var dataRange = Spreadsheetapp.getactiveSpreadsheet().getSheetByName("Sheet1").getRange("B6:E18");
var data = dataRange.getvalues();
// Get css style attributes from range
var fontColors = dataRange.getFontColors();
var backgrounds = dataRange.getBackgrounds();
var fontFamilies = dataRange.getFontFamilies();
var fontSizes = dataRange.getFontSizes();
var fontLines = dataRange.getFontLines();
var fontWeights = dataRange.getFontWeights();
var horizontalAlignments = dataRange.getHorizontalAlignments();
var verticalAlignments = dataRange.getVerticalAlignments();
// Get column widths in pixels
var colWidths = ["10"];
// Get Row heights in pixels
var rowHeights = ["10"];
// Future consideration...
var numberFormats = dataRange.getNumberFormats();
// Build HTML Table,with inline styling for each cell
var tableFormat = 'style="border:1px solid black;border-collapse:collapse;text-align:center" border = 1 cellpadding = 5';
var html = ['<table '+tableFormat+'>'];
// Column widths appear outside of table rows
for (col=0;col<colWidths.length;col++) {
html.push('<col width="'+colWidths[col]+'">')
}
// Populate rows
for (row=0;row<data.length;row++) {
html.push('<tr height="'+rowHeights[row]+'">');
for (col=0;col<data[row].length;col++) {
// Get formatted data
var cellText = data[row][col];
if (cellText instanceof Date) {
cellText = Utilities.formatDate(
cellText,ss.getSpreadsheetTimeZone(),'EEE,MMM dd YY'); /// 'EEE,MMM dd - h:mm a');
}
var style = 'style="'
+ 'color: ' + fontColors[row][col]+'; '
+ 'font-family: ' + fontFamilies[row][col]+'; '
+ 'font-size: ' + fontSizes[row][col]+'; '
+ 'font-weight: ' + fontWeights[row][col]+'; '
+ 'background-color: ' + backgrounds[row][col]+'; '
+ 'text-align: ' + horizontalAlignments[row][col]+'; '
+ 'vertical-align: ' + verticalAlignments[row][col]+'; '
+'"';
html.push('<td ' + style + '>'
+cellText
+'</td>');
}
html.push('</tr>');
}
html.push('</table>');
return html.join('');
}
function sendEmail() {
// Fetch email address
var emailRange = Spreadsheetapp.getactiveSpreadsheet().getSheetByName("Sheet1").getRange("B1");
var emailaddress = emailRange.getvalues();
// Send Email.
var message = getHtmlTable;
var subject = 'Test 34';
MailApp.sendEmail(emailaddress,subject,message);
}
如果有人可以提供帮助,将不胜感激:)真是发疯了