Google Spreadsheets updating A Scripts with Multiple Tabs

Question

I have found a few threads with how to update the script but I cant get it to work with the limit knowledge I have with Scripts

I have upto about 6 Tabs ,Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6 that I want to send emails up of but at this stage it only looks at the first sheet. Can someone let me know how to update this please

Thanks Jarrad

function sendEmails() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var range = sheet.getRange(1, 2);  // Fetch the range of cells B1:B1
   var subject = range.getValues();   // Fetch value for subject line from above range
   var range = sheet.getRange(1, 9);  // Fetch the range of cells I1:I1
   var numRows = range.getValues();   // Fetch value for number of emails from above range
   var startRow = 4;                  // First row of data to process
   var dataRange = sheet.getRange(startRow, 1, numRows,9 ) // Fetch the range of cells A4:I_
   var data = dataRange.getValues();  // Fetch values for each row in the Range.
   for (i in data) {
      var row = data[i];
      var emailAddress = row[1];      // Second column
      var message = row[8];           // Ninth column
      MailApp.sendEmail(emailAddress, subject, message);
   }
}

Hi Cooper thanks for the info but im only just starting out with scripts I want to start with the basics for now and ill work out form there. I cant picture wan you are saying I know its simple info but I cant work it out just yet. Do I remove this line var sheet = SpreadsheetApp.getActiveSheet(); and replace with. I get errors on line var range line. I need to see it in full to try and understand what Im looking at. Sorry for the basic question

var sheets=['Sheet1','Sheet2','Sheet3','Sheet4']
  for(var i=0;i<sheets.length;i++)



function sendEmails() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var range = sheet.getRange(1, 2);  // Fetch the range of cells B1:B1
   var subject = range.getValues();   // Fetch value for subject line from above range
   var range = sheet.getRange(1, 9);  // Fetch the range of cells I1:I1
   var numRows = range.getValues();   // Fetch value for number of emails from above range
   var startRow = 4;                  // First row of data to process
   var dataRange = sheet.getRange(startRow, 1, numRows,9 ) // Fetch the range of cells A4:I_
   var data = dataRange.getValues();  // Fetch values for each row in the Range.
   for (i in data) {
      var row = data[i];
      var emailAddress = row[1];      // Second column
      var message = row[8];           // Ninth column
      MailApp.sendEmail(emailAddress, subject, message);

Cooper Cooper · Accepted Answer · 2017-06-26T16:17:51

You'll probably want to use something like this

function sendEmails()
{
  var mySheets=['Telstra out of date','Optus out of date','Vodafone out of date','Drivers Licence out of date'];
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var allSheets=ss.getSheets();
  for(var i=0;i<allSheets.length;i++)
  { 
    var sht=allSheets[i]; 
    if(mySheets.indexOf(sht.getName())>-1)//if you have sheets you don't want to send emails from
    {
      var rng=sht.getDataRange();
      var rngA=rng.getValues();
      var subject = rngA[0][1];//B1
      for(var j=3;j<rngA.length;j++)//start row was 4
      {
        var emailAddress = rngA[j][1];//col 2
        var message = String(rngA[3][2]) + String(rngA[j][3]);//col 3
        if(emailAddress)
        {
          MailApp.sendEmail(emailAddress, subject, message);
          //Logger.log('EmailAddress: ' + emailAddress + ' Subject: ' + subject + ' Message: ' + message);
        }
      }
    }
  }
}

Google Spreadsheets updating A Scripts with Multiple Tabs

2 Answers

Complete Email Sending and Archiving Example

SendingEmails.gs

Sample Sheet

htmlToBody.html

Utility.gs

Code.gs