CODEWITHSUNDEEP
google-apps-script
Lukec
Lukec

Reputation: 73

Loop through varying amount of rows

I have a script that currently works, but it only works for x amount of rows that I have to enter.

I want this script to be more dynamic, and be able to loop through however many columns I have. My sheet has 4 columns of data that are incorporated into the sending of an email.

The list of emails and therefore the amount of rows will change periodically. I'd like to have the code be able to go through each row until it hits a blank one, rather than resetting the amount of rows it should cycle through.

I'm not sure what to try but I think it may be related to using ++ when identifying my data range.

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 3; // Number of rows to process
// Fetch the range of cells A2:B2
var dataRange = sheet.getRange(startRow, 1, numRows, 4);
// Fetch values for each row in the Range.
var dataRangee = sheet.getRange("D2");
var date = dataRangee.getDisplayValues(); 
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0]; // First column
var subject = 'ILPA Request';
var body = "Dear " + row[1] +   "\n \nIn order to comply with statutory 
requirements";
var file = DriveApp.getFileById('xxxx');
var copy = row[2]; // Third column

GmailApp.sendEmail(emailAddress, subject, body, {cc: copy, attachments: 
[file]});
}
}

This is the entire code, although I did remove the body portion. Right now I have to change the numRows number to get the code to cycle through more rows. Hoping to avoid using a concrete number and have the code run until it hits a blank row.

Upvotes: 0

Views: 69

Answers (1)

ross
ross

Reputation: 2774

Requirement:

Use a variable for the total number of rows in the sheet.

Solution:

Option 1: Use getDataRange() to get the data range for all of the data in the sheet.

var dataRange = sheet.getDataRange();

Option 2: Use getLastRow() as your number of rows then pass the result to your getRange() to get all of the populated rows in the sheet.

var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(2, 1, lastRow, 4);

Note: Using either of these solutions means you no longer need variables startRow and numRows.

References:

  1. getDataRange()
  2. getLastRow()
  3. getRange(row, column, numRows, numColumns)

Upvotes: 2

Related Questions