首页 > 解决方案 > Gmail 两次发送电子邮件

问题描述

我在网上查看过,试图解决这个问题,Gmail 通过 Apps 脚本发送两封电子邮件,但每种情况都不同。我的脚本发送两封电子邮件,见下文;

空白电子邮件

完成的电子邮件

任务表

我不确定我哪里出错了,有人可以帮忙或发送资源链接来修复它吗?:)

function EODReportEmail() { 

 //Skip week-end
var day = new Date();
if (day.getDay()>5 || day.getDay()==0) {
  return;
}

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Tasks");        // Use data from EOD Email Tasks sheet
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information");  // Use data from EOD Email Information sheet
var startRow = 2;                                                                            // First row of data to process
var numRows = sheet1.getLastRow();                                                           // Number of rows to process
var lastColumn = sheet1.getLastColumn();                                                     // Last column
var dataRange = sheet1.getRange(startRow, 1, numRows, lastColumn)                            // Fetch the data range of the active sheet
var data = dataRange.getValues();                                                            // Fetch values for each row in the range

var recipient = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("B3").getValue();          // Email address report will be sent to

var subject = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("C3").getValue();            // Subject heading of email e.g. Shea Murphy - EOD Email

var footer1 = "<b>" + sheet2.getRange("D3").getValue() + "</b>";                                  // Name of Person
var footer2 = sheet2.getRange("D4").getValue()                                                    // Grade of Person
var footer3 = sheet2.getRange("D5").getValue();                                                   // Mobile Number
var footer4 = sheet2.getRange("D6").getValue();                                                   // Email address

var footer = footer1 + "<br>" + footer2 + "<br>" + footer3 + "<br>" + footer4; // Work through each row in the spreadsheet

for (var i = 0; i < data.length; ++i) {
var row = data[i];  // Assign each column a variable
var Point1 = row[0];              // Col A: 1st point to be included in email
var Point2 = row[1];              // Col B: 2nd point to be included in email
var Point3 = row[2];              // Col C: 3rd point to be included in email
var Point4 = row[3];              // Col D: 4th point to be included in email
var Point5 = row[4];              // Col E: 5th point to be included in email
var Point6 = row[5];              // Col E: 6th point to be included in email
var Point7 = row[6];              // Col E: 7th point to be included in email
var Point8 = row[7];              // Col E: 8th point to be included in email
var Point9 = row[8];              // Col E: 9th point to be included in email
var Point10 = row[9];             // Col E: 10th point to be included in email

var Num1 = "<b>1. </b>"           // Number variables for email points
var Num2 = "<b>2. </b>"
var Num3 = "<b>3. </b>"
var Num4 = "<b>4. </b>"
var Num5 = "<b>5. </b>"
var Num6 = "<b>6. </b>"
var Num7 = "<b>7. </b>"
var Num8 = "<b>8. </b>"
var Num9 = "<b>9. </b>"
var Num10 = "<b>10. </b>"

var Task1 = Num1 + Point1;         // Note 1 to be inlcuded in the email
var Task2 = Num2 + Point2;         // Note 2 to be inlcuded in the email
var Task3 = Num3 + Point3;         // Note 3 to be inlcuded in the email
var Task4 = Num4 + Point4;         // Note 4 to be inlcuded in the email
var Task5 = Num5 + Point5;         // Note 5 to be inlcuded in the email
var Task6 = Num6 + Point6;         // Note 6 to be inlcuded in the email
var Task7 = Num7 + Point7;         // Note 7 to be inlcuded in the email
var Task8 = Num8 + Point8;         // Note 8 to be inlcuded in the email
var Task9 = Num9 + Point9;         // Note 9 to be inlcuded in the email
var Task10 = Num10 + Point10;      // Note 10 to be inlcuded in the email

if (Point1 == " ") {Task1 == " "};
if (Point2 == " ") {Task2 == " "};
if (Point3 == " ") {Task3 == " "};
if (Point4 == " ") {Task4 == " "};
if (Point5 == " ") {Task5 == " "};
if (Point6 == " ") {Task6 == " "};
if (Point7 == " ") {Task7 == " "};
if (Point8 == " ") {Task8 == " "};
if (Point9 == " ") {Task9 == " "};
if (Point10 == " ") {Task10 == " "};  // Build the email message
var emailBody =  '<b style="font-family:georgia;font-size:18px;font-style:italic; color: #D04A02";>EOD Report</b>';
      emailBody += '<p>Please see what I have worked on today below:<p>';
      emailBody += '<dl><dd>'+ Task1 +'</dd>';
      emailBody += '<dl><dd>'+ Task2 +'</dd>';
      emailBody += '<dl><dd>'+ Task3 +'</dd>'; 
      emailBody += '<dl><dd>'+ Task4 +'</dd>';
      emailBody += '<dl><dd>'+ Task5 +'</dd>';
      emailBody += '<dl><dd>'+ Task6 +'</dd>';
      emailBody += '<dl><dd>'+ Task7 +'</dd>';
      emailBody += '<dl><dd>'+ Task8 +'</dd>';
      emailBody += '<dl><dd>'+ Task9 +'</dd>';
      emailBody += '<dl><dd>'+ Task10 +'</dd>';

      emailBody += '<p>Let me know if you have any questions.<p>';
      emailBody += '<p>Kind regards,<br>'
      emailBody += '</n> -- <br>'
      emailBody += footer


// Create the email draft
  GmailApp.sendEmail(
    recipient,          // Recipients
    subject,            // Subject
    ' ',                // Body
    {
    htmlBody: emailBody,    // Options: Body (HTML)
  
            
    }

我已经制作了这个功能,它可以填写任务,并将其分配给一个数字,但我的主要问题是它正在复制电子邮件;一个填满了所有的任务,一个什么都没有填。

有人可以建议如何解决这个问题吗?

标签: google-apps-scriptgoogle-sheetsgmail

解决方案


var dataRange = sheet1.getRange(startRow, 1, numRows-1, lastColumn)    

该脚本运行良好,只需要添加 -1,谢谢 Cooper!

编辑:

按照 Cooper 的建议进行定义会更合适numRows,既要保持变量名的意义又便于维护(startRow例如,如果发生更改,您不需要调整代码):

var numRows = sheet1.getLastRow() - startRow + 1;
var dataRange = sheet1.getRange(startRow, 1, numRows, lastColumn);

推荐阅读