CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsautomation
Mike Solty
Mike Solty

Reputation: 21

Get Google Emailer Script To Run For A Specific Set of Tabs

I have an emailer script that I would like to run for specified tabs and also make sure it skips any rows with blank values as it currently seems to break down as soon as it hits a blank row.

I have successfully built the script to work for one tab, but it seems to break down as soon as I try and get it to work for multiple tabs.

function reminders() {

 var ss = SpreadsheetApp.openById("SHEET-ID");
 var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName(("sheet_1","sheet_2","sheet_3")));
 var editedCell = sheet.getActiveRange().getColumnIndex();
 var dataRange = sheet.getDataRange();
 var data = dataRange.getValues();
 var text1 = 'Insert body of email here';
 for (var i = 1; i < data.length; i++)
   (function(val) {
     var row = data[i];
     var recipient = row[4];
     var name = row[2];
     var replyto = '[email protected]';
     var body = 'Dear' + ' ' + name + ',' + '\n\n' + text1;
     var subject = 'Hello World';
     GmailApp.sendEmail(recipient, subject, body, {from:'[email protected]', replyto:'[email protected]'});
   })(i);
}

This currently works for sheet_1 but does not work for sheet_2 or sheet_3.

Does anyone have any suggestions for how I can improve this script to send to multiple sheets and also skip blank rows?

Upvotes: 0

Views: 42

Answers (1)

Tanaike
Tanaike

Reputation: 201358

  • You want to send emails using the values of column "C" and "E" from the Spreadsheet.
  • You want to run your script for the sheet names of "sheet_1", "sheet_2" and "sheet_3".
  • You want to skip the rows which have no values of the column "C" and/or "E".

If my understanding is correct, how about this modification?

Modification points:

  • It seems that when var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName(("sheet_1","sheet_2","sheet_3"))); is run, only "sheet_3" is retrieved.
    • In your case, I think that setActiveSheet() might not be required to be used.
  • Number of arguments of getSheetByName(("sheet_1","sheet_2","sheet_3")) is one.
    • When you want to use multiple sheets, please use each sheet name in the loop.
  • var editedCell = sheet.getActiveRange().getColumnIndex(); is not used in your script.
  • val of (function(val) { is not used in your script.
  • When you want to skip the empty rows, please put the if statement before GmailApp.sendEmail(). By this, the error is removed at GmailApp.sendEmail().

When above points are reflected to your script, it becomes as follows. Please think of this as just one of several answers.

Modified script:

Please copy and paste the following modified script to the script editor.

function reminders() {
  var sheets = ["sheet_1","sheet_2","sheet_3"]; // Please set the sheet names here.

  var ss = SpreadsheetApp.openById("SHEET-ID");
  for (var s = 0; s < sheets.length; s++) {
    var sheet = ss.getSheetByName(sheets[s]);
    var dataRange = sheet.getDataRange();
    var data = dataRange.getValues();
    var text1 = 'Insert body of email here';
    for (var i = 1; i < data.length; i++) {
      var row = data[i];
      var recipient = row[4];
      var name = row[2];
      if (!recipient || !name) continue; // Here, the empty rows are skipped.
      var replyto = '[email protected]';
      var body = 'Dear' + ' ' + name + ',' + '\n\n' + text1;
      var subject = 'Hello World';
      GmailApp.sendEmail(recipient, subject, body, {from:'[email protected]', replyto:'[email protected]'});
    }
  }
}

Note:

  • In this modified script, when "recipient" or "name" are not existing, the row is skipped.
  • When you send a lot of emails, please be careful the quotas of "Email read/write". You can see it at here.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 1

Related Questions