How to send multiple sheets range data in one email google app script?

Question

There are two spreadsheets in a Google Drive folder. How can I send data from multiple sheets in one email

So far, when I execute my script, it will send 3 emails as it contains 3 sheets in two workbooks. One workbook contain two sheets. I want the two sheets to be sent in one email. Now it is sending in two separate emails.

function getAllSheets(){
var file, files = 
DriveApp.getFolderById("1JTtvZNc41D575Ubu3NbzVYTh9tX8KhGj").getFiles();
while (files.hasNext()) {
file = files.next();
var activeSpreadSheet = SpreadsheetApp.open(file);
var sheets = activeSpreadSheet.getSheets();

//loop through sheets to look for value
for (var i in sheets) {


var sheet = sheets[i]
var data = sheet.getDataRange().getValues();

var resultArr=[];
var xTitle = 'Part Numbers'; // XAxis Title
var yTitle = 'Quantity'; // YAxis Title

//To Loop through the whole data Rows
for(var i=1;i<data.length;i++)
  {
    //Takes columns from L to S (To loop through the Columns)
    for(var j=11;j<19;j++)
      {
        var cellVal=data[i][j];
        Logger.log(cellVal)
        if(cellVal>0)
          {
             //Stores the Part No, Month Header Value of the Column, Cell 
              Value which is greater then 0
              resultArr.push([data[i][0],data[0][j],cellVal])
          }
      }
   }
 if(resultArr.length>0)
   {
      var subject = 'Range exceeded Alert' + "" + sheet.getName();

      //Creates a body through the obtained values
       var body='';
       for(var m=0;m<resultArr.length;m++)
          {
            body+="For Part No "+resultArr[m][0].toString()+" and Month 
            "+resultArr[m][1]
            .toString()+", Value is "+resultArr[m][2].toString()+"<br>";
          }


    //send email

    MailApp.sendEmail({to:"[email protected]",subject:subject, 
    htmlBody:body 
    + " Chart! <br> <img src='cid:chartImg'> ! <br> Done"});
         }

       }
   }

 };

Answer 1

You should take the mail sending code outside of the for statement, so the mail is send after you go through all the sheet oh a workbook.

The logic would be

Function{
    Get files
    Through files{
        Get sheets
        Through sheets{
            Get Data
            Write data in body
        }
        Send mail with data
    }
}

It would look like (not tested):

function getAllSheets(){
    var file, files = 
    DriveApp.getFolderById("1JTtvZNc41D575Ubu3NbzVYTh9tX8KhGj").getFiles();
    while (files.hasNext()) {
        file = files.next();
        var activeSpreadSheet = SpreadsheetApp.open(file);
        var sheets = activeSpreadSheet.getSheets();
        var body='';
        //loop through sheets to look for value
        for (var i in sheets) {
            var sheet = sheets[i]
            var data = sheet.getDataRange().getValues();

            var resultArr=[];
            var xTitle = 'Part Numbers'; // XAxis Title
            var yTitle = 'Quantity'; // YAxis Title

            //To Loop through the whole data Rows
            for(var i=1;i<data.length;i++){
                //Takes columns from L to S (To loop through the Columns)
                for(var j=11;j<19;j++){
                    var cellVal=data[i][j];
                    Logger.log(cellVal)
                    if(cellVal>0){
                         //Stores the Part No, Month Header Value of the Column, Cell 
                          Value which is greater then 0
                          resultArr.push([data[i][0],data[0][j],cellVal])
                    }
                }
            }
            if(resultArr.length>0){
                var subject = 'Range exceeded Alert' + "" + sheet.getName();

                //Creates a body through the obtained values

                for(var m=0;m<resultArr.length;m++){
                    body+="For Part No "+resultArr[m][0].toString()+" and Month 
                    "+resultArr[m][1]
                    .toString()+", Value is "+resultArr[m][2].toString()+"<br>";
                }
            }
        }
        //send email
        MailApp.sendEmail({to:"[email protected]",subject:subject, 
        htmlBody:body 
        + " Chart! <br> <img src='cid:chartImg'> ! <br> Done"});
    }
 };

Answer 2

If you want to attach information from both sheets to “body” and ”subject”, you have to declare var body BEFORE looping through the sheets and send the email AFTER looping. In this way you will gather information from both sheets and assign it to the variables “body” and “subject”.

I tested a code similar to Pierre-Marie Richard's, however, with the “subject” issue addressed as well. Adding the full payload for visibility purposes here the end result:

function getAllSheets(){
var file, files = DriveApp.getFolderById("1JTtvZNc41D575Ubu3NbzVYTh9tX8KhGj").getFiles();
while (files.hasNext()) {
file = files.next();
var URL=file.getUrl()
var parent=file.getParents()
Logger.log(parent)  
var activeSpreadSheet = SpreadsheetApp.open(file);
var sheets = activeSpreadSheet.getSheets();


// define body and subject before looping though the sheets, like this information of both sheets will be attached to the body

 var body='';
  var subject = 'Range exceeded Alert';


//loop through sheets to look for value
for (var i in sheets) {

var sheet = sheets[i]
var data = sheet.getDataRange().getValues();

var resultArr=[];
var xTitle = 'Part Numbers'; // XAxis Title
var yTitle = 'Quantity'; // YAxis Title


//To Loop through the whole data Rows
for(var i=1;i<data.length;i++)
  {
    //Takes columns from L to S (To loop through the Columns)
    for(var j=11;j<19;j++)
      {
        var cellVal=data[i][j];
        Logger.log(cellVal)
        if(cellVal>0)
          {
             //Stores the Part No, Month Header Value of the Column, Cell Value which is greater then 0         
              resultArr.push([data[i][0],data[0][j],cellVal])
          }
      }
   }
 if(resultArr.length>0)
    {
   // add the subject of each sheet here
      subject+= " "+'Range exceeded Alert' + "" + sheet.getName();
      //Creates a body through the obtained values
       for(var m=0;m<resultArr.length;m++)
          {
            body+="For Part No "+resultArr[m][0].toString()+" and Month"+resultArr[m][1]
            .toString()+", Value is "+resultArr[m][2].toString()+"<br>";
          }
     }
   }
        //send email after looping through the sheets, so that information of both sheets will be sent
      MailApp.sendEmail({to:"your email",subject:subject,            
    htmlBody:body 
    + " Chart! <br> <img src='cid:chartImg'> ! <br> Done"});
   }
 };