如何使用Google Script实现邮件中单行展示单条设备的所有列数据

解决方案 #

你当前代码的问题出在reduce阶段，把每行的5个设备字段拆解成了独立的数组项，所以后续join换行的时候每个字段单独占一行。只要修改数据存储和拼接逻辑即可实现需求，修改后代码如下：

function sendEmails() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1; // First row of data to process
  var numRows = 6; // Number of rows to process
  // Fetch the range of cells A1:P300
  var dataRange = sheet.getRange(startRow, 1, numRows, 13);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var dayofemail = data[0][12];
  data.reduce((m, [qua_num,manufacturer,model,serial,descrip,,,,daysPastCal,emailAddress]) => {
    if (daysPastCal > -14) {
      // 每行设备字段拼接为一个字符串，padEnd固定长度实现对齐，可按需调整数值
      const deviceLine = `${qua_num.toString().padEnd(10)}${manufacturer.toString().padEnd(12)}${model.toString().padEnd(12)}${serial.toString().padEnd(12)}${descrip}`
      m.set(emailAddress, m.has(emailAddress) ? [...m.get(emailAddress), deviceLine] : [deviceLine]);
    }
    return m;
  }, new Map())
  .forEach((deviceLines, emailAddress) => {
    // 设备行之间用换行分隔，和后面的提示语之间空一行
    var message = deviceLines.join('\n') + '\n\nThe above equipment are approaching due date or out of cal.\nPlease check with QA for replacement or sending the equipment out for re-calibration.';
    var subject = 'AUTOMATED ALERT: CHECK EQUIPMENT CALIBRATION ';
    MailApp.sendEmail(emailAddress, subject + dayofemail, message);
  });
}

如果不需要严格对齐，仅要求同设备的字段在同一行，把deviceLine的拼接逻辑简化为const deviceLine = [qua_num,manufacturer,model,serial,descrip].join(' ')即可。

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