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 = "[email protected]" 
 
    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]; 
}