使用电子表格数据填充电子邮件中的HTML表 [英] Use spreadsheet data to populate HTML table in email

问题描述

我有一个带有数据表的Google电子表格。填写表格后，我的脚本将获取数据并通过电子邮件发送。我有一些代码将从数据范围中获取所需的每个值，并将其放入表格中，但这都是通过识别一百多个变量来完成的。

I've got a Google 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.

有没有一种使用某种数组的方法，以便用数据范围中的每一行数据填充html表？

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?

我自由地承认自己是一名编程新手，这可能是其中最糟糕的意大利面条代码。非常感谢您的帮助（即使它是javascript的入门书籍）。

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自由地对代码进行了一些更改，以及生成表的循环，这里是：

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);
}

我已经开发了一个脚本，可以像您一样基于电子表格数据发送电子邮件在尝试。我认为您可能会觉得有用。叫做FormEmailer。您可以在脚本库（在插入>脚本菜单下）及其站点上找到它。

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.

这篇关于使用电子表格数据填充电子邮件中的HTML表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！