Exporting multiple sheets to multiple PDF's and adding in Sendmail as attachment with HTML body

Question

i've looked and tried many solutions but for some reason I can't get it to work.

My goal:

Automaticly sending e-mails on time trigger (works)
Based on cell value (works)
With HTML mail format (google form) (works)
With 2 or more attachments (doesn't work)

How can I change the code so it'll include 2 pdf files? It can be one of the two options: 1. Grab pdf grom google drive folder by name, where I got a working function to export 2 or more sheets to pdf - using the same name (and removing older version). 2. Convert 2 or more sheets to corresponding PDF files and add them as attachment.

I'm rather new, so still trying to figure my way around.

Tried combinding Several solutions found here but I either keep getting 'file iteration' error.

When using variant of GetFilesByName, if(file.HasNext(), getAs('application/PDF'), I get no error but no mail is sent either.

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getRange(1,5).getValue(); // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, 15, 3); // this function is: Row, Column, Number of Rows, Number of Columns
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
 // var bijlage1 = DriveApp.getFilesByName('EDC-A.pdf'); // definiëring van file 2
 // var bijlage2 = DriveApp.getFilesByName('EDC-B.pdf'); // definiëring van file 1
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var body = '';
    var html =  HtmlService.createTemplateFromFile('body').evaluate().getContent(); // legt link met Body html file
    var options = {
      htmlBody: html 
    };
    var aantaluzk = row[2]; // Third column
    if (aantaluzk != 0) { // Variable
      var subject = 'Uitzendkrachten te evalueren';
      MailApp.sendEmail(emailAddress,subject,body,options);                
      SpreadsheetApp.flush(); // geen idee wat dit doet?
    }
  }
}

EDIT: as by request, Code containing the script for export (was in different script file) µ Based on comments so far, I've managed to make it work through: - Combine the pdf export script with the Sendmail function and defining them as variables. Don't understand fully how it works with blob, but it does. - Repeat the export script for each sheet, changing the required parameters.

Resulting in a script that works but most likely could be made much simpler?

Final result

// Test en goedgekeurd 19/6
function MailExport() {
  var sheetName = "EDCA";
  var folderID = "1gNoRIktbqYjIzE8txUezW5wt_jliIWYJ"; // Folder id to save in a folder.
  var pdfName = "EDC-A"; // "+Date()" toevoegen zonder "" indien timestamp aan file toegevoegd;

  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);

  // Vernietig de vorige versie
  DriveApp.getFilesByName('EDC-A').next().setTrashed(true);  

  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  var destSheet = destSpreadsheet.getSheets()[0];
  //repace cell values with text (to avoid broken references) 
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

  // Afdeling 2

  var sheetName = "EDCB";
  var folderID = "1gNoRIktbqYjIzE8txUezW5wt_jliIWYJ"; // Folder id to save in a folder.
  var pdfName = "EDC-B"; // "+Date()" toevoegen zonder "" indien timestamp aan file toegevoegd;

  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);

  // Vernietig de vorige versie
  DriveApp.getFilesByName('EDC-B').next().setTrashed(true);  

  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  var destSheet = destSpreadsheet.getSheets()[0];
  //repace cell values with text (to avoid broken references) 
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  //save to pdf
  var theBlob2 = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile2 = folder.createFile(theBlob2);

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

  // Onderdeel voor mail

  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getRange(1,5).getValue(); // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, 15, 3); // this function is: Row, Column, Number of Rows, Number of Columns
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var body = '';
    var html =  HtmlService.createTemplateFromFile('body').evaluate().getContent(); // legt link met Body html file
    var options = {
      attachments: [theBlob,theBlob2],
      htmlBody: html
    };
    var aantaluzk = row[2]; // Third column
    if (aantaluzk != 0) { // Variable
      var subject = 'Uitzendkrachten te evalueren';
      MailApp.sendEmail(emailAddress,subject,body,options);                
      SpreadsheetApp.flush(); // geen idee wat dit doet?
    }
  }
}

Where is the part of your script that is exporting the sheet to pdf? — ross
Edited post to add this part. It was in a different script file, as I couldn't get them working together. Managed to do so now, but probably not the most efficiently. — Mr.Pinecone

ziganotschka ziganotschka · Accepted Answer · 2019-06-19T15:40:08

In order to include the attachments into your email, you have to insert them in “Options” with the syntax as here:

attachments: [file.getAs(MimeType.PDF), blob]

So you can either include a pdf file directly as an attachment, or convert with

file.getAs(MimeType.PDF)

a file of another format to pdf. I suggest you to implement the desired files into your code with their ID (since the name can be ambiguous) with the function:

DriveApp.getFileById()

To summarize, your code should look like this:

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getRange(1,5).getValue(); // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, 15, 3); // this function is: Row, Column, Number of Rows, Number of Columns
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var bijlage1 = DriveApp.getFileById(IDofPDFfile); // definiëring van file 2
  var bijlage2 = DriveApp.getFileById(IDofNonPDFFile'); // definiëring van file 1
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var body = '';
        var options = {
       name: 'this is optional',
   //in this example bijlage1 is a pdf already, while bijlage2 has to be converted to a pdf first:
       attachments: [bijlage1, bijlage2.getAs(MimeType.PDF)]
        }

    var aantaluzk = row[2]; 
// Third column
    if (aantaluzk != 0) { // Variable
      var subject = 'Uitzendkrachten te evalueren';
      MailApp.sendEmail(emailAddress,subject,body,options);                
      SpreadsheetApp.flush(); // geen idee wat dit doet?
    }
  }
}

Exporting multiple sheets to multiple PDF's and adding in Sendmail as attachment with HTML body

1 Answers