在Google Apps脚本邮件合并中跳过空白电子邮件 [英] Skipping blank emails in Google Apps Script Mail Merge

问题描述

我使用这个简单邮件合并教程来帮助我创建邮件合并，向学生和他们的父母汇总完成的社区服务时间。他们的电子邮件地址位于Google表格文档中。该脚本一直运行，直到遇到空白单元格。然后它给了我一个错误，说无效的电子邮件：未定义，并使用send email命令引用脚本中的行。

我暂时解决了这个问题在电子表格本身中，通过将虚拟电子邮件插入任何空白单元格中，但我只想将其写入脚本以忽略空白单元格。

完整的代码在上面的链接。我已修改该代码，以便从表格中的三列电子邮件地址中绘制而不是一列。关键是这样的：

pre $函数sendEmails（）{


var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var dataSheet = ss.getSheets（）[0];
var dataRange = dataSheet.getRange（5，1，dataSheet.getMaxRows（） - 1，7）;

var templateSheet = ss.getSheets（）[1];
var emailTemplate = templateSheet.getRange（B7）。getValue（）;

//为每行数据创建一个JavaScript对象。
objects = getRowsData（dataSheet，dataRange）;

//对于每个行对象，从模板创建一个个性化的电子邮件并将
//发送给适当的人。
for（var i = 0; i< objects.length; ++ i）{
//获取一个行对象
var rowData = objects [i];

//生成个性化电子邮件。
//给定一个模板字符串，用
替换标记（例如$ {First Name}）//行对象中的对应值（例如rowData.firstName）。
var emailText = fillInTemplateFromObject（emailTemplate，rowData）;
var emailSubject = templateSheet.getRange（B6）。getValue（）;

MailApp.sendEmail（rowData.studentEmail，emailSubject，emailText）;
MailApp.sendEmail（rowData.parentaEmail，emailSubject，emailText）;
MailApp.sendEmail（rowData.parentbEmail，emailSubject，emailText）;

我需要添加什么才能跳过parentaEmail和parentbEmail列中的空白单元格？

解决方案

好吧，我现在已经开始工作了。感谢rGil让我走上正确的道路。以下是结束工作的结果：

  if（rowData.parentaEmail！= null）{
 MailApp.sendEmail（rowData。 parentaEmail，emailSubject，emailText）; 
} 
  

然后再次做同样的事情，但对于parentbEmail

I'm using this Simple Mail Merge Tutorial to help me create a mail merge to send students and their parents a summary of their completed community service hours. Their email addresses are in a Google Sheets doc. The script works until it hits a blank cell. Then it gives me an error that says "Invalid email: undefined" with a reference to the line in the script with the send email command.

I've temporarily solved this within the spreadsheet itself by having it plug in a dummy email into any blank cell, but I'd like to just write it into the script to ignore blank cells.

The full code is at the link above. I've modified that code to draw from three columns of email addresses in the sheet instead of one. The critical bit is this:

function sendEmails() {


var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(5, 1, dataSheet.getMaxRows() - 1, 7);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("B7").getValue();

  // Create one JavaScript object per row of data.
  objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];

// Generate a personalized email.
// Given a template string, replace markers (for instance ${"First Name"}) with
// the corresponding value in a row object (for instance rowData.firstName).
var emailText = fillInTemplateFromObject(emailTemplate, rowData);
var emailSubject = templateSheet.getRange("B6").getValue();

MailApp.sendEmail(rowData.studentEmail, emailSubject, emailText);
MailApp.sendEmail(rowData.parentaEmail, emailSubject, emailText);
MailApp.sendEmail(rowData.parentbEmail, emailSubject, emailText);
  } 

What do I need to add to skip blank cells in the parentaEmail and parentbEmail columns?

解决方案

Ok, I've got it working now. Thanks rGil for setting me on the right path. Here's what ended up working:

   if (rowData.parentaEmail != null) {
      MailApp.sendEmail(rowData.parentaEmail, emailSubject, emailText);
   }

and then do the same thing again but for parentbEmail

这篇关于在Google Apps脚本邮件合并中跳过空白电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！