Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

javascript - How to loop through an array and pass it to a HTML template in Google app scripts

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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>

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...