Google脚本：使用电子表格数据在电子邮件中填充html表格 [英] Google scripts: use spreadsheet data to populate html table in email
本文介绍了Google脚本：使用电子表格数据在电子邮件中填充html表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个带有数据表的googledocs电子表格。表格填写完毕后，我的脚本将获取数据并通过电子邮件发送。我拥有的代码将从数据范围中获取我想要的每个值，并将其放入一个表中，但这一切都是通过识别超过一百个变量来完成的。
有没有一种方法可以使用某种数组，以便用数据范围中的每行数据填充html表？
我自由承认是一个编程新手，这可能是一些最糟糕的意大利面代码。任何帮助非常感谢（即使它是JavaScript的入门）。
函数Gradereport（）{
code> //此脚本通过电子邮件将表单内容发送给给定收件人
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Report）;
var recloc = sheet.getRange（D2）; //学生电子邮件的单元格位置
var recipient = recloc.getValue（）; //学生的电子邮件地址
var lastloc = sheet.getRange（B2）;
var last = lastloc.getValue（）;
var firstloc = sheet.getRange（A2）;
var first = firstloc.getValue（）;
var courseloc = sheet.getRange（E2）;
var course = courseloc.getValue（）;
var dateloc = sheet.getRange（C2）;
var date = dateloc.getValue（）;
var gradeloc = sheet.getRange（D5）;
var grade = gradeloc.getValue（）;
var totloc = sheet.getRange（D4）;
var total = totloc.getValue（）;
var perloc = sheet.getRange（D6）;
var percent = perloc.getValue（）;
var dataRange = sheet.getRange（A4：B9）;
var data = dataRange.getValues（）;
//赋值单元位置
var assignloc1 = sheet.getRange（A5）;
var assignloc2 = sheet.getRange（A6）;
var assignloc3 = sheet.getRange（A7）;
var assignloc4 = sheet.getRange（A8）;
var assignloc5 = sheet.getRange（A9）;
//分配值
var assign1 = assignloc1.getValue（）;
var assign2 = assignloc2.getValue（）;
var assign3 = assignloc3.getValue（）;
var assign4 = assignloc4.getValue（）;
var assign5 = assignloc5.getValue（）;
//赋值得分位置
var scoreloc1 = sheet.getRange（B5）;
var scoreloc2 = sheet.getRange（B6）;
var scoreloc3 = sheet.getRange（B7）;
var scoreloc4 = sheet.getRange（B8）;
var scoreloc5 = sheet.getRange（B9）;
//赋值得分值
var score1 = scoreloc1.getValue（）;
var score2 = scoreloc2.getValue（）;
var score3 = scoreloc3.getValue（）;
var score4 = scoreloc4.getValue（）;
var score5 = scoreloc5.getValue（）;
//错误信息
var errmess = first +''+ last +'，您的PIN码不匹配。请仔细检查您的输入并重新提交。如果您再次收到此消息，请与您的教授联系。
var subject = course +'成绩报告';
var body = first +''+ last +'，这是您的成绩报告，请求'+ date +'。等级'+等级+'/'+总+'，'+百分比+'％。分数明细：'+数据;
var bodyHTML1 ='< p>'+ first +''+ last +'，这是您的成绩报告。< br>年级'+年级+'/'+总计+'，'+百分比+'％。< / p>';
// var bodyHTML2 ='< p>'+ data +'< / p>';
var bodyHTML2 ='< table> < TR> < TD> '+ assign1 +'< / td> < TD> '+ score1 +'< / td> < / TR> < TR> < TD> '+ assign2 +'< / td> < TD> '+ score2 +'< / td> < / TR> < TR> < TD> '+ assign3 +'< / td> < TD> '+ score3 +'< / td> < / TR> < TR> < TD> '+ assign4 +'< / td> < TD> '+ score4 +'< / td> < / TR> < TR> < TD> '+ assign5 +'< / td> < TD> '+ score5 +'< / td> < / TR> < /表>;
var bodyHTML3 ='< p>由< a href =http://www.steegle.com/>发送> Steegle.com< / a>与我们联系表单Google Apps脚本< / p>';
var advancedArgs = {htmlBody：bodyHTML1 + bodyHTML2，};
var pinloc = sheet.getRange（G2）;
var pin = pinloc.getValue（）;
if（pin == 1）{
MailApp.sendEmail（recipient，subject，body，advancedArgs）;
} else {
MailApp.sendEmail（recipient，subject，errmess）;
}
}
解决方案
I已经自由地对您的代码进行了一些更改，以及生成表的循环，这里是：
function report2（）{
var LOC = {
recipient：[1,3]，// D2
first：[1,0]，// A2
last：[1,1]，// B2
course：[1,4]，// E2
date：[1,2]，// C2
grade：[ 4,3]，// D5
total：[3,3]，// D4
percent：[5,3]，// D6
};
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Report）;
var values = sheet.getDataRange（）。getValues（）; //立即获取电子表格中的所有值
var data = {};
for（var i in LOC）// row // column（all zero-based index）
data [i] = values [LOC [i] [0]] [LOC [i] [1 ]];
var errmess = data.first +''+ data.last +'，您的PIN码不匹配。请仔细检查您的输入并重新提交。如果您再次收到此消息，请与您的教授联系。
var subject = data.course +'成绩报告';
var body = data.first +''+ data.last +'，这是您的成绩报告，请求'+ data.date +'。等级'+ data.grade +'/'+ data.total +'，'+ data.percent +'％。分数明细：'+ data.data;
var bodyHTML1 ='< p>'+ data.first +''+ data.last +'，这是您的成绩报告。< br>等级'+ data.grade +'/'+ data.total +'，'+ data.percent +'％。< / p>';
var bodyHTML2 ='< table>'; $ var b $ for（var i = 4; i <9; ++ i）
bodyHTML2 + ='< tr>< td> '+ values [i] [0] +'< / td>< td> '+ values [i] [1] +'< / td>< / tr>';
bodyHTML2 + ='< / table>';
var advancedArgs = {htmlBody：bodyHTML1 + bodyHTML2};
var pin = values [1] [6]; // G2
if（pin == 1）
MailApp.sendEmail（recipient，subject，body，advancedArgs）;
else
MailApp.sendEmail（recipient，subject，errmess）;
}
我开发了一个脚本来发送基于电子表格数据的电子邮件，就像你正在尝试。我想你可能会觉得它很有用。它被称为FormEmailer。您可以在脚本库（在菜单插入>脚本下）和其网站上找到它。
I've got a googledocs spreadsheet with a table of data. After a form is filled out, my script will take the data and send it in an email. I have code that will take each value I want from the data range and put it into a table, but it's all done by identifying over a hundred variables.
Is there a way to use an array of some kind so that the html table is populated with each row of data from the data range?
I freely admit to being a programming newbie, and that this is probably some of the worst spaghetti code out there. Any help is greatly appreciated (even if it's a primer on javascript).
function Gradereport() {
// This script e-mails the contents of a form to a given recipient
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
var recloc = sheet.getRange("D2"); //cell location of student email
var recipient = recloc.getValue(); //student's email address
var lastloc = sheet.getRange("B2");
var last = lastloc.getValue();
var firstloc = sheet.getRange("A2");
var first = firstloc.getValue();
var courseloc = sheet.getRange("E2");
var course = courseloc.getValue();
var dateloc = sheet.getRange("C2");
var date = dateloc.getValue();
var gradeloc = sheet.getRange("D5");
var grade = gradeloc.getValue();
var totloc = sheet.getRange("D4");
var total = totloc.getValue();
var perloc = sheet.getRange("D6");
var percent = perloc.getValue();
var dataRange = sheet.getRange("A4:B9");
var data = dataRange.getValues();
// assignment cell locations
var assignloc1 = sheet.getRange("A5");
var assignloc2 = sheet.getRange("A6");
var assignloc3 = sheet.getRange("A7");
var assignloc4 = sheet.getRange("A8");
var assignloc5 = sheet.getRange("A9");
// assignment values
var assign1 = assignloc1.getValue();
var assign2 = assignloc2.getValue();
var assign3 = assignloc3.getValue();
var assign4 = assignloc4.getValue();
var assign5 = assignloc5.getValue();
// assignment score locations
var scoreloc1 = sheet.getRange("B5");
var scoreloc2 = sheet.getRange("B6");
var scoreloc3 = sheet.getRange("B7");
var scoreloc4 = sheet.getRange("B8");
var scoreloc5 = sheet.getRange("B9");
// assignment score values
var score1 = scoreloc1.getValue();
var score2 = scoreloc2.getValue();
var score3 = scoreloc3.getValue();
var score4 = scoreloc4.getValue();
var score5 = scoreloc5.getValue();
// error message
var errmess = first+' '+last+', your Pin code did not match. Please double check your entry and re-submit. Contact your professor if you get this message again.';
var subject = course+' Grade Report';
var body = first+' '+last+', here is your grade report, requested on '+date+'. Grade '+grade+'/'+total+', '+percent+'%. Score breakdown: '+data;
var bodyHTML1 = '<p>'+first+' '+last+', here is your grade report.<br> Grade '+grade+'/'+total+', '+percent+'%.</p>';
// var bodyHTML2 = '<p>'+data+'</p>';
var bodyHTML2 = '<table> <tr> <td> '+assign1+' </td> <td> '+score1+' </td> </tr> <tr> <td> '+assign2+' </td> <td> '+score2+' </td> </tr> <tr> <td> '+assign3+' </td> <td> '+score3+' </td> </tr> <tr> <td> '+assign4+' </td> <td> '+score4+' </td> </tr> <tr> <td> '+assign5+' </td> <td> '+score5+' </td> </tr> </table>';
var bodyHTML3 = '<p>Sent by the <a href="http://www.steegle.com/">Steegle.com</a> Contact Us Form Google Apps Script</p>';
var advancedArgs = {htmlBody:bodyHTML1+bodyHTML2 ,};
var pinloc = sheet.getRange("G2");
var pin = pinloc.getValue();
if(pin == 1){
MailApp.sendEmail(recipient, subject, body, advancedArgs);
}else{
MailApp.sendEmail(recipient, subject, errmess);
}
}
解决方案
I've took to the liberty to make some changes on your code, along with the loop to generate the table, here it is:
function report2() {
var LOC = {
recipient: [1,3],//D2
first: [1,0], //A2
last: [1,1], //B2
course: [1,4], //E2
date: [1,2], //C2
grade: [4,3], //D5
total: [3,3], //D4
percent: [5,3], //D6
};
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
var values = sheet.getDataRange().getValues(); //get all values on the spreadsheet at once
var data = {};
for( var i in LOC ) //row //column (all zero-based index)
data[i] = values[ LOC[i][0] ][ LOC[i][1] ];
var errmess = data.first+' '+data.last+', your Pin code did not match. Please double check your entry and re-submit. Contact your professor if you get this message again.';
var subject = data.course+' Grade Report';
var body = data.first+' '+data.last+', here is your grade report, requested on '+data.date+'. Grade '+data.grade+'/'+data.total+', '+data.percent+'%. Score breakdown: '+data.data;
var bodyHTML1 = '<p>'+data.first+' '+data.last+', here is your grade report.<br> Grade '+data.grade+'/'+data.total+', '+data.percent+'%.</p>';
var bodyHTML2 = '<table>';
for( var i = 4; i < 9; ++i )
bodyHTML2 += '<tr><td> '+values[i][0]+' </td><td> '+values[i][1]+' </td></tr>';
bodyHTML2 += '</table>';
var advancedArgs = {htmlBody: bodyHTML1+bodyHTML2};
var pin = values[1][6]; //G2
if( pin == 1 )
MailApp.sendEmail(recipient, subject, body, advancedArgs);
else
MailApp.sendEmail(recipient, subject, errmess);
}
I've developed a script to send emails based on spreadsheet data, like you're trying. I think you might find it useful. It's called FormEmailer. You find it on the Script Gallery (under the menu Insert > Script) and on its site.
这篇关于Google脚本：使用电子表格数据在电子邮件中填充html表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
