Run Google Script Through Last Spreadsheet Row Only?

Question

I'm running an email script which works fine, but I only want it to run through the last row with data in it. I don't need it or want it to run if there is no data obviously. How do I reflect that in the example script below?

For example, say there are only 3 rows of data in my sheet. I only need the script to run for those 3 rows but the code will run for 20 rows. The number of entries to process will vary by the day so I can't just set it at a set number.

Basically I'm looking to replace:

var numRows =20;// Number of rows to process

with some iteration of .getlastrow...I think?

Sample script for reference:

function sendEmails() {
  var sheet =SpreadsheetApp.getActiveSheet();

  var startRow =2;// First row of data to process
  var numRows =20;// Number of rows to process

  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow,1, numRows,2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  for(i in data){
    var row = data[i];
    var emailAddress = row[0];// First column
    var message = row[1];// Second column
    var subject ="Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
   }
}

Thanks for your help!

Amit Agarwal Amit Agarwal · Accepted Answer · 2015-02-12T17:31:40

You can use the getLastRow() method to get the index of the last populated row in the sheet. Or use getDataRange().getValues and it will only get the rows that have data.

function sendEmails(){

  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow =2;// First row of data to process
  var data = sheet.getDataRange().getValues();

  for(var i=startRow; i<data.length; i++){
    var row = data[i];
    var emailAddress = row[0];// First column
    var message = row[1];// Second column
    var subject ="Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }

}

Run Google Script Through Last Spreadsheet Row Only?

1 Answers