Convert Google Sheets into Individual PDFs and Email Them

Question

I have a spreadsheet that always has sheet named "Timesheet" and the person filling it out can create as many additional notes sheets as they need.

I need to have all sheets besides the "Timesheet" sheet converted to individual PDFs and emailed to an address.

I am having trouble creating a script that doesn't roll them into a single PDF. Because of this it also includes the "Timesheet" Sheet in the PDF.

I need each of the notes sheets to be converted to a single PDF, then all PDFs emailed as separate attachments.The person filling it out can also rename the notes to whatever they want so I cannot get the sheet by name.

I have some code that combines all sheets and renames it that I use for a different purpose, I will include it below if it is of any help.

Thank you in advance for any help

function emailGoogleSpreadsheetAsPDF() {

  // Send the PDF of the spreadsheet to this email address
  var email = "email@gmail.com"; 

  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Custom
  var name = ss.getRange("Timesheet!J6:K6").getValue();
  var agency = ss.getRange("Timesheet!B4:C4").getValue();

  // Date
  var today = new Date();
  var dd = today.getDate();
  var mm = today.getMonth() + 1; //January is 0!
  var yyyy = today.getFullYear();

  if (dd < 10) {
    dd = '0' + dd;
  }

  if (mm < 10) {
    mm = '0' + mm;
  }

  today = mm + '/' + dd + '/' + yyyy;

  // Subject of email message
  var subject = name + " has Submitted Their Timesheet and Notes"; 

  // Email Body can  be HTML too 
  var body = "This was submitted on " + today;

  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");

  blob.setName(name + "_" + agency + "_" + today + "_" + "timesheet_notes.pdf");

  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blob]     
    });
}

Answer 1

Emailing Sheets of a Spreadsheet as Separate PDFs

I could not do this by just creating the blobs and putting them in an array. So I created separate PDF files for each page and then trashed them at the end. I also added and exclusion array so that you could exlude certain files from the entire process and only send the sheets that you wish.

The current version includes dialog prompts that follow the progress of the program as it creates files and sends the email. So it's not exactly like your program.

Also these lines is your program are difficult for me to understand because you have a 2 cell array but you using getValue() instead of getValues();

 var name = ss.getRange("Timesheet!J6:K6").getValue();
 var agency = ss.getRange("Timesheet!B4:C4").getValue();

Here's my code:

function savePDFFiles1() {
  var ss=SpreadsheetApp.getActive();
  var exclA=['Summary','Images','Globals','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21'];
  var fA=[];
  var html="";
  var shts=ss.getSheets();
  var pdfFldr=DriveApp.getFolderById('FolderId');//folder where I stored the files temporarily
  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var name=sh.getName();
    if(exclA.indexOf(name)==-1) {
      sh.showSheet();
      for(var j=0;j<shts.length;j++) {
        if(shts[j].getName()!=name) {
          shts[j].hideSheet();
        }
      }
      SpreadsheetApp.flush();//I dont know if this is required
      var file=pdfFldr.createFile(ss.getBlob().getAs('application/pdf').setName(Utilities.formatString('%s_%s.pdf',ss.getName(),name)));
      html+=Utilities.formatString('<br />File: %s Created',file.getName());
      var userInterface=HtmlService.createHtmlOutput(html);
      SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created')                                          
      fA.push(file);
    }
  }
  GmailApp.sendEmail('recipient email', 'Plot Reports', 'Plot Reports Attached', {attachments:fA})
  html+='<br />Email Sent';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created');                                          
  for(var i=0;i<fA.length;i++ ) {
    fA[i].setTrashed(true);
  }
  html+='<br />Files Trashed and Process Complete';
  html+='<script>window.onload=function(){google.script.host.close();}</script>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created');                                          
}

In my example, I was using a sheet that had some sheets named with numbers and I typically use certain sheets as hash tables so I typically keep them hidden all of the time.

I'll go back and take a look at updating your script now.

Okay so this is what I think your script would look like:

function emailGoogleSpreadsheetAsPDF() {
  var email="email@gmail.com"; 
  var ss=SpreadsheetApp.getActive();
  var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
  var agency=ss.getRange("Timesheet!B4").getValue();//same here
  var fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
  var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name); 
  var body=Utilities.formatString('This was submitted on %s',today);
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var name=sh.getName();
    sh.showSheet();
    for(var j=0;j<shts.length;j++) {
      if(shts[j].getName()!=name) {
        shts[j].hideSheet();
      }
    }
    SpreadsheetApp.flush();//this may not be necessary...not sure
    var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
    fA.push(file);
  }
  GmailApp.sendEmail(email,subject,body, {attachments:fA});
  for(var i=0;i<fA.length;i++) {
    fA[i].setTrashed(true); 
  }
}

Note: I have not tested this last one so a little debugging may be required but it's basically the same idea as the other example. Which was tested.

There's another way to do this using UrlFetchApp that's discussed here Personally, I'd rather just create the files and trash them at the end.

With Requested Changes:

function emailGoogleSpreadsheetAsPDF() {
  var email="email@gmail.com";
  var exclA=['TimeSheet'];//and others
  var ss=SpreadsheetApp.getActive();
  var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
  var agency=ss.getRange("Timesheet!B4").getValue();//same here
  var fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
  var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name); 
  var body=Utilities.formatString('This was submitted on %s',today);
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var name=sh.getName();
    if(exclA.indexOf(name)==-1) {
      sh.showSheet();
      for(var j=0;j<shts.length;j++) {
        if(shts[j].getName()!=name) {
          shts[j].hideSheet();
        }
      }
      SpreadsheetApp.flush();//this may not be necessary...not sure
      var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
      fA.push(file);
    }
  }
  GmailApp.sendEmail(email,subject,body, {attachments:fA});
  for(var i=0;i<fA.length;i++) {
    fA[i].setTrashed(true); 
  }
  for(var i=0;i<shts.length;i++) {
    if(exclA.indexOf(shts[i].getName())==-1) {
      shts[i].showSheet();
    }
  }
}

Adding a PDF of the Entire Spreadsheet(except for excluded hidden sheets):

function emailGoogleSpreadsheetAsPDF() {
  var email="email@gmail.com";
  var exclA=['TimeSheet'];//and others
  var ss=SpreadsheetApp.getActive();
  var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
  var agency=ss.getRange("Timesheet!B4").getValue();//same here
  var fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
  var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name); 
  var body=Utilities.formatString('This was submitted on %s',today);
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var name=sh.getName();
    if(exclA.indexOf(name)==-1) {
      sh.showSheet();
      for(var j=0;j<shts.length;j++) {
        if(shts[j].getName()!=name) {
          shts[j].hideSheet();
        }
      }
      SpreadsheetApp.flush();//this may not be necessary...not sure
      var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
      fA.push(file);
    }
  }
  for(var i=0;i<shts.length;i++) {
    if(exclA.indexOf(shts[i].getName())==-1) {
      shts[i].showSheet();
    }
  }
  SpreadsheetApp.flush();//this may not be necessary...not sure
  var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s.pdf',ss.getName()));
  fA.push(file)
  GmailApp.sendEmail(email,subject,body, {attachments:fA});
  for(var i=0;i<fA.length;i++) {
    fA[i].setTrashed(true); 
  }
}