给Google表格单元格和格式发电子邮 [英] Email Google Sheet cells and format
问题描述
背景：我是一名教师。我通过表单进行测试。我通过在每个单元格上使用各种背景颜色来分级测试（这代表了学生对问题的回答）。问题：问题：我想通过电子邮件将整行（包括格式）发送至B列中的地址，以便每个学生都有他们答案的记录以及我如何给他们分级。
问题：一行数据，包括格式化？
我正在使用以下脚本，该脚本适用于无格式发送单个单元格的电子邮件：
`function sendEmails（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var startRow = 2; //处理
的第一行数据var numRows = 1; //要处理的行数
//获取单元格范围A2：B3
var dataRange = sheet.getRange（startRow，1，numRows，2）
//获取每个单元格的值在范围内排。
var data = dataRange.getValues（）;
for（i in data）{
var row = data [i];
var emailAddress = row [1]; //第二列
var message = row [0]; //我想要整行，包括格式。
var subject =从电子表格发送电子邮件;
MailApp.sendEmail（emailAddress，subject，message）;
ContentService.createTextOutput（hello world！）;
}
}`
在这里它是。
我决定添加另一个函数，因为它使它更简洁。您可以通过使用CSS样式来调整输出的样式。如果你保留注释行，你可以使用它们进行调试。我测试了他们的代码，它看起来不错。
函数sendEmails（）
{
var br ='< br />';
var sheet = SpreadsheetApp.getActiveSheet（）;
var dataRange = sheet.getDataRange（）;
var dataA = dataRange.getValues（）;
var backA = dataRange.getBackgrounds（）;
// var s =''; //请留下注释行。如果将来需要的话，他们可以很方便地在（var i = 1; i< dataA.length; i ++）
{
var emailAddress = dataA [i] [1];
var message = formatRow（sheet.getName（），dataA [i]，backA [i]，dataA [0]）;
var subject =从电子表格发送电子邮件;
// s + = br +'< strong> EmailAddress：< / strong>'+ emailAddress + br +'< strong>主题：< / strong>'+主题+ br +讯息+'* *************************************'+ br;
MailApp.sendEmail（{to：emailAddress，subject：subject，htmlBody：message}）;
}
// var userInterface = HtmlService.createHtmlOutput（s）;
//SpreadsheetApp.getUi().showModelessDialog(userInterface，'Email Message'）
}
我刚刚注意到黄色背景，所以我很快为它添加了另一部分。
//假设时间戳，EmailAddres ，Score，FirstName，LastName，Section ...
函数formatRow（sheetName，rowA，rowbackA，titleA）
{
var br ='< br />';
var ss = SpreadsheetApp.getActive（）;
var sh = ss.getSheetByName（sheetName）;
var html ='';
if（rowA&& rowbackA）
{
html ='';
for（var j = 0; j
switch（rowbackA [j]）
{
case'＃ff0000'：
html + = br +'< span style =font-weight：600; font-size：20px;>'+ titleA [j] +'：< / span>'+ br +'< ; span style =background-color：＃ff0000;>'+ rowA [j] +'< / span>'+ br;
休息;
case'＃ffff00'：
html + = br +'< span style =font-weight：600; font-size：20px;>'+ titleA [j] +'：< ; / span>'+ br +'< span style =background-color：＃ffff00;>'+ rowA [j] +'< / span>'+ br;
休息;
case'#ffffff'：
html + = br +'< span style =font-weight：600; font-size：20px;>'+ titleA [j] +'：< ; / span>'+ br +'< span style =background-color：#ffffff;>'+ rowA [j] +'< / span>'+ br;
break
}
}
}
return html;
}
提醒我使用＃ff0000红色，所以不要更改在不改变代码的情况下改为不同的阴影。
如果一个学生的电子邮件被狗吃掉，您可能只需发送一封电子邮件。 / p>
函数sendOneEmail（firstName，lastName）
{
if（firstName&& lastName）
{
var br ='< br />';
var sheet = SpreadsheetApp.getActiveSheet（）;
var dataRange = sheet.getDataRange（）;
var dataA = dataRange.getValues（）;
var backA = dataRange.getBackgrounds（）;
// var s =''; //请留下注释行。如果将来需要，他们可以很方便地为（var i = 1; i< dataA.length; i ++）
{
if（firstName == dataA [i] [3]）获得
。 && lastName == dataA [i] [4]）
{
var emailAddress = dataA [i] [1];
var message = formatRow（sheet.getName（），dataA [i]，backA [i]，dataA [0]）;
var subject =从电子表格发送电子邮件;
// s + = br +'< strong> EmailAddress：< / strong>'+ emailAddress + br +'< strong>主题：< / strong>'+主题+ br +讯息+'* *************************************'+ br;
MailApp.sendEmail（{to：emailAddress，subject：subject，htmlBody：message}）;
}
}
// var userInterface = HtmlService.createHtmlOutput（s）;
//SpreadsheetApp.getUi().showModelessDialog(userInterface，'Email Message'）
}
}
以下是Spreadsheet的鸟瞰图。
Background: I am a teacher. I gave a test through Forms. I graded the test by using various background colors on each cell (which represented an answer to a question by a student). Each row of the sheet has their email address in Column B.
Problem: I would like to email the entire row, including formatting, to that address in Column B so that each student has a record of their answers and how I graded them.
Question: How can I email a row of data, including formatting?
I am working with the following script, which works well for emailing a single cell without formatting:
`function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 1; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[0]; // I want the whole row, including formatting.
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message);
ContentService.createTextOutput("hello world!");
}
}`
Here it is.
I decided to add another function as it makes it a little cleaner. You'll be able to adjust the styles of the output by playing with the css styles. If you keep the commented lines your can use them for debugging. I tested the code with them and it looks good. So let me know how it works on the emails.
function sendEmails()
{
var br='<br />';
var sheet=SpreadsheetApp.getActiveSheet();
var dataRange=sheet.getDataRange();
var dataA=dataRange.getValues();
var backA=dataRange.getBackgrounds();
//var s='';//Please leave the commented lines. If needed for the future they are handy to have
for (var i=1;i<dataA.length;i++)
{
var emailAddress=dataA[i][1];
var message=formatRow(sheet.getName(),dataA[i],backA[i],dataA[0]);
var subject="Sending emails from a Spreadsheet";
//s+=br + '<strong>EmailAddress:</strong>' + emailAddress + br + '<strong>Subject:</strong>' + subject + br + message + '**************************************' + br;
MailApp.sendEmail({to:emailAddress,subject:subject,htmlBody:message});
}
//var userInterface=HtmlService.createHtmlOutput(s);
//SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Email Message')
}
I just noticed the yellow background so I quickly added another section for it.
//assume Timestamp,EmailAddres,Score,FirstName,LastName,Section...
function formatRow(sheetName,rowA,rowbackA,titleA)
{
var br='<br />';
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName(sheetName);
var html='';
if(rowA && rowbackA)
{
html='';
for(var j=0;j<rowA.length;j++)
{
switch(rowbackA[j])
{
case '#ff0000':
html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ff0000;">' + rowA[j] + '</span>' + br;
break;
case '#ffff00':
html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ffff00;">' + rowA[j] + '</span>' + br;
break;
case '#ffffff':
html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ffffff;">' + rowA[j] + '</span>' + br;
break
}
}
}
return html;
}
Just a reminder I'm using #ff0000 for red so don't change to a different shade without making a change to the code.
In the event that one student's email gets eaten by the dog, you might like to send just one email.
function sendOneEmail(firstName,lastName)
{
if(firstName && lastName)
{
var br='<br />';
var sheet=SpreadsheetApp.getActiveSheet();
var dataRange=sheet.getDataRange();
var dataA=dataRange.getValues();
var backA=dataRange.getBackgrounds();
//var s='';//Please leave the commented lines. If needed for the future they are handy to have
for (var i=1;i<dataA.length;i++)
{
if(firstName==dataA[i][3] && lastName==dataA[i][4])
{
var emailAddress=dataA[i][1];
var message=formatRow(sheet.getName(),dataA[i],backA[i],dataA[0]);
var subject="Sending emails from a Spreadsheet";
//s+=br + '<strong>EmailAddress:</strong>' + emailAddress + br + '<strong>Subject:</strong>' + subject + br + message + '**************************************' + br;
MailApp.sendEmail({to:emailAddress,subject:subject,htmlBody:message});
}
}
//var userInterface=HtmlService.createHtmlOutput(s);
//SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Email Message')
}
}
Here's a birds eye view of the Spreadsheet.
这篇关于给Google表格单元格和格式发电子邮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
