Create a PDF of the unhidden sheets Google Script

Question

I have created a simple emailing script for a google sheet. I need it to attach the PDF of the active file, but only 3 sheets (the 3 unhidden sheets). I have tried several things but nothing seems to work, I am honestly not an expert in Google script so I may be missing something obvious. Following is my code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Email');
  var startRow = 2; // First row of data to process
  var numRows = 1; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var subject = row[2];
    var file= SpreadsheetApp.getActive();
    MailApp.sendEmail(emailAddress, subject, message,{cc:"[email protected]",attachments:[file.getAs(MimeType.PDF)]});
  }
}

What it does is attach the whole spreadsheet as a PDF, which doesn't help me because there are a lot of hidden sheets that I don't need in the PDF. Please help me!! Thanks!

Cooper Cooper · Accepted Answer · 2019-10-28T20:40:55

The function will send emails to recipient in the Emails Sheet which also contains the subject and the message. It also creates a PDF of the current Spreadsheet with the sheets that are in the incl array. After the email is sent it returns the sheets back to their original visibility state.

function emailAsPDF() {
  var ss=SpreadsheetApp.getActive();
  var incl=['Sheet2','Emails'];
  var pdfFolderId="Folder Id";
  var folder=DriveApp.getFolderById(pdfFolderId);
  var sObj={shA:[]};
  var shts=ss.getSheets();
  shts.forEach(function(sh,i){
    sObj.shA.push(sh.getName());
    if(sh.isSheetHidden()) {
      sObj[sh.getName()]='hide';
    }else{
      sObj[sh.getName()]='show';
    }
    if(incl.indexOf(sh.getName())!=-1) {
      sh.showSheet();
    }else{
      sh.hideSheet();
    }
  });
  var file=folder.createFile(ss.getBlob()).setName(ss.getName()).getAs(MimeType.PDF);
  var sh=ss.getSheetByName('Emails');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var hObj={};
  hA.forEach(function(e,i){if(e){hObj[e]=i;}})
  var vA=rg.getValues();
  vA.forEach(function(r,i){
    var recipient=r[hObj.recipient];
    var subject=r[hObj.subject];
    var message=r[hObj.message];
    GmailApp.sendEmail(recipient, subject, message, {attachments:[file]})
  })
  sObj.shA.forEach(function(name,i){
    if(sObj[name]=='hide') {
      ss.getSheetByName(name).hideSheet();
    }else{
      ss.getSheetByName(name).showSheet();
    }
  });
}

This is an image of my Emails sheet:

I know...It's pretty trivial but you can improve it as you wish.

Create a PDF of the unhidden sheets Google Script

1 Answers