时间戳

首页 > 解决方案 > 应用程序脚本 - 根据剩余天数发送电子邮件 - 电子邮件内容第 1 次和第 2 次 - 无法在已发送电子邮件中并排显示列

google-apps-script - 应用程序脚本 - 根据剩余天数发送电子邮件 - 电子邮件内容第 1 次和第 2 次 - 无法在已发送电子邮件中并排显示列

问题描述

@NaziA 在这里帮助我编写了这个脚本。

当即将到达单元格的特定日期（3天）时，我正在使用它从电子表格发送一封自动电子邮件。

作为电子邮件的内容，我收到一条消息、一个链接以及即将到达特定日期的第 1 列和第 2 列元素。

我的目标是在输出电子邮件中将第一列和第二列一起或并排显示，以便于比较。

这是我的输出电子邮件：

这是我的电子表格

这是我正在使用的代码：

function offboardingReminderV2() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set active sheet to first sheet
  spreadsheet.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
  // figure out what the last row is
  var lastRow = sheet.getLastRow();
  var startRow = 2;
  // grab all data from user to days left
  var range = sheet.getRange(startRow, 1, lastRow - startRow + 1, 12);
  var values = range.getValues();
  var users1 = [];
  var users2 = [];

  // loop all data per row
  values.forEach(function(row) {
    // if days left is 3
    if(row[11] == 3) {
      // add user if 3 days left
      users1.push(row[0]);
      users2.push(row[1]);
    }
  });

  // if users has elements
  if(users1) {
    // Formatted the message as html to look nicer
    var message = "<html><body></h1><p><b>The following user/s offboarding is due in 3 days</font2></h1><p>https://docs.google.com/spreadsheets/d/1QvJx7AaHT4cPGS6kvfH2Vatpn6jXo4gH1Mae8906ZFQ/edit#gid=372029014:</p>";
    // created bulleted list for list of users
    var emails = "<ul>";
    users1.forEach(function(user){
      emails = emails + "<li>" + user + "</li>";
    });
    
    emails += "</ul>";

  if(users2) {
    // created bulleted list for list of users
    var names = "<ul>";
    users2.forEach(function(user){
      names = names + "<li>" + user + "</li>";
    });
    
    emails += "</ul>";
    names += "</ul>"
    message = message + names + emails + "</body></html>";
    var flexmails = "email@example.com";
    MailApp.sendEmail(flexmails, "Reminder Offboarding Control", "", {htmlBody: message, noReply: true});
  }
}
}

谁能帮我解决这个问题？

标签： google-apps-scriptgoogle-sheetsspreadsheetgoogle-developer-tools

解决方案

我相信你的目标如下。

  • 您想<li>通过排列列将“A”和“B”列中的值放入标签中。

在这种情况下，我想修改if(users1) {}. 当你的脚本被修改后，它变成如下。

修改后的脚本：

从： 
  if(users1) {
    // Formatted the message as html to look nicer
    var message = "<html><body></h1><p><b>The following user/s offboarding is due in 3 days</font2></h1><p>https://docs.google.com/spreadsheets/d/1QvJx7AaHT4cPGS6kvfH2Vatpn6jXo4gH1Mae8906ZFQ/edit#gid=372029014:</p>";
    // created bulleted list for list of users
    var emails = "<ul>";
    users1.forEach(function(user){
      emails = emails + "<li>" + user + "</li>";
    });
    
    emails += "</ul>";

  if(users2) {
    // created bulleted list for list of users
    var names = "<ul>";
    users2.forEach(function(user){
      names = names + "<li>" + user + "</li>";
    });
    
    emails += "</ul>";
    names += "</ul>"
    message = message + names + emails + "</body></html>";
    var flexmails = "email@example.com";
    MailApp.sendEmail(flexmails, "Reminder Offboarding Control", "", {htmlBody: message, noReply: true});
  }
}
至： 
if (users1.length > 0 && users2.length > 0) {
  var message = "<html><body></h1><p><b>The following user/s offboarding is due in 3 days</font2></h1><p>https://docs.google.com/spreadsheets/d/###/edit#gid=372029014:</p>";
  var names = "<ul>";
  users2.forEach(function (user, i) {
    names += `<li>${user}, ${users1[i]}</li>`;
  });
  names += "</ul>"
  message = message + names + "</body></html>";
  var flexmails = "email@example.com";
  MailApp.sendEmail(flexmails, "Reminder Offboarding Control", "", { htmlBody: message, noReply: true });
}
  • 如果要替换列，请修改names += `<li>${user}, ${users1[i]}</li>`;为names += `<li>${users1[i]}, ${user}</li>`;.

推荐阅读