可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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

<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>

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.

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?

I did try passing vars into the sendEmail function, something like

sendEmail(firstName, varA, varB) {    
    // code here
}

but couldn't work out how to call/use them in the HTML file Thanks in advance

回答1:

Loop through data and passing it to HTML

exampl1.gs:

function getData1(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet48')
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var html='<style>td,th{border:1px solid #000;}</style><table>';
  if(vA.length>0){
    for(var i=0;i<vA.length;i++){
      html+='<tr>';
      for(var j=0;j<vA[i].length;j++){
        if(i==0){
          html+=Utilities.formatString('<th>%s</th>', vA[i][j]);
        }else{
          html+=Utilities.formatString('<td>%s</td>', vA[i][j]);
        }
      }
    }
    html+='<table>';
  }
  return html;
}

function showExample1Dialog(){
  var userInterface=HtmlService.createTemplateFromFile('example1').evaluate();
  SpreadsheetApp.getUi().showModelessDialog(userInterface, "Example1");
}

css1.html:

<style>
body {background-color:#ffffff;}
input{padding:2px;margin:2px;}
</style>

script1.html:

<script>
  $(function(){
    google.script.run
    .withSuccessHandler(function(hl){
      document.getElementById('table').innerHTML=hl;
    })
    .getData1();
  });
  console.log('My Code');
</script>

res1.html:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

example1.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('res1') ?>
    <?!= include('css1') ?>
  </head>
  <body>
   <div id="table"></div>

   <?!= include('script1') ?>
  </body>
</html>

回答2:

@Cooper thanks for your proposed solution, I've not tried it yet, but I'll give it a go.

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 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.

It then passes the results to sendEmail function to send the email.

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.

That was my solution, but of course I'm open to all ways of doing it and asking why x is better.