Google Spreadsheet脚本HTML内容赋值错误，求助批量自动发信问题

嘿，我帮你排查出了两个关键问题，这正是你脚本报错的根源，咱们一步步来解决：

1. HTML模板的语法错误（核心原因） #

你看你HTML代码里的这一行：

<? = userEmail?>

这里的<? =中间多了一个空格！Google Apps Script的HTML模板语法要求<?=是连在一起的（没有空格），这个小错误会导致模板无法被正确解析，进而在执行emailTemp.evaluate().getContent()时抛出错误。

把这一行改成：

<?= userEmail?>

其他的<?=语法都是正确的，只需要修正这一处就行。

2. 数组索引不匹配（隐藏问题） #

你定义的列索引变量：

var name = 1;
var email = 2;
var userEmail = 3;
var password = 4;

但getValues()返回的数组是0开始索引的！你的数据范围是B2:E，也就是B列对应数组的row[0]，C列是row[1]，D列是row[2]，E列是row[3]。如果你的表格里B列存的是客户姓名、C列是客户邮箱、D列是OneDrive账号、E列是密码，那你的索引变量完全写反了，会导致取错数据。

修正后的索引变量应该是：

var nameCol = 0;
var emailCol = 1;
var userEmailCol = 2;
var passwordCol = 3;

修正后的完整代码 #

Google Apps Script 代码 #

function sendMail(){
  // 修正列索引：B列(0)=客户姓名, C列(1)=客户邮箱, D列(2)=OneDrive账号, E列(3)=密码
  var nameCol = 0;
  var emailCol = 1;
  var userEmailCol = 2;
  var passwordCol = 3;
  
  var emailTemp = HtmlService.createTemplateFromFile("MailApp");
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy of OneDrive Cloud Storage");
  // 用更清晰的方式获取数据范围：从第2行第2列(B2)开始，取最后一行-1行，4列(B-E)
  var data = ws.getRange(2, 2, ws.getLastRow()-1, 4).getValues();
  
  data.forEach(function(row){
    // 跳过空行，避免发送无效邮件
    if(row[emailCol] === "") return;
    
    emailTemp.name = row[nameCol];
    emailTemp.userEmail = row[userEmailCol];
    emailTemp.password = row[passwordCol];
    emailTemp.email = row[emailCol];
    
    try {
      var htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(
        row[emailCol],
        "OneDrive 5TB Storage",
        "Your email doesn't support HTML.",
        {name: "AutoMail by Shovon", htmlBody: htmlMessage}
      );
      console.log("邮件已成功发送至：" + row[emailCol]);
    } catch(e) {
      // 捕获错误，避免单个邮件失败导致整个脚本中断
      console.error("发送邮件失败：" + row[emailCol] + "，错误详情：" + e.message);
    }
  });
}

修正后的HTML模板代码 #

<html>
<head>
<base target="_top">
</head>
<body>
<p>Dear <b><?= name ?>,</b></p>
<p>I received your <b>OneDrive 5TB Cloud Storage</b> request yesterday. I'm really very sorry for being so late as I had some personal things to do.</p>
<p>But the good news is I've successfully created the following <b>OneDrive Business Account</b> for you: </p>
<p>User email: <b><u><?= userEmail?></u></b><br>
Password: <b><?= password?></b></p>
<p>Please login to this <b>Microsoft</b> account as soon as possible and set up 2 steps verification method by using your own email address(e.g. <b><u><?= email ?></u></b>).</p>
<p>To login <b><u><a href="https://login.live.com/login.srf?wa=wsignin1.0&amp;rpsnv=13&amp;ct=1590813490&amp;rver=7.0.6738.0&amp;wp=MBI_SSL&amp;wreply=https:%2F%2Faccount.microsoft.com%2Fauth%2Fcomplete-signin%3Fru%3Dhttps%253A%252F%252Faccount.microsoft.com%252F%253Frefp%253Dsignedout-index%2526refd%253Dwww.bing.com&amp;lc=1033&amp;id=292666&amp;lw=1&amp;fl=easi2">click here</a></u></b>.</p>
<p>Yours,<br><b>Md. Shahariar Shovon</b></p>
</body>
</html>

最后提醒 #

• 确认你的表格列顺序和代码里的索引对应：B列=姓名，C列=客户邮箱，D列=OneDrive账号，E列=密码
• 第一次运行脚本时，需要授权Gmail和Spreadsheet的访问权限，按照提示操作即可
• 建议先找一行测试数据运行，确认没问题后再批量发送，避免出错

内容的提问来源于stack exchange，提问作者Shovon Showrov