仅通过电子邮件发送Google表格中的新行 [英] Email only new rows in Google sheets

问题描述

我正在编辑[发送一封电子邮件，其中包含电子表格中所有新行的值（Google脚本/ GAS）。该功能在更新工作表的范围内一直有效，但是发送的电子邮件会发送所有行的信息。我希望它仅从尚未标记为'email_fwd'的行发送电子邮件。

I'm editing the [Send single email with values from all new rows in a spreadsheet (Google Script / GAS). The function works as far as updating the Sheet, but the e-mail that is sent sends the info for all rows. I'd like it to only send e-mails from rows that have not yet been labeled 'email_fwd'.

function sendEmail() {

 //setup function
 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var StartRow = 1;
 var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,17);
 var AllValues = WholeRange.getValues();

 var message = "";
 //iterate loop
 for (i in AllValues) {

 //set current row
 var CurrentRow = AllValues[i];

 //define column to check if sent
 var EmailSent = CurrentRow[17];

 //if row has been sent, then continue to next iteration
 if (EmailSent == "email_fwd") 
     continue;

 //set HTML template for information
  message +=
      "<p><b>E-mail: </b>" + CurrentRow[1] + "</p>" +
      "<p><b>Days: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>First Name: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Last Name: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Address: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>City: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>State: </b>" + CurrentRow[7] + "</p>" +
      "<p><b>Phone: </b>" + CurrentRow[8] + "</p>" +
      "<p><b>Country: </b>" + CurrentRow[9] + "</p>" + 
      "<p><b>Phone: </b>" + CurrentRow[10] + "</p>" + 
      "<p><b>Place: </b>" + CurrentRow[11] + "</p>" + 
      "<p><b>Rank: </b>" + CurrentRow[12] + "</p>" + 
      "<p><b>Emergency Contact: </b>" + CurrentRow[13] + "</p>" + 
      "<p><b>Emergency Contact Phone: </b>" + CurrentRow[14] + "</p>" +
      "<p><b>Questions & Comments: </b>" + CurrentRow[15] + "</p><br><br>";

  //set the row to look at
  var setRow = parseInt(i) + StartRow;

  //mark row as "sent"
  ActiveSheet.getRange(setRow, 17).setValue("email_fwd");
}

 //define who to send grants to 
 var SendTo = "[e-mail]"; // + "," + "[e-mail]";

 //set subject line
  var Subject = "RE: Registrations";


  //send the actual email  
  MailApp.sendEmail({
      to: SendTo,
      cc: "",
      subject: Subject,
      htmlBody: message,
});
}

我想我只需要重置 StartRow =没有找到email_fwd 的第一行，但是我不确定该怎么做。没什么帮助。

I think I just need to reset the StartRow = FIRST ROW THAT IS NOT FOUND WITH email_fwd but I'm not sure how to do that. Little help.

推荐答案

我最终从 https://developers.google.com/apps-script/articles/sending_emails 并带有错字修补程序 ActiveSheet

I ended up doing this from https://developers.google.com/apps-script/articles/sending_emails with the typo fix ActiveSheet for third text line from the bottom.

// This constant is written in column C for rows for which an email
// has been sent successfully.

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";

      MailApp.sendEmail(emailAddress, subject, message);
      **ActiveSheet**.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

这篇关于仅通过电子邮件发送Google表格中的新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！