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"});
         }

       }
   }

 };

Pierre-Marie Richard · Accepted Answer · 2019-06-20 07:11:27Z

2

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"});
    }
 };

edited Jun 20, 2019 at 7:11

answered Jun 20, 2019 at 7:00

Pierre-Marie Richard

2,0342 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pierre-Marie Richard Over a year ago

@Sriram It's because you don't store the chart before sending the mail. You should try to store the charts in a array and loop on your mail sending method to add all the charts needed

Sriram Over a year ago

yeah i think i understand. Thank you very much

Sriram Over a year ago

Hey hi. How can I send multiple spreadsheets data in one email?

Pierre-Marie Richard Over a year ago

@Sriram You should put your data in a array and do the mail part once you exit the statement which go through the files

ziganotschka · Accepted Answer · 2019-06-20 10:48:54Z

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"});
   }
 };

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related