Search code examples
pdfgoogle-apps-scriptemail-attachments

Google App Script Email as PDF from Spreadsheet

My code below for generate PDF attachment is working fine but they send all data in the spreadsheet.

Anyone can help to send only the specify spreadsheet like my code only sheet"Email" in stead of All sheets ? Thank you

function SendEmail() {

  try {
    var ss  = SpreadsheetApp.getActive();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=pdf";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email')
    var subjectrange = sheet.getRange("A20");
    var subjectdata = subjectrange.getValues();
    var emailranges=sheet.getRange('E14');
    var emaildata=emailranges.getValue();
    var username=ss.getSheetId();
    blob.setName(ss.getName()+ ".pdf");
    var confirm = Browser.msgBox('Send Email Confirmation','Are you sure you want to send this mail for booking request ?', Browser.Buttons.OK_CANCEL);
    if(confirm=='ok') {
      MailApp.sendEmail(emaildata, subjectdata, " Attachment file is:  \n"  +subjectdata+ "- \n Kindly reply your booking to this email . \n Thank you - ADS Co., Ltd", {attachments: [blob]}) };} catch (f) {Logger.log(f.toString());
    }
}
Solution

  • Answer

    If you don't want to export some sheets you can hide them. Furthermore, you can export a Spreadsheet with the method getBlob. Once you have made the export, you can undo the hide.

    Small code assuming two sheets

    var sheet = ss.getSheetByName('Unwanted Sheet')
sheet.hideSheet()
var blob = ss.getBlob()
blob.setName(ss.getName())
sheet.showSheet()

    Full code working with many sheets

    function exportSheet(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet()
    } 
  }
  var blob = ss.getBlob()
  blob.setName(ss.getName())
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet()
  }
}

    Some tips

    • You don't have to add .pdf to the blob name, it already understand that it is a pdf file and the extension will appear automatically
    • You can use GmailApp service instead of MailApp since it is more versatile and has more functionalities. The main reason to use MailApp is using that it doesn’t require the developer to be a Gmail user.

    Reference