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 · Accepted Answer · 2015-02-12 17:31:40Z

1

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);
  }

}

answered Feb 12, 2015 at 17:31

Amit Agarwal

11.3k1 gold badge35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Drew Over a year ago

Thanks Amit. This seems like it's going to work just fine but I'm having a bit of an issue implementing it. When I run the script it is saying the email address is invalid (the actual script I'm using is much more complicated than the example here) but could that be because it is no longer starting at 'Row 2'? Why else would that be? Nothing had changed from before your suggestion was added. Again- I think this is going to work wonderfully but there's just a bit of a hiccup on my end of not knowing how to correctly implement. Thanks!

Drew Over a year ago

UPDATE: Works perfectly!! I hadn't copied everything over so was having issues (duh) but your script is great. Thanks again!

Collectives™ on Stack Overflow

Run Google Script Through Last Spreadsheet Row Only?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related