AppsScript发送HTML邮件报错：ReferenceError: stocks未定义

尝试用Google Apps Script从Google Sheet拉取数据，生成包含动态内容的HTML表格邮件，遇到以下错误：

ReferenceError: stocks is not defined eval eval doGet

卡在HTML模板中无法识别stocks变量的问题。

现有HTML代码 #

</tr>      
               <? for(var i = 0; i < stocks.length; i++) { ?>
                <tr style="height:66pt">
                    <td style="border-width:0.984375pt 0.984375pt 0.984375pt 2.25pt;border-style:solid;border-color:rgb(183,183,183);vertical-align:top;padding:5pt;overflow:hidden">
                        <p dir="ltr" style="line-height:2.0736;margin-top:0pt;margin-bottom:0pt"><span style="font-size:8pt;font-family:Arial;color:rgb(67,67,67);font-weight:700;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">Business Group</span></p>
                    </td>
                    <td style="border-width:0.984375pt;border-style:solid;border-color:rgb(183,183,183);vertical-align:top;padding:5pt;overflow:hidden">
                        <p dir="ltr" style="line-height:2.0736;margin-top:0pt;margin-bottom:0pt"><span style="font-size:8pt;font-family:Arial;color:rgb(67,67,67);font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">Which of these business groups is affected by this event: </span></p>
                        <p dir="ltr" style="line-height:2.0736;margin-top:0pt;margin-bottom:0pt"><span style="font-size:8pt;font-family:Arial;color:rgb(67,67,67);font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">Corporate Systems &amp; Applications, MITS, THPS, or TELUS International?</span></p>
                    </td>
                    <td style="border-width:0.984375pt 2.25pt 0.984375pt 0.984375pt;border-style:solid;border-color:rgb(183,183,183);vertical-align:top;padding:5pt;overflow:hidden">
                        <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap"><?= stocks[i].ROW1 ?><
                    </td>
                </tr>
                <? } ?>
            </tbody>
        </table>
    </div>
  
</div>

现有JS代码 #

function doGet(stockData) {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template2.html");
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}


function getEmailText(stockData) {
  var text = "";
  stockData.forEach(function(stock) {
  });
  return text;
}

function sendEmail() {
  var stockData = getData();
  var htmlBody = doGet(stockData);
  
  MailApp.sendEmail({
    to: "sample@email.com",
    subject: "Stock update",
    htmlBody: htmlBody
  });
}

function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("TEST").getRange("TEST").getValues();
  values.shift(); //remove headers
  var stocks = [];
  values.forEach(function(value) {
    var stock = {};

    stocks.ROW1 = value[0];
    stocks.ROW2 = value[1];
    stocks.ROW3 = value[2];
    stocks.push(stock);

  })
 // Logger.log(JSON.stringify(stocks))
  return stocks;
}

解决方案 #

1. 给HTML模板绑定stocks变量 #

doGet函数中需要将传入的stockData赋值给模板的stocks属性，这样HTML模板才能识别该变量：

function doGet(stockData) {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template2.html");
  htmlTemplate.stocks = stockData; // 关键：将数据传递给模板变量
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}

2. 修复数据对象的赋值错误 #

getData函数中错误地将属性绑定到数组stocks而非单个stock对象，导致数组中的对象为空。修正后：

function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("TEST").getRange("TEST").getValues();
  values.shift(); // 移除表头
  var stocks = [];
  values.forEach(function(value) {
    var stock = {};
    // 将属性赋值给单个stock对象
    stock.ROW1 = value[0];
    stock.ROW2 = value[1];
    stock.ROW3 = value[2];
    stocks.push(stock);
  })
  return stocks;
}

3. 修正HTML中的语法错误 #

HTML代码最后一行的<?= stocks[i].ROW1 ?><多了一个多余的<，应改为：

<?= stocks[i].ROW1 ?>

内容的提问来源于stack exchange，提问作者Tina Tran