CODEWITHSUNDEEP
google-apps-script
Lukec
Lukec

Reputation: 73

Loop through rows that do and do not have data

I have a Sheet(1) that will look for rows on a different sheet(2) that contain data. if the rows in Sheet(2) have data, then their information will populate in Sheet(1). I need a loop to go through and take action on the lines in Sheet(1), and be able to skip over a number of blank rows to get to the next set of information.

I have a loop that will go through to the last row of data, but it goes through all lines with information until there is a blank row. I need something that will go through blank rows to continue looking for rows with data. 

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var lastRow = sheet.getLastRow(); // Number of rows to process
// Fetch the range of cells A2:B2
var dataRange = sheet.getRange(2, 1, lastRow-1, 6);
// Fetch values for each row in the Range.
var dataRangee = sheet.getRange("D2");
var qdate = dataRangee.getDisplayValues(); 
var dataRangeee = sheet.getRange("E2");
var ddate = dataRangeee.getDisplayValues();
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[2]; // Third column
var subject = 'Request';
var body = "Dear general partner,"
var file = DriveApp.getFileById('xxxxx');
var copy = row[5]; // Sixth column
GmailApp.sendEmail(emailAddress, subject, body, {cc: copy, attachments: 
[file]});
} 
}

This code is the one mentioned above that loops thru until it reaches a blank row.

Upvotes: 0

Views: 1207

Answers (1)

Cooper
Cooper

Reputation: 64032

Try this:

function sendEmails() {
  var sh=SpreadsheetApp.getActiveSheet();
  var data=sh.getRange(2,1,sh.getLastRow()-1,6).getValues();
  //var qdate=sh.getRange("D2").getDisplayValue(); //not used
  //var ddate=sh.getRange("E2").getDisplayValue(); //not used
  var file = DriveApp.getFileById('xxxxx');
  var subject = 'Request';
  var body = "Dear general partner," 
  for (var i=0;i<data.length;i++) {
    if(data[i][2] && data[i][5]) {//if email or copy are undefined just skip
      var emailAddress = data[i][2]; // Third column
      var copy = data[i][5]; // Sixth column
      GmailApp.sendEmail(emailAddress, subject, body, {cc: copy, attachments:[file]});
    }
  } 
}

You should reserve the use of this sort of loop for key/value objects for (i in data) { and you should always declare i with var i=

Upvotes: 1

Related Questions