javascript - Google 脚本 - 发送电子邮件到特定列的最后一行

Question

我正在使用 Google 脚本发送自动电子邮件。我想选择电子表格的最后一个事件行，而不是选择所有行。我不明白仅选择最后一行的具体细节。

这是我现有的 JavaScript

// This constant is written in column O for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 14;   // Instead of the number of rows to process, I would need to select Only the last filled row
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 15) // Instead of the number of rows to process, I would need to select Only the last filled row
  // 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 name = row[2]; // Third column
    var surname = row[3]; // Fourth ...
    var salesRepEmail = row[4]; // Fifth..
    var qualityAnalystEmail = "xxx@yyy.zz"
    var customerEmail = row[5]; // Sixth...
    var websiteURL = row[6]; 
    var solution1 = row[7];
    var solution2 = row[8];
    var solution3 = row[9];
    var toResolve1 = row[10];
    var toResolve2 = row[11];
    var toResolve3 = row[12];
    var checkDate = row[13];
    var message = 'Bonjour '+ name + ' ' + surname + ', ' + 'xxxxxxx';
    var emailSent = row[14];     // Third column
    if (emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
      var subject = "Votre Audit Gratuit Pour Le Site " +websiteURL;
      MailApp.sendEmail(customerEmail, subject, message, {
        cc: "",
        bcc: qualityAnalystEmail + ", " + salesRepEmail
      });
      sheet.getRange(startRow + i, 15).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Answer 1

这并不是一个真正的函数。不要尝试运行它，只需阅读它。它包含获取范围最后一行的几种不同方法。

function lastRow()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  //if you want the last row of a range selected by the user, which  is normally call the activeRange
  var arng=sht.getActiveRange();
  var arngA=arng.getValues();
  //the lastRow of data for the activeRange is
  var lastARow=arng[arng.length-1];

  //if you want the last row of data on a sheet then you can use
  var drng=sht.getDataRange();
  var drngA=drng.getValues();
  var lastDRow=drngA[drngA.length-1];

  //make up a range of your own
  var murng=sht.getRange('A3:Z9');
  //or the same range could written as
  var murng=sht.getRange(3,1,7,26);
  //both cases have the same values
  var murngA=murng.getValues();
  //and in both case the lastRow is
  var lastMuRow=murngA[murngA.length-1];
}