Emailing last row from Google Sheets - Automatically find the new last row when data is added

Question

I have a spreadsheet where I have email addresses in column K and then data in columns A through D. I am trying to write a script that will email all email addresses in col K. The subject should be the last Row in Col C and the email Body should be the last row in col D

The Below is what I have but I have two issues. Whenever I need to add an email address to the list I will need to Adjust the script var dataRange = sheet.getRange("K4:K9"); to contain the new email address in K10. Is there a better way to do this?

Also I tried using getLastRow for the email Subject and Body and it works but it is just getting the last row in the dataRange. I have to update var dataRange2= sheet.getRange("A4:D6"); everyday a new line is added. I tried to make the dataRange larger but then it was grabbing the blank rows as the last row. Again I am not sure how to make this automated.

   /**
 * Sends emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3; // First row of data to process
  var numRows = 2; // Number of rows to process
  // Fetch the Email range of cells K4:K6
    var dataRange = sheet.getRange("K4:K9");
    var emailAddress = dataRange.getValues() // Email Col
    var dataRange2= sheet.getRange("A4:D6");
    var message = SpreadsheetApp.getActiveSheet().getRange("D" + dataRange2.getLastRow()).getValues(); // Fact Col
    var subject = SpreadsheetApp.getActiveSheet().getRange("C" + dataRange2.getLastRow()).getValues(); // Question Col 
    MailApp.sendEmail(emailAddress, subject, message);
}

Don't hard code the range argument, use the other signatures of getRange, namely the one that takes 4 parameters. It will work well with getLastRow(). — tehhowch

Alan Wells Alan Wells · Accepted Answer · 2018-07-30T16:17:39

Use a "for" loop to loop through every row with data. If you want to send an email for every row that has an email address.

Run the code and then VIEW the LOGS. From the code editor, click the View menu, and click Logs.

For testing purposes, you may want to comment out the statement that sends the emails, run the code, and look at the logs.

function sendEmails2() {
  var data,dataRange,emailAddress,i,L,lastRow,message,sheet,startRow,subject,thisRow;

  sheet = SpreadsheetApp.getActiveSheet();
  lastRow = sheet.getLastRow();

  startRow = 3; // First row of data to process

  dataRange = sheet.getRange(startRow, 1, lastRow - startRow, 11);//Start in column 1 and get 11 columns of data
  data = dataRange.getValues();//Get a 2D array of data

  L = data.length;//The number of inner arrays in the 2D array - which is
                 //the number of rows in the range
  for (i=0;i<L;i++) {
    thisRow = data[i];//Get one inner array of data which represents one row

    subject = thisRow[2];//The subject is in column 3 which is index 2
    message = thisRow[3];//The message is in column 4 which is index 3

    emailAddress = thisRow[10];

    Logger.log("subject: " + subject)
    Logger.log("message: " + message)
    Logger.log("subject: " + subject)

    if (!emailAddress) {
      continue;
    }

    MailApp.sendEmail(emailAddress, subject, message);
  }

}

Emailing last row from Google Sheets - Automatically find the new last row when data is added

1 Answers