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

Question

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

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

function checkSales(){
var file, files = DriveApp.getFolderById(
"1QoHyZrhSwqNYaOfgWu7X8WnY-wj9KMRU").getFilesByType(MimeType.GOOGLE_SHEETS)
while (files.hasNext()) {
file = files.next();
Logger.log(file)
var activeSpreadSheet = SpreadsheetApp.open(file);
var sheets = activeSpreadSheet.getSheets();
var body='';

//loop through sheets to look for value
for (var sheetIndex = 0; sheetIndex < sheets.length; sheetIndex++) {


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

var resultArr=[];
var xTitle = 'Part Numbers'; // XAxis Title
var yTitle = 'Quantity'; // YAxis Title
var column = sheet.getRange("A1:A22");
column.setNumberFormat("@");

//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 = "Alert " + " " + activeSpreadSheet.getName() 
           + " " + " >6MO";

            //Creates a body through the obtained values

            body += "<br>" + "<b>"+ sheet.getName() + "</b>" + "<br>"; 
           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>"; // Modified
              }

      }
}

     //send email
    MailApp.sendEmail({to:"[email protected]",subject:subject, 
    htmlBody:body });
}

}

Tanaike Tanaike · Accepted Answer · 2019-06-24T03:29:44

You want to send sheets in multiple Spreadsheets as one email by modifying your script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

In order to send multiple Spreadsheet as an email using your script, MailApp.sendEmail() is required to be moved to the outside of the while loop.
And also, body is required to be moved.
From your script, subject has the Spreadsheet name. For this, subject is moved to the outside of the while loop.

Modified script:

function checkSales(){
  var file, files = DriveApp.getFolderById("1QoHyZrhSwqNYaOfgWu7X8WnY-wj9KMRU").getFilesByType(MimeType.GOOGLE_SHEETS)
  var body = ''; // Added
  var subject = []; // Added
  while (files.hasNext()) {
    file = files.next();
    var activeSpreadSheet = SpreadsheetApp.open(file);
    var sheets = activeSpreadSheet.getSheets();
    for (var sheetIndex = 0; sheetIndex < sheets.length; sheetIndex++) {
      var sheet = sheets[sheetIndex];
      var data = sheet.getDataRange().getValues();
      var resultArr = [];
      var xTitle = 'Part Numbers'; // XAxis Title
      var yTitle = 'Quantity'; // YAxis Title
      var column = sheet.getRange("A1:A22");
      column.setNumberFormat("@");
      for (var i=1;i<data.length;i++) {
        for (var j=11;j<19;j++) {
          var cellVal = data[i][j];
          if (cellVal > 0) {
            resultArr.push([data[i][0],data[0][j],cellVal]);
          }
        }
      }
      if (resultArr.length>0) {
        var spreadsheetName = activeSpreadSheet.getName(); // Added
        subject.push(spreadsheetName); // Modified
        body += "<br>" + "<b>"+ sheet.getName() + "(" + spreadsheetName + ")" + "</b>" + "<br>"; 
        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>";
        }
      }
    }
  }
  if (subject.length > 0) {
    var s = "Alert " + " " + subject.join(",") + " " + " >6MO"; // Added
    MailApp.sendEmail({to: "[email protected]", subject: s, htmlBody: body}); // Added
  }
}

Note:

About the mail subject and the title of each sheet, I prepare them for your situation. If you want to change them, please modify the above script.

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

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

1 Answers

Modification points:

Modified script:

Note: