如何遍历数组并将其传递给Google应用程序脚本中的HTML模板 [英] How to loop through an array and pass it to a HTML template in Google app scripts
问题描述
我基本上是在尝试从Google电子表格中获取数据，创建HTML电子邮件并将其与数据一起发送.
I'm basically trying to take data from a Google Spreadsheet, create a HTML email and send it with the data.
我过去已经做到了，这就是方法.
I've managed this in the past, here is how.
Code.gs
function getData() {
var sh = SpreadsheetApp.getActive()
.getSheetByName('Form responses 1');
return sh.getRange(sh.getLastRow(), 1, 1, sh.getLastColumn())
.getValues()[0]
}
function testEmail() {
var htmlBody = HtmlService
.createTemplateFromFile('mail_template')
.evaluate()
.getContent();
var mailADdy = "email here";
MailApp.sendEmail({
to:mailADdy,
subject: 'Test Email markup - ' + new Date(),
htmlBody: htmlBody,
});
}
mail_template.html
mail_template.html
<html>
<head>
<style>
@media only screen and (max-device-width: 480px) {
/* mobile-specific CSS styles go here */
}
</style>
</head>
<body>
// var data runs the getData function and puts it into an array we can use
<? var data = getData(); ?>
<? var first = data[2]; ?>
<? var second = data[3]; ?>
<? var third = data[4]; ?>
<div class="main">
<p style="text-align: center;"><strong>This is a test HTML email.</strong></p>
<table style="margin-left: auto; margin-right: auto;">
<tbody>
<tr>
<td style="text-align: left;">First question to be put here</td>
<td style="text-align: right;">
<strong><?= first ?></strong>
</td>
</tr>
<tr>
<td style="text-align: left;">Second question here</td>
<td style="text-align: right;">
<?= second ?>
</td>
</tr>
<tr>
<td style="text-align: left;">Third question here</td>
<td style="text-align: right;">
<?= third ?>
</td>
</tr>
<tr>
</tbody>
</table>
<p></p>
</div>
</body>
</html>
我们运行testEmail函数，在HTML中调用getData函数并返回一维数据数组，以供我们在HTML中使用. 这一切都很好.但这只会查看电子表格的最后一行并发送电子邮件.它不会遍历所有行.
We run the testEmail function, in the HTML it calls the getData function and returns a 1D array of data, that we use in the HTML. This all works fine. But this only looks at the last row of a spreadsheet and sends the email. It doesn't iterate through the rows.
我想浏览几行数据，然后每行发送一封电子邮件.
I want to go through several rows of data and send an email per row.
我根据之前的代码尝试了以下操作.但是它只发送一封电子邮件.这是数组中的第一行.
I tried the following, based on my previous code. But it only sends one email. This is the first row in the array.
我只对getData函数进行了调整，但是到目前为止，没有任何效果.这是我现在的位置.
I've only made adjustments to the getData function, but so far, nothing has worked. Here is where I am at the moment.
function getData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange(2,1, sheet.getLastRow()-1, sheet.getLastColumn());
var values = range.getValues();
for (i=0; i < values.length ; i++) {
return values[i];
}
}
它在循环的每次迭代中都返回一个数组，只发送一封电子邮件. 有人知道如何解决此问题或有任何指示吗?
It's returning an array on every iteration on the loop, only sends one email. Does anyone know how to fix this or have any pointers?
我确实尝试将vars传递到sendEmail函数中， 像
I did try passing vars into the sendEmail function, something like
sendEmail(firstName, varA, varB) {
// code here
}
但无法弄清楚如何在HTML文件中调用/使用它们 预先感谢
but couldn't work out how to call/use them in the HTML file Thanks in advance
推荐答案
@Cooper感谢您提出的解决方案，我还没有尝试过，但我会尝试一下.
@Cooper thanks for your proposed solution, I've not tried it yet, but I'll give it a go.
经过一番思考和研究，这篇Google文章非常有用 我认为将我想做的事情分解为不同的功能并通过包裹进行游戏可能会更容易.
After a bit of pondering and research, this Google article being really useful I thought it might be easier to split what I wanted to do into different functions and play pass the parcel.
function createHTML(data){
var t = HtmlService.createTemplateFromFile('template');
t.data = data;
sendEmail(t.evaluate().getContent());
}
function sendEmail(htmlBody){
var mailAddy = "email addy";
MailApp.sendEmail({
to:mailAddy,
subject: 'Test Email markup - ' + new Date(),
htmlBody: htmlBody,
});
}
function getData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn());
var values = range.getValues();
for (var i = 0; i < values.length; i++){
var data = values[i];
createHTML(data);
}
}
getdata函数从电子表格中获取我要使用的数据行，然后将其传递给createHTML，该HTML创建电子邮件的html正文. 这解决了我尝试通过Google代码或html遍历数据的阻止程序.
getdata function gets a line of data I want to use from a spreadsheet, then passes it to createHTML that creates the html body of the email. this solves my blocker of trying to loop through data either via google code or html.
然后将结果传递给sendEmail函数以发送电子邮件.
It then passes the results to sendEmail function to send the email.
改变游戏规则的是createHTML函数. 在.gs文件中声明t.data并将值以我知道的方式传递给html，这意味着我可以遍历数组. 如果有任何意义.
it was the createHTML function that was the game changer. Declaring t.data in the .gs file and passing the values to html in a way i know meant i could loop through my array. If that makes any sense.
那是我的解决方案，但是我当然乐于接受所有解决方案，并询问为什么x更好.
That was my solution, but of course I'm open to all ways of doing it and asking why x is better.
这篇关于如何遍历数组并将其传递给Google应用程序脚本中的HTML模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
