Convert Google Sheets Into Individual Pdfs And Email Them

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 beside

Solution 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 temporarilyfor(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 requiredvar 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:

functionemailGoogleSpreadsheetAsPDF() {
  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 herevar fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(newDate(),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 surevar 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:

functionemailGoogleSpreadsheetAsPDF() {
  var email="email@gmail.com";
  var exclA=['TimeSheet'];//and othersvar 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 herevar fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(newDate(),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 surevar 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):

functionemailGoogleSpreadsheetAsPDF() {
  var email="email@gmail.com";
  var exclA=['TimeSheet'];//and othersvar 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 herevar fldr=DriveApp.getFolderById('folderId');
  var fA=[];
  var today=Utilities.formatDate(newDate(),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 surevar 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 surevar 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); 
  }
}