Google Apps Script统计列值及邮件添加超链接实现方案

实现需求 #

• 无需读取单元格计数结果，直接在脚本内统计指定列的有效值数量，用于确定遍历数据的行数
• 邮件正文中将指定URL展示为可点击的超链接文本，而非直接显示原始URL

初始版本脚本 #

function sendEmails() {
  // 填入工作表名称
  var sheetname = 'CFS Open Cases Report' 
  var counter_sheet = 'count of recipients'
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);

  // A1是统计A列行数的单元格，改为在脚本内直接统计A2:A范围的有效值
  var row_counter = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(counter_sheet).getRange("A2"); 
  var row_count = row_counter.getValue();
 

  var startRow = 2;  // 第一行待处理数据的行号
  var numRows = row_count;   // 待处理的总数据行数

  // 需要实现URL在邮件正文显示为可点击超链接
  var report_url = "https://google.com";

 
  // 读取A2:D范围的单元格
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // 读取范围内的每行数据
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var first_name = row[0]; // 第一列数据
    var emailAddress = row[3]; // 第四列数据
    
    // HTML格式邮件内容
    var msgHtml = 'message and' + report_url
    ; 
    // 取当前日期作为报告日期
    var report_date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
    var report_desc = "Report Name"
    var subject = report_date +' - '+ report_desc; 

            
    // 发送邮件
    MailApp.sendEmail(emailAddress, subject, msgHtml);
  }
}

第一次迭代修改后的脚本 #

按照需求完成行数统计、超链接拼接后的脚本如下：

function sendEmails() {
  var sheetname = 'Sheet1' // 填入存储收件人信息的工作表名称
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var row_count = sheet.getRange("A2:A" + sheet.getLastRow()).getValues().flat().filter(String).length; // 统计A2:A范围的有效行数
  var startRow = 2; // 第一行待处理数据的行号
  var numRows =　row_count; // 待处理的总数据行数
      
  var report_url = "www.google.com";    


  // 读取A2:D范围的单元格
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // 读取范围内的每行数据
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var first_name = row[0]; // 第一列数据
    var emailAddress = row[3]; // 第四列数据
    
    // HTML格式邮件内容
    var msgHtml = 'Hi ' + first_name +',' 
    + '<br/><br/>message here.'
    + '<br/><br/>more message here.'
    + '<br/><br/>and more: '+ '<a href="${report_url}">Go to Google</a>'
    + '<br/><br/>Kind Regards,'
    + '<br/><br/>my name'
    ; 
    // 取当前日期作为报告日期
    var report_date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
    var report_desc = "CFS Open Cases Report"
    var subject = report_date +' - '+ report_desc; 

    // 清除HTML标签，将br转换为换行，生成纯文本版本邮件内容
    var msgPlain = msgHtml.replace(/\<br\/\>/gi, '\n').replace(/(<([^>]+)>)/ig, ""); 
    
    // 发送邮件
    MailApp.sendEmail(emailAddress, subject, msgPlain);
  }
}

迭代后出现的问题 #

发送邮件时HTML格式无法正常渲染，超链接不生效，需要保留换行的同时正确渲染HTML内容，相关代码片段如下：

var msgHtml = 'Hi ' + first_name +',' 
    + '<br/><br/>second line.'
    + '<br/><br/>third line.'
    + '<br/><br/>fourth line ' + `<a href="${report_url}">Go to Google</a>`
    + '<br/><br/>fifth line,'
    + '<br/><br/>sixth line'
    ;    

    // 取当前日期作为报告日期
    var report_date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
    var report_desc = "CFS Open Cases Report"
    var subject = report_date +' - '+ report_desc; 

    // 清除HTML标签，将br转换为换行，生成纯文本版本邮件内容
    var msgPlain = msgHtml.replace(/\<br\/\>/gi, '\n').replace(/(<([^>]+)>)/ig, ""); 
    
    // 发送邮件
    MailApp.sendEmail(emailAddress, subject, msgHtml); 
  }

问题根因与最终修复方案 #

问题根源为MailApp.sendEmail方法的参数使用错误，错误用法为直接将HTML内容作为第三个参数传入：

MailApp.sendEmail(emailAddress, subject, msgHtml); 

MailApp.sendEmail方法的第三个参数默认是纯文本正文，要渲染HTML格式需要显式指定htmlBody字段，修改后的调用方式如下：

MailApp.sendEmail({to: emailAddress, subject: subject, htmlBody: msgHtml});

修改后功能即可正常运行，HTML格式、超链接、换行均可正常展示。

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